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

Commit950f80d

Browse files
committed
Prevent improper reordering of antijoins vs. outer joins.
An outer join appearing within the RHS of an antijoin can't commute withthe antijoin, but somehow I missed teaching make_outerjoininfo() aboutthat. In Teodor Sigaev's recent trouble report, this manifests as a"could not find RelOptInfo for given relids" error within eqjoinsel();but I think silently wrong query results are possible too, if the plannermisorders the joins and doesn't happen to trigger any internal consistencychecks. It's broken as far back as we had antijoins, so back-patch to allsupported branches.
1 parent5eab0f1 commit950f80d

File tree

3 files changed

+63
-3
lines changed

3 files changed

+63
-3
lines changed

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

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -694,9 +694,9 @@ make_outerjoininfo(PlannerInfo *root,
694694
* For a lower OJ in our RHS, if our join condition does not use the
695695
* lower join's RHS and the lower OJ's join condition is strict, we
696696
* can interchange the ordering of the two OJs; otherwise we must add
697-
* lower OJ's full syntactic relset to min_righthand.Here, we must
698-
* preserve ordering anyway if either the current joinis a semijoin,
699-
*or thelower OJ is either a semijoin or an antijoin.
697+
*thelower OJ's full syntactic relset to min_righthand.Also, we
698+
*mustpreserve ordering anyway if either the current joinor the
699+
* lower OJ is either a semijoin or an antijoin.
700700
*
701701
* Here, we have to consider that "our join condition" includes any
702702
* clauses that syntactically appeared above the lower OJ and below
@@ -713,6 +713,7 @@ make_outerjoininfo(PlannerInfo *root,
713713
{
714714
if (bms_overlap(clause_relids,otherinfo->syn_righthand)||
715715
jointype==JOIN_SEMI||
716+
jointype==JOIN_ANTI||
716717
otherinfo->jointype==JOIN_SEMI||
717718
otherinfo->jointype==JOIN_ANTI||
718719
!otherinfo->lhs_strict||otherinfo->delay_upper_joins)

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

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2277,6 +2277,48 @@ WHERE d.f1 IS NULL;
22772277
9999
22782278
(3 rows)
22792279

2280+
--
2281+
-- regression test for proper handling of outer joins within antijoins
2282+
--
2283+
create temp table tt4x(c1 int, c2 int, c3 int);
2284+
explain (costs off)
2285+
select * from tt4x t1
2286+
where not exists (
2287+
select 1 from tt4x t2
2288+
left join tt4x t3 on t2.c3 = t3.c1
2289+
left join ( select t5.c1 as c1
2290+
from tt4x t4 left join tt4x t5 on t4.c2 = t5.c1
2291+
) a1 on t3.c2 = a1.c1
2292+
where t1.c1 = t2.c2
2293+
);
2294+
QUERY PLAN
2295+
---------------------------------------------------------
2296+
Hash Anti Join
2297+
Hash Cond: (t1.c1 = t2.c2)
2298+
-> Seq Scan on tt4x t1
2299+
-> Hash
2300+
-> Merge Right Join
2301+
Merge Cond: (t5.c1 = t3.c2)
2302+
-> Merge Join
2303+
Merge Cond: (t4.c2 = t5.c1)
2304+
-> Sort
2305+
Sort Key: t4.c2
2306+
-> Seq Scan on tt4x t4
2307+
-> Sort
2308+
Sort Key: t5.c1
2309+
-> Seq Scan on tt4x t5
2310+
-> Sort
2311+
Sort Key: t3.c2
2312+
-> Merge Left Join
2313+
Merge Cond: (t2.c3 = t3.c1)
2314+
-> Sort
2315+
Sort Key: t2.c3
2316+
-> Seq Scan on tt4x t2
2317+
-> Sort
2318+
Sort Key: t3.c1
2319+
-> Seq Scan on tt4x t3
2320+
(24 rows)
2321+
22802322
--
22812323
-- regression test for problems of the sort depicted in bug #3494
22822324
--

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

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -443,6 +443,23 @@ LEFT JOIN (
443443
)AS dON (a.f1=d.f1)
444444
WHEREd.f1 ISNULL;
445445

446+
--
447+
-- regression test for proper handling of outer joins within antijoins
448+
--
449+
450+
create temp table tt4x(c1int, c2int, c3int);
451+
452+
explain (costs off)
453+
select*from tt4x t1
454+
where not exists (
455+
select1from tt4x t2
456+
left join tt4x t3ont2.c3=t3.c1
457+
left join (selectt5.c1as c1
458+
from tt4x t4left join tt4x t5ont4.c2=t5.c1
459+
) a1ont3.c2=a1.c1
460+
wheret1.c1=t2.c2
461+
);
462+
446463
--
447464
-- regression test for problems of the sort depicted in bug #3494
448465
--

0 commit comments

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