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

Commitd8f3351

Browse files
author
Richard Guo
committed
Improve the test case from5668a85
In commit5668a85, we fixed an issue with incorrect results in rightsemi joins and introduced a test case to verify the fix. The testcase involves SubPlans and InitPlans, which may not be immediatelyapparent in relation to the issue we addressed.This patch simplifies the test case with a more straightforward query.Per discussion with Melanie Plageman.Author: Richard GuoDiscussion:https://postgr.es/m/CAAKRu_a-Cip2XCXp13fmxq+T9BhLAVApHTyjr94awL2mbXHC-Q@mail.gmail.com
1 parent0172b4c commitd8f3351

File tree

2 files changed

+40
-73
lines changed

2 files changed

+40
-73
lines changed

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

Lines changed: 30 additions & 54 deletions
Original file line numberDiff line numberDiff line change
@@ -3041,63 +3041,39 @@ reset enable_nestloop;
30413041
create temp table tbl_rs(a int, b int);
30423042
insert into tbl_rs select i, i from generate_series(1,10)i;
30433043
analyze tbl_rs;
3044-
set enable_nestloop to off;
3045-
set enable_hashagg to off;
3046-
-- ensure we get a hash right semi join with SubPlan in hash clauses
3047-
explain (costs off)
3048-
select * from tbl_rs t1
3049-
where (select a from tbl_rs t2
3050-
where exists (select 1 from
3051-
(select (b in (select b from tbl_rs t3)) as c from tbl_rs t4 where t4.a = 1) s
3052-
where c in (select t1.a = 1 from tbl_rs t5 union all select true))
3053-
order by a limit 1) >= 0;
3054-
QUERY PLAN
3055-
--------------------------------------------------------------------------------------
3056-
Seq Scan on tbl_rs t1
3057-
Filter: ((SubPlan 3) >= 0)
3058-
SubPlan 3
3059-
-> Limit
3060-
InitPlan 2
3061-
-> Hash Right Semi Join
3062-
Hash Cond: (((t1.a = 1)) = (ANY (t4.b = (hashed SubPlan 1).col1)))
3063-
-> Append
3064-
-> Seq Scan on tbl_rs t5
3065-
-> Result
3066-
-> Hash
3067-
-> Seq Scan on tbl_rs t4
3068-
Filter: (a = 1)
3069-
SubPlan 1
3070-
-> Seq Scan on tbl_rs t3
3071-
-> Sort
3072-
Sort Key: t2.a
3073-
-> Result
3074-
One-Time Filter: (InitPlan 2).col1
3075-
-> Seq Scan on tbl_rs t2
3076-
(20 rows)
3044+
-- ensure we get a hash right semi join
3045+
explain (costs off)
3046+
select * from tbl_rs t1 join
3047+
lateral (select * from tbl_rs t2 where t2.a in
3048+
(select t1.a+t3.a from tbl_rs t3) and t2.a < 5)
3049+
on true;
3050+
QUERY PLAN
3051+
-------------------------------------------
3052+
Nested Loop
3053+
-> Seq Scan on tbl_rs t1
3054+
-> Hash Right Semi Join
3055+
Hash Cond: ((t1.a + t3.a) = t2.a)
3056+
-> Seq Scan on tbl_rs t3
3057+
-> Hash
3058+
-> Seq Scan on tbl_rs t2
3059+
Filter: (a < 5)
3060+
(8 rows)
30773061

30783062
-- and check we get the expected results
3079-
select * from tbl_rs t1
3080-
where (select a from tbl_rs t2
3081-
where exists (select 1 from
3082-
(select (b in (select b from tbl_rs t3)) as c from tbl_rs t4 where t4.a = 1) s
3083-
where c in (select t1.a = 1 from tbl_rs t5 union all select true))
3084-
order by a limit 1) >= 0;
3085-
a | b
3086-
----+----
3087-
1 | 1
3088-
2 | 2
3089-
3 | 3
3090-
4 | 4
3091-
5 | 5
3092-
6 | 6
3093-
7 | 7
3094-
8 | 8
3095-
9 | 9
3096-
10 | 10
3097-
(10 rows)
3063+
select * from tbl_rs t1 join
3064+
lateral (select * from tbl_rs t2 where t2.a in
3065+
(select t1.a+t3.a from tbl_rs t3) and t2.a < 5)
3066+
on true;
3067+
a | b | a | b
3068+
---+---+---+---
3069+
1 | 1 | 2 | 2
3070+
1 | 1 | 3 | 3
3071+
1 | 1 | 4 | 4
3072+
2 | 2 | 3 | 3
3073+
2 | 2 | 4 | 4
3074+
3 | 3 | 4 | 4
3075+
(6 rows)
30983076

3099-
reset enable_nestloop;
3100-
reset enable_hashagg;
31013077
--
31023078
-- regression test for bug #13908 (hash join with skew tuples & nbatch increase)
31033079
--

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

Lines changed: 10 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -741,32 +741,23 @@ reset enable_nestloop;
741741
--
742742
-- regression test for bug with hash-right-semi join
743743
--
744+
744745
create temp table tbl_rs(aint, bint);
745746
insert into tbl_rsselect i, ifrom generate_series(1,10)i;
746747
analyze tbl_rs;
747748

748-
set enable_nestloop to off;
749-
set enable_hashagg to off;
750-
751-
-- ensure we get a hash right semi join with SubPlan in hash clauses
749+
-- ensure we get a hash right semi join
752750
explain (costs off)
753-
select*from tbl_rs t1
754-
where (select afrom tbl_rs t2
755-
where exists (select1from
756-
(select (bin (select bfrom tbl_rs t3))as cfrom tbl_rs t4wheret4.a=1) s
757-
where cin (selectt1.a=1from tbl_rs t5union allselect true))
758-
order by alimit1)>=0;
751+
select*from tbl_rs t1join
752+
lateral (select*from tbl_rs t2wheret2.ain
753+
(selectt1.a+t3.afrom tbl_rs t3)andt2.a<5)
754+
on true;
759755

760756
-- and check we get the expected results
761-
select*from tbl_rs t1
762-
where (select afrom tbl_rs t2
763-
where exists (select1from
764-
(select (bin (select bfrom tbl_rs t3))as cfrom tbl_rs t4wheret4.a=1) s
765-
where cin (selectt1.a=1from tbl_rs t5union allselect true))
766-
order by alimit1)>=0;
767-
768-
reset enable_nestloop;
769-
reset enable_hashagg;
757+
select*from tbl_rs t1join
758+
lateral (select*from tbl_rs t2wheret2.ain
759+
(selectt1.a+t3.afrom tbl_rs t3)andt2.a<5)
760+
on true;
770761

771762
--
772763
-- regression test for bug #13908 (hash join with skew tuples & nbatch increase)

0 commit comments

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