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-- Unspecified-type literals in output columns should resolve as text 225SELECT *, pg_typeof(f1) FROM 226 (SELECT 'foo' AS f1 FROM generate_series(1,3)) ss ORDER BY 1; 227 f1 | pg_typeof 228-----+----------- 229 foo | text 230 foo | text 231 foo | text 232(3 rows) 233 234-- ... unless there's context to suggest differently 235explain verbose select '42' union all select '43'; 236 QUERY PLAN 237------------------------------------------------- 238 Append (cost=0.00..0.04 rows=2 width=32) 239 -> Result (cost=0.00..0.01 rows=1 width=32) 240 Output: '42'::text 241 -> Result (cost=0.00..0.01 rows=1 width=32) 242 Output: '43'::text 243(5 rows) 244 245explain verbose select '42' union all select 43; 246 QUERY PLAN 247------------------------------------------------ 248 Append (cost=0.00..0.04 rows=2 width=4) 249 -> Result (cost=0.00..0.01 rows=1 width=4) 250 Output: 42 251 -> Result (cost=0.00..0.01 rows=1 width=4) 252 Output: 43 253(5 rows) 254 255-- check materialization of an initplan reference (bug #14524) 256explain (verbose, costs off) 257select 1 = all (select (select 1)); 258 QUERY PLAN 259----------------------------------- 260 Result 261 Output: (SubPlan 2) 262 SubPlan 2 263 -> Materialize 264 Output: ($0) 265 InitPlan 1 (returns $0) 266 -> Result 267 Output: 1 268 -> Result 269 Output: $0 270(10 rows) 271 272select 1 = all (select (select 1)); 273 ?column? 274---------- 275 t 276(1 row) 277 278-- 279-- Check EXISTS simplification with LIMIT 280-- 281explain (costs off) 282select * from int4_tbl o where exists 283 (select 1 from int4_tbl i where i.f1=o.f1 limit null); 284 QUERY PLAN 285------------------------------------ 286 Hash Semi Join 287 Hash Cond: (o.f1 = i.f1) 288 -> Seq Scan on int4_tbl o 289 -> Hash 290 -> Seq Scan on int4_tbl i 291(5 rows) 292 293explain (costs off) 294select * from int4_tbl o where not exists 295 (select 1 from int4_tbl i where i.f1=o.f1 limit 1); 296 QUERY PLAN 297------------------------------------ 298 Hash Anti Join 299 Hash Cond: (o.f1 = i.f1) 300 -> Seq Scan on int4_tbl o 301 -> Hash 302 -> Seq Scan on int4_tbl i 303(5 rows) 304 305explain (costs off) 306select * from int4_tbl o where exists 307 (select 1 from int4_tbl i where i.f1=o.f1 limit 0); 308 QUERY PLAN 309-------------------------------------- 310 Seq Scan on int4_tbl o 311 Filter: (SubPlan 1) 312 SubPlan 1 313 -> Limit 314 -> Seq Scan on int4_tbl i 315 Filter: (f1 = o.f1) 316(6 rows) 317 318-- 319-- Test cases to catch unpleasant interactions between IN-join processing 320-- and subquery pullup. 321-- 322select count(*) from 323 (select 1 from tenk1 a 324 where unique1 IN (select hundred from tenk1 b)) ss; 325 count 326------- 327 100 328(1 row) 329 330select count(distinct ss.ten) from 331 (select ten from tenk1 a 332 where unique1 IN (select hundred from tenk1 b)) ss; 333 count 334------- 335 10 336(1 row) 337 338select count(*) from 339 (select 1 from tenk1 a 340 where unique1 IN (select distinct hundred from tenk1 b)) ss; 341 count 342------- 343 100 344(1 row) 345 346select count(distinct ss.ten) from 347 (select ten from tenk1 a 348 where unique1 IN (select distinct hundred from tenk1 b)) ss; 349 count 350------- 351 10 352(1 row) 353 354-- 355-- Test cases to check for overenthusiastic optimization of 356-- "IN (SELECT DISTINCT ...)" and related cases. Per example from 357-- Luca Pireddu and Michael Fuhr. 358-- 359CREATE TEMP TABLE foo (id integer); 360CREATE TEMP TABLE bar (id1 integer, id2 integer); 361INSERT INTO foo VALUES (1); 362INSERT INTO bar VALUES (1, 1); 363INSERT INTO bar VALUES (2, 2); 364INSERT INTO bar VALUES (3, 1); 365-- These cases require an extra level of distinct-ing above subquery s 366SELECT * FROM foo WHERE id IN 367 (SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM bar) AS s); 368 id 369---- 370 1 371(1 row) 372 373SELECT * FROM foo WHERE id IN 374 (SELECT id2 FROM (SELECT id1,id2 FROM bar GROUP BY id1,id2) AS s); 375 id 376---- 377 1 378(1 row) 379 380SELECT * FROM foo WHERE id IN 381 (SELECT id2 FROM (SELECT id1, id2 FROM bar UNION 382 SELECT id1, id2 FROM bar) AS s); 383 id 384---- 385 1 386(1 row) 387 388-- These cases do not 389SELECT * FROM foo WHERE id IN 390 (SELECT id2 FROM (SELECT DISTINCT ON (id2) id1, id2 FROM bar) AS s); 391 id 392---- 393 1 394(1 row) 395 396SELECT * FROM foo WHERE id IN 397 (SELECT id2 FROM (SELECT id2 FROM bar GROUP BY id2) AS s); 398 id 399---- 400 1 401(1 row) 402 403SELECT * FROM foo WHERE id IN 404 (SELECT id2 FROM (SELECT id2 FROM bar UNION 405 SELECT id2 FROM bar) AS s); 406 id 407---- 408 1 409(1 row) 410 411-- 412-- Test case to catch problems with multiply nested sub-SELECTs not getting 413-- recalculated properly. Per bug report from Didier Moens. 414-- 415CREATE TABLE orderstest ( 416 approver_ref integer, 417 po_ref integer, 418 ordercanceled boolean 419); 420INSERT INTO orderstest VALUES (1, 1, false); 421INSERT INTO orderstest VALUES (66, 5, false); 422INSERT INTO orderstest VALUES (66, 6, false); 423INSERT INTO orderstest VALUES (66, 7, false); 424INSERT INTO orderstest VALUES (66, 1, true); 425INSERT INTO orderstest VALUES (66, 8, false); 426INSERT INTO orderstest VALUES (66, 1, false); 427INSERT INTO orderstest VALUES (77, 1, false); 428INSERT INTO orderstest VALUES (1, 1, false); 429INSERT INTO orderstest VALUES (66, 1, false); 430INSERT INTO orderstest VALUES (1, 1, false); 431CREATE VIEW orders_view AS 432SELECT *, 433(SELECT CASE 434 WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved' 435 END) AS "Approved", 436(SELECT CASE 437 WHEN ord.ordercanceled 438 THEN 'Canceled' 439 ELSE 440 (SELECT CASE 441 WHEN ord.po_ref=1 442 THEN 443 (SELECT CASE 444 WHEN ord.approver_ref=1 445 THEN '---' 446 ELSE 'Approved' 447 END) 448 ELSE 'PO' 449 END) 450END) AS "Status", 451(CASE 452 WHEN ord.ordercanceled 453 THEN 'Canceled' 454 ELSE 455 (CASE 456 WHEN ord.po_ref=1 457 THEN 458 (CASE 459 WHEN ord.approver_ref=1 460 THEN '---' 461 ELSE 'Approved' 462 END) 463 ELSE 'PO' 464 END) 465END) AS "Status_OK" 466FROM orderstest ord; 467SELECT * FROM orders_view; 468 approver_ref | po_ref | ordercanceled | Approved | Status | Status_OK 469--------------+--------+---------------+----------+----------+----------- 470 1 | 1 | f | --- | --- | --- 471 66 | 5 | f | Approved | PO | PO 472 66 | 6 | f | Approved | PO | PO 473 66 | 7 | f | Approved | PO | PO 474 66 | 1 | t | Approved | Canceled | Canceled 475 66 | 8 | f | Approved | PO | PO 476 66 | 1 | f | Approved | Approved | Approved 477 77 | 1 | f | Approved | Approved | Approved 478 1 | 1 | f | --- | --- | --- 479 66 | 1 | f | Approved | Approved | Approved 480 1 | 1 | f | --- | --- | --- 481(11 rows) 482 483DROP TABLE orderstest cascade; 484NOTICE: drop cascades to view orders_view 485-- 486-- Test cases to catch situations where rule rewriter fails to propagate 487-- hasSubLinks flag correctly. Per example from Kyle Bateman. 488-- 489create temp table parts ( 490 partnum text, 491 cost float8 492); 493create temp table shipped ( 494 ttype char(2), 495 ordnum int4, 496 partnum text, 497 value float8 498); 499create temp view shipped_view as 500 select * from shipped where ttype = 'wt'; 501create rule shipped_view_insert as on insert to shipped_view do instead 502 insert into shipped values('wt', new.ordnum, new.partnum, new.value); 503insert into parts (partnum, cost) values (1, 1234.56); 504insert into shipped_view (ordnum, partnum, value) 505 values (0, 1, (select cost from parts where partnum = '1')); 506select * from shipped_view; 507 ttype | ordnum | partnum | value 508-------+--------+---------+--------- 509 wt | 0 | 1 | 1234.56 510(1 row) 511 512create rule shipped_view_update as on update to shipped_view do instead 513 update shipped set partnum = new.partnum, value = new.value 514 where ttype = new.ttype and ordnum = new.ordnum; 515update shipped_view set value = 11 516 from int4_tbl a join int4_tbl b 517 on (a.f1 = (select f1 from int4_tbl c where c.f1=b.f1)) 518 where ordnum = a.f1; 519select * from shipped_view; 520 ttype | ordnum | partnum | value 521-------+--------+---------+------- 522 wt | 0 | 1 | 11 523(1 row) 524 525select f1, ss1 as relabel from 526 (select *, (select sum(f1) from int4_tbl b where f1 >= a.f1) as ss1 527 from int4_tbl a) ss; 528 f1 | relabel 529-------------+------------ 530 0 | 2147607103 531 123456 | 2147607103 532 -123456 | 2147483647 533 2147483647 | 2147483647 534 -2147483647 | 0 535(5 rows) 536 537-- 538-- Test cases involving PARAM_EXEC parameters and min/max index optimizations. 539-- Per bug report from David Sanchez i Gregori. 540-- 541select * from ( 542 select max(unique1) from tenk1 as a 543 where exists (select 1 from tenk1 as b where b.thousand = a.unique2) 544) ss; 545 max 546------ 547 9997 548(1 row) 549 550select * from ( 551 select min(unique1) from tenk1 as a 552 where not exists (select 1 from tenk1 as b where b.unique2 = 10000) 553) ss; 554 min 555----- 556 0 557(1 row) 558 559-- 560-- Test that an IN implemented using a UniquePath does unique-ification 561-- with the right semantics, as per bug #4113. (Unfortunately we have 562-- no simple way to ensure that this test case actually chooses that type 563-- of plan, but it does in releases 7.4-8.3. Note that an ordering difference 564-- here might mean that some other plan type is being used, rendering the test 565-- pointless.) 566-- 567create temp table numeric_table (num_col numeric); 568insert into numeric_table values (1), (1.000000000000000000001), (2), (3); 569create temp table float_table (float_col float8); 570insert into float_table values (1), (2), (3); 571select * from float_table 572 where float_col in (select num_col from numeric_table); 573 float_col 574----------- 575 1 576 2 577 3 578(3 rows) 579 580select * from numeric_table 581 where num_col in (select float_col from float_table); 582 num_col 583------------------------- 584 1 585 1.000000000000000000001 586 2 587 3 588(4 rows) 589 590-- 591-- Test case for bug #4290: bogus calculation of subplan param sets 592-- 593create temp table ta (id int primary key, val int); 594insert into ta values(1,1); 595insert into ta values(2,2); 596create temp table tb (id int primary key, aval int); 597insert into tb values(1,1); 598insert into tb values(2,1); 599insert into tb values(3,2); 600insert into tb values(4,2); 601create temp table tc (id int primary key, aid int); 602insert into tc values(1,1); 603insert into tc values(2,2); 604select 605 ( select min(tb.id) from tb 606 where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_id 607from tc; 608 min_tb_id 609----------- 610 1 611 3 612(2 rows) 613 614-- 615-- Test case for 8.3 "failed to locate grouping columns" bug 616-- 617create temp table t1 (f1 numeric(14,0), f2 varchar(30)); 618select * from 619 (select distinct f1, f2, (select f2 from t1 x where x.f1 = up.f1) as fs 620 from t1 up) ss 621group by f1,f2,fs; 622 f1 | f2 | fs 623----+----+---- 624(0 rows) 625 626-- 627-- Test case for bug #5514 (mishandling of whole-row Vars in subselects) 628-- 629create temp table table_a(id integer); 630insert into table_a values (42); 631create temp view view_a as select * from table_a; 632select view_a from view_a; 633 view_a 634-------- 635 (42) 636(1 row) 637 638select (select view_a) from view_a; 639 view_a 640-------- 641 (42) 642(1 row) 643 644select (select (select view_a)) from view_a; 645 view_a 646-------- 647 (42) 648(1 row) 649 650select (select (a.*)::text) from view_a a; 651 a 652------ 653 (42) 654(1 row) 655 656-- 657-- Check that whole-row Vars reading the result of a subselect don't include 658-- any junk columns therein 659-- 660select q from (select max(f1) from int4_tbl group by f1 order by f1) q; 661 q 662--------------- 663 (-2147483647) 664 (-123456) 665 (0) 666 (123456) 667 (2147483647) 668(5 rows) 669 670with q as (select max(f1) from int4_tbl group by f1 order by f1) 671 select q from q; 672 q 673--------------- 674 (-2147483647) 675 (-123456) 676 (0) 677 (123456) 678 (2147483647) 679(5 rows) 680 681-- 682-- Test case for sublinks pulled up into joinaliasvars lists in an 683-- inherited update/delete query 684-- 685begin; -- this shouldn't delete anything, but be safe 686delete from road 687where exists ( 688 select 1 689 from 690 int4_tbl cross join 691 ( select f1, array(select q1 from int8_tbl) as arr 692 from text_tbl ) ss 693 where road.name = ss.f1 ); 694rollback; 695-- 696-- Test case for sublinks pushed down into subselects via join alias expansion 697-- 698select 699 (select sq1) as qq1 700from 701 (select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy 702 from int8_tbl) sq0 703 join 704 int4_tbl i4 on dummy = i4.f1; 705 qq1 706----- 707(0 rows) 708 709-- 710-- Test case for subselect within UPDATE of INSERT...ON CONFLICT DO UPDATE 711-- 712create temp table upsert(key int4 primary key, val text); 713insert into upsert values(1, 'val') on conflict (key) do update set val = 'not seen'; 714insert 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; 715select * from upsert; 716 key | val 717-----+---------------------------- 718 1 | seen with subselect 123456 719(1 row) 720 721with aa as (select 'int4_tbl' u from int4_tbl limit 1) 722insert into upsert values (1, 'x'), (999, 'y') 723on conflict (key) do update set val = (select u from aa) 724returning *; 725 key | val 726-----+---------- 727 1 | int4_tbl 728 999 | y 729(2 rows) 730 731-- 732-- Test case for cross-type partial matching in hashed subplan (bug #7597) 733-- 734create temp table outer_7597 (f1 int4, f2 int4); 735insert into outer_7597 values (0, 0); 736insert into outer_7597 values (1, 0); 737insert into outer_7597 values (0, null); 738insert into outer_7597 values (1, null); 739create temp table inner_7597(c1 int8, c2 int8); 740insert into inner_7597 values(0, null); 741select * from outer_7597 where (f1, f2) not in (select * from inner_7597); 742 f1 | f2 743----+---- 744 1 | 0 745 1 | 746(2 rows) 747 748-- 749-- Test case for premature memory release during hashing of subplan output 750-- 751select '1'::text in (select '1'::name union all select '1'::name); 752 ?column? 753---------- 754 t 755(1 row) 756 757-- 758-- Test that we don't try to use a hashed subplan if the simplified 759-- testexpr isn't of the right shape 760-- 761create temp table inner_text (c1 text, c2 text); 762insert into inner_text values ('a', null); 763insert into inner_text values ('123', '456'); 764-- this fails by default, of course 765select * from int8_tbl where q1 in (select c1 from inner_text); 766ERROR: operator does not exist: bigint = text 767LINE 1: select * from int8_tbl where q1 in (select c1 from inner_tex... 768 ^ 769HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. 770begin; 771-- make an operator to allow it to succeed 772create function bogus_int8_text_eq(int8, text) returns boolean 773language sql as 'select $1::text = $2'; 774create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text); 775explain (costs off) 776select * from int8_tbl where q1 in (select c1 from inner_text); 777 QUERY PLAN 778-------------------------------- 779 Seq Scan on int8_tbl 780 Filter: (hashed SubPlan 1) 781 SubPlan 1 782 -> Seq Scan on inner_text 783(4 rows) 784 785select * from int8_tbl where q1 in (select c1 from inner_text); 786 q1 | q2 787-----+------------------ 788 123 | 456 789 123 | 4567890123456789 790(2 rows) 791 792-- inlining of this function results in unusual number of hash clauses, 793-- which we can still cope with 794create or replace function bogus_int8_text_eq(int8, text) returns boolean 795language sql as 'select $1::text = $2 and $1::text = $2'; 796explain (costs off) 797select * from int8_tbl where q1 in (select c1 from inner_text); 798 QUERY PLAN 799-------------------------------- 800 Seq Scan on int8_tbl 801 Filter: (hashed SubPlan 1) 802 SubPlan 1 803 -> Seq Scan on inner_text 804(4 rows) 805 806select * from int8_tbl where q1 in (select c1 from inner_text); 807 q1 | q2 808-----+------------------ 809 123 | 456 810 123 | 4567890123456789 811(2 rows) 812 813-- inlining of this function causes LHS and RHS to be switched, 814-- which we can't cope with, so hashing should be abandoned 815create or replace function bogus_int8_text_eq(int8, text) returns boolean 816language sql as 'select $2 = $1::text'; 817explain (costs off) 818select * from int8_tbl where q1 in (select c1 from inner_text); 819 QUERY PLAN 820-------------------------------------- 821 Seq Scan on int8_tbl 822 Filter: (SubPlan 1) 823 SubPlan 1 824 -> Materialize 825 -> Seq Scan on inner_text 826(5 rows) 827 828select * from int8_tbl where q1 in (select c1 from inner_text); 829 q1 | q2 830-----+------------------ 831 123 | 456 832 123 | 4567890123456789 833(2 rows) 834 835rollback; -- to get rid of the bogus operator 836-- 837-- Test case for planner bug with nested EXISTS handling 838-- 839select a.thousand from tenk1 a, tenk1 b 840where a.thousand = b.thousand 841 and exists ( select 1 from tenk1 c where b.hundred = c.hundred 842 and not exists ( select 1 from tenk1 d 843 where a.thousand = d.thousand ) ); 844 thousand 845---------- 846(0 rows) 847 848-- 849-- Check that nested sub-selects are not pulled up if they contain volatiles 850-- 851explain (verbose, costs off) 852 select x, x from 853 (select (select now()) as x from (values(1),(2)) v(y)) ss; 854 QUERY PLAN 855--------------------------- 856 Values Scan on "*VALUES*" 857 Output: $0, $1 858 InitPlan 1 (returns $0) 859 -> Result 860 Output: now() 861 InitPlan 2 (returns $1) 862 -> Result 863 Output: now() 864(8 rows) 865 866explain (verbose, costs off) 867 select x, x from 868 (select (select random()) as x from (values(1),(2)) v(y)) ss; 869 QUERY PLAN 870---------------------------------- 871 Subquery Scan on ss 872 Output: ss.x, ss.x 873 -> Values Scan on "*VALUES*" 874 Output: $0 875 InitPlan 1 (returns $0) 876 -> Result 877 Output: random() 878(7 rows) 879 880explain (verbose, costs off) 881 select x, x from 882 (select (select now() where y=y) as x from (values(1),(2)) v(y)) ss; 883 QUERY PLAN 884---------------------------------------------------------------------- 885 Values Scan on "*VALUES*" 886 Output: (SubPlan 1), (SubPlan 2) 887 SubPlan 1 888 -> Result 889 Output: now() 890 One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) 891 SubPlan 2 892 -> Result 893 Output: now() 894 One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) 895(10 rows) 896 897explain (verbose, costs off) 898 select x, x from 899 (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss; 900 QUERY PLAN 901---------------------------------------------------------------------------- 902 Subquery Scan on ss 903 Output: ss.x, ss.x 904 -> Values Scan on "*VALUES*" 905 Output: (SubPlan 1) 906 SubPlan 1 907 -> Result 908 Output: random() 909 One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) 910(8 rows) 911 912-- 913-- Check we behave sanely in corner case of empty SELECT list (bug #8648) 914-- 915create temp table nocolumns(); 916select exists(select * from nocolumns); 917 exists 918-------- 919 f 920(1 row) 921 922-- 923-- Check behavior with a SubPlan in VALUES (bug #14924) 924-- 925select val.x 926 from generate_series(1,10) as s(i), 927 lateral ( 928 values ((select s.i + 1)), (s.i + 101) 929 ) as val(x) 930where s.i < 10 and (select val.x) < 110; 931 x 932----- 933 2 934 102 935 3 936 103 937 4 938 104 939 5 940 105 941 6 942 106 943 7 944 107 945 8 946 108 947 9 948 109 949 10 950(17 rows) 951 952-- another variant of that (bug #16213) 953explain (verbose, costs off) 954select * from 955(values 956 (3 not in (select * from (values (1), (2)) ss1)), 957 (false) 958) ss; 959 QUERY PLAN 960---------------------------------------- 961 Values Scan on "*VALUES*" 962 Output: "*VALUES*".column1 963 SubPlan 1 964 -> Values Scan on "*VALUES*_1" 965 Output: "*VALUES*_1".column1 966(5 rows) 967 968select * from 969(values 970 (3 not in (select * from (values (1), (2)) ss1)), 971 (false) 972) ss; 973 column1 974--------- 975 t 976 f 977(2 rows) 978 979-- 980-- Check sane behavior with nested IN SubLinks 981-- 982explain (verbose, costs off) 983select * from int4_tbl where 984 (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in 985 (select ten from tenk1 b); 986 QUERY PLAN 987--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 988 Nested Loop Semi Join 989 Output: int4_tbl.f1 990 Join Filter: (CASE WHEN (hashed SubPlan 1) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten) 991 -> Seq Scan on public.int4_tbl 992 Output: int4_tbl.f1 993 -> Seq Scan on public.tenk1 b 994 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 995 SubPlan 1 996 -> Index Only Scan using tenk1_unique1 on public.tenk1 a 997 Output: a.unique1 998(10 rows) 999 1000select * from int4_tbl where 1001 (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in 1002 (select ten from tenk1 b); 1003 f1 1004---- 1005 0 1006(1 row) 1007 1008-- 1009-- Check for incorrect optimization when IN subquery contains a SRF 1010-- 1011explain (verbose, costs off) 1012select * from int4_tbl o where (f1, f1) in 1013 (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1); 1014 QUERY PLAN 1015------------------------------------------------------------------- 1016 Nested Loop Semi Join 1017 Output: o.f1 1018 Join Filter: (o.f1 = "ANY_subquery".f1) 1019 -> Seq Scan on public.int4_tbl o 1020 Output: o.f1 1021 -> Materialize 1022 Output: "ANY_subquery".f1, "ANY_subquery".g 1023 -> Subquery Scan on "ANY_subquery" 1024 Output: "ANY_subquery".f1, "ANY_subquery".g 1025 Filter: ("ANY_subquery".f1 = "ANY_subquery".g) 1026 -> Result 1027 Output: i.f1, ((generate_series(1, 2)) / 10) 1028 -> ProjectSet 1029 Output: generate_series(1, 2), i.f1 1030 -> HashAggregate 1031 Output: i.f1 1032 Group Key: i.f1 1033 -> Seq Scan on public.int4_tbl i 1034 Output: i.f1 1035(19 rows) 1036 1037select * from int4_tbl o where (f1, f1) in 1038 (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1); 1039 f1 1040---- 1041 0 1042(1 row) 1043 1044-- 1045-- check for over-optimization of whole-row Var referencing an Append plan 1046-- 1047select (select q from 1048 (select 1,2,3 where f1 > 0 1049 union all 1050 select 4,5,6.0 where f1 <= 0 1051 ) q ) 1052from int4_tbl; 1053 q 1054----------- 1055 (4,5,6.0) 1056 (1,2,3) 1057 (4,5,6.0) 1058 (1,2,3) 1059 (4,5,6.0) 1060(5 rows) 1061 1062-- 1063-- Check for sane handling of a lateral reference in a subquery's quals 1064-- (most of the complication here is to prevent the test case from being 1065-- flattened too much) 1066-- 1067explain (verbose, costs off) 1068select * from 1069 int4_tbl i4, 1070 lateral ( 1071 select i4.f1 > 1 as b, 1 as id 1072 from (select random() order by 1) as t1 1073 union all 1074 select true as b, 2 as id 1075 ) as t2 1076where b and f1 >= 0; 1077 QUERY PLAN 1078-------------------------------------------- 1079 Nested Loop 1080 Output: i4.f1, ((i4.f1 > 1)), (1) 1081 -> Seq Scan on public.int4_tbl i4 1082 Output: i4.f1 1083 Filter: (i4.f1 >= 0) 1084 -> Append 1085 -> Subquery Scan on t1 1086 Output: (i4.f1 > 1), 1 1087 Filter: (i4.f1 > 1) 1088 -> Sort 1089 Output: (random()) 1090 Sort Key: (random()) 1091 -> Result 1092 Output: random() 1093 -> Result 1094 Output: true, 2 1095(16 rows) 1096 1097select * from 1098 int4_tbl i4, 1099 lateral ( 1100 select i4.f1 > 1 as b, 1 as id 1101 from (select random() order by 1) as t1 1102 union all 1103 select true as b, 2 as id 1104 ) as t2 1105where b and f1 >= 0; 1106 f1 | b | id 1107------------+---+---- 1108 0 | t | 2 1109 123456 | t | 1 1110 123456 | t | 2 1111 2147483647 | t | 1 1112 2147483647 | t | 2 1113(5 rows) 1114 1115-- 1116-- Check that volatile quals aren't pushed down past a DISTINCT: 1117-- nextval() should not be called more than the nominal number of times 1118-- 1119create temp sequence ts1; 1120select * from 1121 (select distinct ten from tenk1) ss 1122 where ten < 10 + nextval('ts1') 1123 order by 1; 1124 ten 1125----- 1126 0 1127 1 1128 2 1129 3 1130 4 1131 5 1132 6 1133 7 1134 8 1135 9 1136(10 rows) 1137 1138select nextval('ts1'); 1139 nextval 1140--------- 1141 11 1142(1 row) 1143 1144-- 1145-- Check that volatile quals aren't pushed down past a set-returning function; 1146-- while a nonvolatile qual can be, if it doesn't reference the SRF. 1147-- 1148create function tattle(x int, y int) returns bool 1149volatile language plpgsql as $$ 1150begin 1151 raise notice 'x = %, y = %', x, y; 1152 return x > y; 1153end$$; 1154explain (verbose, costs off) 1155select * from 1156 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 1157 where tattle(x, 8); 1158 QUERY PLAN 1159---------------------------------------------------------- 1160 Subquery Scan on ss 1161 Output: x, u 1162 Filter: tattle(ss.x, 8) 1163 -> ProjectSet 1164 Output: 9, unnest('{1,2,3,11,12,13}'::integer[]) 1165 -> Result 1166(6 rows) 1167 1168select * from 1169 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 1170 where tattle(x, 8); 1171NOTICE: x = 9, y = 8 1172NOTICE: x = 9, y = 8 1173NOTICE: x = 9, y = 8 1174NOTICE: x = 9, y = 8 1175NOTICE: x = 9, y = 8 1176NOTICE: x = 9, y = 8 1177 x | u 1178---+---- 1179 9 | 1 1180 9 | 2 1181 9 | 3 1182 9 | 11 1183 9 | 12 1184 9 | 13 1185(6 rows) 1186 1187-- if we pretend it's stable, we get different results: 1188alter function tattle(x int, y int) stable; 1189explain (verbose, costs off) 1190select * from 1191 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 1192 where tattle(x, 8); 1193 QUERY PLAN 1194---------------------------------------------------- 1195 ProjectSet 1196 Output: 9, unnest('{1,2,3,11,12,13}'::integer[]) 1197 -> Result 1198 One-Time Filter: tattle(9, 8) 1199(4 rows) 1200 1201select * from 1202 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 1203 where tattle(x, 8); 1204NOTICE: x = 9, y = 8 1205 x | u 1206---+---- 1207 9 | 1 1208 9 | 2 1209 9 | 3 1210 9 | 11 1211 9 | 12 1212 9 | 13 1213(6 rows) 1214 1215-- although even a stable qual should not be pushed down if it references SRF 1216explain (verbose, costs off) 1217select * from 1218 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 1219 where tattle(x, u); 1220 QUERY PLAN 1221---------------------------------------------------------- 1222 Subquery Scan on ss 1223 Output: x, u 1224 Filter: tattle(ss.x, ss.u) 1225 -> ProjectSet 1226 Output: 9, unnest('{1,2,3,11,12,13}'::integer[]) 1227 -> Result 1228(6 rows) 1229 1230select * from 1231 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 1232 where tattle(x, u); 1233NOTICE: x = 9, y = 1 1234NOTICE: x = 9, y = 2 1235NOTICE: x = 9, y = 3 1236NOTICE: x = 9, y = 11 1237NOTICE: x = 9, y = 12 1238NOTICE: x = 9, y = 13 1239 x | u 1240---+--- 1241 9 | 1 1242 9 | 2 1243 9 | 3 1244(3 rows) 1245 1246drop function tattle(x int, y int); 1247-- 1248-- Ensure that backward scan direction isn't propagated into 1249-- expression subqueries (bug #15336) 1250-- 1251begin; 1252declare c1 scroll cursor for 1253 select * from generate_series(1,4) i 1254 where i <> all (values (2),(3)); 1255move forward all in c1; 1256fetch backward all in c1; 1257 i 1258--- 1259 4 1260 1 1261(2 rows) 1262 1263commit; 1264