Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitacc5821

Browse files
committed
Further fixes in qual nullingrel adjustment for outer join commutation.
One of the add_nulling_relids calls in deconstruct_distribute_oj_qualsadded an OJ relid to too few Vars, while the other added it to toomany. We should consider the syntactic structure notmin_left/righthand while deciding which Vars to decorate, and whenconsidering pushing up a lower outer join pursuant to transforming thesecond form of OJ identity 3 to the first form, we only want todecorate Vars coming from its LHS.In a related bug, I realized that make_outerjoininfo was failing tocheck a very basic property that's needed to apply OJ identity 3:the syntactically-upper outer join clause can't refer to the lowerjoin's LHS. This didn't break the join order restriction logic,but it led to setting bogus commute_xxx bits, possibly resultingin bogus nullingrel markings in modified quals.Richard Guo and Tom LaneDiscussion:https://postgr.es/m/CAMbWs497CmBruMx1SOjepWEz+T5NWa4scqbdE9v7ZzSXqH_gQw@mail.gmail.comDiscussion:https://postgr.es/m/CAEP4nAx9C5gXNBfEA0JBfz7B+5f1Bawt-RWQWyhev-wdps8BZA@mail.gmail.com
1 parentf8ba1bf commitacc5821

File tree

3 files changed

+50
-5
lines changed

3 files changed

+50
-5
lines changed

‎src/backend/optimizer/plan/initsplan.c

Lines changed: 16 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1540,7 +1540,8 @@ make_outerjoininfo(PlannerInfo *root,
15401540
}
15411541
elseif (jointype==JOIN_LEFT&&
15421542
otherinfo->jointype==JOIN_LEFT&&
1543-
bms_overlap(strict_relids,otherinfo->min_righthand))
1543+
bms_overlap(strict_relids,otherinfo->min_righthand)&&
1544+
!bms_overlap(clause_relids,otherinfo->syn_lefthand))
15441545
{
15451546
/* Identity 3 applies, so remove the ordering restriction */
15461547
min_lefthand=bms_del_member(min_lefthand,otherinfo->ojrelid);
@@ -1985,12 +1986,18 @@ deconstruct_distribute_oj_quals(PlannerInfo *root,
19851986
/*
19861987
* When we are looking at joins above sjinfo, we are envisioning
19871988
* pushing sjinfo to above othersj, so add othersj's nulling bit
1988-
* before distributing the quals.
1989+
* before distributing the quals. We should add it to Vars coming
1990+
* from the current join's LHS: we want to transform the second
1991+
* form of OJ identity 3 to the first form, in which Vars of
1992+
* relation B will appear nulled by the syntactically-upper OJ
1993+
* within the Pbc clause, but those of relation C will not. (In
1994+
* the notation used by optimizer/README, we're converting a qual
1995+
* of the form Pbc to Pb*c.)
19891996
*/
19901997
if (above_sjinfo)
19911998
quals= (List*)
19921999
add_nulling_relids((Node*)quals,
1993-
othersj->min_righthand,
2000+
sjinfo->syn_lefthand,
19942001
bms_make_singleton(othersj->ojrelid));
19952002

19962003
/* Compute qualscope and ojscope for this join level */
@@ -2041,12 +2048,16 @@ deconstruct_distribute_oj_quals(PlannerInfo *root,
20412048
/*
20422049
* Adjust qual nulling bits for next level up, if needed. We
20432050
* don't want to put sjinfo's own bit in at all, and if we're
2044-
* above sjinfo then we did it already.
2051+
* above sjinfo then we did it already. Here, we should mark all
2052+
* Vars coming from the lower join's RHS. (Again, we are
2053+
* converting a qual of the form Pbc to Pb*c, but now we are
2054+
* putting back bits that were there in the parser output and were
2055+
* temporarily stripped above.)
20452056
*/
20462057
if (below_sjinfo)
20472058
quals= (List*)
20482059
add_nulling_relids((Node*)quals,
2049-
othersj->min_righthand,
2060+
othersj->syn_righthand,
20502061
bms_make_singleton(othersj->ojrelid));
20512062

20522063
/* ... and track joins processed so far */

‎src/test/regress/expected/join.out

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5068,6 +5068,31 @@ where current_user is not null; -- this is to add a Result node
50685068
-> Seq Scan on int4_tbl i4
50695069
(6 rows)
50705070

5071+
-- and further discussion of bug #17781
5072+
explain (costs off)
5073+
select *
5074+
from int8_tbl t1
5075+
left join (int8_tbl t2 left join onek t3 on t2.q1 > t3.unique1)
5076+
on t1.q2 = t2.q2
5077+
left join onek t4
5078+
on t2.q2 < t3.unique2;
5079+
QUERY PLAN
5080+
-------------------------------------------------
5081+
Nested Loop Left Join
5082+
Join Filter: (t2.q2 < t3.unique2)
5083+
-> Nested Loop Left Join
5084+
Join Filter: (t2.q1 > t3.unique1)
5085+
-> Hash Left Join
5086+
Hash Cond: (t1.q2 = t2.q2)
5087+
-> Seq Scan on int8_tbl t1
5088+
-> Hash
5089+
-> Seq Scan on int8_tbl t2
5090+
-> Materialize
5091+
-> Seq Scan on onek t3
5092+
-> Materialize
5093+
-> Seq Scan on onek t4
5094+
(13 rows)
5095+
50715096
-- check that join removal works for a left join when joining a subquery
50725097
-- that is guaranteed to be unique by its GROUP BY clause
50735098
explain (costs off)

‎src/test/regress/sql/join.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1820,6 +1820,15 @@ from (select f1/2 as x from int4_tbl i4 left join a on a.id = i4.f1) ss1
18201820
right join int8_tbl i8on true
18211821
wherecurrent_useris not null;-- this is to add a Result node
18221822

1823+
-- and further discussion of bug #17781
1824+
explain (costs off)
1825+
select*
1826+
from int8_tbl t1
1827+
left join (int8_tbl t2left join onek t3ont2.q1>t3.unique1)
1828+
ont1.q2=t2.q2
1829+
left join onek t4
1830+
ont2.q2<t3.unique2;
1831+
18231832
-- check that join removal works for a left join when joining a subquery
18241833
-- that is guaranteed to be unique by its GROUP BY clause
18251834
explain (costs off)

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp