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

Commit0ffc0ac

Browse files
author
Richard Guo
committed
Fix right-anti-joins when the inner relation is proven unique
For an inner_unique join, we always assume that the executor will stopscanning for matches after the first match. Therefore, for a mergejointhat is inner_unique and whose mergeclauses are sufficient to identify amatch, we set the skip_mark_restore flag to true, indicating that theexecutor need not do mark/restore calls. However, merge-right-anti-joindid not get this memo and continues scanning the inner side for matchesafter the first match. If there are duplicates in the outer scan, wemay incorrectly skip matching some inner tuples, which can lead to wrongresults.Here we fix this issue by ensuring that merge-right-anti-join alsoadvances to next outer tuple after the first match in inner_uniquecases. This also saves cycles by avoiding unnecessary scanning of innertuples after the first match.Although hash-right-anti-join does not suffer from this wrong resultsissue, we apply the same change to it as well, to help save cycles forthe same reason.Per bug #18522 from Antti Lampinen, and bug #18526 from Feliphe Pozzer.Back-patch to v16 where right-anti-join was introduced.Author: Richard GuoDiscussion:https://postgr.es/m/18522-c7a8956126afdfd0@postgresql.org
1 parent74b8e6a commit0ffc0ac

File tree

4 files changed

+100
-20
lines changed

4 files changed

+100
-20
lines changed

‎src/backend/executor/nodeHashjoin.c

Lines changed: 11 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -573,20 +573,21 @@ ExecHashJoinImpl(PlanState *pstate, bool parallel)
573573
}
574574

575575
/*
576-
*In a right-antijoin, we never return a matched tuple.
577-
*And we need tostay on the currentouter tupleto
578-
*continue scanning the inner side for matches.
576+
*If we only need to consider the first matching inner
577+
*tuple, then advance tonextouter tupleafter we've
578+
*processed this one.
579579
*/
580-
if (node->js.jointype==JOIN_RIGHT_ANTI)
581-
continue;
580+
if (node->js.single_match)
581+
node->hj_JoinState=HJ_NEED_NEW_OUTER;
582582

583583
/*
584-
* If we only need to join to the first matching inner
585-
* tuple, then consider returning this one, but after that
586-
* continue with next outer tuple.
584+
* In a right-antijoin, we never return a matched tuple.
585+
* If it's not an inner_unique join, we need to stay on
586+
* the current outer tuple to continue scanning the inner
587+
* side for matches.
587588
*/
588-
if (node->js.single_match)
589-
node->hj_JoinState=HJ_NEED_NEW_OUTER;
589+
if (node->js.jointype==JOIN_RIGHT_ANTI)
590+
continue;
590591

591592
if (otherqual==NULL||ExecQual(otherqual,econtext))
592593
returnExecProject(node->js.ps.ps_ProjInfo);

‎src/backend/executor/nodeMergejoin.c

Lines changed: 11 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -805,20 +805,21 @@ ExecMergeJoin(PlanState *pstate)
805805
}
806806

807807
/*
808-
*In a right-antijoin, we never return a matched tuple.
809-
*And we need tostay on the currentouter tupleto
810-
*continue scanning the inner side for matches.
808+
*If we only need to consider the first matching inner
809+
*tuple, then advance tonextouter tupleafter we've
810+
*processed this one.
811811
*/
812-
if (node->js.jointype==JOIN_RIGHT_ANTI)
813-
break;
812+
if (node->js.single_match)
813+
node->mj_JoinState=EXEC_MJ_NEXTOUTER;
814814

815815
/*
816-
* If we only need to join to the first matching inner
817-
* tuple, then consider returning this one, but after that
818-
* continue with next outer tuple.
816+
* In a right-antijoin, we never return a matched tuple.
817+
* If it's not an inner_unique join, we need to stay on
818+
* the current outer tuple to continue scanning the inner
819+
* side for matches.
819820
*/
820-
if (node->js.single_match)
821-
node->mj_JoinState=EXEC_MJ_NEXTOUTER;
821+
if (node->js.jointype==JOIN_RIGHT_ANTI)
822+
break;
822823

823824
qualResult= (otherqual==NULL||
824825
ExecQual(otherqual,econtext));

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

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2924,6 +2924,60 @@ select tt1.*, tt2.* from tt2 right join tt1 on tt1.joincol = tt2.joincol;
29242924
2 | | |
29252925
(3 rows)
29262926

2927+
reset enable_hashjoin;
2928+
reset enable_nestloop;
2929+
--
2930+
-- regression test for bug #18522 (merge-right-anti-join in inner_unique cases)
2931+
--
2932+
create temp table tbl_ra(a int unique, b int);
2933+
insert into tbl_ra select i, i%100 from generate_series(1,1000)i;
2934+
create index on tbl_ra (b);
2935+
analyze tbl_ra;
2936+
set enable_hashjoin to off;
2937+
set enable_nestloop to off;
2938+
-- ensure we get a merge right anti join
2939+
explain (costs off)
2940+
select * from tbl_ra t1
2941+
where not exists (select 1 from tbl_ra t2 where t2.b = t1.a) and t1.b < 2;
2942+
QUERY PLAN
2943+
-------------------------------------------------------
2944+
Merge Right Anti Join
2945+
Merge Cond: (t2.b = t1.a)
2946+
-> Index Only Scan using tbl_ra_b_idx on tbl_ra t2
2947+
-> Sort
2948+
Sort Key: t1.a
2949+
-> Bitmap Heap Scan on tbl_ra t1
2950+
Recheck Cond: (b < 2)
2951+
-> Bitmap Index Scan on tbl_ra_b_idx
2952+
Index Cond: (b < 2)
2953+
(9 rows)
2954+
2955+
-- and check we get the expected results
2956+
select * from tbl_ra t1
2957+
where not exists (select 1 from tbl_ra t2 where t2.b = t1.a) and t1.b < 2;
2958+
a | b
2959+
------+---
2960+
100 | 0
2961+
101 | 1
2962+
200 | 0
2963+
201 | 1
2964+
300 | 0
2965+
301 | 1
2966+
400 | 0
2967+
401 | 1
2968+
500 | 0
2969+
501 | 1
2970+
600 | 0
2971+
601 | 1
2972+
700 | 0
2973+
701 | 1
2974+
800 | 0
2975+
801 | 1
2976+
900 | 0
2977+
901 | 1
2978+
1000 | 0
2979+
(19 rows)
2980+
29272981
reset enable_hashjoin;
29282982
reset enable_nestloop;
29292983
--

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

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -693,6 +693,30 @@ select tt1.*, tt2.* from tt2 right join tt1 on tt1.joincol = tt2.joincol;
693693
reset enable_hashjoin;
694694
reset enable_nestloop;
695695

696+
--
697+
-- regression test for bug #18522 (merge-right-anti-join in inner_unique cases)
698+
--
699+
700+
create temp table tbl_ra(aint unique, bint);
701+
insert into tbl_raselect i, i%100from generate_series(1,1000)i;
702+
createindexon tbl_ra (b);
703+
analyze tbl_ra;
704+
705+
set enable_hashjoin to off;
706+
set enable_nestloop to off;
707+
708+
-- ensure we get a merge right anti join
709+
explain (costs off)
710+
select*from tbl_ra t1
711+
where not exists (select1from tbl_ra t2wheret2.b=t1.a)andt1.b<2;
712+
713+
-- and check we get the expected results
714+
select*from tbl_ra t1
715+
where not exists (select1from tbl_ra t2wheret2.b=t1.a)andt1.b<2;
716+
717+
reset enable_hashjoin;
718+
reset enable_nestloop;
719+
696720
--
697721
-- regression test for bug #13908 (hash join with skew tuples & nbatch increase)
698722
--

0 commit comments

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