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

On Fri, Feb 26, 2021 at 4:40 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

Tom explains the reasons for the fail.
We can't be sure of what is needed but perhaps it's this:

WHERE
subtask_id < max_subtask_count

which gives you the wanted output.

Best regards
Pantelis

>
>