Обсуждение: 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
On Mon, Feb 6, 2023 at 1:41 PM PG Bug reporting form <noreply@postgresql.org> wrote:
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;
Thanks for the report! I can reproduce this issue with the following
query.
create table t (a int unique, b int);
select t1.a from t t1 left join (select 2 as n from t t2 left join t t3 on true) ss on true where ss.n = 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?
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -236,7 +236,7 @@ join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo)
continue; /* it definitely doesn't reference innerrel */
if (bms_is_subset(phinfo->ph_eval_at, innerrel->relids))
return false; /* there isn't any other place to eval PHV */
- if (bms_overlap(pull_varnos(root, (Node *) phinfo->ph_var->phexpr),
+ if (bms_overlap(pull_varnos(root, (Node *) phinfo->ph_var),
innerrel->relids))
Thanks
Richard
query.
create table t (a int unique, b int);
select t1.a from t t1 left join (select 2 as n from t t2 left join t t3 on true) ss on true where ss.n = 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?
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -236,7 +236,7 @@ join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo)
continue; /* it definitely doesn't reference innerrel */
if (bms_is_subset(phinfo->ph_eval_at, innerrel->relids))
return false; /* there isn't any other place to eval PHV */
- if (bms_overlap(pull_varnos(root, (Node *) phinfo->ph_var->phexpr),
+ if (bms_overlap(pull_varnos(root, (Node *) phinfo->ph_var),
innerrel->relids))
Thanks
Richard
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