Re: BUG #17320: A SEGV in optimizer

At Mon, 06 Dec 2021 06:42:57 +0000, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote in
> The following bug has been logged on the website:
>
> Bug reference: 17320
> Logged by: Zhiyong Wu
> Email address: 253540651(at)qq(dot)com
> PostgreSQL version: 14.1
> Operating system: Linux version 5.13.0-1-MANJARO (builduser(at)LEGION)
> Description:
>
> PoC:
> WITH RECURSIVE x ( x ) AS ( SELECT 4 UNION ( WITH x AS ( SELECT 5 UNION (
> WITH TIMESTAMP AS ( SELECT 2 UNION ( WITH x ( x ) AS ( SELECT 1 UNION ( WITH
> x AS ( SELECT 6 FROM ( VALUES ( ROW ( 1 , 2 ) ) , ( ROW ( 1 , 4 ) ) ) x ( x
> ) UNION ( WITH x AS ( SELECT 7 ) SELECT * FROM x ) ) SELECT * FROM x UNION
> SELECT * FROM x ) ) SELECT * FROM x ) ) SELECT * FROM ( SELECT * FROM x
> WHERE x = x ) x ) ) SELECT * FROM x ) ) SEARCH BREADTH FIRST BY x SET NCHAR
> SELECT * FROM x WHERE x BETWEEN 0 AND 1000000 ;
> COPY x FROM STDIN WHERE x IN ( x ( 1 , 5 ) ) ;
> CREATE OR REPLACE TEMP VIEW x AS SELECT x , x ( x ) OVER ( ORDER BY x ROWS
> BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE NO OTHERS ) AS x FROM x ( 1 , 10
> ) x ;
> EXECUTE x ( '-9223372036854775800' ) ;
..
> #0 0xda215c in bms_add_members
> /root/postgres/bld/../src/backend/nodes/bitmapset.c:806:9
> #1 0xf25518 in add_vars_to_targetlist
> /root/postgres/bld/../src/backend/optimizer/plan/initsplan.c:259:30
> #2 0xf250c7 in build_base_rel_tlists

I had the following assertion failure from the original query by
master head.

> TRAP: FailedAssertion("attno >= rel->min_attr && attno <= rel->max_attr", File: "initsplan.c", Line: 249, PID: 25862)

max_attr is 1 and attno is 2 here. I could reduce the query like this.

WITH RECURSIVE x ( x ) AS
(SELECT 1
UNION
(WITH x AS
(WITH TIMESTAMP AS (SELECT 2)
SELECT * FROM x)
SELECT * FROM x)
) SEARCH BREADTH FIRST BY x SET NCHAR
SELECT * FROM x;

If I tried to execute the following query, I got the follwoing
error. This looks like rooted from the same mistake.

WITH RECURSIVE x ( x ) AS
(SELECT 1
UNION
(WITH x AS (SELECT * FROM x)
SELECT * FROM x)
) SEARCH BREADTH FIRST BY x SET NCHAR
SELECT * FROM x;

> ERROR: could not find attribute 2 in subquery targetlist

The outmost query tries to access nchar that the second level query
doesn't have. The implicit column is not surfaced through the
upper-level CTEs.

By the way, if I executed the following query, I get the following
another assertion failure.

WITH RECURSIVE x ( x ) AS
(SELECT 1
UNION
(WITH y AS (SELECT * FROM x)
SELECT * FROM y)
) SEARCH BREADTH FIRST BY x SET NCHAR
SELECT * FROM x;

> TRAP: FailedAssertion("cte_rtindex > 0", File: "rewriteSearchCycle.c", Line: 398, PID: 31288)

This looks a bit different but rooted from the same issue.

The most simple way to avoid such assertion failures or internal erors
is to reject indirecto references to CTEs that have SEARCH or CYCLE
clause. I'm not sure it's worth the trouble somehow allowing such
references.

I'm not confident that it is the right fix, but the attached catches
the original problem query and the above reduced queries as syntax
error.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center