2568
-- nested nestloops can require nested PlaceHolderVars
2570
create temp table nt1 (
2575
create temp table nt2 (
2580
foreign key (nt1_id) references nt1(id)
2582
create temp table nt3 (
2586
foreign key (nt2_id) references nt2(id)
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);
2601
(select nt2.*, (nt2.b1 and ss1.a3) AS b3
2604
(select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1
2605
on ss1.id = nt2.nt1_id
2607
on ss2.id = nt3.nt2_id
2608
where nt3.id = 1 and ss2.b3;
2610
-----------------------------------------------
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))
2625
(select nt2.*, (nt2.b1 and ss1.a3) AS b3
2628
(select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1
2629
on ss1.id = nt2.nt1_id
2631
on ss2.id = nt3.nt2_id
2632
where nt3.id = 1 and ss2.b3;
2568
2639
-- test case where a PlaceHolderVar is propagated into a subquery
2570
2641
explain (costs off)