5 SELECT 1 AS one WHERE 1 IN (SELECT 1);
7 SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1);
9 SELECT 1 AS zero WHERE 1 IN (SELECT 2);
11 -- Set up some simple test tables
13 CREATE TABLE SUBSELECT_TBL (
19 INSERT INTO SUBSELECT_TBL VALUES (1, 2, 3);
20 INSERT INTO SUBSELECT_TBL VALUES (2, 3, 4);
21 INSERT INTO SUBSELECT_TBL VALUES (3, 4, 5);
22 INSERT INTO SUBSELECT_TBL VALUES (1, 1, 1);
23 INSERT INTO SUBSELECT_TBL VALUES (2, 2, 2);
24 INSERT INTO SUBSELECT_TBL VALUES (3, 3, 3);
25 INSERT INTO SUBSELECT_TBL VALUES (6, 7, 8);
26 INSERT INTO SUBSELECT_TBL VALUES (8, 9, NULL);
28 SELECT '' AS eight, * FROM SUBSELECT_TBL;
30 -- Uncorrelated subselects
32 SELECT '' AS two, f1 AS "Constant Select" FROM SUBSELECT_TBL
33 WHERE f1 IN (SELECT 1);
35 SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
36 WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL);
38 SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
39 WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE
40 f2 IN (SELECT f1 FROM SUBSELECT_TBL));
42 SELECT '' AS three, f1, f2
44 WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
45 WHERE f3 IS NOT NULL);
47 -- Correlated subselects
49 SELECT '' AS six, f1 AS "Correlated Field", f2 AS "Second Field"
50 FROM SUBSELECT_TBL upper
51 WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f1 = upper.f1);
53 SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
54 FROM SUBSELECT_TBL upper
56 (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(upper.f2 AS float) = f3);
58 SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
59 FROM SUBSELECT_TBL upper
60 WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL
61 WHERE f2 = CAST(f3 AS integer));
63 SELECT '' AS five, f1 AS "Correlated Field"
65 WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
66 WHERE f3 IS NOT NULL);
69 -- Use some existing tables in the regression test
72 SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field"
74 WHERE f1 NOT IN (SELECT f1+1 FROM INT4_TBL
75 WHERE f1 != ss.f1 AND f1 < 2147483647);
77 select q1, float8(count(*)) / (select count(*) from int8_tbl)
78 from int8_tbl group by q1 order by q1;
81 -- Test cases to catch unpleasant interactions between IN-join processing
82 -- and subquery pullup.
86 (select 1 from tenk1 a
87 where unique1 IN (select hundred from tenk1 b)) ss;
88 select count(distinct ss.ten) from
89 (select ten from tenk1 a
90 where unique1 IN (select hundred from tenk1 b)) ss;
92 (select 1 from tenk1 a
93 where unique1 IN (select distinct hundred from tenk1 b)) ss;
94 select count(distinct ss.ten) from
95 (select ten from tenk1 a
96 where unique1 IN (select distinct hundred from tenk1 b)) ss;
99 -- Test cases to check for overenthusiastic optimization of
100 -- "IN (SELECT DISTINCT ...)" and related cases. Per example from
101 -- Luca Pireddu and Michael Fuhr.
104 CREATE TEMP TABLE foo (id integer);
105 CREATE TEMP TABLE bar (id1 integer, id2 integer);
107 INSERT INTO foo VALUES (1);
109 INSERT INTO bar VALUES (1, 1);
110 INSERT INTO bar VALUES (2, 2);
111 INSERT INTO bar VALUES (3, 1);
113 -- These cases require an extra level of distinct-ing above subquery s
114 SELECT * FROM foo WHERE id IN
115 (SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM bar) AS s);
116 SELECT * FROM foo WHERE id IN
117 (SELECT id2 FROM (SELECT id1,id2 FROM bar GROUP BY id1,id2) AS s);
118 SELECT * FROM foo WHERE id IN
119 (SELECT id2 FROM (SELECT id1, id2 FROM bar UNION
120 SELECT id1, id2 FROM bar) AS s);
122 -- These cases do not
123 SELECT * FROM foo WHERE id IN
124 (SELECT id2 FROM (SELECT DISTINCT ON (id2) id1, id2 FROM bar) AS s);
125 SELECT * FROM foo WHERE id IN
126 (SELECT id2 FROM (SELECT id2 FROM bar GROUP BY id2) AS s);
127 SELECT * FROM foo WHERE id IN
128 (SELECT id2 FROM (SELECT id2 FROM bar UNION
129 SELECT id2 FROM bar) AS s);
132 -- Test case to catch problems with multiply nested sub-SELECTs not getting
133 -- recalculated properly. Per bug report from Didier Moens.
136 CREATE TABLE orderstest (
137 approver_ref integer,
139 ordercanceled boolean
142 INSERT INTO orderstest VALUES (1, 1, false);
143 INSERT INTO orderstest VALUES (66, 5, false);
144 INSERT INTO orderstest VALUES (66, 6, false);
145 INSERT INTO orderstest VALUES (66, 7, false);
146 INSERT INTO orderstest VALUES (66, 1, true);
147 INSERT INTO orderstest VALUES (66, 8, false);
148 INSERT INTO orderstest VALUES (66, 1, false);
149 INSERT INTO orderstest VALUES (77, 1, false);
150 INSERT INTO orderstest VALUES (1, 1, false);
151 INSERT INTO orderstest VALUES (66, 1, false);
152 INSERT INTO orderstest VALUES (1, 1, false);
154 CREATE VIEW orders_view AS
157 WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved'
160 WHEN ord.ordercanceled
167 WHEN ord.approver_ref=1
175 WHEN ord.ordercanceled
182 WHEN ord.approver_ref=1
191 SELECT * FROM orders_view;
193 DROP TABLE orderstest cascade;
196 -- Test cases to catch situations where rule rewriter fails to propagate
197 -- hasSubLinks flag correctly. Per example from Kyle Bateman.
200 create temp table parts (
205 create temp table shipped (
212 create temp view shipped_view as
213 select * from shipped where ttype = 'wt';
215 create rule shipped_view_insert as on insert to shipped_view do instead
216 insert into shipped values('wt', new.ordnum, new.partnum, new.value);
218 insert into parts (partnum, cost) values (1, 1234.56);
220 insert into shipped_view (ordnum, partnum, value)
221 values (0, 1, (select cost from parts where partnum = '1'));
223 select * from shipped_view;
225 create rule shipped_view_update as on update to shipped_view do instead
226 update shipped set partnum = new.partnum, value = new.value
227 where ttype = new.ttype and ordnum = new.ordnum;
229 update shipped_view set value = 11
230 from int4_tbl a join int4_tbl b
231 on (a.f1 = (select f1 from int4_tbl c where c.f1=b.f1))
234 select * from shipped_view;
236 select f1, ss1 as relabel from
237 (select *, (select sum(f1) from int4_tbl b where f1 >= a.f1) as ss1
241 -- Test cases involving PARAM_EXEC parameters and min/max index optimizations.
242 -- Per bug report from David Sanchez i Gregori.
246 select max(unique1) from tenk1 as a
247 where exists (select 1 from tenk1 as b where b.thousand = a.unique2)
251 select min(unique1) from tenk1 as a
252 where not exists (select 1 from tenk1 as b where b.unique2 = 10000)
256 -- Test that an IN implemented using a UniquePath does unique-ification
257 -- with the right semantics, as per bug #4113. (Unfortunately we have
258 -- no simple way to ensure that this test case actually chooses that type
259 -- of plan, but it does in releases 7.4-8.3. Note that an ordering difference
260 -- here might mean that some other plan type is being used, rendering the test
264 create temp table numeric_table (num_col numeric);
265 insert into numeric_table values (1), (1.000000000000000000001), (2), (3);
267 create temp table float_table (float_col float8);
268 insert into float_table values (1), (2), (3);
270 select * from float_table
271 where float_col in (select num_col from numeric_table);
273 select * from numeric_table
274 where num_col in (select float_col from float_table);
277 -- Test case for bug #4290: bogus calculation of subplan param sets
280 create temp table ta (id int primary key, val int);
282 insert into ta values(1,1);
283 insert into ta values(2,2);
285 create temp table tb (id int primary key, aval int);
287 insert into tb values(1,1);
288 insert into tb values(2,1);
289 insert into tb values(3,2);
290 insert into tb values(4,2);
292 create temp table tc (id int primary key, aid int);
294 insert into tc values(1,1);
295 insert into tc values(2,2);
298 ( select min(tb.id) from tb
299 where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_id
303 -- Test case for 8.3 "failed to locate grouping columns" bug
306 create temp table t1 (f1 numeric(14,0), f2 varchar(30));
309 (select distinct f1, f2, (select f2 from t1 x where x.f1 = up.f1) as fs
314 -- Test case for bug #5514 (mishandling of whole-row Vars in subselects)
317 create temp table table_a(id integer);
318 insert into table_a values (42);
320 create temp view view_a as select * from table_a;
322 select view_a from view_a;
323 select (select view_a) from view_a;
324 select (select (select view_a)) from view_a;
325 select (select (a.*)::text) from view_a a;
328 -- Test case for sublinks pushed down into subselects via join alias expansion
334 (select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy
337 int4_tbl i4 on dummy = i4.f1;
340 -- Test case for premature memory release during hashing of subplan output
343 select '1'::text in (select '1'::name union all select '1'::name);
346 -- Test case for planner bug with nested EXISTS handling
348 select a.thousand from tenk1 a, tenk1 b
349 where a.thousand = b.thousand
350 and exists ( select 1 from tenk1 c where b.hundred = c.hundred
351 and not exists ( select 1 from tenk1 d
352 where a.thousand = d.thousand ) );