Re: BUG #16900: SELECT MAX subquery not allowed in WHERE clause when using WITH RECURSIVE

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> CREATE TABLE IF NOT EXISTS
> Tasks (task_id int, subtasks_count int);
> ...

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

max_subtask_count is not a field of Tasks; perhaps you meant to
write MAX(subtasks_count)?

As the query is written, the aggregate is over a field of
possible_tasks_subtasks, making it illegal in WHERE, just
as the error says. (From the point of view of the "SELECT FROM Tasks"
subquery, it's a constant outer reference, not an aggregate of
that subquery. This is per SQL spec.)

regards, tom lane