BUG #17700: An assert failed in prepjointree.c - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17700
Logged by:          xin wen
Email address:      xinwen@stu.scu.edu.cn
PostgreSQL version: 15.1
Operating system:   Ubuntu 20.04
Description:

When executing the following query:

WITH table3 ( column34 ) AS NOT MATERIALIZED ( SELECT 1 ) SELECT 1 FROM (
SELECT 1 FROM table3 WHERE column34 = ANY ( SELECT alias4 . column34 FROM
table3 FULL JOIN table3 AS alias4 ON TRUE )) AS alias5 ;

I get a failed assertion with the following stacktrace:

Core was generated by `postgres: postgres postgres [local] SELECT
'.
Program terminated with signal SIGABRT, Aborted.
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007ff7f1a36859 in __GI_abort () at abort.c:79
#2  0x0000563be15a2a88 in ExceptionalCondition
(conditionName=conditionName@entry=0x563be1713818
"!find_dependent_phvs(root, varno)",
errorType=errorType@entry=0x563be1600498 "FailedAssertion", 
    fileName=fileName@entry=0x563be1713580
"/home/postgres/postgresql-15.1/bld_debugging/../src/backend/optimizer/prep/prepjointree.c",
lineNumber=lineNumber@entry=3301)
    at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/utils/error/assert.c:69
#3  0x0000563be13b15cb in remove_useless_results_recurse
(root=0x563be32a6cb8, jtnode=0x563be32a6158) at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/optimizer/prep/prepjointree.c:3301
#4  0x0000563be13b1304 in remove_useless_results_recurse
(root=0x563be32a6cb8, jtnode=0x563be32a6bd0) at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/optimizer/prep/prepjointree.c:3155
#5  0x0000563be13b15f6 in remove_useless_result_rtes
(root=root@entry=0x563be32a6cb8) at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/optimizer/prep/prepjointree.c:3095
#6  0x0000563be13a4fca in subquery_planner (glob=glob@entry=0x563be32a6000,
parse=parse@entry=0x563be31dee50, parent_root=parent_root@entry=0x0,
hasRecursion=hasRecursion@entry=false, 
    tuple_fraction=tuple_fraction@entry=0) at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/optimizer/plan/planner.c:1039
#7  0x0000563be13a52a3 in standard_planner (parse=0x563be31dee50,
query_string=<optimized out>, cursorOptions=2048, boundParams=<optimized
out>)
    at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/optimizer/plan/planner.c:406
#8  0x0000563be1476edc in pg_plan_query (querytree=0x563be31dee50, 
    query_string=0x563be31dd070 "WITH table3 ( column34 ) AS NOT
MATERIALIZED ( SELECT 1 ) SELECT 1 FROM ( SELECT 1 FROM table3 WHERE
column34 = ANY ( SELECT alias4 . column34 FROM table3 FULL JOIN table3 AS
alias4 ON TRUE )) AS alia"..., cursorOptions=2048, boundParams=0x0) at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/tcop/postgres.c:883
#9  0x0000563be1476fd1 in pg_plan_queries (querytrees=0x563be32a6c60, 
    query_string=query_string@entry=0x563be31dd070 "WITH table3 ( column34 )
AS NOT MATERIALIZED ( SELECT 1 ) SELECT 1 FROM ( SELECT 1 FROM table3 WHERE
column34 = ANY ( SELECT alias4 . column34 FROM table3 FULL JOIN table3 AS
alias4 ON TRUE )) AS alia"..., cursorOptions=cursorOptions@entry=2048,
boundParams=boundParams@entry=0x0)
    at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/tcop/postgres.c:975
#10 0x0000563be14773df in exec_simple_query (
    query_string=0x563be31dd070 "WITH table3 ( column34 ) AS NOT
MATERIALIZED ( SELECT 1 ) SELECT 1 FROM ( SELECT 1 FROM table3 WHERE
column34 = ANY ( SELECT alias4 . column34 FROM table3 FULL JOIN table3 AS
alias4 ON TRUE )) AS alia"...) at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/tcop/postgres.c:1169
#11 0x0000563be1478f8c in PostgresMain (dbname=<optimized out>,
username=<optimized out>) at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/tcop/postgres.c:4581
#12 0x0000563be13e5e8a in BackendRun (port=<optimized out>, port=<optimized
out>) at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/postmaster/postmaster.c:4504
#13 BackendStartup (port=<optimized out>) at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/postmaster/postmaster.c:4232
#14 ServerLoop () at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/postmaster/postmaster.c:1806
#15 0x0000563be13e6ffb in PostmasterMain (argc=<optimized out>,
argv=0x563be31d7370) at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/postmaster/postmaster.c:1478
#16 0x0000563be1111630 in main (argc=3, argv=0x563be31d7370) at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/main/main.c:202

I also find this assert failed in 12.13, 13.9 and 14.6 using the same
statement.