BUG #17786: Assert on indxpath.c

The following bug has been logged on the website:

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

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

Tested on: f8ba1bf4e4(at)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(at)master ===
Core was generated by `postgres: f8ba1bf4e4(at)master@sqith: ubuntu t
127.0.0.1(57768) 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 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(at)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