Horton

Thread

Commits

  1. Fix join removal logic to clean up sub-RestrictInfos of OR clauses.

  2. Fix thinko in outer-join removal.

  1. BUG #17786: Assert on indxpath.c

    The Post Office <noreply@postgresql.org> — 2023-02-10T12:56:28Z

    The following bug has been logged on the website:

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

This reproducible assert() seems to have surfaced after commit 8538519db1.

Tested on: f8ba1bf4e4@master
TRAP: failed Assert("outer_rel->rows > 0"), File: "indxpath.c", Line: 1909,
PID: 3247224

SQL
===
create table t();
SELECT
FROM t AS sample_1
     INNER JOIN (pg_catalog.pg_user AS ref_1
                 RIGHT JOIN pg_catalog.pg_conversion AS ref_2 ON NULL)
        ON ref_2.conname = ref_1.passwd
        OR (SELECT calls
            FROM pg_catalog.pg_stat_xact_user_functions) <= 52


Checking (f8ba1bf4e4~35) - 9f452feeeb - Crash
Checking (f8ba1bf4e4~36) - 8538519db1 - Crash
Checking (f8ba1bf4e4~37) - 5840c20272 - Success
Checking (f8ba1bf4e4~38) - faff8f8e47 - Success


=== Backtrace - PID 3247224 - f8ba1bf4e4@master ===
Core was generated by `postgres: f8ba1bf4e4@master@sqith: ubuntu t
127.0.0.1(57768) 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  0x00007f0b57bc6859 in __GI_abort () at abort.c:79
#2  0x0000555ec56d3ff3 in ExceptionalCondition (conditionName=0x555ec5887ff0
"outer_rel->rows > 0", fileName=0x555ec5887f2c "indxpath.c",
lineNumber=1909) at assert.c:66
#3  0x0000555ec538a67a in get_loop_count (root=0x555ec5f72680, cur_relid=3,
outer_relids=0x555ec5f93960) at indxpath.c:1909
#4  0x0000555ec5388b5e in build_index_paths (root=0x555ec5f72680,
rel=0x555ec5f8f648, index=0x555ec5f8ca90, clauses=0x7fffeea57480,
useful_predicate=false, scantype=ST_BITMAPSCAN, skip_nonnative_saop=0x0,
skip_lower_saop=0x0) at indxpath.c:957
#5  0x0000555ec5389261 in build_paths_for_OR (root=0x555ec5f72680,
rel=0x555ec5f8f648, clauses=0x555ec5f93f00, other_clauses=0x555ec5f935e0) at
indxpath.c:1208
#6  0x0000555ec53894cb in generate_bitmap_or_paths (root=0x555ec5f72680,
rel=0x555ec5f8f648, clauses=0x555ec5f93af0, other_clauses=0x0) at
indxpath.c:1288
#7  0x0000555ec538793d in create_index_paths (root=0x555ec5f72680,
rel=0x555ec5f8f648) at indxpath.c:325
#8  0x0000555ec536ef89 in set_plain_rel_pathlist (root=0x555ec5f72680,
rel=0x555ec5f8f648, rte=0x555ec5f4a118) at allpaths.c:768
#9  0x0000555ec536eaf6 in set_rel_pathlist (root=0x555ec5f72680,
rel=0x555ec5f8f648, rti=3, rte=0x555ec5f4a118) at allpaths.c:484
#10 0x0000555ec536e73c in set_base_rel_pathlists (root=0x555ec5f72680) at
allpaths.c:336
#11 0x0000555ec536e470 in make_one_rel (root=0x555ec5f72680,
joinlist=0x555ec5f92510) at allpaths.c:206
#12 0x0000555ec53ae8f6 in query_planner (root=0x555ec5f72680,
qp_callback=0x555ec53b5121 <standard_qp_callback>, qp_extra=0x7fffeea57d70)
at planmain.c:278
#13 0x0000555ec53b1368 in grouping_planner (root=0x555ec5f72680,
tuple_fraction=0) at planner.c:1496
#14 0x0000555ec53b0a17 in subquery_planner (glob=0x555ec5f71ee0,
parse=0x555ec5e2cf58, parent_root=0x0, hasRecursion=false, tuple_fraction=0)
at planner.c:1065
#15 0x0000555ec53aefcf in standard_planner (parse=0x555ec5e2cf58,
query_string=0x555ec5e2b508 "SELECT\nFROM t AS sample_1\n     INNER JOIN
(pg_catalog.pg_user AS ref_1\n", ' ' <repeats 17 times>, "RIGHT JOIN
pg_catalog.pg_conversion AS ref_2 ON NULL)\n        ON ref_2.conname =
ref_1.passwd\n        OR (SELECT"..., cursorOptions=2048, boundParams=0x0)
at planner.c:411
#16 0x00007f0b54bb408b in pgss_planner (parse=0x555ec5e2cf58,
query_string=0x555ec5e2b508 "SELECT\nFROM t AS sample_1\n     INNER JOIN
(pg_catalog.pg_user AS ref_1\n", ' ' <repeats 17 times>, "RIGHT JOIN
pg_catalog.pg_conversion AS ref_2 ON NULL)\n        ON ref_2.conname =
ref_1.passwd\n        OR (SELECT"..., cursorOptions=2048, boundParams=0x0)
at pg_stat_statements.c:954
#17 0x0000555ec53aece0 in planner (parse=0x555ec5e2cf58,
query_string=0x555ec5e2b508 "SELECT\nFROM t AS sample_1\n     INNER JOIN
(pg_catalog.pg_user AS ref_1\n", ' ' <repeats 17 times>, "RIGHT JOIN
pg_catalog.pg_conversion AS ref_2 ON NULL)\n        ON ref_2.conname =
ref_1.passwd\n        OR (SELECT"..., cursorOptions=2048, boundParams=0x0)
at planner.c:279
#18 0x0000555ec54f41de in pg_plan_query (querytree=0x555ec5e2cf58,
query_string=0x555ec5e2b508 "SELECT\nFROM t AS sample_1\n     INNER JOIN
(pg_catalog.pg_user AS ref_1\n", ' ' <repeats 17 times>, "RIGHT JOIN
pg_catalog.pg_conversion AS ref_2 ON NULL)\n        ON ref_2.conname =
ref_1.passwd\n        OR (SELECT"..., cursorOptions=2048, boundParams=0x0)
at postgres.c:870
#19 0x0000555ec54f4336 in pg_plan_queries (querytrees=0x555ec5f6ea18,
query_string=0x555ec5e2b508 "SELECT\nFROM t AS sample_1\n     INNER JOIN
(pg_catalog.pg_user AS ref_1\n", ' ' <repeats 17 times>, "RIGHT JOIN
pg_catalog.pg_conversion AS ref_2 ON NULL)\n        ON ref_2.conname =
ref_1.passwd\n        OR (SELECT"..., cursorOptions=2048, boundParams=0x0)
at postgres.c:962
#20 0x0000555ec54f4719 in exec_simple_query (query_string=0x555ec5e2b508
"SELECT\nFROM t AS sample_1\n     INNER JOIN (pg_catalog.pg_user AS
ref_1\n", ' ' <repeats 17 times>, "RIGHT JOIN pg_catalog.pg_conversion AS
ref_2 ON NULL)\n        ON ref_2.conname = ref_1.passwd\n        OR
(SELECT"...) at postgres.c:1156
#21 0x0000555ec54f95cf in PostgresMain (dbname=0x555ec5e6b9c8 "t",
username=0x555ec5e26478 "ubuntu") at postgres.c:4565
#22 0x0000555ec5421dd6 in BackendRun (port=0x555ec5e67290) at
postmaster.c:4461
#23 0x0000555ec54216a9 in BackendStartup (port=0x555ec5e67290) at
postmaster.c:4189
#24 0x0000555ec541dad8 in ServerLoop () at postmaster.c:1779
#25 0x0000555ec541d3ad in PostmasterMain (argc=3, argv=0x555ec5e243d0) at
postmaster.c:1463
#26 0x0000555ec52d05eb in main (argc=3, argv=0x555ec5e243d0) at main.c:200


=== Backtrace FULL - PID 3247224 - f8ba1bf4e4@master ===
#2  0x0000555ec56d3ff3 in ExceptionalCondition (conditionName=0x555ec5887ff0
"outer_rel->rows > 0", fileName=0x555ec5887f2c "indxpath.c",
lineNumber=1909) at assert.c:66
No locals.
#3  0x0000555ec538a67a in get_loop_count (root=0x555ec5f72680, cur_relid=3,
outer_relids=0x555ec5f93960) at indxpath.c:1909
        outer_rel = 0x555ec5f90458
        rowcount = 1
        result = 1
        outer_relid = 8
#4  0x0000555ec5388b5e in build_index_paths (root=0x555ec5f72680,
rel=0x555ec5f8f648, index=0x555ec5f8ca90, clauses=0x7fffeea57480,
useful_predicate=false, scantype=ST_BITMAPSCAN, skip_nonnative_saop=0x0,
skip_lower_saop=0x0) at indxpath.c:957
        result = 0x0
        ipath = 0x555ec538abc6 <match_clauses_to_index+93>
        index_clauses = 0x555ec5f940f0
        outer_relids = 0x555ec5f93960
        loop_count = 6.0264680463413734e-312
        orderbyclauses = 0x555ec5f72680
        orderbyclausecols = 0x0
        index_pathkeys = 0x7fffeea573f0
        useful_pathkeys = 0x0
        found_lower_saop_clause = false
        pathkeys_possibly_useful = 40
        index_is_ordered = 249
        index_only_scan = 197
        indexcol = 2
#5  0x0000555ec5389261 in build_paths_for_OR (root=0x555ec5f72680,
rel=0x555ec5f8f648, clauses=0x555ec5f93f00, other_clauses=0x555ec5f935e0) at
indxpath.c:1208
        index = 0x555ec5f8ca90
        clauseset = {nonempty = true, indexclauses = {0x555ec5f940c0, 0x0
<repeats 31 times>}}
        indexpaths = 0x555ec5f93ef0
        useful_predicate = false
        lc__state = {l = 0x555ec5f8fea0, i = 1}
        result = 0x0
        all_clauses = 0x0
        lc = 0x555ec5f90390
#6  0x0000555ec53894cb in generate_bitmap_or_paths (root=0x555ec5f72680,
rel=0x555ec5f8f648, clauses=0x555ec5f93af0, other_clauses=0x0) at
indxpath.c:1288
        ri = 0x555ec5f925b0
        orargs = 0x555ec5f93f00
        orarg = 0x555ec5f925b0
        indlist = 0x7fffeea57640
        j__state = {l = 0x555ec5f92740, i = 0}
        rinfo = 0x555ec5f928b0
        pathlist = 0x0
        bitmapqual = 0x555ec5f72680
        j = 0x555ec5f92720
        lc__state = {l = 0x555ec5f93af0, i = 0}
        result = 0x0
        all_clauses = 0x555ec5f935e0
        lc = 0x555ec5f93ed0
#7  0x0000555ec538793d in create_index_paths (root=0x555ec5f72680,
rel=0x555ec5f8f648) at indxpath.c:325
        indexpaths = 0x0
        bitindexpaths = 0x555ec5f93e20
        bitjoinpaths = 0x0
        joinorclauses = 0x555ec5f93af0
        rclauseset = {nonempty = false, indexclauses = {0x0 <repeats 32
times>}}
        jclauseset = {nonempty = false, indexclauses = {0x0 <repeats 32
times>}}
        eclauseset = {nonempty = false, indexclauses = {0x0 <repeats 32
times>}}
        lc = 0x0
#8  0x0000555ec536ef89 in set_plain_rel_pathlist (root=0x555ec5f72680,
rel=0x555ec5f8f648, rte=0x555ec5f4a118) at allpaths.c:768
        required_outer = 0x0
#9  0x0000555ec536eaf6 in set_rel_pathlist (root=0x555ec5f72680,
rel=0x555ec5f8f648, rti=3, rte=0x555ec5f4a118) at allpaths.c:484
        __func__ = "set_rel_pathlist"
#10 0x0000555ec536e73c in set_base_rel_pathlists (root=0x555ec5f72680) at
allpaths.c:336
        rel = 0x555ec5f8f648
        rti = 3
#11 0x0000555ec536e470 in make_one_rel (root=0x555ec5f72680,
joinlist=0x555ec5f92510) at allpaths.c:206
        rel = 0x0
        rti = 10
        total_pages = 17
#12 0x0000555ec53ae8f6 in query_planner (root=0x555ec5f72680,
qp_callback=0x555ec53b5121 <standard_qp_callback>, qp_extra=0x7fffeea57d70)
at planmain.c:278
        parse = 0x555ec5e2cf58
        joinlist = 0x555ec5f92510
        final_rel = 0x7fffeea57c20
        __func__ = "query_planner"
#13 0x0000555ec53b1368 in grouping_planner (root=0x555ec5f72680,
tuple_fraction=0) at planner.c:1496
        sort_input_targets = 0x0
        sort_input_target_parallel_safe = 238
        grouping_target = 0x1c5f8e798
        scanjoin_target = 0x555ec5f72190
        activeWindows = 0x0
        qp_extra = {activeWindows = 0x0, gset_data = 0x0}
        sort_input_targets_contain_srfs = 0x0
        have_grouping = false
        wflists = 0x0
        gset_data = 0x0
        sort_input_target = 0x100000001
        grouping_targets = 0x555e00000000
        grouping_target_parallel_safe = 255
        scanjoin_targets = 0x555ec5f8e6e8
        scanjoin_target_parallel_safe = 127
        grouping_targets_contain_srfs = 0x7fffeea57d80
        scanjoin_targets_contain_srfs = 0x0
        scanjoin_target_same_exprs = false
        parse = 0x555ec5e2cf58
        offset_est = 0
        count_est = 0
        limit_tuples = -1
        have_postponed_srfs = false
        final_target = 0x7fffeea57cb0
        final_targets = 0x555ec5f722d0
        final_targets_contain_srfs = 0x555ec5f72680
        final_target_parallel_safe = 165
        current_rel = 0x555ec52f53ed <new_list+75>
        final_rel = 0x555ec5f72190
        extra = {limit_needed = false, limit_tuples = 0, count_est = 0,
offset_est = 0}
        lc = 0x7fffeea57cd0
        __func__ = "grouping_planner"
#14 0x0000555ec53b0a17 in subquery_planner (glob=0x555ec5f71ee0,
parse=0x555ec5e2cf58, parent_root=0x0, hasRecursion=false, tuple_fraction=0)
at planner.c:1065
        root = 0x555ec5f72680
        newWithCheckOptions = 0x0
        newHaving = 0x0
        hasOuterJoins = true
        hasResultRTEs = false
        final_rel = 0x555ec5e2cf58
        l = 0x0
#15 0x0000555ec53aefcf in standard_planner (parse=0x555ec5e2cf58,
query_string=0x555ec5e2b508 "SELECT\nFROM t AS sample_1\n     INNER JOIN
(pg_catalog.pg_user AS ref_1\n", ' ' <repeats 17 times>, "RIGHT JOIN
pg_catalog.pg_conversion AS ref_2 ON NULL)\n        ON ref_2.conname =
ref_1.passwd\n        OR (SELECT"..., cursorOptions=2048, boundParams=0x0)
at planner.c:411
        result = 0x0
        glob = 0x555ec5f71ee0
        tuple_fraction = 0
        root = 0x555ec5f6ea18
        final_rel = 0x8
        best_path = 0x0
        top_plan = 0x0
        lp = 0x4d430000
        lr = 0x555ec5f6ea18


Thanks to SQLSmith / SQLReduce for the find.

-
Robins Tharakan
Amazon Web Services

  2. Re: BUG #17786: Assert on indxpath.c

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-02-10T20:14:56Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> This reproducible assert() seems to have surfaced after commit 8538519db1.

Huh.  That's a totally different and very old bug, which is easily
visible now but I wonder whether it had any consequences before.
Anyway, fix pushed.  Thanks so much for this testing!

			regards, tom lane