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

On Fri, Feb 26, 2021 at 9:20 PM Pantelis Theodosiou <ypercube(at)gmail(dot)com>
wrote:

>
>
> 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:
>> ...
>> > 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.
>
> And if I understand correctly the desired query, it can be expressed
without recursive CTEs, using generate_series():

SELECT
T.task_id, G.subtask_id
FROM
Tasks AS T
CROSS JOIN LATERAL
generate_series(1, subtasks_count) AS G (subtask_id)
LEFT JOIN
Executed E
ON T.task_id = E.task_id AND G.subtask_id = E.subtask_id
WHERE
E.task_id IS NULL ;

> Best regards
> Pantelis
>
>>
>>
>