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-- Another test case for cross-type hashed subplans: comparison of 440-- inner-side values must be done with appropriate operator 441-- 442 443explain (verbose, costs off) 444select 'foo'::text in (select 'bar'::name union all select 'bar'::name); 445 446select 'foo'::text in (select 'bar'::name union all select 'bar'::name); 447 448-- 449-- Test case for premature memory release during hashing of subplan output 450-- 451 452select '1'::text in (select '1'::name union all select '1'::name); 453 454-- 455-- Test that we don't try to use a hashed subplan if the simplified 456-- testexpr isn't of the right shape 457-- 458 459create temp table inner_text (c1 text, c2 text); 460insert into inner_text values ('a', null); 461insert into inner_text values ('123', '456'); 462 463-- this fails by default, of course 464select * from int8_tbl where q1 in (select c1 from inner_text); 465 466begin; 467 468-- make an operator to allow it to succeed 469create function bogus_int8_text_eq(int8, text) returns boolean 470language sql as 'select $1::text = $2'; 471 472create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text); 473 474explain (costs off) 475select * from int8_tbl where q1 in (select c1 from inner_text); 476select * from int8_tbl where q1 in (select c1 from inner_text); 477 478-- inlining of this function results in unusual number of hash clauses, 479-- which we can still cope with 480create or replace function bogus_int8_text_eq(int8, text) returns boolean 481language sql as 'select $1::text = $2 and $1::text = $2'; 482 483explain (costs off) 484select * from int8_tbl where q1 in (select c1 from inner_text); 485select * from int8_tbl where q1 in (select c1 from inner_text); 486 487-- inlining of this function causes LHS and RHS to be switched, 488-- which we can't cope with, so hashing should be abandoned 489create or replace function bogus_int8_text_eq(int8, text) returns boolean 490language sql as 'select $2 = $1::text'; 491 492explain (costs off) 493select * from int8_tbl where q1 in (select c1 from inner_text); 494select * from int8_tbl where q1 in (select c1 from inner_text); 495 496rollback; -- to get rid of the bogus operator 497 498-- 499-- Test case for planner bug with nested EXISTS handling 500-- 501select a.thousand from tenk1 a, tenk1 b 502where a.thousand = b.thousand 503 and exists ( select 1 from tenk1 c where b.hundred = c.hundred 504 and not exists ( select 1 from tenk1 d 505 where a.thousand = d.thousand ) ); 506 507-- 508-- Check that nested sub-selects are not pulled up if they contain volatiles 509-- 510explain (verbose, costs off) 511 select x, x from 512 (select (select now()) as x from (values(1),(2)) v(y)) ss; 513explain (verbose, costs off) 514 select x, x from 515 (select (select random()) as x from (values(1),(2)) v(y)) ss; 516explain (verbose, costs off) 517 select x, x from 518 (select (select now() where y=y) as x from (values(1),(2)) v(y)) ss; 519explain (verbose, costs off) 520 select x, x from 521 (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss; 522 523-- 524-- Test rescan of a hashed subplan (the use of random() is to prevent the 525-- sub-select from being pulled up, which would result in not hashing) 526-- 527explain (verbose, costs off) 528select sum(ss.tst::int) from 529 onek o cross join lateral ( 530 select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst, 531 random() as r 532 from onek i where i.unique1 = o.unique1 ) ss 533where o.ten = 0; 534 535select sum(ss.tst::int) from 536 onek o cross join lateral ( 537 select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst, 538 random() as r 539 from onek i where i.unique1 = o.unique1 ) ss 540where o.ten = 0; 541 542-- 543-- Test rescan of a SetOp node 544-- 545explain (costs off) 546select count(*) from 547 onek o cross join lateral ( 548 select * from onek i1 where i1.unique1 = o.unique1 549 except 550 select * from onek i2 where i2.unique1 = o.unique2 551 ) ss 552where o.ten = 1; 553 554select count(*) from 555 onek o cross join lateral ( 556 select * from onek i1 where i1.unique1 = o.unique1 557 except 558 select * from onek i2 where i2.unique1 = o.unique2 559 ) ss 560where o.ten = 1; 561 562-- 563-- Test rescan of a RecursiveUnion node 564-- 565explain (costs off) 566select sum(o.four), sum(ss.a) from 567 onek o cross join lateral ( 568 with recursive x(a) as 569 (select o.four as a 570 union 571 select a + 1 from x 572 where a < 10) 573 select * from x 574 ) ss 575where o.ten = 1; 576 577select sum(o.four), sum(ss.a) from 578 onek o cross join lateral ( 579 with recursive x(a) as 580 (select o.four as a 581 union 582 select a + 1 from x 583 where a < 10) 584 select * from x 585 ) ss 586where o.ten = 1; 587 588-- 589-- Check we behave sanely in corner case of empty SELECT list (bug #8648) 590-- 591create temp table nocolumns(); 592select exists(select * from nocolumns); 593 594-- 595-- Check behavior with a SubPlan in VALUES (bug #14924) 596-- 597select val.x 598 from generate_series(1,10) as s(i), 599 lateral ( 600 values ((select s.i + 1)), (s.i + 101) 601 ) as val(x) 602where s.i < 10 and (select val.x) < 110; 603 604-- another variant of that (bug #16213) 605explain (verbose, costs off) 606select * from 607(values 608 (3 not in (select * from (values (1), (2)) ss1)), 609 (false) 610) ss; 611 612select * from 613(values 614 (3 not in (select * from (values (1), (2)) ss1)), 615 (false) 616) ss; 617 618-- 619-- Check sane behavior with nested IN SubLinks 620-- 621explain (verbose, costs off) 622select * from int4_tbl where 623 (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in 624 (select ten from tenk1 b); 625select * from int4_tbl where 626 (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in 627 (select ten from tenk1 b); 628 629-- 630-- Check for incorrect optimization when IN subquery contains a SRF 631-- 632explain (verbose, costs off) 633select * from int4_tbl o where (f1, f1) in 634 (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1); 635select * from int4_tbl o where (f1, f1) in 636 (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1); 637 638-- 639-- check for over-optimization of whole-row Var referencing an Append plan 640-- 641select (select q from 642 (select 1,2,3 where f1 > 0 643 union all 644 select 4,5,6.0 where f1 <= 0 645 ) q ) 646from int4_tbl; 647 648-- 649-- Check for sane handling of a lateral reference in a subquery's quals 650-- (most of the complication here is to prevent the test case from being 651-- flattened too much) 652-- 653explain (verbose, costs off) 654select * from 655 int4_tbl i4, 656 lateral ( 657 select i4.f1 > 1 as b, 1 as id 658 from (select random() order by 1) as t1 659 union all 660 select true as b, 2 as id 661 ) as t2 662where b and f1 >= 0; 663 664select * from 665 int4_tbl i4, 666 lateral ( 667 select i4.f1 > 1 as b, 1 as id 668 from (select random() order by 1) as t1 669 union all 670 select true as b, 2 as id 671 ) as t2 672where b and f1 >= 0; 673 674-- 675-- Check that volatile quals aren't pushed down past a DISTINCT: 676-- nextval() should not be called more than the nominal number of times 677-- 678create temp sequence ts1; 679 680select * from 681 (select distinct ten from tenk1) ss 682 where ten < 10 + nextval('ts1') 683 order by 1; 684 685select nextval('ts1'); 686 687-- 688-- Check that volatile quals aren't pushed down past a set-returning function; 689-- while a nonvolatile qual can be, if it doesn't reference the SRF. 690-- 691create function tattle(x int, y int) returns bool 692volatile language plpgsql as $$ 693begin 694 raise notice 'x = %, y = %', x, y; 695 return x > y; 696end$$; 697 698explain (verbose, costs off) 699select * from 700 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 701 where tattle(x, 8); 702 703select * from 704 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 705 where tattle(x, 8); 706 707-- if we pretend it's stable, we get different results: 708alter function tattle(x int, y int) stable; 709 710explain (verbose, costs off) 711select * from 712 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 713 where tattle(x, 8); 714 715select * from 716 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 717 where tattle(x, 8); 718 719-- although even a stable qual should not be pushed down if it references SRF 720explain (verbose, costs off) 721select * from 722 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 723 where tattle(x, u); 724 725select * from 726 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 727 where tattle(x, u); 728 729drop function tattle(x int, y int); 730 731-- 732-- Test that LIMIT can be pushed to SORT through a subquery that just projects 733-- columns. We check for that having happened by looking to see if EXPLAIN 734-- ANALYZE shows that a top-N sort was used. We must suppress or filter away 735-- all the non-invariant parts of the EXPLAIN ANALYZE output. 736-- 737create table sq_limit (pk int primary key, c1 int, c2 int); 738insert into sq_limit values 739 (1, 1, 1), 740 (2, 2, 2), 741 (3, 3, 3), 742 (4, 4, 4), 743 (5, 1, 1), 744 (6, 2, 2), 745 (7, 3, 3), 746 (8, 4, 4); 747 748create function explain_sq_limit() returns setof text language plpgsql as 749$$ 750declare ln text; 751begin 752 for ln in 753 explain (analyze, summary off, timing off, costs off) 754 select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3 755 loop 756 ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); 757 -- this case might occur if force_parallel_mode is on: 758 ln := regexp_replace(ln, 'Worker 0: Sort Method', 'Sort Method'); 759 return next ln; 760 end loop; 761end; 762$$; 763 764select * from explain_sq_limit(); 765 766select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3; 767 768drop function explain_sq_limit(); 769 770drop table sq_limit; 771 772-- 773-- Ensure that backward scan direction isn't propagated into 774-- expression subqueries (bug #15336) 775-- 776 777begin; 778 779declare c1 scroll cursor for 780 select * from generate_series(1,4) i 781 where i <> all (values (2),(3)); 782 783move forward all in c1; 784fetch backward all in c1; 785 786commit; 787