PG Bug reporting form <noreply@postgresql.org> writes:
> WITH RECURSIVE x ( x ) AS ( SELECT 1 UNION ALL SELECT x FROM LATERAL ( (
> SELECT * FROM ( ( SELECT 4 AS x ) UNION ALL ( SELECT 5 AS x ) ) AS x WHERE x
> BETWEEN 1 AND 2 AND x < ( SELECT 3 GROUP BY DISTINCT ROLLUP ( x , x ) ,
> ROLLUP ( x , x ) ) ) UNION ALL ( SELECT ( SELECT x LIMIT 1 ) FROM x OFFSET 0
> LIMIT 5 ) ) AS x GROUP BY ROLLUP ( ( x , x , x ) , ( ( SELECT TRIM (
> TRAILING ' ' FROM SUBSTRING ( VERSION ( ) FROM '^[^0-9]*' ) ) WHERE ( x IS
> NOT NULL ) ) , x ) ) ) CYCLE x SET BOOLEAN USING VALUES SELECT FROM x GROUP
> BY DISTINCT CUBE ( x , x , x ) ;
I simplified this to
WITH RECURSIVE x ( x ) AS
( SELECT 1
UNION ALL
SELECT x FROM
(
SELECT 4 AS x
UNION ALL
SELECT x FROM x
) AS x
)
CYCLE x SET b USING v
SELECT * FROM x
;
and now I'm not sure whether to consider this an optimizer bug
or failure to detect an unsupported case. Our SELECT ref page
says
Both the SEARCH and the CYCLE clause are only valid for recursive WITH
queries. The with_query must be a UNION (or UNION ALL) of two SELECT
(or equivalent) commands (no nested UNIONs).
This WITH query sure looks like nested UNIONs to me, so either
that restriction is stated incorrectly, or it's being enforced
inadequately. If the former, we have an optimizer problem.
regards, tom lane