1-- 2-- SUBSELECT 3-- 4 5SELECT 1 AS one WHERE 1 IN (SELECT 1); 6 7SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1); 8 9SELECT 1 AS zero WHERE 1 IN (SELECT 2); 10 11-- Check grammar's handling of extra parens in assorted contexts 12 13SELECT * FROM (SELECT 1 AS x) ss; 14SELECT * FROM ((SELECT 1 AS x)) ss; 15 16(SELECT 2) UNION SELECT 2; 17((SELECT 2)) UNION SELECT 2; 18 19SELECT ((SELECT 2) UNION SELECT 2); 20SELECT (((SELECT 2)) UNION SELECT 2); 21 22SELECT (SELECT ARRAY[1,2,3])[1]; 23SELECT ((SELECT ARRAY[1,2,3]))[2]; 24SELECT (((SELECT ARRAY[1,2,3])))[3]; 25 26-- Set up some simple test tables 27 28CREATE TABLE SUBSELECT_TBL ( 29 f1 integer, 30 f2 integer, 31 f3 float 32); 33 34INSERT INTO SUBSELECT_TBL VALUES (1, 2, 3); 35INSERT INTO SUBSELECT_TBL VALUES (2, 3, 4); 36INSERT INTO SUBSELECT_TBL VALUES (3, 4, 5); 37INSERT INTO SUBSELECT_TBL VALUES (1, 1, 1); 38INSERT INTO SUBSELECT_TBL VALUES (2, 2, 2); 39INSERT INTO SUBSELECT_TBL VALUES (3, 3, 3); 40INSERT INTO SUBSELECT_TBL VALUES (6, 7, 8); 41INSERT INTO SUBSELECT_TBL VALUES (8, 9, NULL); 42 43SELECT '' AS eight, * FROM SUBSELECT_TBL; 44 45-- Uncorrelated subselects 46 47SELECT '' AS two, f1 AS "Constant Select" FROM SUBSELECT_TBL 48 WHERE f1 IN (SELECT 1); 49 50SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL 51 WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL); 52 53SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL 54 WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE 55 f2 IN (SELECT f1 FROM SUBSELECT_TBL)); 56 57SELECT '' AS three, f1, f2 58 FROM SUBSELECT_TBL 59 WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL 60 WHERE f3 IS NOT NULL); 61 62-- Correlated subselects 63 64SELECT '' AS six, f1 AS "Correlated Field", f2 AS "Second Field" 65 FROM SUBSELECT_TBL upper 66 WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f1 = upper.f1); 67 68SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" 69 FROM SUBSELECT_TBL upper 70 WHERE f1 IN 71 (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(upper.f2 AS float) = f3); 72 73SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" 74 FROM SUBSELECT_TBL upper 75 WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL 76 WHERE f2 = CAST(f3 AS integer)); 77 78SELECT '' AS five, f1 AS "Correlated Field" 79 FROM SUBSELECT_TBL 80 WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL 81 WHERE f3 IS NOT NULL); 82 83-- 84-- Use some existing tables in the regression test 85-- 86 87SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field" 88 FROM SUBSELECT_TBL ss 89 WHERE f1 NOT IN (SELECT f1+1 FROM INT4_TBL 90 WHERE f1 != ss.f1 AND f1 < 2147483647); 91 92select q1, float8(count(*)) / (select count(*) from int8_tbl) 93from int8_tbl group by q1 order by q1; 94 95-- Unspecified-type literals in output columns should resolve as text 96 97SELECT *, pg_typeof(f1) FROM 98 (SELECT 'foo' AS f1 FROM generate_series(1,3)) ss ORDER BY 1; 99 100-- ... unless there's context to suggest differently 101 102explain (verbose, costs off) select '42' union all select '43'; 103explain (verbose, costs off) select '42' union all select 43; 104 105-- check materialization of an initplan reference (bug #14524) 106explain (verbose, costs off) 107select 1 = all (select (select 1)); 108select 1 = all (select (select 1)); 109 110-- 111-- Check EXISTS simplification with LIMIT 112-- 113explain (costs off) 114select * from int4_tbl o where exists 115 (select 1 from int4_tbl i where i.f1=o.f1 limit null); 116explain (costs off) 117select * from int4_tbl o where not exists 118 (select 1 from int4_tbl i where i.f1=o.f1 limit 1); 119explain (costs off) 120select * from int4_tbl o where exists 121 (select 1 from int4_tbl i where i.f1=o.f1 limit 0); 122 123-- 124-- Test cases to catch unpleasant interactions between IN-join processing 125-- and subquery pullup. 126-- 127 128select count(*) from 129 (select 1 from tenk1 a 130 where unique1 IN (select hundred from tenk1 b)) ss; 131select count(distinct ss.ten) from 132 (select ten from tenk1 a 133 where unique1 IN (select hundred from tenk1 b)) ss; 134select count(*) from 135 (select 1 from tenk1 a 136 where unique1 IN (select distinct hundred from tenk1 b)) ss; 137select count(distinct ss.ten) from 138 (select ten from tenk1 a 139 where unique1 IN (select distinct hundred from tenk1 b)) ss; 140 141-- 142-- Test cases to check for overenthusiastic optimization of 143-- "IN (SELECT DISTINCT ...)" and related cases. Per example from 144-- Luca Pireddu and Michael Fuhr. 145-- 146 147CREATE TEMP TABLE foo (id integer); 148CREATE TEMP TABLE bar (id1 integer, id2 integer); 149 150INSERT INTO foo VALUES (1); 151 152INSERT INTO bar VALUES (1, 1); 153INSERT INTO bar VALUES (2, 2); 154INSERT INTO bar VALUES (3, 1); 155 156-- These cases require an extra level of distinct-ing above subquery s 157SELECT * FROM foo WHERE id IN 158 (SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM bar) AS s); 159SELECT * FROM foo WHERE id IN 160 (SELECT id2 FROM (SELECT id1,id2 FROM bar GROUP BY id1,id2) AS s); 161SELECT * FROM foo WHERE id IN 162 (SELECT id2 FROM (SELECT id1, id2 FROM bar UNION 163 SELECT id1, id2 FROM bar) AS s); 164 165-- These cases do not 166SELECT * FROM foo WHERE id IN 167 (SELECT id2 FROM (SELECT DISTINCT ON (id2) id1, id2 FROM bar) AS s); 168SELECT * FROM foo WHERE id IN 169 (SELECT id2 FROM (SELECT id2 FROM bar GROUP BY id2) AS s); 170SELECT * FROM foo WHERE id IN 171 (SELECT id2 FROM (SELECT id2 FROM bar UNION 172 SELECT id2 FROM bar) AS s); 173 174-- 175-- Test case to catch problems with multiply nested sub-SELECTs not getting 176-- recalculated properly. Per bug report from Didier Moens. 177-- 178 179CREATE TABLE orderstest ( 180 approver_ref integer, 181 po_ref integer, 182 ordercanceled boolean 183); 184 185INSERT INTO orderstest VALUES (1, 1, false); 186INSERT INTO orderstest VALUES (66, 5, false); 187INSERT INTO orderstest VALUES (66, 6, false); 188INSERT INTO orderstest VALUES (66, 7, false); 189INSERT INTO orderstest VALUES (66, 1, true); 190INSERT INTO orderstest VALUES (66, 8, false); 191INSERT INTO orderstest VALUES (66, 1, false); 192INSERT INTO orderstest VALUES (77, 1, false); 193INSERT INTO orderstest VALUES (1, 1, false); 194INSERT INTO orderstest VALUES (66, 1, false); 195INSERT INTO orderstest VALUES (1, 1, false); 196 197CREATE VIEW orders_view AS 198SELECT *, 199(SELECT CASE 200 WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved' 201 END) AS "Approved", 202(SELECT CASE 203 WHEN ord.ordercanceled 204 THEN 'Canceled' 205 ELSE 206 (SELECT CASE 207 WHEN ord.po_ref=1 208 THEN 209 (SELECT CASE 210 WHEN ord.approver_ref=1 211 THEN '---' 212 ELSE 'Approved' 213 END) 214 ELSE 'PO' 215 END) 216END) AS "Status", 217(CASE 218 WHEN ord.ordercanceled 219 THEN 'Canceled' 220 ELSE 221 (CASE 222 WHEN ord.po_ref=1 223 THEN 224 (CASE 225 WHEN ord.approver_ref=1 226 THEN '---' 227 ELSE 'Approved' 228 END) 229 ELSE 'PO' 230 END) 231END) AS "Status_OK" 232FROM orderstest ord; 233 234SELECT * FROM orders_view; 235 236DROP TABLE orderstest cascade; 237 238-- 239-- Test cases to catch situations where rule rewriter fails to propagate 240-- hasSubLinks flag correctly. Per example from Kyle Bateman. 241-- 242 243create temp table parts ( 244 partnum text, 245 cost float8 246); 247 248create temp table shipped ( 249 ttype char(2), 250 ordnum int4, 251 partnum text, 252 value float8 253); 254 255create temp view shipped_view as 256 select * from shipped where ttype = 'wt'; 257 258create rule shipped_view_insert as on insert to shipped_view do instead 259 insert into shipped values('wt', new.ordnum, new.partnum, new.value); 260 261insert into parts (partnum, cost) values (1, 1234.56); 262 263insert into shipped_view (ordnum, partnum, value) 264 values (0, 1, (select cost from parts where partnum = '1')); 265 266select * from shipped_view; 267 268create rule shipped_view_update as on update to shipped_view do instead 269 update shipped set partnum = new.partnum, value = new.value 270 where ttype = new.ttype and ordnum = new.ordnum; 271 272update shipped_view set value = 11 273 from int4_tbl a join int4_tbl b 274 on (a.f1 = (select f1 from int4_tbl c where c.f1=b.f1)) 275 where ordnum = a.f1; 276 277select * from shipped_view; 278 279select f1, ss1 as relabel from 280 (select *, (select sum(f1) from int4_tbl b where f1 >= a.f1) as ss1 281 from int4_tbl a) ss; 282 283-- 284-- Test cases involving PARAM_EXEC parameters and min/max index optimizations. 285-- Per bug report from David Sanchez i Gregori. 286-- 287 288select * from ( 289 select max(unique1) from tenk1 as a 290 where exists (select 1 from tenk1 as b where b.thousand = a.unique2) 291) ss; 292 293select * from ( 294 select min(unique1) from tenk1 as a 295 where not exists (select 1 from tenk1 as b where b.unique2 = 10000) 296) ss; 297 298-- 299-- Test that an IN implemented using a UniquePath does unique-ification 300-- with the right semantics, as per bug #4113. (Unfortunately we have 301-- no simple way to ensure that this test case actually chooses that type 302-- of plan, but it does in releases 7.4-8.3. Note that an ordering difference 303-- here might mean that some other plan type is being used, rendering the test 304-- pointless.) 305-- 306 307create temp table numeric_table (num_col numeric); 308insert into numeric_table values (1), (1.000000000000000000001), (2), (3); 309 310create temp table float_table (float_col float8); 311insert into float_table values (1), (2), (3); 312 313select * from float_table 314 where float_col in (select num_col from numeric_table); 315 316select * from numeric_table 317 where num_col in (select float_col from float_table); 318 319-- 320-- Test case for bug #4290: bogus calculation of subplan param sets 321-- 322 323create temp table ta (id int primary key, val int); 324 325insert into ta values(1,1); 326insert into ta values(2,2); 327 328create temp table tb (id int primary key, aval int); 329 330insert into tb values(1,1); 331insert into tb values(2,1); 332insert into tb values(3,2); 333insert into tb values(4,2); 334 335create temp table tc (id int primary key, aid int); 336 337insert into tc values(1,1); 338insert into tc values(2,2); 339 340select 341 ( select min(tb.id) from tb 342 where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_id 343from tc; 344 345-- 346-- Test case for 8.3 "failed to locate grouping columns" bug 347-- 348 349create temp table t1 (f1 numeric(14,0), f2 varchar(30)); 350 351select * from 352 (select distinct f1, f2, (select f2 from t1 x where x.f1 = up.f1) as fs 353 from t1 up) ss 354group by f1,f2,fs; 355 356-- 357-- Test case for bug #5514 (mishandling of whole-row Vars in subselects) 358-- 359 360create temp table table_a(id integer); 361insert into table_a values (42); 362 363create temp view view_a as select * from table_a; 364 365select view_a from view_a; 366select (select view_a) from view_a; 367select (select (select view_a)) from view_a; 368select (select (a.*)::text) from view_a a; 369 370-- 371-- Check that whole-row Vars reading the result of a subselect don't include 372-- any junk columns therein 373-- 374 375select q from (select max(f1) from int4_tbl group by f1 order by f1) q; 376with q as (select max(f1) from int4_tbl group by f1 order by f1) 377 select q from q; 378 379-- 380-- Test case for sublinks pulled up into joinaliasvars lists in an 381-- inherited update/delete query 382-- 383 384begin; -- this shouldn't delete anything, but be safe 385 386delete from road 387where exists ( 388 select 1 389 from 390 int4_tbl cross join 391 ( select f1, array(select q1 from int8_tbl) as arr 392 from text_tbl ) ss 393 where road.name = ss.f1 ); 394 395rollback; 396 397-- 398-- Test case for sublinks pushed down into subselects via join alias expansion 399-- 400 401select 402 (select sq1) as qq1 403from 404 (select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy 405 from int8_tbl) sq0 406 join 407 int4_tbl i4 on dummy = i4.f1; 408 409-- 410-- Test case for subselect within UPDATE of INSERT...ON CONFLICT DO UPDATE 411-- 412create temp table upsert(key int4 primary key, val text); 413insert into upsert values(1, 'val') on conflict (key) do update set val = 'not seen'; 414insert into upsert values(1, 'val') on conflict (key) do update set val = 'seen with subselect ' || (select f1 from int4_tbl where f1 != 0 limit 1)::text; 415 416select * from upsert; 417 418with aa as (select 'int4_tbl' u from int4_tbl limit 1) 419insert into upsert values (1, 'x'), (999, 'y') 420on conflict (key) do update set val = (select u from aa) 421returning *; 422 423-- 424-- Test case for cross-type partial matching in hashed subplan (bug #7597) 425-- 426 427create temp table outer_7597 (f1 int4, f2 int4); 428insert into outer_7597 values (0, 0); 429insert into outer_7597 values (1, 0); 430insert into outer_7597 values (0, null); 431insert into outer_7597 values (1, null); 432 433create temp table inner_7597(c1 int8, c2 int8); 434insert into inner_7597 values(0, null); 435 436select * from outer_7597 where (f1, f2) not in (select * from inner_7597); 437 438-- 439-- Similar test case using text that verifies that collation 440-- information is passed through by execTuplesEqual() in nodeSubplan.c 441-- (otherwise it would error in texteq()) 442-- 443 444create temp table outer_text (f1 text, f2 text); 445insert into outer_text values ('a', 'a'); 446insert into outer_text values ('b', 'a'); 447insert into outer_text values ('a', null); 448insert into outer_text values ('b', null); 449 450create temp table inner_text (c1 text, c2 text); 451insert into inner_text values ('a', null); 452 453select * from outer_text where (f1, f2) not in (select * from inner_text); 454 455-- 456-- Test case for premature memory release during hashing of subplan output 457-- 458 459select '1'::text in (select '1'::name union all select '1'::name); 460 461-- 462-- Test case for planner bug with nested EXISTS handling 463-- 464select a.thousand from tenk1 a, tenk1 b 465where a.thousand = b.thousand 466 and exists ( select 1 from tenk1 c where b.hundred = c.hundred 467 and not exists ( select 1 from tenk1 d 468 where a.thousand = d.thousand ) ); 469 470-- 471-- Check that nested sub-selects are not pulled up if they contain volatiles 472-- 473explain (verbose, costs off) 474 select x, x from 475 (select (select now()) as x from (values(1),(2)) v(y)) ss; 476explain (verbose, costs off) 477 select x, x from 478 (select (select random()) as x from (values(1),(2)) v(y)) ss; 479explain (verbose, costs off) 480 select x, x from 481 (select (select now() where y=y) as x from (values(1),(2)) v(y)) ss; 482explain (verbose, costs off) 483 select x, x from 484 (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss; 485 486-- 487-- Check we don't misoptimize a NOT IN where the subquery returns no rows. 488-- 489create temp table notinouter (a int); 490create temp table notininner (b int not null); 491insert into notinouter values (null), (1); 492 493select * from notinouter where a not in (select b from notininner); 494 495-- 496-- Check we behave sanely in corner case of empty SELECT list (bug #8648) 497-- 498create temp table nocolumns(); 499select exists(select * from nocolumns); 500 501-- 502-- Check behavior with a SubPlan in VALUES (bug #14924) 503-- 504select val.x 505 from generate_series(1,10) as s(i), 506 lateral ( 507 values ((select s.i + 1)), (s.i + 101) 508 ) as val(x) 509where s.i < 10 and (select val.x) < 110; 510 511-- 512-- Check sane behavior with nested IN SubLinks 513-- 514explain (verbose, costs off) 515select * from int4_tbl where 516 (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in 517 (select ten from tenk1 b); 518select * from int4_tbl where 519 (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in 520 (select ten from tenk1 b); 521 522-- 523-- Check for incorrect optimization when IN subquery contains a SRF 524-- 525explain (verbose, costs off) 526select * from int4_tbl o where (f1, f1) in 527 (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1); 528select * from int4_tbl o where (f1, f1) in 529 (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1); 530 531-- 532-- check for over-optimization of whole-row Var referencing an Append plan 533-- 534select (select q from 535 (select 1,2,3 where f1 > 0 536 union all 537 select 4,5,6.0 where f1 <= 0 538 ) q ) 539from int4_tbl; 540 541-- 542-- Check that volatile quals aren't pushed down past a DISTINCT: 543-- nextval() should not be called more than the nominal number of times 544-- 545create temp sequence ts1; 546 547select * from 548 (select distinct ten from tenk1) ss 549 where ten < 10 + nextval('ts1') 550 order by 1; 551 552select nextval('ts1'); 553 554-- 555-- Check that volatile quals aren't pushed down past a set-returning function; 556-- while a nonvolatile qual can be, if it doesn't reference the SRF. 557-- 558create function tattle(x int, y int) returns bool 559volatile language plpgsql as $$ 560begin 561 raise notice 'x = %, y = %', x, y; 562 return x > y; 563end$$; 564 565explain (verbose, costs off) 566select * from 567 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 568 where tattle(x, 8); 569 570select * from 571 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 572 where tattle(x, 8); 573 574-- if we pretend it's stable, we get different results: 575alter function tattle(x int, y int) stable; 576 577explain (verbose, costs off) 578select * from 579 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 580 where tattle(x, 8); 581 582select * from 583 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 584 where tattle(x, 8); 585 586-- although even a stable qual should not be pushed down if it references SRF 587explain (verbose, costs off) 588select * from 589 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 590 where tattle(x, u); 591 592select * from 593 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 594 where tattle(x, u); 595 596drop function tattle(x int, y int); 597 598-- 599-- Test that LIMIT can be pushed to SORT through a subquery that just projects 600-- columns. We check for that having happened by looking to see if EXPLAIN 601-- ANALYZE shows that a top-N sort was used. We must suppress or filter away 602-- all the non-invariant parts of the EXPLAIN ANALYZE output. 603-- 604create table sq_limit (pk int primary key, c1 int, c2 int); 605insert into sq_limit values 606 (1, 1, 1), 607 (2, 2, 2), 608 (3, 3, 3), 609 (4, 4, 4), 610 (5, 1, 1), 611 (6, 2, 2), 612 (7, 3, 3), 613 (8, 4, 4); 614 615create function explain_sq_limit() returns setof text language plpgsql as 616$$ 617declare ln text; 618begin 619 for ln in 620 explain (analyze, summary off, timing off, costs off) 621 select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3 622 loop 623 ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); 624 -- this case might occur if force_parallel_mode is on: 625 ln := regexp_replace(ln, 'Worker 0: Sort Method', 'Sort Method'); 626 return next ln; 627 end loop; 628end; 629$$; 630 631select * from explain_sq_limit(); 632 633select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3; 634 635drop function explain_sq_limit(); 636 637drop table sq_limit; 638 639-- 640-- Ensure that backward scan direction isn't propagated into 641-- expression subqueries (bug #15336) 642-- 643 644begin; 645 646declare c1 scroll cursor for 647 select * from generate_series(1,4) i 648 where i <> all (values (2),(3)); 649 650move forward all in c1; 651fetch backward all in c1; 652 653commit; 654 655-- 656-- Tests for CTE inlining behavior 657-- 658 659-- Basic subquery that can be inlined 660explain (verbose, costs off) 661with x as (select * from (select f1 from subselect_tbl) ss) 662select * from x where f1 = 1; 663 664-- Explicitly request materialization 665explain (verbose, costs off) 666with x as materialized (select * from (select f1 from subselect_tbl) ss) 667select * from x where f1 = 1; 668 669-- Stable functions are safe to inline 670explain (verbose, costs off) 671with x as (select * from (select f1, now() from subselect_tbl) ss) 672select * from x where f1 = 1; 673 674-- Volatile functions prevent inlining 675explain (verbose, costs off) 676with x as (select * from (select f1, random() from subselect_tbl) ss) 677select * from x where f1 = 1; 678 679-- SELECT FOR UPDATE cannot be inlined 680explain (verbose, costs off) 681with x as (select * from (select f1 from subselect_tbl for update) ss) 682select * from x where f1 = 1; 683 684-- Multiply-referenced CTEs are inlined only when requested 685explain (verbose, costs off) 686with x as (select * from (select f1, now() as n from subselect_tbl) ss) 687select * from x, x x2 where x.n = x2.n; 688 689explain (verbose, costs off) 690with x as not materialized (select * from (select f1, now() as n from subselect_tbl) ss) 691select * from x, x x2 where x.n = x2.n; 692 693-- Multiply-referenced CTEs can't be inlined if they contain outer self-refs 694explain (verbose, costs off) 695with recursive x(a) as 696 ((values ('a'), ('b')) 697 union all 698 (with z as not materialized (select * from x) 699 select z.a || z1.a as a from z cross join z as z1 700 where length(z.a || z1.a) < 5)) 701select * from x; 702 703with recursive x(a) as 704 ((values ('a'), ('b')) 705 union all 706 (with z as not materialized (select * from x) 707 select z.a || z1.a as a from z cross join z as z1 708 where length(z.a || z1.a) < 5)) 709select * from x; 710 711explain (verbose, costs off) 712with recursive x(a) as 713 ((values ('a'), ('b')) 714 union all 715 (with z as not materialized (select * from x) 716 select z.a || z.a as a from z 717 where length(z.a || z.a) < 5)) 718select * from x; 719 720with recursive x(a) as 721 ((values ('a'), ('b')) 722 union all 723 (with z as not materialized (select * from x) 724 select z.a || z.a as a from z 725 where length(z.a || z.a) < 5)) 726select * from x; 727 728-- Check handling of outer references 729explain (verbose, costs off) 730with x as (select * from int4_tbl) 731select * from (with y as (select * from x) select * from y) ss; 732 733explain (verbose, costs off) 734with x as materialized (select * from int4_tbl) 735select * from (with y as (select * from x) select * from y) ss; 736 737-- Ensure that we inline the currect CTE when there are 738-- multiple CTEs with the same name 739explain (verbose, costs off) 740with x as (select 1 as y) 741select * from (with x as (select 2 as y) select * from x) ss; 742 743-- Row marks are not pushed into CTEs 744explain (verbose, costs off) 745with x as (select * from subselect_tbl) 746select * from x for update; 747