BUG #17769: Assert triggered in indxpath.c

The following bug has been logged on the website:

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

This assert() is reproducible. It is similar to a recent bug-report [1],
but reporting separately since the TRAP signature is different.

TRAP: failed Assert("outer_rel->rows > 0"), File: "indxpath.c", Line: 1909,
PID: 3364016
Git: 117d2604c2(at)master

SQL / backtrace / backtrace full excerpt below.

Backtrace
=========
Core was generated by `postgres: 117d2604c2(at)master@sqith: ubuntu postgres
127.0.0.1(55644) 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 0x00007f8691c5c859 in __GI_abort () at abort.c:79
#2 0x0000558e00499dfe in ExceptionalCondition (conditionName=0x558e0064c590
"outer_rel->rows > 0", fileName=0x558e0064c4cc "indxpath.c",
lineNumber=1909) at assert.c:66
#3 0x0000558e0015194f in get_loop_count (root=0x558e01c056e0, cur_relid=3,
outer_relids=0x7f8676a230e8) at indxpath.c:1909
#4 0x0000558e0014fe33 in build_index_paths (root=0x558e01c056e0,
rel=0x558e01c201a0, index=0x558e01c2ba98, clauses=0x7ffed1004890,
useful_predicate=false, scantype=ST_ANYSCAN,
skip_nonnative_saop=0x7ffed10047a6, skip_lower_saop=0x7ffed10047a7) at
indxpath.c:957
#5 0x0000558e0014f9b4 in get_index_paths (root=0x558e01c056e0,
rel=0x558e01c201a0, index=0x558e01c2ba98, clauses=0x7ffed1004890,
bitindexpaths=0x7ffed1004b20) at indxpath.c:728
#6 0x0000558e0014f83b in get_join_index_paths (root=0x558e01c056e0,
rel=0x558e01c201a0, index=0x558e01c2ba98, rclauseset=0x7ffed1004c30,
jclauseset=0x7ffed1004d40,
eclauseset=0x7ffed1004e50, bitindexpaths=0x7ffed1004b20,
relids=0x7f8676a230c8, considered_relids=0x7ffed1004ac0) at indxpath.c:665
#7 0x0000558e0014f3a5 in consider_index_join_outer_rels
(root=0x558e01c056e0, rel=0x558e01c201a0, index=0x558e01c2ba98,
rclauseset=0x7ffed1004c30, jclauseset=0x7ffed1004d40,
eclauseset=0x7ffed1004e50, bitindexpaths=0x7ffed1004b20,
indexjoinclauses=0x7f8676a23368, considered_clauses=1,
considered_relids=0x7ffed1004ac0) at indxpath.c:579
#8 0x0000558e0014f1cf in consider_index_join_clauses (root=0x558e01c056e0,
rel=0x558e01c201a0, index=0x558e01c2ba98, rclauseset=0x7ffed1004c30,
jclauseset=0x7ffed1004d40,
eclauseset=0x7ffed1004e50, bitindexpaths=0x7ffed1004b20) at
indxpath.c:476
#9 0x0000558e0014eb20 in create_index_paths (root=0x558e01c056e0,
rel=0x558e01c201a0) at indxpath.c:306
#10 0x0000558e0013625e in set_plain_rel_pathlist (root=0x558e01c056e0,
rel=0x558e01c201a0, rte=0x558e01c05050) at allpaths.c:768
#11 0x0000558e00135dcb in set_rel_pathlist (root=0x558e01c056e0,
rel=0x558e01c201a0, rti=3, rte=0x558e01c05050) at allpaths.c:484
#12 0x0000558e00135a11 in set_base_rel_pathlists (root=0x558e01c056e0) at
allpaths.c:336
#13 0x0000558e00135745 in make_one_rel (root=0x558e01c056e0,
joinlist=0x7f8676a224c8) at allpaths.c:206
#14 0x0000558e00175ae4 in query_planner (root=0x558e01c056e0,
qp_callback=0x558e0017c30f <standard_qp_callback>, qp_extra=0x7ffed1005210)
at planmain.c:278
#15 0x0000558e00178556 in grouping_planner (root=0x558e01c056e0,
tuple_fraction=0) at planner.c:1496
#16 0x0000558e00177c05 in subquery_planner (glob=0x558e01c04e30,
parse=0x558e01aa9238, parent_root=0x0, hasRecursion=false, tuple_fraction=0)
at planner.c:1065
#17 0x0000558e001761bd in standard_planner (parse=0x558e01aa9238,
query_string=0x558e01aa7f38 "SELECT\nFROM
pg_catalog.pg_statio_all_tables AS ref_0,\n LATERAL (SELECT\n", ' '
<repeats 14 times>, "WHERE ref_0.schemaname = ref_0.relname) AS subq_0\n
;", cursorOptions=2048, boundParams=0x0) at planner.c:411

Backtrace full excerpt
======================
#2 0x0000558e00499dfe in ExceptionalCondition (conditionName=0x558e0064c590
"outer_rel->rows > 0", fileName=0x558e0064c4cc "indxpath.c",
lineNumber=1909) at assert.c:66
No locals.
#3 0x0000558e0015194f in get_loop_count (root=0x558e01c056e0, cur_relid=3,
outer_relids=0x7f8676a230e8) at indxpath.c:1909
outer_rel = 0x558e01c2cf58
rowcount = 9.4860604001519336e-322
result = 0
outer_relid = 6
#4 0x0000558e0014fe33 in build_index_paths (root=0x558e01c056e0,
rel=0x558e01c201a0, index=0x558e01c2ba98, clauses=0x7ffed1004890,
useful_predicate=false, scantype=ST_ANYSCAN,
skip_nonnative_saop=0x7ffed10047a6, skip_lower_saop=0x7ffed10047a7) at
indxpath.c:957
result = 0x0
ipath = 0x558e004dff49 <palloc+278>
index_clauses = 0x7f8676a233c8
outer_relids = 0x7f8676a230e8
loop_count = 4.6475954467446791e-310
orderbyclauses = 0x0
orderbyclausecols = 0x0
index_pathkeys = 0x7ffed1004790
useful_pathkeys = 0x6491e3bad6b60900
found_lower_saop_clause = false
pathkeys_possibly_useful = false
index_is_ordered = false
index_only_scan = false
indexcol = 2
#5 0x0000558e0014f9b4 in get_index_paths (root=0x558e01c056e0,
rel=0x558e01c201a0, index=0x558e01c2ba98, clauses=0x7ffed1004890,
bitindexpaths=0x7ffed1004b20) at indxpath.c:728
indexpaths = 0x100000001
skip_nonnative_saop = false
skip_lower_saop = false
lc = 0x7ffed10047e0
#6 0x0000558e0014f83b in get_join_index_paths (root=0x558e01c056e0,
rel=0x558e01c201a0, index=0x558e01c2ba98, rclauseset=0x7ffed1004c30,
jclauseset=0x7ffed1004d40,
eclauseset=0x7ffed1004e50, bitindexpaths=0x7ffed1004b20,
relids=0x7f8676a230c8, considered_relids=0x7ffed1004ac0) at indxpath.c:665
clauseset = {nonempty = true, indexclauses = {0x7f8676a23398, 0x0
<repeats 31 times>}}
indexcol = 2
#7 0x0000558e0014f3a5 in consider_index_join_outer_rels
(root=0x558e01c056e0, rel=0x558e01c201a0, index=0x558e01c2ba98,
rclauseset=0x7ffed1004c30, jclauseset=0x7ffed1004d40,
eclauseset=0x7ffed1004e50, bitindexpaths=0x7ffed1004b20,
indexjoinclauses=0x7f8676a23368, considered_clauses=1,
considered_relids=0x7ffed1004ac0) at indxpath.c:579
iclause = 0x7f8676a23108
clause_relids = 0x7f8676a230c8
parent_ec = 0x7f8676a209a8
num_considered_relids = 0
lc__state = {l = 0x7f8676a23368, i = 0}
lc = 0x7f8676a23380

SQL
===
SELECT
FROM pg_catalog.pg_statio_all_tables AS ref_0,
LATERAL (SELECT
WHERE ref_0.schemaname = ref_0.relname) AS subq_0;

Thanks to SQLSmith / SQLReduce for the find.

Reference:
1.
https://www.postgresql.org/message-id/flat/CAMbWs490BcsZqXF%2BfoXiQ0zdw-FffZJA6%2BvBNYtJF0vkDyJ98g%40mail.gmail.com#1a3b983d61aeb5b76ac7f4012da5f7ea

-
Robins Tharakan
Amazon Web Services