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); 452insert into inner_text values ('123', '456'); 453 454select * from outer_text where (f1, f2) not in (select * from inner_text); 455 456-- 457-- Another test case for cross-type hashed subplans: comparison of 458-- inner-side values must be done with appropriate operator 459-- 460 461explain (verbose, costs off) 462select 'foo'::text in (select 'bar'::name union all select 'bar'::name); 463 464select 'foo'::text in (select 'bar'::name union all select 'bar'::name); 465 466-- 467-- Test case for premature memory release during hashing of subplan output 468-- 469 470select '1'::text in (select '1'::name union all select '1'::name); 471 472-- 473-- Test that we don't try to use a hashed subplan if the simplified 474-- testexpr isn't of the right shape 475-- 476 477-- this fails by default, of course 478select * from int8_tbl where q1 in (select c1 from inner_text); 479 480begin; 481 482-- make an operator to allow it to succeed 483create function bogus_int8_text_eq(int8, text) returns boolean 484language sql as 'select $1::text = $2'; 485 486create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text); 487 488explain (costs off) 489select * from int8_tbl where q1 in (select c1 from inner_text); 490select * from int8_tbl where q1 in (select c1 from inner_text); 491 492-- inlining of this function results in unusual number of hash clauses, 493-- which we can still cope with 494create or replace function bogus_int8_text_eq(int8, text) returns boolean 495language sql as 'select $1::text = $2 and $1::text = $2'; 496 497explain (costs off) 498select * from int8_tbl where q1 in (select c1 from inner_text); 499select * from int8_tbl where q1 in (select c1 from inner_text); 500 501-- inlining of this function causes LHS and RHS to be switched, 502-- which we can't cope with, so hashing should be abandoned 503create or replace function bogus_int8_text_eq(int8, text) returns boolean 504language sql as 'select $2 = $1::text'; 505 506explain (costs off) 507select * from int8_tbl where q1 in (select c1 from inner_text); 508select * from int8_tbl where q1 in (select c1 from inner_text); 509 510rollback; -- to get rid of the bogus operator 511 512-- 513-- Test case for planner bug with nested EXISTS handling 514-- 515select a.thousand from tenk1 a, tenk1 b 516where a.thousand = b.thousand 517 and exists ( select 1 from tenk1 c where b.hundred = c.hundred 518 and not exists ( select 1 from tenk1 d 519 where a.thousand = d.thousand ) ); 520 521-- 522-- Check that nested sub-selects are not pulled up if they contain volatiles 523-- 524explain (verbose, costs off) 525 select x, x from 526 (select (select now()) as x from (values(1),(2)) v(y)) ss; 527explain (verbose, costs off) 528 select x, x from 529 (select (select random()) as x from (values(1),(2)) v(y)) ss; 530explain (verbose, costs off) 531 select x, x from 532 (select (select now() where y=y) as x from (values(1),(2)) v(y)) ss; 533explain (verbose, costs off) 534 select x, x from 535 (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss; 536 537-- 538-- Test rescan of a hashed subplan (the use of random() is to prevent the 539-- sub-select from being pulled up, which would result in not hashing) 540-- 541explain (verbose, costs off) 542select sum(ss.tst::int) from 543 onek o cross join lateral ( 544 select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst, 545 random() as r 546 from onek i where i.unique1 = o.unique1 ) ss 547where o.ten = 0; 548 549select sum(ss.tst::int) from 550 onek o cross join lateral ( 551 select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst, 552 random() as r 553 from onek i where i.unique1 = o.unique1 ) ss 554where o.ten = 0; 555 556-- 557-- Test rescan of a SetOp node 558-- 559explain (costs off) 560select count(*) from 561 onek o cross join lateral ( 562 select * from onek i1 where i1.unique1 = o.unique1 563 except 564 select * from onek i2 where i2.unique1 = o.unique2 565 ) ss 566where o.ten = 1; 567 568select count(*) from 569 onek o cross join lateral ( 570 select * from onek i1 where i1.unique1 = o.unique1 571 except 572 select * from onek i2 where i2.unique1 = o.unique2 573 ) ss 574where o.ten = 1; 575 576-- 577-- Test rescan of a RecursiveUnion node 578-- 579explain (costs off) 580select sum(o.four), sum(ss.a) from 581 onek o cross join lateral ( 582 with recursive x(a) as 583 (select o.four as a 584 union 585 select a + 1 from x 586 where a < 10) 587 select * from x 588 ) ss 589where o.ten = 1; 590 591select sum(o.four), sum(ss.a) from 592 onek o cross join lateral ( 593 with recursive x(a) as 594 (select o.four as a 595 union 596 select a + 1 from x 597 where a < 10) 598 select * from x 599 ) ss 600where o.ten = 1; 601 602-- 603-- Check we don't misoptimize a NOT IN where the subquery returns no rows. 604-- 605create temp table notinouter (a int); 606create temp table notininner (b int not null); 607insert into notinouter values (null), (1); 608 609select * from notinouter where a not in (select b from notininner); 610 611-- 612-- Check we behave sanely in corner case of empty SELECT list (bug #8648) 613-- 614create temp table nocolumns(); 615select exists(select * from nocolumns); 616 617-- 618-- Check behavior with a SubPlan in VALUES (bug #14924) 619-- 620select val.x 621 from generate_series(1,10) as s(i), 622 lateral ( 623 values ((select s.i + 1)), (s.i + 101) 624 ) as val(x) 625where s.i < 10 and (select val.x) < 110; 626 627-- another variant of that (bug #16213) 628explain (verbose, costs off) 629select * from 630(values 631 (3 not in (select * from (values (1), (2)) ss1)), 632 (false) 633) ss; 634 635select * from 636(values 637 (3 not in (select * from (values (1), (2)) ss1)), 638 (false) 639) ss; 640 641-- 642-- Check sane behavior with nested IN SubLinks 643-- 644explain (verbose, costs off) 645select * from int4_tbl where 646 (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in 647 (select ten from tenk1 b); 648select * from int4_tbl where 649 (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in 650 (select ten from tenk1 b); 651 652-- 653-- Check for incorrect optimization when IN subquery contains a SRF 654-- 655explain (verbose, costs off) 656select * from int4_tbl o where (f1, f1) in 657 (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1); 658select * from int4_tbl o where (f1, f1) in 659 (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1); 660 661-- 662-- check for over-optimization of whole-row Var referencing an Append plan 663-- 664select (select q from 665 (select 1,2,3 where f1 > 0 666 union all 667 select 4,5,6.0 where f1 <= 0 668 ) q ) 669from int4_tbl; 670 671-- 672-- Check for sane handling of a lateral reference in a subquery's quals 673-- (most of the complication here is to prevent the test case from being 674-- flattened too much) 675-- 676explain (verbose, costs off) 677select * from 678 int4_tbl i4, 679 lateral ( 680 select i4.f1 > 1 as b, 1 as id 681 from (select random() order by 1) as t1 682 union all 683 select true as b, 2 as id 684 ) as t2 685where b and f1 >= 0; 686 687select * from 688 int4_tbl i4, 689 lateral ( 690 select i4.f1 > 1 as b, 1 as id 691 from (select random() order by 1) as t1 692 union all 693 select true as b, 2 as id 694 ) as t2 695where b and f1 >= 0; 696 697-- 698-- Check that volatile quals aren't pushed down past a DISTINCT: 699-- nextval() should not be called more than the nominal number of times 700-- 701create temp sequence ts1; 702 703select * from 704 (select distinct ten from tenk1) ss 705 where ten < 10 + nextval('ts1') 706 order by 1; 707 708select nextval('ts1'); 709 710-- 711-- Check that volatile quals aren't pushed down past a set-returning function; 712-- while a nonvolatile qual can be, if it doesn't reference the SRF. 713-- 714create function tattle(x int, y int) returns bool 715volatile language plpgsql as $$ 716begin 717 raise notice 'x = %, y = %', x, y; 718 return x > y; 719end$$; 720 721explain (verbose, costs off) 722select * from 723 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 724 where tattle(x, 8); 725 726select * from 727 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 728 where tattle(x, 8); 729 730-- if we pretend it's stable, we get different results: 731alter function tattle(x int, y int) stable; 732 733explain (verbose, costs off) 734select * from 735 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 736 where tattle(x, 8); 737 738select * from 739 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 740 where tattle(x, 8); 741 742-- although even a stable qual should not be pushed down if it references SRF 743explain (verbose, costs off) 744select * from 745 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 746 where tattle(x, u); 747 748select * from 749 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 750 where tattle(x, u); 751 752drop function tattle(x int, y int); 753 754-- 755-- Test that LIMIT can be pushed to SORT through a subquery that just projects 756-- columns. We check for that having happened by looking to see if EXPLAIN 757-- ANALYZE shows that a top-N sort was used. We must suppress or filter away 758-- all the non-invariant parts of the EXPLAIN ANALYZE output. 759-- 760create table sq_limit (pk int primary key, c1 int, c2 int); 761insert into sq_limit values 762 (1, 1, 1), 763 (2, 2, 2), 764 (3, 3, 3), 765 (4, 4, 4), 766 (5, 1, 1), 767 (6, 2, 2), 768 (7, 3, 3), 769 (8, 4, 4); 770 771create function explain_sq_limit() returns setof text language plpgsql as 772$$ 773declare ln text; 774begin 775 for ln in 776 explain (analyze, summary off, timing off, costs off) 777 select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3 778 loop 779 ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); 780 return next ln; 781 end loop; 782end; 783$$; 784 785select * from explain_sq_limit(); 786 787select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3; 788 789drop function explain_sq_limit(); 790 791drop table sq_limit; 792 793-- 794-- Ensure that backward scan direction isn't propagated into 795-- expression subqueries (bug #15336) 796-- 797 798begin; 799 800declare c1 scroll cursor for 801 select * from generate_series(1,4) i 802 where i <> all (values (2),(3)); 803 804move forward all in c1; 805fetch backward all in c1; 806 807commit; 808 809-- 810-- Tests for CTE inlining behavior 811-- 812 813-- Basic subquery that can be inlined 814explain (verbose, costs off) 815with x as (select * from (select f1 from subselect_tbl) ss) 816select * from x where f1 = 1; 817 818-- Explicitly request materialization 819explain (verbose, costs off) 820with x as materialized (select * from (select f1 from subselect_tbl) ss) 821select * from x where f1 = 1; 822 823-- Stable functions are safe to inline 824explain (verbose, costs off) 825with x as (select * from (select f1, now() from subselect_tbl) ss) 826select * from x where f1 = 1; 827 828-- Volatile functions prevent inlining 829explain (verbose, costs off) 830with x as (select * from (select f1, random() from subselect_tbl) ss) 831select * from x where f1 = 1; 832 833-- SELECT FOR UPDATE cannot be inlined 834explain (verbose, costs off) 835with x as (select * from (select f1 from subselect_tbl for update) ss) 836select * from x where f1 = 1; 837 838-- Multiply-referenced CTEs are inlined only when requested 839explain (verbose, costs off) 840with x as (select * from (select f1, now() as n from subselect_tbl) ss) 841select * from x, x x2 where x.n = x2.n; 842 843explain (verbose, costs off) 844with x as not materialized (select * from (select f1, now() as n from subselect_tbl) ss) 845select * from x, x x2 where x.n = x2.n; 846 847-- Multiply-referenced CTEs can't be inlined if they contain outer self-refs 848explain (verbose, costs off) 849with recursive x(a) as 850 ((values ('a'), ('b')) 851 union all 852 (with z as not materialized (select * from x) 853 select z.a || z1.a as a from z cross join z as z1 854 where length(z.a || z1.a) < 5)) 855select * from x; 856 857with recursive x(a) as 858 ((values ('a'), ('b')) 859 union all 860 (with z as not materialized (select * from x) 861 select z.a || z1.a as a from z cross join z as z1 862 where length(z.a || z1.a) < 5)) 863select * from x; 864 865explain (verbose, costs off) 866with recursive x(a) as 867 ((values ('a'), ('b')) 868 union all 869 (with z as not materialized (select * from x) 870 select z.a || z.a as a from z 871 where length(z.a || z.a) < 5)) 872select * from x; 873 874with recursive x(a) as 875 ((values ('a'), ('b')) 876 union all 877 (with z as not materialized (select * from x) 878 select z.a || z.a as a from z 879 where length(z.a || z.a) < 5)) 880select * from x; 881 882-- Check handling of outer references 883explain (verbose, costs off) 884with x as (select * from int4_tbl) 885select * from (with y as (select * from x) select * from y) ss; 886 887explain (verbose, costs off) 888with x as materialized (select * from int4_tbl) 889select * from (with y as (select * from x) select * from y) ss; 890 891-- Ensure that we inline the currect CTE when there are 892-- multiple CTEs with the same name 893explain (verbose, costs off) 894with x as (select 1 as y) 895select * from (with x as (select 2 as y) select * from x) ss; 896 897-- Row marks are not pushed into CTEs 898explain (verbose, costs off) 899with x as (select * from subselect_tbl) 900select * from x for update; 901