-- -- SUBSELECT -- SELECT 1 AS one WHERE 1 IN (SELECT 1); one ----- 1 (1 row) SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1); zero ------ (0 rows) SELECT 1 AS zero WHERE 1 IN (SELECT 2); zero ------ (0 rows) -- Set up some simple test tables CREATE TABLE SUBSELECT_TBL ( f1 integer, f2 integer, f3 float ); INSERT INTO SUBSELECT_TBL VALUES (1, 2, 3); INSERT INTO SUBSELECT_TBL VALUES (2, 3, 4); INSERT INTO SUBSELECT_TBL VALUES (3, 4, 5); INSERT INTO SUBSELECT_TBL VALUES (1, 1, 1); INSERT INTO SUBSELECT_TBL VALUES (2, 2, 2); INSERT INTO SUBSELECT_TBL VALUES (3, 3, 3); INSERT INTO SUBSELECT_TBL VALUES (6, 7, 8); INSERT INTO SUBSELECT_TBL VALUES (8, 9, NULL); SELECT '' AS eight, * FROM SUBSELECT_TBL; eight | f1 | f2 | f3 -------+----+----+---- | 1 | 2 | 3 | 2 | 3 | 4 | 3 | 4 | 5 | 1 | 1 | 1 | 2 | 2 | 2 | 3 | 3 | 3 | 6 | 7 | 8 | 8 | 9 | (8 rows) -- Uncorrelated subselects SELECT '' AS two, f1 AS "Constant Select" FROM SUBSELECT_TBL WHERE f1 IN (SELECT 1); two | Constant Select -----+----------------- | 1 | 1 (2 rows) SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL); six | Uncorrelated Field -----+-------------------- | 1 | 2 | 3 | 1 | 2 | 3 (6 rows) SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f2 IN (SELECT f1 FROM SUBSELECT_TBL)); six | Uncorrelated Field -----+-------------------- | 1 | 2 | 3 | 1 | 2 | 3 (6 rows) SELECT '' AS three, f1, f2 FROM SUBSELECT_TBL WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL WHERE f3 IS NOT NULL); three | f1 | f2 -------+----+---- | 1 | 2 | 6 | 7 | 8 | 9 (3 rows) -- Correlated subselects SELECT '' AS six, f1 AS "Correlated Field", f2 AS "Second Field" FROM SUBSELECT_TBL upper WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f1 = upper.f1); six | Correlated Field | Second Field -----+------------------+-------------- | 1 | 2 | 2 | 3 | 3 | 4 | 1 | 1 | 2 | 2 | 3 | 3 (6 rows) SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" FROM SUBSELECT_TBL upper WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(upper.f2 AS float) = f3); six | Correlated Field | Second Field -----+------------------+-------------- | 2 | 4 | 3 | 5 | 1 | 1 | 2 | 2 | 3 | 3 (5 rows) SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" FROM SUBSELECT_TBL upper WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL WHERE f2 = CAST(f3 AS integer)); six | Correlated Field | Second Field -----+------------------+-------------- | 1 | 3 | 2 | 4 | 3 | 5 | 6 | 8 (4 rows) SELECT '' AS five, f1 AS "Correlated Field" FROM SUBSELECT_TBL WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL WHERE f3 IS NOT NULL); five | Correlated Field ------+------------------ | 2 | 3 | 1 | 2 | 3 (5 rows) -- -- Use some existing tables in the regression test -- SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field" FROM SUBSELECT_TBL ss WHERE f1 NOT IN (SELECT f1+1 FROM INT4_TBL WHERE f1 != ss.f1 AND f1 < 2147483647); eight | Correlated Field | Second Field -------+------------------+-------------- | 2 | 4 | 3 | 5 | 2 | 2 | 3 | 3 | 6 | 8 | 8 | (6 rows) select q1, float8(count(*)) / (select count(*) from int8_tbl) from int8_tbl group by q1 order by q1; q1 | ?column? ------------------+---------- 123 | 0.4 4567890123456789 | 0.6 (2 rows) -- -- Test cases to catch unpleasant interactions between IN-join processing -- and subquery pullup. -- select count(*) from (select 1 from tenk1 a where unique1 IN (select hundred from tenk1 b)) ss; count ------- 100 (1 row) select count(distinct ss.ten) from (select ten from tenk1 a where unique1 IN (select hundred from tenk1 b)) ss; count ------- 10 (1 row) select count(*) from (select 1 from tenk1 a where unique1 IN (select distinct hundred from tenk1 b)) ss; count ------- 100 (1 row) select count(distinct ss.ten) from (select ten from tenk1 a where unique1 IN (select distinct hundred from tenk1 b)) ss; count ------- 10 (1 row) -- -- Test case to catch problems with multiply nested sub-SELECTs not getting -- recalculated properly. Per bug report from Didier Moens. -- CREATE TABLE orderstest ( approver_ref integer, po_ref integer, ordercancelled boolean ); INSERT INTO orderstest VALUES (1, 1, false); INSERT INTO orderstest VALUES (66, 5, false); INSERT INTO orderstest VALUES (66, 6, false); INSERT INTO orderstest VALUES (66, 7, false); INSERT INTO orderstest VALUES (66, 1, true); INSERT INTO orderstest VALUES (66, 8, false); INSERT INTO orderstest VALUES (66, 1, false); INSERT INTO orderstest VALUES (77, 1, false); INSERT INTO orderstest VALUES (1, 1, false); INSERT INTO orderstest VALUES (66, 1, false); INSERT INTO orderstest VALUES (1, 1, false); CREATE VIEW orders_view AS SELECT *, (SELECT CASE WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved' END) AS "Approved", (SELECT CASE WHEN ord.ordercancelled THEN 'Cancelled' ELSE (SELECT CASE WHEN ord.po_ref=1 THEN (SELECT CASE WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved' END) ELSE 'PO' END) END) AS "Status", (CASE WHEN ord.ordercancelled THEN 'Cancelled' ELSE (CASE WHEN ord.po_ref=1 THEN (CASE WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved' END) ELSE 'PO' END) END) AS "Status_OK" FROM orderstest ord; SELECT * FROM orders_view; approver_ref | po_ref | ordercancelled | Approved | Status | Status_OK --------------+--------+----------------+----------+-----------+----------- 1 | 1 | f | --- | --- | --- 66 | 5 | f | Approved | PO | PO 66 | 6 | f | Approved | PO | PO 66 | 7 | f | Approved | PO | PO 66 | 1 | t | Approved | Cancelled | Cancelled 66 | 8 | f | Approved | PO | PO 66 | 1 | f | Approved | Approved | Approved 77 | 1 | f | Approved | Approved | Approved 1 | 1 | f | --- | --- | --- 66 | 1 | f | Approved | Approved | Approved 1 | 1 | f | --- | --- | --- (11 rows) DROP TABLE orderstest cascade; NOTICE: drop cascades to rule _RETURN on view orders_view NOTICE: drop cascades to view orders_view