Date: Wed, 8 Dec 2021 10:41:02 +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: <20211208094102.qrxyu2u2426e4wy5@localhost>
References: <17318-2eb65a3a611d2368@postgresql.org>
 <3048015.1638802600@sss.pgh.pa.us>
 <20211207125925.jps4l6tr7htd6h3z@localhost>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="6q7mnkietnguam5x"
Content-Disposition: inline
In-Reply-To: <20211207125925.jps4l6tr7htd6h3z@localhost>
Archived-At: 
 <https://www.postgresql.org/message-id/20211208094102.qrxyu2u2426e4wy5%40localhost>
Precedence: bulk


--6q7mnkietnguam5x
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

> On Tue, Dec 07, 2021 at 01:59:25PM +0100, Dmitry Dolgov wrote:
> > 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.

Probably the most straightforward way to extend validation for such
queries would be to add some sort of flag for catching nested UNIONs in
CteState and verify presence of UNION and CYCLE in checkWellFormedSelectStmt.
Not sure if it's a right way to fix the problem, but it passes the tests.

One note, in the thread [1] Kyotaro Horiguchi is investigating similar
issue and has proposed another patch to fix it, but it doesn't catch the
problematic query here.

[1]: https://www.postgresql.org/message-id/20211207.172546.1329872704555357126.horikyota.ntt%40gmail.com

--6q7mnkietnguam5x
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="0001-Prevent-nested-UNIONs-in-CTE-with-CYCLE-clause.patch"