subquery/in-subquery/not-in-group-by.sql

0001 -- A test suite for NOT IN GROUP BY in parent side, subquery, and both predicate subquery
0002 -- It includes correlated cases.
0003
0004 -- Test aggregate operator with codegen on and off.
0005 --CONFIG_DIM1 spark.sql.codegen.wholeStage=true
0006 --CONFIG_DIM1 spark.sql.codegen.wholeStage=false,spark.sql.codegen.factoryMode=CODEGEN_ONLY
0007 --CONFIG_DIM1 spark.sql.codegen.wholeStage=false,spark.sql.codegen.factoryMode=NO_CODEGEN
0008
0009 create temporary view t1 as select * from values
0010   ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
0011   ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0012   ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
0013   ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
0014   ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
0015   ("val1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
0016   ("val1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
0017   ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
0018   ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
0019   ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
0020   ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
0021   ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
0022   as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i);
0023
0024 create temporary view t2 as select * from values
0025   ("val2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
0026   ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0027   ("val1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
0028   ("val1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
0029   ("val1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
0030   ("val2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
0031   ("val1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0032   ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
0033   ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
0034   ("val1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
0035   ("val1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
0036   ("val1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
0037   ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
0038   as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i);
0039
0040 create temporary view t3 as select * from values
0041   ("val3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
0042   ("val3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0043   ("val1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0044   ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0045   ("val1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
0046   ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
0047   ("val3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
0048   ("val3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
0049   ("val1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
0050   ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
0051   ("val3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0052   ("val3b", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
0053   as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i);
0054
0055
0056 -- correlated IN subquery
0057 -- GROUP BY in parent side
0058 -- TC 01.01
0059 SELECT t1a,
0060        Avg(t1b)
0061 FROM   t1
0062 WHERE  t1a NOT IN (SELECT t2a
0063                    FROM   t2)
0064 GROUP  BY t1a;
0065
0066 -- TC 01.02
0067 SELECT t1a,
0068        Sum(DISTINCT( t1b ))
0069 FROM   t1
0070 WHERE  t1d NOT IN (SELECT t2d
0071                    FROM   t2
0072                    WHERE  t1h < t2h)
0073 GROUP  BY t1a;
0074
0075 -- TC 01.03
0076 SELECT Count(*)
0077 FROM   (SELECT *
0078         FROM   t2
0079         WHERE  t2a NOT IN (SELECT t3a
0080                            FROM   t3
0081                            WHERE  t3h != t2h)) t2
0082 WHERE  t2b NOT IN (SELECT Min(t2b)
0083                    FROM   t2
0084                    WHERE  t2b = t2b
0085                    GROUP  BY t2c);
0086
0087 -- TC 01.04
0088 SELECT t1a,
0089        max(t1b)
0090 FROM   t1
0091 WHERE  t1c NOT IN (SELECT Max(t2b)
0092                    FROM   t2
0093                    WHERE  t1a = t2a
0094                    GROUP  BY t2a)
0095 GROUP BY t1a;
0096
0097 -- TC 01.05
0098 SELECT t1a,
0099        t1b
0100 FROM   t1
0101 WHERE  t1c IN (SELECT t2b
0102                FROM   t2
0103                WHERE  t2a NOT IN (SELECT Min(t3a)
0104                                   FROM   t3
0105                                   WHERE  t3a = t2a
0106                                   GROUP  BY t3b) order by t2a);