subquery/in-subquery/in-order-by.sql

0001 -- A test suite for ORDER BY in parent side, subquery, and both predicate subquery
0002 -- It includes correlated cases.
0003
0004 -- Test sort 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, 20E2BD, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
0011   ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0012   ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2BD, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
0013   ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2BD, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
0014   ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
0015   ("val1d", null, 16, 22L, float(17.0), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', null),
0016   ("val1d", null, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-07-04 01:02:00.001', null),
0017   ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2BD, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
0018   ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
0019   ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2BD, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
0020   ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2BD, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
0021   ("val1e", 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   ("val2a", 6S, 12, 14L, float(15), 20D, 20E2BD, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
0026   ("val1b", 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0027   ("val1b", 8S, 16, 119L, float(17), 25D, 26E2BD, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
0028   ("val1c", 12S, 16, 219L, float(17), 25D, 26E2BD, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
0029   ("val1b", null, 16, 319L, float(17), 25D, 26E2BD, timestamp '2017-05-04 01:01:00.000', null),
0030   ("val2e", 8S, null, 419L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
0031   ("val1f", 19S, null, 519L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0032   ("val1b", 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
0033   ("val1b", 8S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
0034   ("val1c", 12S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
0035   ("val1e", 8S, null, 19L, float(17), 25D, 26E2BD, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
0036   ("val1f", 19S, null, 19L, float(17), 25D, 26E2BD, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
0037   ("val1b", 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   ("val3a", 6S, 12, 110L, float(15), 20D, 20E2BD, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
0042   ("val3a", 6S, 12, 10L, float(15), 20D, 20E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0043   ("val1b", 10S, 12, 219L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0044   ("val1b", 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0045   ("val1b", 8S, 16, 319L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
0046   ("val1b", 8S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
0047   ("val3c", 17S, 16, 519L, float(17), 25D, 26E2BD, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
0048   ("val3c", 17S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
0049   ("val1b", null, 16, 419L, float(17), 25D, 26E2BD, timestamp '2014-10-04 01:02:00.000', null),
0050   ("val1b", null, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-11-04 01:02:00.000', null),
0051   ("val3b", 8S, null, 719L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0052   ("val3b", 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 -- ORDER BY in parent side
0057 -- TC 01.01
0058 SELECT *
0059 FROM   t1
0060 WHERE  t1a IN (SELECT t2a
0061                FROM   t2)
0062 ORDER  BY t1a;
0063
0064 -- TC 01.02
0065 SELECT t1a
0066 FROM   t1
0067 WHERE  t1b IN (SELECT t2b
0068                FROM   t2
0069                WHERE  t1a = t2a)
0070 ORDER  BY t1b DESC;
0071
0072 -- TC 01.03
0073 SELECT t1a,
0074        t1b
0075 FROM   t1
0076 WHERE  t1c IN (SELECT t2c
0077                FROM   t2
0078                WHERE  t1a = t2a)
0079 ORDER  BY 2 DESC nulls last;
0080
0081 -- TC 01.04
0082 SELECT Count(DISTINCT( t1a ))
0083 FROM   t1
0084 WHERE  t1b IN (SELECT t2b
0085                FROM   t2
0086                WHERE  t1a = t2a)
0087 ORDER  BY Count(DISTINCT( t1a ));
0088
0089 -- ORDER BY in subquery
0090 -- TC 01.05
0091 SELECT *
0092 FROM   t1
0093 WHERE  t1b IN (SELECT t2c
0094                FROM   t2
0095                ORDER  BY t2d);
0096
0097 -- ORDER BY in BOTH
0098 -- TC 01.06
0099 SELECT *
0100 FROM   t1
0101 WHERE  t1b IN (SELECT Min(t2b)
0102                FROM   t2
0103                WHERE  t1b = t2b
0104                ORDER  BY Min(t2b))
0105 ORDER BY t1c DESC nulls first;
0106
0107 -- TC 01.07
0108 SELECT t1a,
0109        t1b,
0110        t1h
0111 FROM   t1
0112 WHERE  t1c IN (SELECT t2c
0113                FROM   t2
0114                WHERE  t1a = t2a
0115                ORDER  BY t2b DESC nulls first)
0116         OR t1h IN (SELECT t2h
0117                    FROM   t2
0118                    WHERE  t1h > t2h)
0119 ORDER  BY t1h DESC nulls last;
0120
0121 -- ORDER BY with NOT IN
0122 -- TC 01.08
0123 SELECT *
0124 FROM   t1
0125 WHERE  t1a NOT IN (SELECT t2a
0126                    FROM   t2)
0127 ORDER  BY t1a;
0128
0129 -- TC 01.09
0130 SELECT t1a,
0131        t1b
0132 FROM   t1
0133 WHERE  t1a NOT IN (SELECT t2a
0134                    FROM   t2
0135                    WHERE  t1a = t2a)
0136 ORDER  BY t1b DESC nulls last;
0137
0138 -- TC 01.10
0139 SELECT *
0140 FROM   t1
0141 WHERE  t1a NOT IN (SELECT t2a
0142                    FROM   t2
0143                    ORDER  BY t2a DESC nulls first)
0144        and t1c IN (SELECT t2c
0145                    FROM   t2
0146                    ORDER  BY t2b DESC nulls last)
0147 ORDER  BY t1c DESC nulls last;
0148
0149 -- GROUP BY and ORDER BY
0150 -- TC 01.11
0151 SELECT *
0152 FROM   t1
0153 WHERE  t1b IN (SELECT Min(t2b)
0154                FROM   t2
0155                GROUP  BY t2a
0156                ORDER  BY t2a DESC);
0157
0158 -- TC 01.12
0159 SELECT t1a,
0160        Count(DISTINCT( t1b ))
0161 FROM   t1
0162 WHERE  t1b IN (SELECT Min(t2b)
0163                FROM   t2
0164                WHERE  t1a = t2a
0165                GROUP  BY t2a
0166                ORDER  BY t2a)
0167 GROUP  BY t1a,
0168           t1h
0169 ORDER BY t1a;
0170
0171 -- GROUP BY and ORDER BY with NOT IN
0172 -- TC 01.13
0173 SELECT *
0174 FROM   t1
0175 WHERE  t1b NOT IN (SELECT Min(t2b)
0176                    FROM   t2
0177                    GROUP  BY t2a
0178                    ORDER  BY t2a);
0179
0180 -- TC 01.14
0181 SELECT t1a,
0182        Sum(DISTINCT( t1b ))
0183 FROM   t1
0184 WHERE  t1b NOT IN (SELECT Min(t2b)
0185                    FROM   t2
0186                    WHERE  t1a = t2a
0187                    GROUP  BY t2c
0188                    ORDER  BY t2c DESC nulls last)
0189 GROUP  BY t1a;
0190
0191 -- TC 01.15
0192 SELECT Count(DISTINCT( t1a )),
0193        t1b
0194 FROM   t1
0195 WHERE  t1h NOT IN (SELECT t2h
0196                    FROM   t2
0197                    where t1a = t2a
0198                    order by t2d DESC nulls first
0199                    )
0200 GROUP  BY t1a,
0201           t1b
0202 ORDER  BY t1b DESC nulls last;