4 SELECT 1 AS one WHERE 1 IN (SELECT 1);
10 SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1);
15 SELECT 1 AS zero WHERE 1 IN (SELECT 2);
20 -- Set up some simple test tables
21 CREATE TABLE SUBSELECT_TBL (
26 INSERT INTO SUBSELECT_TBL VALUES (1, 2, 3);
27 INSERT INTO SUBSELECT_TBL VALUES (2, 3, 4);
28 INSERT INTO SUBSELECT_TBL VALUES (3, 4, 5);
29 INSERT INTO SUBSELECT_TBL VALUES (1, 1, 1);
30 INSERT INTO SUBSELECT_TBL VALUES (2, 2, 2);
31 INSERT INTO SUBSELECT_TBL VALUES (3, 3, 3);
32 INSERT INTO SUBSELECT_TBL VALUES (6, 7, 8);
33 INSERT INTO SUBSELECT_TBL VALUES (8, 9, NULL);
34 SELECT '' AS eight, * FROM SUBSELECT_TBL;
36 -------+----+----+----
47 -- Uncorrelated subselects
48 SELECT '' AS two, f1 AS "Constant Select" FROM SUBSELECT_TBL
49 WHERE f1 IN (SELECT 1);
51 -----+-----------------
56 SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
57 WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL);
58 six | Uncorrelated Field
59 -----+--------------------
68 SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
69 WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE
70 f2 IN (SELECT f1 FROM SUBSELECT_TBL));
71 six | Uncorrelated Field
72 -----+--------------------
81 SELECT '' AS three, f1, f2
83 WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
84 WHERE f3 IS NOT NULL);
92 -- Correlated subselects
93 SELECT '' AS six, f1 AS "Correlated Field", f2 AS "Second Field"
94 FROM SUBSELECT_TBL upper
95 WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f1 = upper.f1);
96 six | Correlated Field | Second Field
97 -----+------------------+--------------
106 SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
107 FROM SUBSELECT_TBL upper
109 (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(upper.f2 AS float) = f3);
110 six | Correlated Field | Second Field
111 -----+------------------+--------------
119 SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
120 FROM SUBSELECT_TBL upper
121 WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL
122 WHERE f2 = CAST(f3 AS integer));
123 six | Correlated Field | Second Field
124 -----+------------------+--------------
131 SELECT '' AS five, f1 AS "Correlated Field"
133 WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
134 WHERE f3 IS NOT NULL);
135 five | Correlated Field
136 ------+------------------
145 -- Use some existing tables in the regression test
147 SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field"
148 FROM SUBSELECT_TBL ss
149 WHERE f1 NOT IN (SELECT f1+1 FROM INT4_TBL
150 WHERE f1 != ss.f1 AND f1 < 2147483647);
151 eight | Correlated Field | Second Field
152 -------+------------------+--------------
161 select q1, float8(count(*)) / (select count(*) from int8_tbl)
162 from int8_tbl group by q1 order by q1;
164 ------------------+----------
166 4567890123456789 | 0.6
170 -- Test cases to catch unpleasant interactions between IN-join processing
171 -- and subquery pullup.
174 (select 1 from tenk1 a
175 where unique1 IN (select hundred from tenk1 b)) ss;
181 select count(distinct ss.ten) from
182 (select ten from tenk1 a
183 where unique1 IN (select hundred from tenk1 b)) ss;
190 (select 1 from tenk1 a
191 where unique1 IN (select distinct hundred from tenk1 b)) ss;
197 select count(distinct ss.ten) from
198 (select ten from tenk1 a
199 where unique1 IN (select distinct hundred from tenk1 b)) ss;
206 -- Test cases to check for overenthusiastic optimization of
207 -- "IN (SELECT DISTINCT ...)" and related cases. Per example from
208 -- Luca Pireddu and Michael Fuhr.
210 CREATE TEMP TABLE foo (id integer);
211 CREATE TEMP TABLE bar (id1 integer, id2 integer);
212 INSERT INTO foo VALUES (1);
213 INSERT INTO bar VALUES (1, 1);
214 INSERT INTO bar VALUES (2, 2);
215 INSERT INTO bar VALUES (3, 1);
216 -- These cases require an extra level of distinct-ing above subquery s
217 SELECT * FROM foo WHERE id IN
218 (SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM bar) AS s);
224 SELECT * FROM foo WHERE id IN
225 (SELECT id2 FROM (SELECT id1,id2 FROM bar GROUP BY id1,id2) AS s);
231 SELECT * FROM foo WHERE id IN
232 (SELECT id2 FROM (SELECT id1, id2 FROM bar UNION
233 SELECT id1, id2 FROM bar) AS s);
239 -- These cases do not
240 SELECT * FROM foo WHERE id IN
241 (SELECT id2 FROM (SELECT DISTINCT ON (id2) id1, id2 FROM bar) AS s);
247 SELECT * FROM foo WHERE id IN
248 (SELECT id2 FROM (SELECT id2 FROM bar GROUP BY id2) AS s);
254 SELECT * FROM foo WHERE id IN
255 (SELECT id2 FROM (SELECT id2 FROM bar UNION
256 SELECT id2 FROM bar) AS s);
263 -- Test case to catch problems with multiply nested sub-SELECTs not getting
264 -- recalculated properly. Per bug report from Didier Moens.
266 CREATE TABLE orderstest (
267 approver_ref integer,
269 ordercancelled boolean
271 INSERT INTO orderstest VALUES (1, 1, false);
272 INSERT INTO orderstest VALUES (66, 5, false);
273 INSERT INTO orderstest VALUES (66, 6, false);
274 INSERT INTO orderstest VALUES (66, 7, false);
275 INSERT INTO orderstest VALUES (66, 1, true);
276 INSERT INTO orderstest VALUES (66, 8, false);
277 INSERT INTO orderstest VALUES (66, 1, false);
278 INSERT INTO orderstest VALUES (77, 1, false);
279 INSERT INTO orderstest VALUES (1, 1, false);
280 INSERT INTO orderstest VALUES (66, 1, false);
281 INSERT INTO orderstest VALUES (1, 1, false);
282 CREATE VIEW orders_view AS
285 WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved'
288 WHEN ord.ordercancelled
295 WHEN ord.approver_ref=1
303 WHEN ord.ordercancelled
310 WHEN ord.approver_ref=1
318 SELECT * FROM orders_view;
319 approver_ref | po_ref | ordercancelled | Approved | Status | Status_OK
320 --------------+--------+----------------+----------+-----------+-----------
321 1 | 1 | f | --- | --- | ---
322 66 | 5 | f | Approved | PO | PO
323 66 | 6 | f | Approved | PO | PO
324 66 | 7 | f | Approved | PO | PO
325 66 | 1 | t | Approved | Cancelled | Cancelled
326 66 | 8 | f | Approved | PO | PO
327 66 | 1 | f | Approved | Approved | Approved
328 77 | 1 | f | Approved | Approved | Approved
329 1 | 1 | f | --- | --- | ---
330 66 | 1 | f | Approved | Approved | Approved
331 1 | 1 | f | --- | --- | ---
334 DROP TABLE orderstest cascade;
335 NOTICE: drop cascades to view orders_view
337 -- Test cases to catch situations where rule rewriter fails to propagate
338 -- hasSubLinks flag correctly. Per example from Kyle Bateman.
340 create temp table parts (
344 create temp table shipped (
350 create temp view shipped_view as
351 select * from shipped where ttype = 'wt';
352 create rule shipped_view_insert as on insert to shipped_view do instead
353 insert into shipped values('wt', new.ordnum, new.partnum, new.value);
354 insert into parts (partnum, cost) values (1, 1234.56);
355 insert into shipped_view (ordnum, partnum, value)
356 values (0, 1, (select cost from parts where partnum = '1'));
357 select * from shipped_view;
358 ttype | ordnum | partnum | value
359 -------+--------+---------+---------
363 create rule shipped_view_update as on update to shipped_view do instead
364 update shipped set partnum = new.partnum, value = new.value
365 where ttype = new.ttype and ordnum = new.ordnum;
366 update shipped_view set value = 11
367 from int4_tbl a join int4_tbl b
368 on (a.f1 = (select f1 from int4_tbl c where c.f1=b.f1))
370 select * from shipped_view;
371 ttype | ordnum | partnum | value
372 -------+--------+---------+-------
376 select f1, ss1 as relabel from
377 (select *, (select sum(f1) from int4_tbl b where f1 >= a.f1) as ss1
380 -------------+------------
384 2147483647 | 2147483647
389 -- Test cases involving PARAM_EXEC parameters and min/max index optimizations.
390 -- Per bug report from David Sanchez i Gregori.
393 select max(unique1) from tenk1 as a
394 where exists (select 1 from tenk1 as b where b.thousand = a.unique2)
402 select min(unique1) from tenk1 as a
403 where not exists (select 1 from tenk1 as b where b.unique2 = 10000)
411 -- Test that an IN implemented using a UniquePath does unique-ification
412 -- with the right semantics, as per bug #4113. (Unfortunately we have
413 -- no simple way to ensure that this test case actually chooses that type
414 -- of plan, but it does in releases 7.4-8.3. Note that an ordering difference
415 -- here might mean that some other plan type is being used, rendering the test
418 create temp table numeric_table (num_col numeric);
419 insert into numeric_table values (1), (1.000000000000000000001), (2), (3);
420 create temp table float_table (float_col float8);
421 insert into float_table values (1), (2), (3);
422 select * from float_table
423 where float_col in (select num_col from numeric_table);
431 select * from numeric_table
432 where num_col in (select float_col from float_table);
434 -------------------------
436 1.000000000000000000001
442 -- Test case for bug #4290: bogus calculation of subplan param sets
444 create temp table ta (id int primary key, val int);
445 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ta_pkey" for table "ta"
446 insert into ta values(1,1);
447 insert into ta values(2,2);
448 create temp table tb (id int primary key, aval int);
449 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tb_pkey" for table "tb"
450 insert into tb values(1,1);
451 insert into tb values(2,1);
452 insert into tb values(3,2);
453 insert into tb values(4,2);
454 create temp table tc (id int primary key, aid int);
455 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tc_pkey" for table "tc"
456 insert into tc values(1,1);
457 insert into tc values(2,2);
459 ( select min(tb.id) from tb
460 where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_id
469 -- Test case for 8.3 "failed to locate grouping columns" bug
471 create temp table t1 (f1 numeric(14,0), f2 varchar(30));
473 (select distinct f1, f2, (select f2 from t1 x where x.f1 = up.f1) as fs
481 -- Test case for bug #5514 (mishandling of whole-row Vars in subselects)
483 create temp table table_a(id integer);
484 insert into table_a values (42);
485 create temp view view_a as select * from table_a;
486 select view_a from view_a;
492 select (select view_a) from view_a;
498 select (select (select view_a)) from view_a;
504 select (select (a.*)::text) from view_a a;
511 -- Test case for sublinks pushed down into subselects via join alias expansion
516 (select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy
519 int4_tbl i4 on dummy = i4.f1;
525 -- Test case for premature memory release during hashing of subplan output
527 select '1'::text in (select '1'::name union all select '1'::name);