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

0001 -- A test suite for 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   ("t1a", 6S, 8, 10L, float(15.0), 20D, 20E2BD, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
0011   ("t1b", 8S, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0012   ("t1a", 16S, 12, 21L, float(15.0), 20D, 20E2BD, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
0013   ("t1a", 16S, 12, 10L, float(15.0), 20D, 20E2BD, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
0014   ("t1c", 8S, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
0015   ("t1d", null, 16, 22L, float(17.0), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', null),
0016   ("t1d", null, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-07-04 01:02:00.001', null),
0017   ("t1e", 10S, null, 25L, float(17.0), 25D, 26E2BD, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
0018   ("t1e", 10S, null, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
0019   ("t1d", 10S, null, 12L, float(17.0), 25D, 26E2BD, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
0020   ("t1a", 6S, 8, 10L, float(15.0), 20D, 20E2BD, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
0021   ("t1e", 10S, null, 19L, float(17.0), 25D, 26E2BD, 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   ("t2a", 6S, 12, 14L, float(15), 20D, 20E2BD, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
0026   ("t1b", 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0027   ("t1b", 8S, 16, 119L, float(17), 25D, 26E2BD, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
0028   ("t1c", 12S, 16, 219L, float(17), 25D, 26E2BD, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
0029   ("t1b", null, 16, 319L, float(17), 25D, 26E2BD, timestamp '2017-05-04 01:01:00.000', null),
0030   ("t2e", 8S, null, 419L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
0031   ("t1f", 19S, null, 519L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0032   ("t1b", 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
0033   ("t1b", 8S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
0034   ("t1c", 12S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
0035   ("t1e", 8S, null, 19L, float(17), 25D, 26E2BD, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
0036   ("t1f", 19S, null, 19L, float(17), 25D, 26E2BD, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
0037   ("t1b", null, 16, 19L, float(17), 25D, 26E2BD, 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   ("t3a", 6S, 12, 110L, float(15), 20D, 20E2BD, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
0042   ("t3a", 6S, 12, 10L, float(15), 20D, 20E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0043   ("t1b", 10S, 12, 219L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0044   ("t1b", 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0045   ("t1b", 8S, 16, 319L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
0046   ("t1b", 8S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
0047   ("t3c", 17S, 16, 519L, float(17), 25D, 26E2BD, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
0048   ("t3c", 17S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
0049   ("t1b", null, 16, 419L, float(17), 25D, 26E2BD, timestamp '2014-10-04 01:02:00.000', null),
0050   ("t1b", null, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-11-04 01:02:00.000', null),
0051   ("t3b", 8S, null, 719L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0052   ("t3b", 8S, null, 19L, float(17), 25D, 26E2BD, 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 -- correlated IN subquery
0056 -- GROUP BY in parent side
0057 -- TC 01.01
0058 SELECT t1a,
0059        Avg(t1b)
0060 FROM   t1
0061 WHERE  t1a IN (SELECT t2a
0062                FROM   t2)
0063 GROUP  BY t1a;
0064
0065 -- TC 01.02
0066 SELECT t1a,
0067        Max(t1b)
0068 FROM   t1
0069 WHERE  t1b IN (SELECT t2b
0070                FROM   t2
0071                WHERE  t1a = t2a)
0072 GROUP  BY t1a,
0073           t1d;
0074
0075 -- TC 01.03
0076 SELECT t1a,
0077        t1b
0078 FROM   t1
0079 WHERE  t1c IN (SELECT t2c
0080                FROM   t2
0081                WHERE  t1a = t2a)
0082 GROUP  BY t1a,
0083           t1b;
0084
0085 -- TC 01.04
0086 SELECT t1a,
0087        Sum(DISTINCT( t1b ))
0088 FROM   t1
0089 WHERE  t1c IN (SELECT t2c
0090                FROM   t2
0091                WHERE  t1a = t2a)
0092         OR t1c IN (SELECT t3c
0093                    FROM   t3
0094                    WHERE  t1a = t3a)
0095 GROUP  BY t1a,
0096           t1c;
0097
0098 -- TC 01.05
0099 SELECT t1a,
0100        Sum(DISTINCT( t1b ))
0101 FROM   t1
0102 WHERE  t1c IN (SELECT t2c
0103                FROM   t2
0104                WHERE  t1a = t2a)
0105        AND t1c IN (SELECT t3c
0106                    FROM   t3
0107                    WHERE  t1a = t3a)
0108 GROUP  BY t1a,
0109           t1c;
0110
0111 -- TC 01.06
0112 SELECT t1a,
0113        Count(DISTINCT( t1b ))
0114 FROM   t1
0115 WHERE  t1c IN (SELECT t2c
0116                FROM   t2
0117                WHERE  t1a = t2a)
0118 GROUP  BY t1a,
0119           t1c
0120 HAVING t1a = "t1b";
0121
0122 -- GROUP BY in subquery
0123 -- TC 01.07
0124 SELECT *
0125 FROM   t1
0126 WHERE  t1b IN (SELECT Max(t2b)
0127                FROM   t2
0128                GROUP  BY t2a);
0129
0130 -- TC 01.08
0131 SELECT *
0132 FROM   (SELECT t2a,
0133                t2b
0134         FROM   t2
0135         WHERE  t2a IN (SELECT t1a
0136                        FROM   t1
0137                        WHERE  t1b = t2b)
0138         GROUP  BY t2a,
0139                   t2b) t2;
0140
0141 -- TC 01.09
0142 SELECT Count(DISTINCT( * ))
0143 FROM   t1
0144 WHERE  t1b IN (SELECT Min(t2b)
0145                FROM   t2
0146                WHERE  t1a = t2a
0147                       AND t1c = t2c
0148                GROUP  BY t2a);
0149
0150 -- TC 01.10
0151 SELECT t1a,
0152        t1b
0153 FROM   t1
0154 WHERE  t1c IN (SELECT Max(t2c)
0155                FROM   t2
0156                WHERE  t1a = t2a
0157                GROUP  BY t2a,
0158                          t2c
0159                HAVING t2c > 8);
0160
0161 -- TC 01.11
0162 SELECT t1a,
0163        t1b
0164 FROM   t1
0165 WHERE  t1c IN (SELECT t2c
0166                FROM   t2
0167                WHERE  t2a IN (SELECT Min(t3a)
0168                               FROM   t3
0169                               WHERE  t3a = t2a
0170                               GROUP  BY t3b)
0171                GROUP  BY t2c);
0172
0173 -- GROUP BY in both
0174 -- TC 01.12
0175 SELECT t1a,
0176        Min(t1b)
0177 FROM   t1
0178 WHERE  t1c IN (SELECT Min(t2c)
0179                FROM   t2
0180                WHERE  t2b = t1b
0181                GROUP  BY t2a)
0182 GROUP  BY t1a;
0183
0184 -- TC 01.13
0185 SELECT t1a,
0186        Min(t1b)
0187 FROM   t1
0188 WHERE  t1c IN (SELECT Min(t2c)
0189                FROM   t2
0190                WHERE  t2b IN (SELECT Min(t3b)
0191                               FROM   t3
0192                               WHERE  t2a = t3a
0193                               GROUP  BY t3a)
0194                GROUP  BY t2c)
0195 GROUP  BY t1a,
0196           t1d;
0197
0198 -- TC 01.14
0199 SELECT t1a,
0200        Min(t1b)
0201 FROM   t1
0202 WHERE  t1c IN (SELECT Min(t2c)
0203                FROM   t2
0204                WHERE  t2b = t1b
0205                GROUP  BY t2a)
0206        AND t1d IN (SELECT t3d
0207                    FROM   t3
0208                    WHERE  t1c = t3c
0209                    GROUP  BY t3d)
0210 GROUP  BY t1a;
0211
0212 -- TC 01.15
0213 SELECT t1a,
0214        Min(t1b)
0215 FROM   t1
0216 WHERE  t1c IN (SELECT Min(t2c)
0217                FROM   t2
0218                WHERE  t2b = t1b
0219                GROUP  BY t2a)
0220         OR t1d IN (SELECT t3d
0221                    FROM   t3
0222                    WHERE  t1c = t3c
0223                    GROUP  BY t3d)
0224 GROUP  BY t1a;
0225
0226 -- TC 01.16
0227 SELECT t1a,
0228        Min(t1b)
0229 FROM   t1
0230 WHERE  t1c IN (SELECT Min(t2c)
0231                FROM   t2
0232                WHERE  t2b = t1b
0233                GROUP  BY t2a
0234                HAVING t2a > t1a)
0235         OR t1d IN (SELECT t3d
0236                    FROM   t3
0237                    WHERE  t1c = t3c
0238                    GROUP  BY t3d
0239                    HAVING t3d = t1d)
0240 GROUP  BY t1a
0241 HAVING Min(t1b) IS NOT NULL;
0242
0243
0244