Re: Remove restrictions in recursive query

On Thu, Mar 27, 2025 at 12:02 PM Renan Alves Fonseca
<renanfonseca(at)gmail(dot)com> wrote:
> WITH RECURSIVE t1 AS ( SELECT 1 UNION SELECT generate_series(2,3) FROM t1 ORDER BY 1 DESC) SELECT * FROM t1 ;
>
> The parser attaches the "order by" clause to the "union" operator, and then we error out with the following message: "ORDER BY in a recursive query is not implemented"
>
> The comment in the code (parser_cte.c:900) says "Disallow ORDER BY and similar decoration atop the UNION". Then, if we wrap the recursive clause around parentheses:
>
> WITH RECURSIVE t1 AS ( SELECT 1 UNION (SELECT generate_series(2,3) FROM t1 ORDER BY 1 DESC)) SELECT * FROM t1 ;
>
> It works as expected. So, do we support the ORDER BY in a recursive query or not?

A recursive CTE effectively takes two queries that will be run as
arguments. For some reason, instead of choosing syntax like WITH
RECURSIVE t1 AS BASE_CASE (initial_query) RECURSIVE_CASE
(iterated_query), somebody chose WITH RECURSIVE t1 AS (initial_query
UNION iterated_query) which really doesn't make it very clear that we
need to be able to break it apart into two separate queries, one of
which will be run once and one of which will be iterated.

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.

--
Robert Haas
EDB: http://www.enterprisedb.com