> On Mon, Dec 06, 2021 at 09:56:40AM -0500, Tom Lane wrote:
> 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.
Looking through the original thread [1] it seems "nested UNIONs" means
constructions like "foo UNION bar UNION baz", which is indeed handled in
parse_cte. The existing restrictions probably have to be extended to
cover cases like here as well.
[1]: https://www.postgresql.org/message-id/flat/db80ceee-6f97-9b4a-8ee8-3ba0c58e5be2%402ndquadrant.com