BUG #17700: An assert failed in prepjointree.c

The following bug has been logged on the website:

Bug reference: 17700
Logged by: xin wen
Email address: xinwen(at)stu(dot)scu(dot)edu(dot)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(at)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(at)entry=0x563be1713818
"!find_dependent_phvs(root, varno)",
errorType=errorType(at)entry=0x563be1600498 "FailedAssertion",
fileName=fileName(at)entry=0x563be1713580
"/home/postgres/postgresql-15.1/bld_debugging/../src/backend/optimizer/prep/prepjointree.c",
lineNumber=lineNumber(at)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(at)entry=0x563be32a6cb8) at
/home/postgres/postgresql-15.1/bld_debugging/../src/backend/optimizer/prep/prepjointree.c:3095
#6 0x0000563be13a4fca in subquery_planner (glob=glob(at)entry=0x563be32a6000,
parse=parse(at)entry=0x563be31dee50, parent_root=parent_root(at)entry=0x0,
hasRecursion=hasRecursion(at)entry=false,
tuple_fraction=tuple_fraction(at)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(at)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(at)entry=2048,
boundParams=boundParams(at)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.