subquery/in-subquery/in-multiple-columns.sql

0001 -- A test suite for multiple columns in predicate in parent side, subquery, and both predicate subquery
0002 -- It includes correlated cases.
0003
0004 create temporary view t1 as select * from values
0005   ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
0006   ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0007   ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
0008   ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
0009   ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
0010   ("val1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
0011   ("val1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
0012   ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
0013   ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
0014   ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
0015   ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
0016   ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, 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   ("val2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
0021   ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0022   ("val1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
0023   ("val1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
0024   ("val1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
0025   ("val2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
0026   ("val1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0027   ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
0028   ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
0029   ("val1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
0030   ("val1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
0031   ("val1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
0032   ("val1b", null, 16, 19L, float(17), 25D, 26E2, 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   ("val3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
0037   ("val3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0038   ("val1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0039   ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0040   ("val1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
0041   ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
0042   ("val3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
0043   ("val3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
0044   ("val1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
0045   ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
0046   ("val3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0047   ("val3b", 8S, null, 19L, float(17), 25D, 26E2, 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 -- TC 01.01
0052 SELECT t1a,
0053        t1b,
0054        t1h
0055 FROM   t1
0056 WHERE  ( t1a, t1h ) NOT IN (SELECT t2a,
0057                                    t2h
0058                             FROM   t2
0059                             WHERE  t2a = t1a
0060                             ORDER  BY t2a)
0061 AND t1a = 'val1a';
0062
0063 -- TC 01.02
0064 SELECT t1a,
0065        t1b,
0066        t1d
0067 FROM   t1
0068 WHERE  ( t1b, t1d ) IN (SELECT t2b,
0069                                t2d
0070                         FROM   t2
0071                         WHERE  t2i IN (SELECT t3i
0072                                        FROM   t3
0073                                        WHERE  t2b > t3b));
0074
0075 -- TC 01.03
0076 SELECT t1a,
0077        t1b,
0078        t1d
0079 FROM   t1
0080 WHERE  ( t1b, t1d ) NOT IN (SELECT t2b,
0081                                    t2d
0082                             FROM   t2
0083                             WHERE  t2h IN (SELECT t3h
0084                                            FROM   t3
0085                                            WHERE  t2b > t3b))
0086 AND t1a = 'val1a';
0087
0088 -- TC 01.04
0089 SELECT t2a
0090 FROM   (SELECT t2a
0091         FROM   t2
0092         WHERE  ( t2a, t2b ) IN (SELECT t1a,
0093                                        t1b
0094                                 FROM   t1)
0095         UNION ALL
0096         SELECT t2a
0097         FROM   t2
0098         WHERE  ( t2a, t2b ) IN (SELECT t1a,
0099                                        t1b
0100                                 FROM   t1)
0101         UNION DISTINCT
0102         SELECT t2a
0103         FROM   t2
0104         WHERE  ( t2a, t2b ) IN (SELECT t3a,
0105                                        t3b
0106                                 FROM   t3)) AS t4;
0107
0108 -- TC 01.05
0109 WITH cte1 AS
0110 (
0111        SELECT t1a,
0112               t1b
0113        FROM   t1
0114        WHERE  (
0115                      t1b, t1d) IN
0116               (
0117                      SELECT t2b,
0118                             t2d
0119                      FROM   t2
0120                      WHERE  t1c = t2c))
0121 SELECT *
0122 FROM            (
0123                            SELECT     *
0124                            FROM       cte1
0125                            JOIN       cte1 cte2
0126                            on         cte1.t1b = cte2.t1b) s;
0127