pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: dan.farlow@gmail.com
Subject: BUG #16900: SELECT MAX subquery not allowed in WHERE clause when using WITH RECURSIVE
Date: Fri, 26 Feb 2021 11:30:57 +0000
Message-ID: <16900-5b4d30739ea178c7@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      16900
Logged by:          Daniel Farlow
Email address:      dan.farlow@gmail.com
PostgreSQL version: 13.2
Operating system:   macOS Mojave 10.14.6
Description:        

Exact sequence to reproduce problem:

CREATE TABLE IF NOT EXISTS
  Tasks (task_id int, subtasks_count int);

TRUNCATE TABLE Tasks;

INSERT INTO
  Tasks (task_id, subtasks_count)
VALUES
  ('1', '3'),
  ('2', '2'),
  ('3', '4');


CREATE TABLE IF NOT EXISTS
  Executed (task_id int, subtask_id int);

TRUNCATE TABLE Executed;

INSERT INTO
  Executed (task_id, subtask_id)
VALUES
  ('1', '2'),
  ('3', '1'),
  ('3', '2'),
  ('3', '3'),
  ('3', '4');

WITH RECURSIVE possible_tasks_subtasks AS (
  SELECT
    task_id, subtasks_count as max_subtask_count, 1 AS subtask_id
  FROM
    Tasks
  UNION ALL
  SELECT
    task_id, max_subtask_count, subtask_id + 1
  FROM
    possible_tasks_subtasks
  -- using SELECT MAX in the WHERE clause below is what causes the error
  WHERE
    subtask_id < (SELECT MAX(max_subtask_count) FROM Tasks))
SELECT
  P.task_id, P.subtask_id
FROM
  possible_tasks_subtasks P
LEFT JOIN
  Executed E ON P.task_id = E.task_id AND P.subtask_id = E.subtask_id
WHERE
  E.task_id IS NULL OR E.subtask_id IS NULL;

Output received: ERROR: aggregate functions are not allowed in WHERE

Output expected (result set in any order):

+---------+---------------+
| task_id | subtask_id |
+---------+---------------+
| 1          | 1                |
| 1          | 3                |
| 2          | 1                |
| 2          | 2                |
+---------+---------------+

Platform information: PostgreSQL 13.2 on x86_64-apple-darwin18.7.0, compiled
by Apple clang version 11.0.0 (clang-1100.0.33.17), 64-bit

Comments: As highlighted in the docs on aggregate functions (ref:
https://www.postgresql.org/docs/current/tutorial-agg.html), the aggregate
max cannot be used directly in the WHERE clause but a query can be restated
so as to get the desired result using a subquery; hence, something like the
example in the docs

SELECT city FROM weather WHERE temp_lo = max(temp_lo);

can be transformed to 

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

I tried this same approach in my WITH query, as stated above, but received
"ERROR: aggregate functions are not allowed in WHERE". I looked at the docs
on WITH queries (ref:
https://www.postgresql.org/docs/current/queries-with.html) but did not see
any mention of aggregate function restriction.

Two other things possibly worth mentioning that may be relevant:
1. The problematic query executes as expected when using MySQL version
8.0.23. (This seems to be relevant since a rival database has implemented
this ability.)
2. I can modify my PostgreSQL query to work as expected by modifying

WHERE
  subtask_id < (SELECT MAX(max_subtask_count) FROM Tasks)

to be 

WHERE
  subtask_id < (SELECT max_subtask_count FROM Tasks ORDER BY
max_subtask_count DESC LIMIT 1)



view thread (4+ messages)  latest in thread

Message-ID: <16900-5b4d30739ea178c7@postgresql.org>
Permalink:  ../16900-5b4d30739ea178c7@postgresql.org/
Also on:    postgresql.org/message-id/16900-5b4d30739ea178c7@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, dan.farlow@gmail.com
  Subject: Re: BUG #16900: SELECT MAX subquery not allowed in WHERE clause when using WITH RECURSIVE
  In-Reply-To: <16900-5b4d30739ea178c7@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox