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

Commitab76208

Browse files
committed
Forward-port regression test for bug #10587 into 9.3 and HEAD.
Although this bug is already fixed in post-9.2 branches, the casetriggering it is quite different from what was under considerationat the time. It seems worth memorializing this example in HEADjust to make sure it doesn't get broken again in future.Extracted from commit187ae17.
1 parentc170655 commitab76208

File tree

2 files changed

+129
-0
lines changed

2 files changed

+129
-0
lines changed

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

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2564,6 +2564,77 @@ SELECT qq, unique1
25642564
456 | 7318
25652565
(3 rows)
25662566

2567+
--
2568+
-- nested nestloops can require nested PlaceHolderVars
2569+
--
2570+
create temp table nt1 (
2571+
id int primary key,
2572+
a1 boolean,
2573+
a2 boolean
2574+
);
2575+
create temp table nt2 (
2576+
id int primary key,
2577+
nt1_id int,
2578+
b1 boolean,
2579+
b2 boolean,
2580+
foreign key (nt1_id) references nt1(id)
2581+
);
2582+
create temp table nt3 (
2583+
id int primary key,
2584+
nt2_id int,
2585+
c1 boolean,
2586+
foreign key (nt2_id) references nt2(id)
2587+
);
2588+
insert into nt1 values (1,true,true);
2589+
insert into nt1 values (2,true,false);
2590+
insert into nt1 values (3,false,false);
2591+
insert into nt2 values (1,1,true,true);
2592+
insert into nt2 values (2,2,true,false);
2593+
insert into nt2 values (3,3,false,false);
2594+
insert into nt3 values (1,1,true);
2595+
insert into nt3 values (2,2,false);
2596+
insert into nt3 values (3,3,true);
2597+
explain (costs off)
2598+
select nt3.id
2599+
from nt3 as nt3
2600+
left join
2601+
(select nt2.*, (nt2.b1 and ss1.a3) AS b3
2602+
from nt2 as nt2
2603+
left join
2604+
(select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1
2605+
on ss1.id = nt2.nt1_id
2606+
) as ss2
2607+
on ss2.id = nt3.nt2_id
2608+
where nt3.id = 1 and ss2.b3;
2609+
QUERY PLAN
2610+
-----------------------------------------------
2611+
Nested Loop
2612+
-> Nested Loop
2613+
-> Index Scan using nt3_pkey on nt3
2614+
Index Cond: (id = 1)
2615+
-> Index Scan using nt2_pkey on nt2
2616+
Index Cond: (id = nt3.nt2_id)
2617+
-> Index Only Scan using nt1_pkey on nt1
2618+
Index Cond: (id = nt2.nt1_id)
2619+
Filter: (nt2.b1 AND (id IS NOT NULL))
2620+
(9 rows)
2621+
2622+
select nt3.id
2623+
from nt3 as nt3
2624+
left join
2625+
(select nt2.*, (nt2.b1 and ss1.a3) AS b3
2626+
from nt2 as nt2
2627+
left join
2628+
(select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1
2629+
on ss1.id = nt2.nt1_id
2630+
) as ss2
2631+
on ss2.id = nt3.nt2_id
2632+
where nt3.id = 1 and ss2.b3;
2633+
id
2634+
----
2635+
1
2636+
(1 row)
2637+
25672638
--
25682639
-- test case where a PlaceHolderVar is propagated into a subquery
25692640
--

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

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -660,6 +660,64 @@ SELECT qq, unique1
660660
USING (qq)
661661
INNER JOIN tenk1 cON qq= unique2;
662662

663+
--
664+
-- nested nestloops can require nested PlaceHolderVars
665+
--
666+
667+
create temp table nt1 (
668+
idintprimary key,
669+
a1boolean,
670+
a2boolean
671+
);
672+
create temp table nt2 (
673+
idintprimary key,
674+
nt1_idint,
675+
b1boolean,
676+
b2boolean,
677+
foreign key (nt1_id)references nt1(id)
678+
);
679+
create temp table nt3 (
680+
idintprimary key,
681+
nt2_idint,
682+
c1boolean,
683+
foreign key (nt2_id)references nt2(id)
684+
);
685+
686+
insert into nt1values (1,true,true);
687+
insert into nt1values (2,true,false);
688+
insert into nt1values (3,false,false);
689+
insert into nt2values (1,1,true,true);
690+
insert into nt2values (2,2,true,false);
691+
insert into nt2values (3,3,false,false);
692+
insert into nt3values (1,1,true);
693+
insert into nt3values (2,2,false);
694+
insert into nt3values (3,3,true);
695+
696+
explain (costs off)
697+
selectnt3.id
698+
from nt3as nt3
699+
left join
700+
(select nt2.*, (nt2.b1andss1.a3)AS b3
701+
from nt2as nt2
702+
left join
703+
(select nt1.*, (nt1.idis not null)as a3from nt1)as ss1
704+
onss1.id=nt2.nt1_id
705+
)as ss2
706+
onss2.id=nt3.nt2_id
707+
wherent3.id=1andss2.b3;
708+
709+
selectnt3.id
710+
from nt3as nt3
711+
left join
712+
(select nt2.*, (nt2.b1andss1.a3)AS b3
713+
from nt2as nt2
714+
left join
715+
(select nt1.*, (nt1.idis not null)as a3from nt1)as ss1
716+
onss1.id=nt2.nt1_id
717+
)as ss2
718+
onss2.id=nt3.nt2_id
719+
wherent3.id=1andss2.b3;
720+
663721
--
664722
-- test case where a PlaceHolderVar is propagated into a subquery
665723
--

0 commit comments

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