BUG #17773: Assert triggered on analyzejoins.c - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17773
Logged by:          Robins Tharakan
Email address:      tharakan@gmail.com
PostgreSQL version: 15.1
Operating system:   Ubuntu 20.04
Description:

This assert() is easily reproducible as of 71c37797d7@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@master@sqith: ubuntu t
127.0.0.1(57752) SELECT           '.
Program terminated with signal SIGABRT, Aborted.
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#0  __GI_raise (sig=sig@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