Date: Tue, 7 Dec 2021 13:59:25 +0100
From: Dmitry Dolgov <9erthalion6@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: 253540651@qq.com, pgsql-bugs@lists.postgresql.org,
	Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Subject: Re: BUG #17318: ERROR: AddressSanitizer: SEGV on unknown address in
 optimizer
Message-ID: <20211207125925.jps4l6tr7htd6h3z@localhost>
References: <17318-2eb65a3a611d2368@postgresql.org>
 <3048015.1638802600@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3048015.1638802600@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/20211207125925.jps4l6tr7htd6h3z%40localhost>
Precedence: bulk

> 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