BUG #17773: Assert triggered on analyzejoins.c

The following bug has been logged on the website:

Bug reference: 17773
Logged by: Robins Tharakan
Email address: tharakan(at)gmail(dot)com
PostgreSQL version: 15.1
Operating system: Ubuntu 20.04
Description:

This assert() is easily reproducible as of 71c37797d7(at)master and surfaced
after the recent 8538519db1.

TRAP: failed Assert("!bms_is_member(innerrelid,
restrictinfo->clause_relids)"), File: "analyzejoins.c", Line: 279, PID:
861786

Backtrace / SQL / backtrace full (excerpt) below.

SQL
===
create table txt();
SELECT
FROM pg_catalog.pg_roles AS ref_0
RIGHT JOIN txt AS ref_1 ON NULL,
LATERAL (SELECT
WHERE ref_0.rolpassword ~>=~ ref_0.rolpassword) AS subq_2;

Checking (71c37797d7~0) - 71c37797d7bd78266146a5829ab62b3687c47295 - Crash
Checking (71c37797d7~1) - 2f6e15ac93c58c1140e4a4affe61e78f7346497a - Crash
Checking (71c37797d7~2) - b2d0e13a0a4c31167d01e9871f907060c80b8fae - Crash
Checking (71c37797d7~3) - 9f452feeeb830534dc2ce743a2a14b109128326d - Crash
Checking (71c37797d7~4) - 8538519db107777a6b06b7277185e6605caf8d4c - Crash
Checking (71c37797d7~5) - 5840c2027264d5dfad743c50874e0ebf8b840f3f -
Success
Checking (71c37797d7~6) - faff8f8e47f18c7d589453e2e0d841d2bd96c1ac -
Success
Checking (71c37797d7~7) - 1b6f632a35f8715f8c64e7930adebc7f1d292074 -
Success

Backtrace
=========
Core was generated by `postgres: 71c37797d7(at)master@sqith: ubuntu t
127.0.0.1(57752) SELECT '.
Program terminated with signal SIGABRT, Aborted.
#0 __GI_raise (sig=sig(at)entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#0 __GI_raise (sig=sig(at)entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1 0x00007f7acd873859 in __GI_abort () at abort.c:79
#2 0x0000560e580aa045 in ExceptionalCondition (conditionName=0x560e5825ef68
"!bms_is_member(innerrelid, restrictinfo->clause_relids)",
fileName=0x560e5825ef3f "analyzejoins.c",
lineNumber=279) at assert.c:66
#3 0x0000560e57d700b3 in join_is_removable (root=0x560e59e8f3e8,
sjinfo=0x560e59e97e80) at analyzejoins.c:279
#4 0x0000560e57d6fb2b in remove_useless_joins (root=0x560e59e8f3e8,
joinlist=0x560e59e97dc0) at analyzejoins.c:78
#5 0x0000560e57d857bf in query_planner (root=0x560e59e8f3e8,
qp_callback=0x560e57d8c055 <standard_qp_callback>, qp_extra=0x7fff9597ef10)
at planmain.c:223
#6 0x0000560e57d8829c in grouping_planner (root=0x560e59e8f3e8,
tuple_fraction=0) at planner.c:1496
#7 0x0000560e57d8794b in subquery_planner (glob=0x560e59e83780,
parse=0x560e59d5e768, parent_root=0x0, hasRecursion=false, tuple_fraction=0)
at planner.c:1065
#8 0x0000560e57d85f03 in standard_planner (parse=0x560e59d5e768,
query_string=0x560e59d5d2e8 "SELECT\nFROM pg_catalog.pg_roles AS ref_0\n
RIGHT JOIN txt AS ref_1 ON NULL,\n LATERAL (SELECT\n", ' ' <repeats
14 times>, "WHERE ref_0.rolpassword ~
>=~ ref_0.rolpassword) AS subq_2;", cursorOptions=2048, boundParams=0x0) at
planner.c:411

Backtrace full excerpt
=================
#2 0x0000560e580aa045 in ExceptionalCondition (conditionName=0x560e5825ef68
"!bms_is_member(innerrelid, restrictinfo->clause_relids)",
fileName=0x560e5825ef3f "analyzejoins.c",
lineNumber=279) at assert.c:66
No locals.
#3 0x0000560e57d700b3 in join_is_removable (root=0x560e59e8f3e8,
sjinfo=0x560e59e97e80) at analyzejoins.c:279
restrictinfo = 0x560e59e980a0
l__state = {l = 0x560e59e985c0, i = 1}
innerrelid = 6
innerrel = 0x560e59e96858
inputrelids = 0x560e59e99510
joinrelids = 0x560e59e99860
clause_list = 0x0
l = 0x560e59e99888
attroff = -1
#4 0x0000560e57d6fb2b in remove_useless_joins (root=0x560e59e8f3e8,
joinlist=0x560e59e97dc0) at analyzejoins.c:78
sjinfo = 0x560e59e97e80
joinrelids = 0xfffffffe00000003
innerrelid = 0
nremoved = 0
lc__state = {l = 0x560e59e98480, i = 0}
lc = 0x560e59e985f0
__func__ = "remove_useless_joins"
#5 0x0000560e57d857bf in query_planner (root=0x560e59e8f3e8,
qp_callback=0x560e57d8c055 <standard_qp_callback>, qp_extra=0x7fff9597ef10)
at planmain.c:223
parse = 0x560e59d5e768
joinlist = 0x560e59e97dc0
final_rel = 0x7fff9597edc0
__func__ = "query_planner"
#6 0x0000560e57d8829c in grouping_planner (root=0x560e59e8f3e8,
tuple_fraction=0) at planner.c:1496
sort_input_targets = 0x0
sort_input_target_parallel_safe = 149
grouping_target = 0xffffffffffffffff
scanjoin_target = 0x560e59e80398
activeWindows = 0x0
qp_extra = {activeWindows = 0x0, gset_data = 0x0}
sort_input_targets_contain_srfs = 0x560e59e96070
have_grouping = false
wflists = 0x0
gset_data = 0x0
sort_input_target = 0x560e00000001
grouping_targets = 0x0
grouping_target_parallel_safe = 255
scanjoin_targets = 0x7fff9597ee50
scanjoin_target_parallel_safe = 127
grouping_targets_contain_srfs = 0xcea6a82500000000
scanjoin_targets_contain_srfs = 0x0
scanjoin_target_same_exprs = false
parse = 0x560e59d5e768
offset_est = 0
count_est = 0
limit_tuples = -1
have_postponed_srfs = false
final_target = 0x99597ee50
final_targets = 0x859e96070
final_targets_contain_srfs = 0xffffffff59d5e768
final_target_parallel_safe = 151
current_rel = 0x560e59e80638
final_rel = 0x560e59e80398
extra = {limit_needed = false, limit_tuples = 0, count_est = 0,
offset_est = 0}
lc = 0x19597ee50
__func__ = "grouping_planner"
#7 0x0000560e57d8794b in subquery_planner (glob=0x560e59e83780,
parse=0x560e59d5e768, parent_root=0x0, hasRecursion=false, tuple_fraction=0)
at planner.c:1065
root = 0x560e59e8f3e8
newWithCheckOptions = 0x0
newHaving = 0x0
hasOuterJoins = true
hasResultRTEs = true
final_rel = 0x560e59d5e768
l = 0x0

Thanks to SQLSmith / SQLReduce for the find.

-
Robins Tharakan
Amazon Web Services