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)
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