🌐 AI搜索 & 代理 主页
Skip to content

Commit ab76208

Browse files
tglsfdctglsfdc
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 case triggering it is quite different from what was under consideration at the time. It seems worth memorializing this example in HEAD just to make sure it doesn't get broken again in future. Extracted from commit 187ae17.
1 parent c170655 commit ab76208

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 c ON qq = unique2;
662662

663+
--
664+
-- nested nestloops can require nested PlaceHolderVars
665+
--
666+
667+
create temp table nt1 (
668+
id int primary key,
669+
a1 boolean,
670+
a2 boolean
671+
);
672+
create temp table nt2 (
673+
id int primary key,
674+
nt1_id int,
675+
b1 boolean,
676+
b2 boolean,
677+
foreign key (nt1_id) references nt1(id)
678+
);
679+
create temp table nt3 (
680+
id int primary key,
681+
nt2_id int,
682+
c1 boolean,
683+
foreign key (nt2_id) references nt2(id)
684+
);
685+
686+
insert into nt1 values (1,true,true);
687+
insert into nt1 values (2,true,false);
688+
insert into nt1 values (3,false,false);
689+
insert into nt2 values (1,1,true,true);
690+
insert into nt2 values (2,2,true,false);
691+
insert into nt2 values (3,3,false,false);
692+
insert into nt3 values (1,1,true);
693+
insert into nt3 values (2,2,false);
694+
insert into nt3 values (3,3,true);
695+
696+
explain (costs off)
697+
select nt3.id
698+
from nt3 as nt3
699+
left join
700+
(select nt2.*, (nt2.b1 and ss1.a3) AS b3
701+
from nt2 as nt2
702+
left join
703+
(select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1
704+
on ss1.id = nt2.nt1_id
705+
) as ss2
706+
on ss2.id = nt3.nt2_id
707+
where nt3.id = 1 and ss2.b3;
708+
709+
select nt3.id
710+
from nt3 as nt3
711+
left join
712+
(select nt2.*, (nt2.b1 and ss1.a3) AS b3
713+
from nt2 as nt2
714+
left join
715+
(select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1
716+
on ss1.id = nt2.nt1_id
717+
) as ss2
718+
on ss2.id = nt3.nt2_id
719+
where nt3.id = 1 and ss2.b3;
720+
663721
--
664722
-- test case where a PlaceHolderVar is propagated into a subquery
665723
--

0 commit comments

Comments
 (0)