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 select '42' union all select '43'; 103explain verbose 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-- Test case for premature memory release during hashing of subplan output 440-- 441 442select '1'::text in (select '1'::name union all select '1'::name); 443 444-- 445-- Test that we don't try to use a hashed subplan if the simplified 446-- testexpr isn't of the right shape 447-- 448 449create temp table inner_text (c1 text, c2 text); 450insert into inner_text values ('a', null); 451insert into inner_text values ('123', '456'); 452 453-- this fails by default, of course 454select * from int8_tbl where q1 in (select c1 from inner_text); 455 456begin; 457 458-- make an operator to allow it to succeed 459create function bogus_int8_text_eq(int8, text) returns boolean 460language sql as 'select $1::text = $2'; 461 462create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text); 463 464explain (costs off) 465select * from int8_tbl where q1 in (select c1 from inner_text); 466select * from int8_tbl where q1 in (select c1 from inner_text); 467 468-- inlining of this function results in unusual number of hash clauses, 469-- which we can still cope with 470create or replace function bogus_int8_text_eq(int8, text) returns boolean 471language sql as 'select $1::text = $2 and $1::text = $2'; 472 473explain (costs off) 474select * from int8_tbl where q1 in (select c1 from inner_text); 475select * from int8_tbl where q1 in (select c1 from inner_text); 476 477-- inlining of this function causes LHS and RHS to be switched, 478-- which we can't cope with, so hashing should be abandoned 479create or replace function bogus_int8_text_eq(int8, text) returns boolean 480language sql as 'select $2 = $1::text'; 481 482explain (costs off) 483select * from int8_tbl where q1 in (select c1 from inner_text); 484select * from int8_tbl where q1 in (select c1 from inner_text); 485 486rollback; -- to get rid of the bogus operator 487 488-- 489-- Test case for planner bug with nested EXISTS handling 490-- 491select a.thousand from tenk1 a, tenk1 b 492where a.thousand = b.thousand 493 and exists ( select 1 from tenk1 c where b.hundred = c.hundred 494 and not exists ( select 1 from tenk1 d 495 where a.thousand = d.thousand ) ); 496 497-- 498-- Check that nested sub-selects are not pulled up if they contain volatiles 499-- 500explain (verbose, costs off) 501 select x, x from 502 (select (select now()) as x from (values(1),(2)) v(y)) ss; 503explain (verbose, costs off) 504 select x, x from 505 (select (select random()) as x from (values(1),(2)) v(y)) ss; 506explain (verbose, costs off) 507 select x, x from 508 (select (select now() where y=y) as x from (values(1),(2)) v(y)) ss; 509explain (verbose, costs off) 510 select x, x from 511 (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss; 512 513-- 514-- Check we behave sanely in corner case of empty SELECT list (bug #8648) 515-- 516create temp table nocolumns(); 517select exists(select * from nocolumns); 518 519-- 520-- Check behavior with a SubPlan in VALUES (bug #14924) 521-- 522select val.x 523 from generate_series(1,10) as s(i), 524 lateral ( 525 values ((select s.i + 1)), (s.i + 101) 526 ) as val(x) 527where s.i < 10 and (select val.x) < 110; 528 529-- another variant of that (bug #16213) 530explain (verbose, costs off) 531select * from 532(values 533 (3 not in (select * from (values (1), (2)) ss1)), 534 (false) 535) ss; 536 537select * from 538(values 539 (3 not in (select * from (values (1), (2)) ss1)), 540 (false) 541) ss; 542 543-- 544-- Check sane behavior with nested IN SubLinks 545-- 546explain (verbose, costs off) 547select * from int4_tbl where 548 (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in 549 (select ten from tenk1 b); 550select * from int4_tbl where 551 (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in 552 (select ten from tenk1 b); 553 554-- 555-- Check for incorrect optimization when IN subquery contains a SRF 556-- 557explain (verbose, costs off) 558select * from int4_tbl o where (f1, f1) in 559 (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1); 560select * from int4_tbl o where (f1, f1) in 561 (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1); 562 563-- 564-- check for over-optimization of whole-row Var referencing an Append plan 565-- 566select (select q from 567 (select 1,2,3 where f1 > 0 568 union all 569 select 4,5,6.0 where f1 <= 0 570 ) q ) 571from int4_tbl; 572 573-- 574-- Check for sane handling of a lateral reference in a subquery's quals 575-- (most of the complication here is to prevent the test case from being 576-- flattened too much) 577-- 578explain (verbose, costs off) 579select * from 580 int4_tbl i4, 581 lateral ( 582 select i4.f1 > 1 as b, 1 as id 583 from (select random() order by 1) as t1 584 union all 585 select true as b, 2 as id 586 ) as t2 587where b and f1 >= 0; 588 589select * from 590 int4_tbl i4, 591 lateral ( 592 select i4.f1 > 1 as b, 1 as id 593 from (select random() order by 1) as t1 594 union all 595 select true as b, 2 as id 596 ) as t2 597where b and f1 >= 0; 598 599-- 600-- Check that volatile quals aren't pushed down past a DISTINCT: 601-- nextval() should not be called more than the nominal number of times 602-- 603create temp sequence ts1; 604 605select * from 606 (select distinct ten from tenk1) ss 607 where ten < 10 + nextval('ts1') 608 order by 1; 609 610select nextval('ts1'); 611 612-- 613-- Check that volatile quals aren't pushed down past a set-returning function; 614-- while a nonvolatile qual can be, if it doesn't reference the SRF. 615-- 616create function tattle(x int, y int) returns bool 617volatile language plpgsql as $$ 618begin 619 raise notice 'x = %, y = %', x, y; 620 return x > y; 621end$$; 622 623explain (verbose, costs off) 624select * from 625 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 626 where tattle(x, 8); 627 628select * from 629 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 630 where tattle(x, 8); 631 632-- if we pretend it's stable, we get different results: 633alter function tattle(x int, y int) stable; 634 635explain (verbose, costs off) 636select * from 637 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 638 where tattle(x, 8); 639 640select * from 641 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 642 where tattle(x, 8); 643 644-- although even a stable qual should not be pushed down if it references SRF 645explain (verbose, costs off) 646select * from 647 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 648 where tattle(x, u); 649 650select * from 651 (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss 652 where tattle(x, u); 653 654drop function tattle(x int, y int); 655 656-- 657-- Ensure that backward scan direction isn't propagated into 658-- expression subqueries (bug #15336) 659-- 660 661begin; 662 663declare c1 scroll cursor for 664 select * from generate_series(1,4) i 665 where i <> all (values (2),(3)); 666 667move forward all in c1; 668fetch backward all in c1; 669 670commit; 671