granicus.if.org Git - postgresql/blob - src/test/regress/sql/subselect.sql

   1 --
   2 -- SUBSELECT
   3 --
   4
   5 SELECT 1 AS one WHERE 1 IN (SELECT 1);
   6
   7 SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1);
   8
   9 SELECT 1 AS zero WHERE 1 IN (SELECT 2);
  10
  11 -- Set up some simple test tables
  12
  13 CREATE TABLE SUBSELECT_TBL (
  14   f1 integer,
  15   f2 integer,
  16   f3 float
  17 );
  18
  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);
  27
  28 SELECT '' AS eight, * FROM SUBSELECT_TBL;
  29
  30 -- Uncorrelated subselects
  31
  32 SELECT '' AS two, f1 AS "Constant Select" FROM SUBSELECT_TBL
  33   WHERE f1 IN (SELECT 1);
  34
  35 SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
  36   WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL);
  37
  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));
  41
  42 SELECT '' AS three, f1, f2
  43   FROM SUBSELECT_TBL
  44   WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
  45                          WHERE f3 IS NOT NULL);
  46
  47 -- Correlated subselects
  48
  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);
  52
  53 SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"
  54   FROM SUBSELECT_TBL upper
  55   WHERE f1 IN
  56     (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(upper.f2 AS float) = f3);
  57
  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));
  62
  63 SELECT '' AS five, f1 AS "Correlated Field"
  64   FROM SUBSELECT_TBL
  65   WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
  66                      WHERE f3 IS NOT NULL);
  67
  68 --
  69 -- Use some existing tables in the regression test
  70 --
  71
  72 SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field"
  73   FROM SUBSELECT_TBL ss
  74   WHERE f1 NOT IN (SELECT f1+1 FROM INT4_TBL WHERE f1 != ss.f1);
  75
  76 select q1, float8(count(*)) / (select count(*) from int8_tbl)
  77 from int8_tbl group by q1 order by q1;
  78
  79 --
  80 -- Test cases to catch unpleasant interactions between IN-join processing
  81 -- and subquery pullup.
  82 --
  83
  84 select count(*) from
  85   (select 1 from tenk1 a
  86    where unique1 IN (select hundred from tenk1 b)) ss;
  87 select count(distinct ss.ten) from
  88   (select ten from tenk1 a
  89    where unique1 IN (select hundred from tenk1 b)) ss;
  90 select count(*) from
  91   (select 1 from tenk1 a
  92    where unique1 IN (select distinct hundred from tenk1 b)) ss;
  93 select count(distinct ss.ten) from
  94   (select ten from tenk1 a
  95    where unique1 IN (select distinct hundred from tenk1 b)) ss;