On Thu, Mar 27, 2025 at 12:02 PM Renan Alves Fonseca <renanfons...@gmail.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
