subquery/in-subquery/in-having.sql

0001 -- A test suite for IN HAVING 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 -- HAVING in the subquery
0052 -- TC 01.01
0053 SELECT t1a,
0054        t1b,
0055        t1h
0056 FROM   t1
0057 WHERE  t1b IN (SELECT t2b
0058                FROM   t2
0059                GROUP BY t2b
0060                HAVING t2b < 10);
0061
0062 -- TC 01.02
0063 SELECT t1a,
0064        t1b,
0065        t1c
0066 FROM   t1
0067 WHERE  t1b IN (SELECT Min(t2b)
0068                FROM   t2
0069                WHERE  t1a = t2a
0070                GROUP  BY t2b
0071                HAVING t2b > 1);
0072
0073 -- HAVING in the parent
0074 -- TC 01.03
0075 SELECT t1a, t1b, t1c
0076 FROM   t1
0077 WHERE  t1b IN (SELECT t2b
0078                FROM   t2
0079                WHERE t1c < t2c)
0080 GROUP BY t1a, t1b, t1c
0081 HAVING t1b < 10;
0082
0083 -- TC 01.04
0084 SELECT t1a, t1b, t1c
0085 FROM   t1
0086 WHERE  t1b IN (SELECT t2b
0087                FROM   t2
0088                WHERE t1c = t2c)
0089 GROUP BY t1a, t1b, t1c
0090 HAVING COUNT (DISTINCT t1b) < 10;
0091
0092 -- BOTH
0093 -- TC 01.05
0094 SELECT Count(DISTINCT( t1a )),
0095        t1b
0096 FROM   t1
0097 WHERE  t1c IN (SELECT t2c
0098                FROM   t2
0099                WHERE  t1a = t2a
0100                GROUP BY t2c
0101                HAVING t2c > 10)
0102 GROUP  BY t1b
0103 HAVING t1b >= 8;
0104
0105 -- TC 01.06
0106 SELECT t1a,
0107        Max(t1b)
0108 FROM   t1
0109 WHERE  t1b > 0
0110 GROUP  BY t1a
0111 HAVING t1a IN (SELECT t2a
0112                FROM   t2
0113                WHERE  t2b IN (SELECT t3b
0114                               FROM   t3
0115                               WHERE  t2c = t3c)
0116                );
0117
0118 -- HAVING clause with NOT IN
0119 -- TC 01.07
0120 SELECT t1a,
0121        t1c,
0122        Min(t1d)
0123 FROM   t1
0124 WHERE  t1a NOT IN (SELECT t2a
0125                    FROM   t2
0126                    GROUP BY t2a
0127                    HAVING t2a > 'val2a')
0128 GROUP BY t1a, t1c
0129 HAVING Min(t1d) > t1c;
0130
0131 -- TC 01.08
0132 SELECT t1a,
0133        t1b
0134 FROM   t1
0135 WHERE  t1d NOT IN (SELECT t2d
0136                    FROM   t2
0137                    WHERE  t1a = t2a
0138                    GROUP BY t2c, t2d
0139                    HAVING t2c > 8)
0140 GROUP  BY t1a, t1b
0141 HAVING t1b < 10;
0142
0143 -- TC 01.09
0144 SELECT t1a,
0145        Max(t1b)
0146 FROM   t1
0147 WHERE  t1b > 0
0148 GROUP  BY t1a
0149 HAVING t1a NOT IN (SELECT t2a
0150                    FROM   t2
0151                    WHERE  t2b > 3);
0152