Thread: 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@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

Richard Guo <guofenglinux@gmail.com> writes:
> On Mon, Feb 6, 2023 at 1:41 PM PG Bug reporting form <noreply@postgresql.org>
> wrote:
>> 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;

> It seems something is wrong about the check on PlaceHolderVars in
> outer-join removal codes.  When we want to know if a PHV actually
> references inner-rel's attributes, we check phinfo->ph_var->phexpr with
> pull_varnos.  I suspect this is wrong.  Shouldn't we check
> phinfo->ph_var?

No -- that would destroy the entire point of that bit of code, which
is that ph_eval_at may include a "dummy" reference to the inner rel
that we're hoping to remove.  That happens if we made the PHV's
ph_eval_at equal to its syntactic scope due to its not containing any
Vars.  We can still remove the join, as long as it's possible to
eval the PHV at the join's outer rel instead.

After thinking about this I concluded that the Assert I left behind
yesterday is just wrong.  If we get down to that part of the code,
then we know that it's okay to trim the ph_eval_at values of any such
PHVs --- but if one of them is mentioned in a qual clause then the
clause_relids will also contain those relids.  We have to allow that
during join_is_removable and then remove the relids during
remove_rel_from_query.

It's slightly annoying to lose the cross-checking that those
Asserts used to afford: what if the mention in clause_relids
didn't come from one of the PHVs we're fixing up?  But I don't
see any simple way to re-implement that cross-check.  Adding
a lot of logic to do so would be a bit self-defeating I think;
the extra code might have bugs itself.

            regards, tom lane