subquery/scalar-subquery/scalar-subquery-select.sql

0001 -- A test suite for scalar subquery in SELECT clause
0002
0003 create temporary view t1 as select * from values
0004   ('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 00:00:00.000', date '2014-04-04'),
0005   ('val1b', 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0006   ('val1a', 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
0007   ('val1a', 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
0008   ('val1c', 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
0009   ('val1d', null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
0010   ('val1d', null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
0011   ('val1e', 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
0012   ('val1e', 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
0013   ('val1d', 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
0014   ('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
0015   ('val1e', 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
0016   as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i);
0017
0018 create temporary view t2 as select * from values
0019   ('val2a', 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
0020   ('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0021   ('val1b', 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
0022   ('val1c', 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
0023   ('val1b', null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
0024   ('val2e', 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
0025   ('val1f', 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0026   ('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
0027   ('val1b', 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
0028   ('val1c', 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
0029   ('val1e', 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
0030   ('val1f', 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
0031   ('val1b', null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
0032   as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i);
0033
0034 create temporary view t3 as select * from values
0035   ('val3a', 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
0036   ('val3a', 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0037   ('val1b', 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0038   ('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0039   ('val1b', 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
0040   ('val1b', 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
0041   ('val3c', 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
0042   ('val3c', 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
0043   ('val1b', null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
0044   ('val1b', null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
0045   ('val3b', 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0046   ('val3b', 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
0047   as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i);
0048
0049 -- Group 1: scalar subquery in SELECT clause
0050 --          no correlation
0051 -- TC 01.01
0052 -- more than one scalar subquery
0053 SELECT (SELECT min(t3d) FROM t3) min_t3d,
0054        (SELECT max(t2h) FROM t2) max_t2h
0055 FROM   t1
0056 WHERE  t1a = 'val1c';
0057
0058 -- TC 01.02
0059 -- scalar subquery in an IN subquery
0060 SELECT   t1a, count(*)
0061 FROM     t1
0062 WHERE    t1c IN (SELECT   (SELECT min(t3c) FROM t3)
0063                  FROM     t2
0064                  GROUP BY t2g
0065                  HAVING   count(*) > 1)
0066 GROUP BY t1a;
0067
0068 -- TC 01.03
0069 -- under a set op
0070 SELECT (SELECT min(t3d) FROM t3) min_t3d,
0071        null
0072 FROM   t1
0073 WHERE  t1a = 'val1c'
0074 UNION
0075 SELECT null,
0076        (SELECT max(t2h) FROM t2) max_t2h
0077 FROM   t1
0078 WHERE  t1a = 'val1c';
0079
0080 -- TC 01.04
0081 SELECT (SELECT min(t3c) FROM t3) min_t3d
0082 FROM   t1
0083 WHERE  t1a = 'val1a'
0084 INTERSECT
0085 SELECT (SELECT min(t2c) FROM t2) min_t2d
0086 FROM   t1
0087 WHERE  t1a = 'val1d';
0088
0089 -- TC 01.05
0090 SELECT q1.t1a, q2.t2a, q1.min_t3d, q2.avg_t3d
0091 FROM   (SELECT t1a, (SELECT min(t3d) FROM t3) min_t3d
0092         FROM   t1
0093         WHERE  t1a IN ('val1e', 'val1c')) q1
0094        FULL OUTER JOIN
0095        (SELECT t2a, (SELECT avg(t3d) FROM t3) avg_t3d
0096         FROM   t2
0097         WHERE  t2a IN ('val1c', 'val2a')) q2
0098 ON     q1.t1a = q2.t2a
0099 AND    q1.min_t3d < q2.avg_t3d;
0100
0101 -- Group 2: scalar subquery in SELECT clause
0102 --          with correlation
0103 -- TC 02.01
0104 SELECT (SELECT min(t3d) FROM t3 WHERE t3.t3a = t1.t1a) min_t3d,
0105        (SELECT max(t2h) FROM t2 WHERE t2.t2a = t1.t1a) max_t2h
0106 FROM   t1
0107 WHERE  t1a = 'val1b';
0108
0109 -- TC 02.02
0110 SELECT (SELECT min(t3d) FROM t3 WHERE t3a = t1a) min_t3d
0111 FROM   t1
0112 WHERE  t1a = 'val1b'
0113 MINUS
0114 SELECT (SELECT min(t3d) FROM t3) abs_min_t3d
0115 FROM   t1
0116 WHERE  t1a = 'val1b';
0117
0118 -- TC 02.03
0119 SELECT t1a, t1b
0120 FROM   t1
0121 WHERE  NOT EXISTS (SELECT (SELECT max(t2b)
0122                            FROM   t2 LEFT JOIN t1
0123                            ON     t2a = t1a
0124                            WHERE  t2c = t3c) dummy
0125                    FROM   t3
0126                    WHERE  t3b < (SELECT max(t2b)
0127                                  FROM   t2 LEFT JOIN t1
0128                                  ON     t2a = t1a
0129                                  WHERE  t2c = t3c)
0130                    AND    t3a = t1a);