1-- 2-- SUBSELECT 3-- 4SELECT 1 AS one WHERE 1 IN (SELECT 1); 5 one 6----- 7 1 8(1 row) 9 10SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1); 11 zero 12------ 13(0 rows) 14 15SELECT 1 AS zero WHERE 1 IN (SELECT 2); 16 zero 17------ 18(0 rows) 19 20-- Check grammar's handling of extra parens in assorted contexts 21SELECT * FROM (SELECT 1 AS x) ss; 22 x 23--- 24 1 25(1 row) 26 27SELECT * FROM ((SELECT 1 AS x)) ss; 28 x 29--- 30 1 31(1 row) 32 33(SELECT 2) UNION SELECT 2; 34 ?column? 35---------- 36 2 37(1 row) 38 39((SELECT 2)) UNION SELECT 2; 40 ?column? 41---------- 42 2 43(1 row) 44 45SELECT ((SELECT 2) UNION SELECT 2); 46 ?column? 47---------- 48 2 49(1 row) 50 51SELECT (((SELECT 2)) UNION SELECT 2); 52 ?column? 53---------- 54 2 55(1 row) 56 57SELECT (SELECT ARRAY[1,2,3])[1]; 58 array 59------- 60 1 61(1 row) 62 63SELECT ((SELECT ARRAY[1,2,3]))[2]; 64 array 65------- 66 2 67(1 row) 68 69SELECT (((SELECT ARRAY[1,2,3])))[3]; 70 array 71------- 72 3 73(1 row) 74 75-- Set up some simple test tables 76CREATE TABLE SUBSELECT_TBL ( 77 f1 integer, 78 f2 integer, 79 f3 float 80); 81INSERT INTO SUBSELECT_TBL VALUES (1, 2, 3); 82INSERT INTO SUBSELECT_TBL VALUES (2, 3, 4); 83INSERT INTO SUBSELECT_TBL VALUES (3, 4, 5); 84INSERT INTO SUBSELECT_TBL VALUES (1, 1, 1); 85INSERT INTO SUBSELECT_TBL VALUES (2, 2, 2); 86INSERT INTO SUBSELECT_TBL VALUES (3, 3, 3); 87INSERT INTO SUBSELECT_TBL VALUES (6, 7, 8); 88INSERT INTO SUBSELECT_TBL VALUES (8, 9, NULL); 89SELECT '' AS eight, * FROM SUBSELECT_TBL; 90 eight | f1 | f2 | f3 91-------+----+----+---- 92 | 1 | 2 | 3 93 | 2 | 3 | 4 94 | 3 | 4 | 5 95 | 1 | 1 | 1 96 | 2 | 2 | 2 97 | 3 | 3 | 3 98 | 6 | 7 | 8 99 | 8 | 9 | 100(8 rows) 101 102-- Uncorrelated subselects 103SELECT '' AS two, f1 AS "Constant Select" FROM SUBSELECT_TBL 104 WHERE f1 IN (SELECT 1); 105 two | Constant Select 106-----+----------------- 107 | 1 108 | 1 109(2 rows) 110 111SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL 112 WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL); 113 six | Uncorrelated Field 114-----+-------------------- 115 | 1 116 | 2 117 | 3 118 | 1 119 | 2 120 | 3 121(6 rows) 122 123SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL 124 WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE 125 f2 IN (SELECT f1 FROM SUBSELECT_TBL)); 126 six | Uncorrelated Field 127-----+-------------------- 128 | 1 129 | 2 130 | 3 131 | 1 132 | 2 133 | 3 134(6 rows) 135 136SELECT '' AS three, f1, f2 137 FROM SUBSELECT_TBL 138 WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL 139 WHERE f3 IS NOT NULL); 140 three | f1 | f2 141-------+----+---- 142 | 1 | 2 143 | 6 | 7 144 | 8 | 9 145(3 rows) 146 147-- Correlated subselects 148SELECT '' AS six, f1 AS "Correlated Field", f2 AS "Second Field" 149 FROM SUBSELECT_TBL upper 150 WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f1 = upper.f1); 151 six | Correlated Field | Second Field 152-----+------------------+-------------- 153 | 1 | 2 154 | 2 | 3 155 | 3 | 4 156 | 1 | 1 157 | 2 | 2 158 | 3 | 3 159(6 rows) 160 161SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" 162 FROM SUBSELECT_TBL upper 163 WHERE f1 IN 164 (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(upper.f2 AS float) = f3); 165 six | Correlated Field | Second Field 166-----+------------------+-------------- 167 | 2 | 4 168 | 3 | 5 169 | 1 | 1 170 | 2 | 2 171 | 3 | 3 172(5 rows) 173 174SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" 175 FROM SUBSELECT_TBL upper 176 WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL 177 WHERE f2 = CAST(f3 AS integer)); 178 six | Correlated Field | Second Field 179-----+------------------+-------------- 180 | 1 | 3 181 | 2 | 4 182 | 3 | 5 183 | 6 | 8 184(4 rows) 185 186SELECT '' AS five, f1 AS "Correlated Field" 187 FROM SUBSELECT_TBL 188 WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL 189 WHERE f3 IS NOT NULL); 190 five | Correlated Field 191------+------------------ 192 | 2 193 | 3 194 | 1 195 | 2 196 | 3 197(5 rows) 198 199-- 200-- Use some existing tables in the regression test 201-- 202SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field" 203 FROM SUBSELECT_TBL ss 204 WHERE f1 NOT IN (SELECT f1+1 FROM INT4_TBL 205 WHERE f1 != ss.f1 AND f1 < 2147483647); 206 eight | Correlated Field | Second Field 207-------+------------------+-------------- 208 | 2 | 4 209 | 3 | 5 210 | 2 | 2 211 | 3 | 3 212 | 6 | 8 213 | 8 | 214(6 rows) 215 216select q1, float8(count(*)) / (select count(*) from int8_tbl) 217from int8_tbl group by q1 order by q1; 218 q1 | ?column? 219------------------+---------- 220 123 | 0.4 221 4567890123456789 | 0.6 222(2 rows) 223 224-- check materialization of an initplan reference (bug #14524) 225explain (verbose, costs off) 226select 1 = all (select (select 1)); 227 QUERY PLAN 228----------------------------------- 229 Result 230 Output: (SubPlan 2) 231 SubPlan 2 232 -> Materialize 233 Output: ($0) 234 InitPlan 1 (returns $0) 235 -> Result 236 Output: 1 237 -> Result 238 Output: $0 239(10 rows) 240 241select 1 = all (select (select 1)); 242 ?column? 243---------- 244 t 245(1 row) 246 247-- 248-- Check EXISTS simplification with LIMIT 249-- 250explain (costs off) 251select * from int4_tbl o where exists 252 (select 1 from int4_tbl i where i.f1=o.f1 limit null); 253 QUERY PLAN 254------------------------------------ 255 Hash Semi Join 256 Hash Cond: (o.f1 = i.f1) 257 -> Seq Scan on int4_tbl o 258 -> Hash 259 -> Seq Scan on int4_tbl i 260(5 rows) 261 262explain (costs off) 263select * from int4_tbl o where not exists 264 (select 1 from int4_tbl i where i.f1=o.f1 limit 1); 265 QUERY PLAN 266------------------------------------ 267 Hash Anti Join 268 Hash Cond: (o.f1 = i.f1) 269 -> Seq Scan on int4_tbl o 270 -> Hash 271 -> Seq Scan on int4_tbl i 272(5 rows) 273 274explain (costs off) 275select * from int4_tbl o where exists 276 (select 1 from int4_tbl i where i.f1=o.f1 limit 0); 277 QUERY PLAN 278-------------------------------------- 279 Seq Scan on int4_tbl o 280 Filter: (SubPlan 1) 281 SubPlan 1 282 -> Limit 283 -> Seq Scan on int4_tbl i 284 Filter: (f1 = o.f1) 285(6 rows) 286 287-- 288-- Test cases to catch unpleasant interactions between IN-join processing 289-- and subquery pullup. 290-- 291select count(*) from 292 (select 1 from tenk1 a 293 where unique1 IN (select hundred from tenk1 b)) ss; 294 count 295------- 296 100 297(1 row) 298 299select count(distinct ss.ten) from 300 (select ten from tenk1 a 301 where unique1 IN (select hundred from tenk1 b)) ss; 302 count 303------- 304 10 305(1 row) 306 307select count(*) from 308 (select 1 from tenk1 a 309 where unique1 IN (select distinct hundred from tenk1 b)) ss; 310 count 311------- 312 100 313(1 row) 314 315select count(distinct ss.ten) from 316 (select ten from tenk1 a 317 where unique1 IN (select distinct hundred from tenk1 b)) ss; 318 count 319------- 320 10 321(1 row) 322 323-- 324-- Test cases to check for overenthusiastic optimization of 325-- "IN (SELECT DISTINCT ...)" and related cases. Per example from 326-- Luca Pireddu and Michael Fuhr. 327-- 328CREATE TEMP TABLE foo (id integer); 329CREATE TEMP TABLE bar (id1 integer, id2 integer); 330INSERT INTO foo VALUES (1); 331INSERT INTO bar VALUES (1, 1); 332INSERT INTO bar VALUES (2, 2); 333INSERT INTO bar VALUES (3, 1); 334-- These cases require an extra level of distinct-ing above subquery s 335SELECT * FROM foo WHERE id IN 336 (SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM bar) AS s); 337 id 338---- 339 1 340(1 row) 341 342SELECT * FROM foo WHERE id IN 343 (SELECT id2 FROM (SELECT id1,id2 FROM bar GROUP BY id1,id2) AS s); 344 id 345---- 346 1 347(1 row) 348 349SELECT * FROM foo WHERE id IN 350 (SELECT id2 FROM (SELECT id1, id2 FROM bar UNION 351 SELECT id1, id2 FROM bar) AS s); 352 id 353---- 354 1 355(1 row) 356 357-- These cases do not 358SELECT * FROM foo WHERE id IN 359 (SELECT id2 FROM (SELECT DISTINCT ON (id2) id1, id2 FROM bar) AS s); 360 id 361---- 362 1 363(1 row) 364 365SELECT * FROM foo WHERE id IN 366 (SELECT id2 FROM (SELECT id2 FROM bar GROUP BY id2) AS s); 367 id 368---- 369 1 370(1 row) 371 372SELECT * FROM foo WHERE id IN 373 (SELECT id2 FROM (SELECT id2 FROM bar UNION 374 SELECT id2 FROM bar) AS s); 375 id 376---- 377 1 378(1 row) 379 380-- 381-- Test case to catch problems with multiply nested sub-SELECTs not getting 382-- recalculated properly. Per bug report from Didier Moens. 383-- 384CREATE TABLE orderstest ( 385 approver_ref integer, 386 po_ref integer, 387 ordercanceled boolean 388); 389INSERT INTO orderstest VALUES (1, 1, false); 390INSERT INTO orderstest VALUES (66, 5, false); 391INSERT INTO orderstest VALUES (66, 6, false); 392INSERT INTO orderstest VALUES (66, 7, false); 393INSERT INTO orderstest VALUES (66, 1, true); 394INSERT INTO orderstest VALUES (66, 8, false); 395INSERT INTO orderstest VALUES (66, 1, false); 396INSERT INTO orderstest VALUES (77, 1, false); 397INSERT INTO orderstest VALUES (1, 1, false); 398INSERT INTO orderstest VALUES (66, 1, false); 399INSERT INTO orderstest VALUES (1, 1, false); 400CREATE VIEW orders_view AS 401SELECT *, 402(SELECT CASE 403 WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved' 404 END) AS "Approved", 405(SELECT CASE 406 WHEN ord.ordercanceled 407 THEN 'Canceled' 408 ELSE 409 (SELECT CASE 410 WHEN ord.po_ref=1 411 THEN 412 (SELECT CASE 413 WHEN ord.approver_ref=1 414 THEN '---' 415 ELSE 'Approved' 416 END) 417 ELSE 'PO' 418 END) 419END) AS "Status", 420(CASE 421 WHEN ord.ordercanceled 422 THEN 'Canceled' 423 ELSE 424 (CASE 425 WHEN ord.po_ref=1 426 THEN 427 (CASE 428 WHEN ord.approver_ref=1 429 THEN '---' 430 ELSE 'Approved' 431 END) 432 ELSE 'PO' 433 END) 434END) AS "Status_OK" 435FROM orderstest ord; 436SELECT * FROM orders_view; 437 approver_ref | po_ref | ordercanceled | Approved | Status | Status_OK 438--------------+--------+---------------+----------+----------+----------- 439 1 | 1 | f | --- | --- | --- 440 66 | 5 | f | Approved | PO | PO 441 66 | 6 | f | Approved | PO | PO 442 66 | 7 | f | Approved | PO | PO 443 66 | 1 | t | Approved | Canceled | Canceled 444 66 | 8 | f | Approved | PO | PO 445 66 | 1 | f | Approved | Approved | Approved 446 77 | 1 | f | Approved | Approved | Approved 447 1 | 1 | f | --- | --- | --- 448 66 | 1 | f | Approved | Approved | Approved 449 1 | 1 | f | --- | --- | --- 450(11 rows) 451 452DROP TABLE orderstest cascade; 453NOTICE: drop cascades to view orders_view 454-- 455-- Test cases to catch situations where rule rewriter fails to propagate 456-- hasSubLinks flag correctly. Per example from Kyle Bateman. 457-- 458create temp table parts ( 459 partnum text, 460 cost float8 461); 462create temp table shipped ( 463 ttype char(2), 464 ordnum int4, 465 partnum text, 466 value float8 467); 468create temp view shipped_view as 469 select * from shipped where ttype = 'wt'; 470create rule shipped_view_insert as on insert to shipped_view do instead 471 insert into shipped values('wt', new.ordnum, new.partnum, new.value); 472insert into parts (partnum, cost) values (1, 1234.56); 473insert into shipped_view (ordnum, partnum, value) 474 values (0, 1, (select cost from parts where partnum = '1')); 475select * from shipped_view; 476 ttype | ordnum | partnum | value 477-------+--------+---------+--------- 478 wt | 0 | 1 | 1234.56 479(1 row) 480 481create rule shipped_view_update as on update to shipped_view do instead 482 update shipped set partnum = new.partnum, value = new.value 483 where ttype = new.ttype and ordnum = new.ordnum; 484update shipped_view set value = 11 485 from int4_tbl a join int4_tbl b 486 on (a.f1 = (select f1 from int4_tbl c where c.f1=b.f1)) 487 where ordnum = a.f1; 488select * from shipped_view; 489 ttype | ordnum | partnum | value 490-------+--------+---------+------- 491 wt | 0 | 1 | 11 492(1 row) 493 494select f1, ss1 as relabel from 495 (select *, (select sum(f1) from int4_tbl b where f1 >= a.f1) as ss1 496 from int4_tbl a) ss; 497 f1 | relabel 498-------------+------------ 499 0 | 2147607103 500 123456 | 2147607103 501 -123456 | 2147483647 502 2147483647 | 2147483647 503 -2147483647 | 0 504(5 rows) 505 506-- 507-- Test cases involving PARAM_EXEC parameters and min/max index optimizations. 508-- Per bug report from David Sanchez i Gregori. 509-- 510select * from ( 511 select max(unique1) from tenk1 as a 512 where exists (select 1 from tenk1 as b where b.thousand = a.unique2) 513) ss; 514 max 515------ 516 9997 517(1 row) 518 519select * from ( 520 select min(unique1) from tenk1 as a 521 where not exists (select 1 from tenk1 as b where b.unique2 = 10000) 522) ss; 523 min 524----- 525 0 526(1 row) 527 528-- 529-- Test that an IN implemented using a UniquePath does unique-ification 530-- with the right semantics, as per bug #4113. (Unfortunately we have 531-- no simple way to ensure that this test case actually chooses that type 532-- of plan, but it does in releases 7.4-8.3. Note that an ordering difference 533-- here might mean that some other plan type is being used, rendering the test 534-- pointless.) 535-- 536create temp table numeric_table (num_col numeric); 537insert into numeric_table values (1), (1.000000000000000000001), (2), (3); 538create temp table float_table (float_col float8); 539insert into float_table values (1), (2), (3); 540select * from float_table 541 where float_col in (select num_col from numeric_table); 542 float_col 543----------- 544 1 545 2 546 3 547(3 rows) 548 549select * from numeric_table 550 where num_col in (select float_col from float_table); 551 num_col 552------------------------- 553 1 554 1.000000000000000000001 555 2 556 3 557(4 rows) 558 559-- 560-- Test case for bug #4290: bogus calculation of subplan param sets 561-- 562create temp table ta (id int primary key, val int); 563insert into ta values(1,1); 564insert into ta values(2,2); 565create temp table tb (id int primary key, aval int); 566insert into tb values(1,1); 567insert into tb values(2,1); 568insert into tb values(3,2); 569insert into tb values(4,2); 570create temp table tc (id int primary key, aid int); 571insert into tc values(1,1); 572insert into tc values(2,2); 573select 574 ( select min(tb.id) from tb 575 where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_id 576from tc; 577 min_tb_id 578----------- 579 1 580 3 581(2 rows) 582 583-- 584-- Test case for 8.3 "failed to locate grouping columns" bug 585-- 586create temp table t1 (f1 numeric(14,0), f2 varchar(30)); 587select * from 588 (select distinct f1, f2, (select f2 from t1 x where x.f1 = up.f1) as fs 589 from t1 up) ss 590group by f1,f2,fs; 591 f1 | f2 | fs 592----+----+---- 593(0 rows) 594 595-- 596-- Test case for bug #5514 (mishandling of whole-row Vars in subselects) 597-- 598create temp table table_a(id integer); 599insert into table_a values (42); 600create temp view view_a as select * from table_a; 601select view_a from view_a; 602 view_a 603-------- 604 (42) 605(1 row) 606 607select (select view_a) from view_a; 608 view_a 609-------- 610 (42) 611(1 row) 612 613select (select (select view_a)) from view_a; 614 view_a 615-------- 616 (42) 617(1 row) 618 619select (select (a.*)::text) from view_a a; 620 a 621------ 622 (42) 623(1 row) 624 625-- 626-- Check that whole-row Vars reading the result of a subselect don't include 627-- any junk columns therein 628-- 629select q from (select max(f1) from int4_tbl group by f1 order by f1) q; 630 q 631--------------- 632 (-2147483647) 633 (-123456) 634 (0) 635 (123456) 636 (2147483647) 637(5 rows) 638 639with q as (select max(f1) from int4_tbl group by f1 order by f1) 640 select q from q; 641 q 642--------------- 643 (-2147483647) 644 (-123456) 645 (0) 646 (123456) 647 (2147483647) 648(5 rows) 649 650-- 651-- Test case for sublinks pushed down into subselects via join alias expansion 652-- 653select 654 (select sq1) as qq1 655from 656 (select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy 657 from int8_tbl) sq0 658 join 659 int4_tbl i4 on dummy = i4.f1; 660 qq1 661----- 662(0 rows) 663 664-- 665-- Test case for subselect within UPDATE of INSERT...ON CONFLICT DO UPDATE 666-- 667create temp table upsert(key int4 primary key, val text); 668insert into upsert values(1, 'val') on conflict (key) do update set val = 'not seen'; 669insert 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; 670select * from upsert; 671 key | val 672-----+---------------------------- 673 1 | seen with subselect 123456 674(1 row) 675 676with aa as (select 'int4_tbl' u from int4_tbl limit 1) 677insert into upsert values (1, 'x'), (999, 'y') 678on conflict (key) do update set val = (select u from aa) 679returning *; 680 key | val 681-----+---------- 682 1 | int4_tbl 683 999 | y 684(2 rows) 685 686-- 687-- Test case for cross-type partial matching in hashed subplan (bug #7597) 688-- 689create temp table outer_7597 (f1 int4, f2 int4); 690insert into outer_7597 values (0, 0); 691insert into outer_7597 values (1, 0); 692insert into outer_7597 values (0, null); 693insert into outer_7597 values (1, null); 694create temp table inner_7597(c1 int8, c2 int8); 695insert into inner_7597 values(0, null); 696select * from outer_7597 where (f1, f2) not in (select * from inner_7597); 697 f1 | f2 698----+---- 699 1 | 0 700 1 | 701(2 rows) 702 703-- 704-- Test case for premature memory release during hashing of subplan output 705-- 706select '1'::text in (select '1'::name union all select '1'::name); 707 ?column? 708---------- 709 t 710(1 row) 711 712-- 713-- Test that we don't try to use a hashed subplan if the simplified 714-- testexpr isn't of the right shape 715-- 716create temp table inner_text (c1 text, c2 text); 717insert into inner_text values ('a', null); 718insert into inner_text values ('123', '456'); 719-- this fails by default, of course 720select * from int8_tbl where q1 in (select c1 from inner_text); 721ERROR: operator does not exist: bigint = text 722LINE 1: select * from int8_tbl where q1 in (select c1 from inner_tex... 723 ^ 724HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. 725begin; 726-- make an operator to allow it to succeed 727create function bogus_int8_text_eq(int8, text) returns boolean 728language sql as 'select $1::text = $2'; 729create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text); 730explain (costs off) 731select * from int8_tbl where q1 in (select c1 from inner_text); 732 QUERY PLAN 733-------------------------------- 734 Seq Scan on int8_tbl 735 Filter: (hashed SubPlan 1) 736 SubPlan 1 737 -> Seq Scan on inner_text 738(4 rows) 739 740select * from int8_tbl where q1 in (select c1 from inner_text); 741 q1 | q2 742-----+------------------ 743 123 | 456 744 123 | 4567890123456789 745(2 rows) 746 747-- inlining of this function results in unusual number of hash clauses, 748-- which we can still cope with 749create or replace function bogus_int8_text_eq(int8, text) returns boolean 750language sql as 'select $1::text = $2 and $1::text = $2'; 751explain (costs off) 752select * from int8_tbl where q1 in (select c1 from inner_text); 753 QUERY PLAN 754-------------------------------- 755 Seq Scan on int8_tbl 756 Filter: (hashed SubPlan 1) 757 SubPlan 1 758 -> Seq Scan on inner_text 759(4 rows) 760 761select * from int8_tbl where q1 in (select c1 from inner_text); 762 q1 | q2 763-----+------------------ 764 123 | 456 765 123 | 4567890123456789 766(2 rows) 767 768-- inlining of this function causes LHS and RHS to be switched, 769-- which we can't cope with, so hashing should be abandoned 770create or replace function bogus_int8_text_eq(int8, text) returns boolean 771language sql as 'select $2 = $1::text'; 772explain (costs off) 773select * from int8_tbl where q1 in (select c1 from inner_text); 774 QUERY PLAN 775-------------------------------------- 776 Seq Scan on int8_tbl 777 Filter: (SubPlan 1) 778 SubPlan 1 779 -> Materialize 780 -> Seq Scan on inner_text 781(5 rows) 782 783select * from int8_tbl where q1 in (select c1 from inner_text); 784 q1 | q2 785-----+------------------ 786 123 | 456 787 123 | 4567890123456789 788(2 rows) 789 790rollback; -- to get rid of the bogus operator 791-- 792-- Test case for planner bug with nested EXISTS handling 793-- 794select a.thousand from tenk1 a, tenk1 b 795where a.thousand = b.thousand 796 and exists ( select 1 from tenk1 c where b.hundred = c.hundred 797 and not exists ( select 1 from tenk1 d 798 where a.thousand = d.thousand ) ); 799 thousand 800---------- 801(0 rows) 802 803-- 804-- Check that nested sub-selects are not pulled up if they contain volatiles 805-- 806explain (verbose, costs off) 807 select x, x from 808 (select (select now()) as x from (values(1),(2)) v(y)) ss; 809 QUERY PLAN 810--------------------------- 811 Values Scan on "*VALUES*" 812 Output: $0, $1 813 InitPlan 1 (returns $0) 814 -> Result 815 Output: now() 816 InitPlan 2 (returns $1) 817 -> Result 818 Output: now() 819(8 rows) 820 821explain (verbose, costs off) 822 select x, x from 823 (select (select random()) as x from (values(1),(2)) v(y)) ss; 824 QUERY PLAN 825---------------------------------- 826 Subquery Scan on ss 827 Output: ss.x, ss.x 828 -> Values Scan on "*VALUES*" 829 Output: $0 830 InitPlan 1 (returns $0) 831 -> Result 832 Output: random() 833(7 rows) 834 835explain (verbose, costs off) 836 select x, x from 837 (select (select now() where y=y) as x from (values(1),(2)) v(y)) ss; 838 QUERY PLAN 839---------------------------------------------------------------------- 840 Values Scan on "*VALUES*" 841 Output: (SubPlan 1), (SubPlan 2) 842 SubPlan 1 843 -> Result 844 Output: now() 845 One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) 846 SubPlan 2 847 -> Result 848 Output: now() 849 One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) 850(10 rows) 851 852explain (verbose, costs off) 853 select x, x from 854 (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss; 855 QUERY PLAN 856---------------------------------------------------------------------------- 857 Subquery Scan on ss 858 Output: ss.x, ss.x 859 -> Values Scan on "*VALUES*" 860 Output: (SubPlan 1) 861 SubPlan 1 862 -> Result 863 Output: random() 864 One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) 865(8 rows) 866 867-- 868-- Check we behave sanely in corner case of empty SELECT list (bug #8648) 869-- 870create temp table nocolumns(); 871select exists(select * from nocolumns); 872 exists 873-------- 874 f 875(1 row) 876 877-- 878-- Check behavior with a SubPlan in VALUES (bug #14924) 879-- 880select val.x 881 from generate_series(1,10) as s(i), 882 lateral ( 883 values ((select s.i + 1)), (s.i + 101) 884 ) as val(x) 885where s.i < 10 and (select val.x) < 110; 886 x 887----- 888 2 889 102 890 3 891 103 892 4 893 104 894 5 895 105 896 6 897 106 898 7 899 107 900 8 901 108 902 9 903 109 904 10 905(17 rows) 906 907-- another variant of that (bug #16213) 908explain (verbose, costs off) 909select * from 910(values 911 (3 not in (select * from (values (1), (2)) ss1)), 912 (false) 913) ss; 914 QUERY PLAN 915---------------------------------------- 916 Values Scan on "*VALUES*" 917 Output: "*VALUES*".column1 918 SubPlan 1 919 -> Values Scan on "*VALUES*_1" 920 Output: "*VALUES*_1".column1 921(5 rows) 922 923select * from 924(values 925 (3 not in (select * from (values (1), (2)) ss1)), 926 (false) 927) ss; 928 column1 929--------- 930 t 931 f 932(2 rows) 933 934-- 935-- Check sane behavior with nested IN SubLinks 936-- 937explain (verbose, costs off) 938select * from int4_tbl where 939 (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in 940 (select ten from tenk1 b); 941 QUERY PLAN 942--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 943 Nested Loop Semi Join 944 Output: int4_tbl.f1 945 Join Filter: (CASE WHEN (hashed SubPlan 1) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten) 946 -> Seq Scan on public.int4_tbl 947 Output: int4_tbl.f1 948 -> Seq Scan on public.tenk1 b 949 Output: b.unique1, b.unique2, b.two, b.four, b.ten, b.twenty, b.hundred, b.thousand, b.twothousand, b.fivethous, b.tenthous, b.odd, b.even, b.stringu1, b.stringu2, b.string4 950 SubPlan 1 951 -> Index Only Scan using tenk1_unique1 on public.tenk1 a 952 Output: a.unique1 953(10 rows) 954 955select * from int4_tbl where 956 (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in 957 (select ten from tenk1 b); 958 f1 959---- 960 0 961(1 row) 962 963-- 964-- Check for incorrect optimization when IN subquery contains a SRF 965-- 966explain (verbose, costs off) 967select * from int4_tbl o where (f1, f1) in 968 (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1); 969 QUERY PLAN 970---------------------------------------------------------------- 971 Hash Semi Join 972 Output: o.f1 973 Hash Cond: (o.f1 = "ANY_subquery".f1) 974 -> Seq Scan on public.int4_tbl o 975 Output: o.f1 976 -> Hash 977 Output: "ANY_subquery".f1, "ANY_subquery".g 978 -> Subquery Scan on "ANY_subquery" 979 Output: "ANY_subquery".f1, "ANY_subquery".g 980 Filter: ("ANY_subquery".f1 = "ANY_subquery".g) 981 -> HashAggregate 982 Output: i.f1, (generate_series(1, 2) / 10) 983 Group Key: i.f1 984 -> Seq Scan on public.int4_tbl i 985 Output: i.f1 986(15 rows) 987 988select * from int4_tbl o where (f1, f1) in 989 (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1); 990 f1 991---- 992 0 993(1 row) 994 995-- 996-- check for over-optimization of whole-row Var referencing an Append plan 997-- 998select (select q from 999 (select 1,2,3 where f1 > 0 1000 union all 1001 select 4,5,6.0 where f1 <= 0 1002 ) q ) 1003from int4_tbl; 1004 q 1005----------- 1006 (4,5,6.0) 1007 (1,2,3) 1008 (4,5,6.0) 1009 (1,2,3) 1010 (4,5,6.0) 1011(5 rows) 1012 1013-- 1014-- Check for sane handling of a lateral reference in a subquery's quals 1015-- (most of the complication here is to prevent the test case from being 1016-- flattened too much) 1017-- 1018explain (verbose, costs off) 1019select * from 1020 int4_tbl i4, 1021 lateral ( 1022 select i4.f1 > 1 as b, 1 as id 1023 from (select random() order by 1) as t1 1024 union all 1025 select true as b, 2 as id 1026 ) as t2 1027where b and f1 >= 0; 1028 QUERY PLAN 1029-------------------------------------------- 1030 Nested Loop 1031 Output: i4.f1, ((i4.f1 > 1)), (1) 1032 -> Seq Scan on public.int4_tbl i4 1033 Output: i4.f1 1034 Filter: (i4.f1 >= 0) 1035 -> Append 1036 -> Subquery Scan on t1 1037 Output: (i4.f1 > 1), 1 1038 Filter: (i4.f1 > 1) 1039 -> Sort 1040 Output: (random()) 1041 Sort Key: (random()) 1042 -> Result 1043 Output: random() 1044 -> Result 1045 Output: true, 2 1046(16 rows) 1047 1048select * from 1049 int4_tbl i4, 1050 lateral ( 1051 select i4.f1 > 1 as b, 1 as id 1052 from (select random() order by 1) as t1 1053 union all 1054 select true as b, 2 as id 1055 ) as t2 1056where b and f1 >= 0; 1057 f1 | b | id 1058------------+---+---- 1059 0 | t | 2 1060 123456 | t | 1 1061 123456 | t | 2 1062 2147483647 | t | 1 1063 2147483647 | t | 2 1064(5 rows) 1065 1066-- 1067-- Check that volatile quals aren't pushed down past a DISTINCT: 1068-- nextval() should not be called more than the nominal number of times 1069-- 1070create temp sequence ts1; 1071select * from 1072 (select distinct ten from tenk1) ss 1073 where ten < 10 + nextval('ts1') 1074 order by 1; 1075 ten 1076----- 1077 0 1078 1 1079 2 1080 3 1081 4 1082 5 1083 6 1084 7 1085 8 1086 9 1087(10 rows) 1088 1089select nextval('ts1'); 1090 nextval 1091--------- 1092 11 1093(1 row) 1094 1095-- 1096-- Ensure that backward scan direction isn't propagated into 1097-- expression subqueries (bug #15336) 1098-- 1099begin; 1100declare c1 scroll cursor for 1101 select * from generate_series(1,4) i 1102 where i <> all (values (2),(3)); 1103move forward all in c1; 1104fetch backward all in c1; 1105 i 1106--- 1107 4 1108 1 1109(2 rows) 1110 1111commit; 1112