subquery/in-subquery/simple-in.sql

0001 -- A test suite for simple IN predicate subquery
0002 -- It includes correlated cases.
0003
0004 create temporary view t1 as select * from values
0005   ("t1a", 6S, 8, 10L, float(15.0), 20D, 20E2BD, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
0006   ("t1b", 8S, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0007   ("t1a", 16S, 12, 21L, float(15.0), 20D, 20E2BD, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
0008   ("t1a", 16S, 12, 10L, float(15.0), 20D, 20E2BD, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
0009   ("t1c", 8S, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
0010   ("t1d", null, 16, 22L, float(17.0), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', null),
0011   ("t1d", null, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-07-04 01:02:00.001', null),
0012   ("t1e", 10S, null, 25L, float(17.0), 25D, 26E2BD, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
0013   ("t1e", 10S, null, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
0014   ("t1d", 10S, null, 12L, float(17.0), 25D, 26E2BD, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
0015   ("t1a", 6S, 8, 10L, float(15.0), 20D, 20E2BD, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
0016   ("t1e", 10S, null, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
0017   as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i);
0018
0019 create temporary view t2 as select * from values
0020   ("t2a", 6S, 12, 14L, float(15), 20D, 20E2BD, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
0021   ("t1b", 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0022   ("t1b", 8S, 16, 119L, float(17), 25D, 26E2BD, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
0023   ("t1c", 12S, 16, 219L, float(17), 25D, 26E2BD, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
0024   ("t1b", null, 16, 319L, float(17), 25D, 26E2BD, timestamp '2017-05-04 01:01:00.000', null),
0025   ("t2e", 8S, null, 419L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
0026   ("t1f", 19S, null, 519L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0027   ("t1b", 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
0028   ("t1b", 8S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
0029   ("t1c", 12S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
0030   ("t1e", 8S, null, 19L, float(17), 25D, 26E2BD, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
0031   ("t1f", 19S, null, 19L, float(17), 25D, 26E2BD, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
0032   ("t1b", null, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', null)
0033   as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i);
0034
0035 create temporary view t3 as select * from values
0036   ("t3a", 6S, 12, 110L, float(15), 20D, 20E2BD, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
0037   ("t3a", 6S, 12, 10L, float(15), 20D, 20E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0038   ("t1b", 10S, 12, 219L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0039   ("t1b", 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0040   ("t1b", 8S, 16, 319L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
0041   ("t1b", 8S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
0042   ("t3c", 17S, 16, 519L, float(17), 25D, 26E2BD, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
0043   ("t3c", 17S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
0044   ("t1b", null, 16, 419L, float(17), 25D, 26E2BD, timestamp '2014-10-04 01:02:00.000', null),
0045   ("t1b", null, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-11-04 01:02:00.000', null),
0046   ("t3b", 8S, null, 719L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0047   ("t3b", 8S, null, 19L, float(17), 25D, 26E2BD, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
0048   as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i);
0049
0050 -- correlated IN subquery
0051 -- simple select
0052 -- TC 01.01
0053 SELECT *
0054 FROM   t1
0055 WHERE  t1a IN (SELECT t2a
0056                FROM   t2);
0057
0058 -- TC 01.02
0059 SELECT *
0060 FROM   t1
0061 WHERE  t1b IN (SELECT t2b
0062                FROM   t2
0063                WHERE  t1a = t2a);
0064
0065 -- TC 01.03
0066 SELECT t1a,
0067        t1b
0068 FROM   t1
0069 WHERE  t1c IN (SELECT t2b
0070                FROM   t2
0071                WHERE  t1a != t2a);
0072
0073 -- TC 01.04
0074 SELECT t1a,
0075        t1b
0076 FROM   t1
0077 WHERE  t1c IN (SELECT t2b
0078                FROM   t2
0079                WHERE  t1a = t2a
0080                        OR t1b > t2b);
0081
0082 -- TC 01.05
0083 SELECT t1a,
0084        t1b
0085 FROM   t1
0086 WHERE  t1c IN (SELECT t2b
0087                FROM   t2
0088                WHERE  t2i IN (SELECT t3i
0089                               FROM   t3
0090                               WHERE  t2c = t3c));
0091
0092 -- TC 01.06
0093 SELECT t1a,
0094        t1b
0095 FROM   t1
0096 WHERE  t1c IN (SELECT t2b
0097                FROM   t2
0098                WHERE  t2a IN (SELECT t3a
0099                               FROM   t3
0100                               WHERE  t2c = t3c
0101                                      AND t2b IS NOT NULL));
0102
0103 -- simple select for NOT IN
0104 -- TC 01.07
0105 SELECT DISTINCT( t1a ),
0106                t1b,
0107                t1h
0108 FROM   t1
0109 WHERE  t1a NOT IN (SELECT t2a
0110                    FROM   t2);
0111
0112 -- DDLs
0113 create temporary view a as select * from values
0114   (1, 1), (2, 1), (null, 1), (1, 3), (null, 3), (1, null), (null, 2)
0115   as a(a1, a2);
0116
0117 create temporary view b as select * from values
0118   (1, 1, 2), (null, 3, 2), (1, null, 2), (1, 2, null)
0119   as b(b1, b2, b3);
0120
0121 -- TC 02.01
0122 SELECT a1, a2
0123 FROM   a
0124 WHERE  a1 NOT IN (SELECT b.b1
0125                   FROM   b
0126                   WHERE  a.a2 = b.b2)
0127 ;
0128
0129 -- TC 02.02
0130 SELECT a1, a2
0131 FROM   a
0132 WHERE  a1 NOT IN (SELECT b.b1
0133                   FROM   b
0134                   WHERE  a.a2 = b.b2
0135                   AND    b.b3 > 1)
0136 ;