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-- check materialization of an initplan reference (bug #14524) 96explain (verbose, costs off) 97select 1 = all (select (select 1)); 98select 1 = all (select (select 1)); 99 100-- 101-- Check EXISTS simplification with LIMIT 102-- 103explain (costs off) 104select * from int4_tbl o where exists 105 (select 1 from int4_tbl i where i.f1=o.f1 limit null); 106explain (costs off) 107select * from int4_tbl o where not exists 108 (select 1 from int4_tbl i where i.f1=o.f1 limit 1); 109explain (costs off) 110select * from int4_tbl o where exists 111 (select 1 from int4_tbl i where i.f1=o.f1 limit 0); 112 113-- 114-- Test cases to catch unpleasant interactions between IN-join processing 115-- and subquery pullup. 116-- 117 118select count(*) from 119 (select 1 from tenk1 a 120 where unique1 IN (select hundred from tenk1 b)) ss; 121select count(distinct ss.ten) from 122 (select ten from tenk1 a 123 where unique1 IN (select hundred from tenk1 b)) ss; 124select count(*) from 125 (select 1 from tenk1 a 126 where unique1 IN (select distinct hundred from tenk1 b)) ss; 127select count(distinct ss.ten) from 128 (select ten from tenk1 a 129 where unique1 IN (select distinct hundred from tenk1 b)) ss; 130 131-- 132-- Test cases to check for overenthusiastic optimization of 133-- "IN (SELECT DISTINCT ...)" and related cases. Per example from 134-- Luca Pireddu and Michael Fuhr. 135-- 136 137CREATE TEMP TABLE foo (id integer); 138CREATE TEMP TABLE bar (id1 integer, id2 integer); 139 140INSERT INTO foo VALUES (1); 141 142INSERT INTO bar VALUES (1, 1); 143INSERT INTO bar VALUES (2, 2); 144INSERT INTO bar VALUES (3, 1); 145 146-- These cases require an extra level of distinct-ing above subquery s 147SELECT * FROM foo WHERE id IN 148 (SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM bar) AS s); 149SELECT * FROM foo WHERE id IN 150 (SELECT id2 FROM (SELECT id1,id2 FROM bar GROUP BY id1,id2) AS s); 151SELECT * FROM foo WHERE id IN 152 (SELECT id2 FROM (SELECT id1, id2 FROM bar UNION 153 SELECT id1, id2 FROM bar) AS s); 154 155-- These cases do not 156SELECT * FROM foo WHERE id IN 157 (SELECT id2 FROM (SELECT DISTINCT ON (id2) id1, id2 FROM bar) AS s); 158SELECT * FROM foo WHERE id IN 159 (SELECT id2 FROM (SELECT id2 FROM bar GROUP BY id2) AS s); 160SELECT * FROM foo WHERE id IN 161 (SELECT id2 FROM (SELECT id2 FROM bar UNION 162 SELECT id2 FROM bar) AS s); 163 164-- 165-- Test case to catch problems with multiply nested sub-SELECTs not getting 166-- recalculated properly. Per bug report from Didier Moens. 167-- 168 169CREATE TABLE orderstest ( 170 approver_ref integer, 171 po_ref integer, 172 ordercanceled boolean 173); 174 175INSERT INTO orderstest VALUES (1, 1, false); 176INSERT INTO orderstest VALUES (66, 5, false); 177INSERT INTO orderstest VALUES (66, 6, false); 178INSERT INTO orderstest VALUES (66, 7, false); 179INSERT INTO orderstest VALUES (66, 1, true); 180INSERT INTO orderstest VALUES (66, 8, false); 181INSERT INTO orderstest VALUES (66, 1, false); 182INSERT INTO orderstest VALUES (77, 1, false); 183INSERT INTO orderstest VALUES (1, 1, false); 184INSERT INTO orderstest VALUES (66, 1, false); 185INSERT INTO orderstest VALUES (1, 1, false); 186 187CREATE VIEW orders_view AS 188SELECT *, 189(SELECT CASE 190 WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved' 191 END) AS "Approved", 192(SELECT CASE 193 WHEN ord.ordercanceled 194 THEN 'Canceled' 195 ELSE 196 (SELECT CASE 197 WHEN ord.po_ref=1 198 THEN 199 (SELECT CASE 200 WHEN ord.approver_ref=1 201 THEN '---' 202 ELSE 'Approved' 203 END) 204 ELSE 'PO' 205 END) 206END) AS "Status", 207(CASE 208 WHEN ord.ordercanceled 209 THEN 'Canceled' 210 ELSE 211 (CASE 212 WHEN ord.po_ref=1 213 THEN 214 (CASE 215 WHEN ord.approver_ref=1 216 THEN '---' 217 ELSE 'Approved' 218 END) 219 ELSE 'PO' 220 END) 221END) AS "Status_OK" 222FROM orderstest ord; 223 224SELECT * FROM orders_view; 225 226DROP TABLE orderstest cascade; 227 228-- 229-- Test cases to catch situations where rule rewriter fails to propagate 230-- hasSubLinks flag correctly. Per example from Kyle Bateman. 231-- 232 233create temp table parts ( 234 partnum text, 235 cost float8 236); 237 238create temp table shipped ( 239 ttype char(2), 240 ordnum int4, 241 partnum text, 242 value float8 243); 244 245create temp view shipped_view as 246 select * from shipped where ttype = 'wt'; 247 248create rule shipped_view_insert as on insert to shipped_view do instead 249 insert into shipped values('wt', new.ordnum, new.partnum, new.value); 250 251insert into parts (partnum, cost) values (1, 1234.56); 252 253insert into shipped_view (ordnum, partnum, value) 254 values (0, 1, (select cost from parts where partnum = '1')); 255 256select * from shipped_view; 257 258create rule shipped_view_update as on update to shipped_view do instead 259 update shipped set partnum = new.partnum, value = new.value 260 where ttype = new.ttype and ordnum = new.ordnum; 261 262update shipped_view set value = 11 263 from int4_tbl a join int4_tbl b 264 on (a.f1 = (select f1 from int4_tbl c where c.f1=b.f1)) 265 where ordnum = a.f1; 266 267select * from shipped_view; 268 269select f1, ss1 as relabel from 270 (select *, (select sum(f1) from int4_tbl b where f1 >= a.f1) as ss1 271 from int4_tbl a) ss; 272 273-- 274-- Test cases involving PARAM_EXEC parameters and min/max index optimizations. 275-- Per bug report from David Sanchez i Gregori. 276-- 277 278select * from ( 279 select max(unique1) from tenk1 as a 280 where exists (select 1 from tenk1 as b where b.thousand = a.unique2) 281) ss; 282 283select * from ( 284 select min(unique1) from tenk1 as a 285 where not exists (select 1 from tenk1 as b where b.unique2 = 10000) 286) ss; 287 288-- 289-- Test that an IN implemented using a UniquePath does unique-ification 290-- with the right semantics, as per bug #4113. (Unfortunately we have 291-- no simple way to ensure that this test case actually chooses that type 292-- of plan, but it does in releases 7.4-8.3. Note that an ordering difference 293-- here might mean that some other plan type is being used, rendering the test 294-- pointless.) 295-- 296 297create temp table numeric_table (num_col numeric); 298insert into numeric_table values (1), (1.000000000000000000001), (2), (3); 299 300create temp table float_table (float_col float8); 301insert into float_table values (1), (2), (3); 302 303select * from float_table 304 where float_col in (select num_col from numeric_table); 305 306select * from numeric_table 307 where num_col in (select float_col from float_table); 308 309-- 310-- Test case for bug #4290: bogus calculation of subplan param sets 311-- 312 313create temp table ta (id int primary key, val int); 314 315insert into ta values(1,1); 316insert into ta values(2,2); 317 318create temp table tb (id int primary key, aval int); 319 320insert into tb values(1,1); 321insert into tb values(2,1); 322insert into tb values(3,2); 323insert into tb values(4,2); 324 325create temp table tc (id int primary key, aid int); 326 327insert into tc values(1,1); 328insert into tc values(2,2); 329 330select 331 ( select min(tb.id) from tb 332 where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_id 333from tc; 334 335-- 336-- Test case for 8.3 "failed to locate grouping columns" bug 337-- 338 339create temp table t1 (f1 numeric(14,0), f2 varchar(30)); 340 341select * from 342 (select distinct f1, f2, (select f2 from t1 x where x.f1 = up.f1) as fs 343 from t1 up) ss 344group by f1,f2,fs; 345 346-- 347-- Test case for bug #5514 (mishandling of whole-row Vars in subselects) 348-- 349 350create temp table table_a(id integer); 351insert into table_a values (42); 352 353create temp view view_a as select * from table_a; 354 355select view_a from view_a; 356select (select view_a) from view_a; 357select (select (select view_a)) from view_a; 358select (select (a.*)::text) from view_a a; 359 360-- 361-- Check that whole-row Vars reading the result of a subselect don't include 362-- any junk columns therein 363-- 364 365select q from (select max(f1) from int4_tbl group by f1 order by f1) q; 366with q as (select max(f1) from int4_tbl group by f1 order by f1) 367 select q from q; 368 369-- 370-- Test case for sublinks pushed down into subselects via join alias expansion 371-- 372 373select 374 (select sq1) as qq1 375from 376 (select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy 377 from int8_tbl) sq0 378 join 379 int4_tbl i4 on dummy = i4.f1; 380 381-- 382-- Test case for subselect within UPDATE of INSERT...ON CONFLICT DO UPDATE 383-- 384create temp table upsert(key int4 primary key, val text); 385insert into upsert values(1, 'val') on conflict (key) do update set val = 'not seen'; 386insert 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; 387 388select * from upsert; 389 390with aa as (select 'int4_tbl' u from int4_tbl limit 1) 391insert into upsert values (1, 'x'), (999, 'y') 392on conflict (key) do update set val = (select u from aa) 393returning *; 394 395-- 396-- Test case for cross-type partial matching in hashed subplan (bug #7597) 397-- 398 399create temp table outer_7597 (f1 int4, f2 int4); 400insert into outer_7597 values (0, 0); 401insert into outer_7597 values (1, 0); 402insert into outer_7597 values (0, null); 403insert into outer_7597 values (1, null); 404 405create temp table inner_7597(c1 int8, c2 int8); 406insert into inner_7597 values(0, null); 407 408select * from outer_7597 where (f1, f2) not in (select * from inner_7597); 409 410-- 411-- Test case for premature memory release during hashing of subplan output 412-- 413 414select '1'::text in (select '1'::name union all select '1'::name); 415 416-- 417-- Test that we don't try to use a hashed subplan if the simplified 418-- testexpr isn't of the right shape 419-- 420 421create temp table inner_text (c1 text, c2 text); 422insert into inner_text values ('a', null); 423insert into inner_text values ('123', '456'); 424 425-- this fails by default, of course 426select * from int8_tbl where q1 in (select c1 from inner_text); 427 428begin; 429 430-- make an operator to allow it to succeed 431create function bogus_int8_text_eq(int8, text) returns boolean 432language sql as 'select $1::text = $2'; 433 434create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text); 435 436explain (costs off) 437select * from int8_tbl where q1 in (select c1 from inner_text); 438select * from int8_tbl where q1 in (select c1 from inner_text); 439 440-- inlining of this function results in unusual number of hash clauses, 441-- which we can still cope with 442create or replace function bogus_int8_text_eq(int8, text) returns boolean 443language sql as 'select $1::text = $2 and $1::text = $2'; 444 445explain (costs off) 446select * from int8_tbl where q1 in (select c1 from inner_text); 447select * from int8_tbl where q1 in (select c1 from inner_text); 448 449-- inlining of this function causes LHS and RHS to be switched, 450-- which we can't cope with, so hashing should be abandoned 451create or replace function bogus_int8_text_eq(int8, text) returns boolean 452language sql as 'select $2 = $1::text'; 453 454explain (costs off) 455select * from int8_tbl where q1 in (select c1 from inner_text); 456select * from int8_tbl where q1 in (select c1 from inner_text); 457 458rollback; -- to get rid of the bogus operator 459 460-- 461-- Test case for planner bug with nested EXISTS handling 462-- 463select a.thousand from tenk1 a, tenk1 b 464where a.thousand = b.thousand 465 and exists ( select 1 from tenk1 c where b.hundred = c.hundred 466 and not exists ( select 1 from tenk1 d 467 where a.thousand = d.thousand ) ); 468 469-- 470-- Check that nested sub-selects are not pulled up if they contain volatiles 471-- 472explain (verbose, costs off) 473 select x, x from 474 (select (select now()) as x from (values(1),(2)) v(y)) ss; 475explain (verbose, costs off) 476 select x, x from 477 (select (select random()) as x from (values(1),(2)) v(y)) ss; 478explain (verbose, costs off) 479 select x, x from 480 (select (select now() where y=y) as x from (values(1),(2)) v(y)) ss; 481explain (verbose, costs off) 482 select x, x from 483 (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss; 484 485-- 486-- Check we behave sanely in corner case of empty SELECT list (bug #8648) 487-- 488create temp table nocolumns(); 489select exists(select * from nocolumns); 490 491-- 492-- Check behavior with a SubPlan in VALUES (bug #14924) 493-- 494select val.x 495 from generate_series(1,10) as s(i), 496 lateral ( 497 values ((select s.i + 1)), (s.i + 101) 498 ) as val(x) 499where s.i < 10 and (select val.x) < 110; 500 501-- another variant of that (bug #16213) 502explain (verbose, costs off) 503select * from 504(values 505 (3 not in (select * from (values (1), (2)) ss1)), 506 (false) 507) ss; 508 509select * from 510(values 511 (3 not in (select * from (values (1), (2)) ss1)), 512 (false) 513) ss; 514 515-- 516-- Check sane behavior with nested IN SubLinks 517-- 518explain (verbose, costs off) 519select * from int4_tbl where 520 (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in 521 (select ten from tenk1 b); 522select * from int4_tbl where 523 (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in 524 (select ten from tenk1 b); 525 526-- 527-- Check for incorrect optimization when IN subquery contains a SRF 528-- 529explain (verbose, costs off) 530select * from int4_tbl o where (f1, f1) in 531 (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1); 532select * from int4_tbl o where (f1, f1) in 533 (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1); 534 535-- 536-- check for over-optimization of whole-row Var referencing an Append plan 537-- 538select (select q from 539 (select 1,2,3 where f1 > 0 540 union all 541 select 4,5,6.0 where f1 <= 0 542 ) q ) 543from int4_tbl; 544 545-- 546-- Check for sane handling of a lateral reference in a subquery's quals 547-- (most of the complication here is to prevent the test case from being 548-- flattened too much) 549-- 550explain (verbose, costs off) 551select * from 552 int4_tbl i4, 553 lateral ( 554 select i4.f1 > 1 as b, 1 as id 555 from (select random() order by 1) as t1 556 union all 557 select true as b, 2 as id 558 ) as t2 559where b and f1 >= 0; 560 561select * from 562 int4_tbl i4, 563 lateral ( 564 select i4.f1 > 1 as b, 1 as id 565 from (select random() order by 1) as t1 566 union all 567 select true as b, 2 as id 568 ) as t2 569where b and f1 >= 0; 570 571-- 572-- Check that volatile quals aren't pushed down past a DISTINCT: 573-- nextval() should not be called more than the nominal number of times 574-- 575create temp sequence ts1; 576 577select * from 578 (select distinct ten from tenk1) ss 579 where ten < 10 + nextval('ts1') 580 order by 1; 581 582select nextval('ts1'); 583 584-- 585-- Ensure that backward scan direction isn't propagated into 586-- expression subqueries (bug #15336) 587-- 588 589begin; 590 591declare c1 scroll cursor for 592 select * from generate_series(1,4) i 593 where i <> all (values (2),(3)); 594 595move forward all in c1; 596fetch backward all in c1; 597 598commit; 599