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
  75                    WHERE f1 != ss.f1 AND f1 < 2147483647);
  76
  77 select q1, float8(count(*)) / (select count(*) from int8_tbl)
  78 from int8_tbl group by q1 order by q1;
  79
  80 --
  81 -- Test cases to catch unpleasant interactions between IN-join processing
  82 -- and subquery pullup.
  83 --
  84
  85 select count(*) from
  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;
  91 select count(*) from
  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;
  97
  98 --
  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.
 102 --
 103
 104 CREATE TEMP TABLE foo (id integer);
 105 CREATE TEMP TABLE bar (id1 integer, id2 integer);
 106
 107 INSERT INTO foo VALUES (1);
 108
 109 INSERT INTO bar VALUES (1, 1);
 110 INSERT INTO bar VALUES (2, 2);
 111 INSERT INTO bar VALUES (3, 1);
 112
 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);
 121
 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);
 130
 131 --
 132 -- Test case to catch problems with multiply nested sub-SELECTs not getting
 133 -- recalculated properly.  Per bug report from Didier Moens.
 134 --
 135
 136 CREATE TABLE orderstest (
 137     approver_ref integer,
 138     po_ref integer,
 139     ordercancelled boolean
 140 );
 141
 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);
 153
 154 CREATE VIEW orders_view AS
 155 SELECT *,
 156 (SELECT CASE
 157    WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved'
 158  END) AS "Approved",
 159 (SELECT CASE
 160  WHEN ord.ordercancelled
 161  THEN 'Cancelled'
 162  ELSE
 163   (SELECT CASE
 164                 WHEN ord.po_ref=1
 165                 THEN
 166                  (SELECT CASE
 167                                 WHEN ord.approver_ref=1
 168                                 THEN '---'
 169                                 ELSE 'Approved'
 170                         END)
 171                 ELSE 'PO'
 172         END)
 173 END) AS "Status",
 174 (CASE
 175  WHEN ord.ordercancelled
 176  THEN 'Cancelled'
 177  ELSE
 178   (CASE
 179                 WHEN ord.po_ref=1
 180                 THEN
 181                  (CASE
 182                                 WHEN ord.approver_ref=1
 183                                 THEN '---'
 184                                 ELSE 'Approved'
 185                         END)
 186                 ELSE 'PO'
 187         END)
 188 END) AS "Status_OK"
 189 FROM orderstest ord;
 190
 191 SELECT * FROM orders_view;
 192
 193 DROP TABLE orderstest cascade;
 194
 195 --
 196 -- Test cases to catch situations where rule rewriter fails to propagate
 197 -- hasSubLinks flag correctly.  Per example from Kyle Bateman.
 198 --
 199
 200 create temp table parts (
 201     partnum     text,
 202     cost        float8
 203 );
 204
 205 create temp table shipped (
 206     ttype       char(2),
 207     ordnum      int4,
 208     partnum     text,
 209     value       float8
 210 );
 211
 212 create temp view shipped_view as
 213     select * from shipped where ttype = 'wt';
 214
 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);
 217
 218 insert into parts (partnum, cost) values (1, 1234.56);
 219
 220 insert into shipped_view (ordnum, partnum, value)
 221     values (0, 1, (select cost from parts where partnum = 1));
 222
 223 select * from shipped_view;
 224
 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;
 228
 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))
 232     where ordnum = a.f1;
 233
 234 select * from shipped_view;
 235
 236 select f1, ss1 as relabel from
 237     (select *, (select sum(f1) from int4_tbl b where f1 >= a.f1) as ss1
 238      from int4_tbl a) ss;