Re: Remove restrictions in recursive query

On Thu, Mar 27, 2025 at 11:03 AM Renan Alves Fonseca <renanfonseca(at)gmail(dot)com>
wrote:

> On Thu, Mar 27, 2025 at 5:38 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > It's not a problem if UNION ALL is used within the initial_query and
> > it's not a problem if UNION ALL is used within the iterated_query. But
> > you can't apply ORDER BY to the result of the UNION, because the UNION
> > is kind of fake -- we're not running the UNION as a single query,
> > we're running the two halves separately, the first once and the second
> > as many times as needed.
>
> I understand that we can only apply ORDER BY separately in the
> initial/iterated query. What disturbs me here is that the UNION
> operator has associativity precedence over the ORDER BY only when
> inside a recursive CTE. Consider the following query:
>
> SELECT 1 UNION SELECT 1 GROUP BY 1;
>
> It returns 2 rows. The GROUP BY clause attaches to the second
> selectStmt without the need to add parenthesis. I would expect the
> same syntax inside a recursive CTE.
>

There is distinct behavior between group by and order by here. You seem to
be mixing them up.

From Select:

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO
KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. (ORDER BY and
LIMIT can be attached to a subexpression if it is enclosed in parentheses.
Without parentheses, these clauses will be taken to apply to the result of
the UNION, not to its right-hand input expression.)

This is the exact same parsing precedence order by is being given in the
recursive CTE query situation presented earlier.

David J.