-- A test suite for scalar subquery in SELECT clause

create table t1(
  t1a varchar(10), t1b smallint, t1c int, t1d bigint,
  t1e float, t1f double, t1g decimal(9,2), t1h timestamp, t1i date);

insert into t1
values
  ('t1a', 6, 8, 10, float(15.0), 20, 20E2, timestamp('2014-04-04 00:00:00.000'), date('2014-04-04')),
  ('t1b', 8, 16, 19, float(17.0), 25, 26E2, timestamp('2014-05-04 01:01:00.000'), date('2014-05-04')),
  ('t1a', 16, 12, 21, float(15.0), 20, 20E2, timestamp('2014-06-04 01:02:00.001'), date('2014-06-04')),
  ('t1a', 16, 12, 10, float(15.0), 20, 20E2, timestamp('2014-07-04 01:01:00.000'), date('2014-07-04')),
  ('t1c', 8, 16, 19, float(17.0), 25, 26E2, timestamp('2014-05-04 01:02:00.001'), date('2014-05-05')),
  ('t1d', null, 16, 22, float(17.0), 25, 26E2, timestamp('2014-06-04 01:01:00.000'), null),
  ('t1d', null, 16, 19, float(17.0), 25, 26E2, timestamp('2014-07-04 01:02:00.001'), null),
  ('t1e', 10, null, 25, float(17.0), 25, 26E2, timestamp('2014-08-04 01:01:00.000'), date('2014-08-04')),
  ('t1e', 10, null, 19, float(17.0), 25, 26E2, timestamp('2014-09-04 01:02:00.001'), date('2014-09-04')),
  ('t1d', 10, null, 12, float(17.0), 25, 26E2, timestamp('2015-05-04 01:01:00.000'), date('2015-05-04')),
  ('t1a', 6, 8, 10, float(15.0), 20, 20E2, timestamp('2014-04-04 01:02:00.001'), date('2014-04-04')),
  ('t1e', 10, null, 19, float(17.0), 25, 26E2, timestamp('2014-05-04 01:01:00.000'), date('2014-05-04'))
;

create table t2(
  t2a varchar(10), t2b smallint, t2c int, t2d bigint,
  t2e float, t2f double, t2g decimal(9,2), t2h timestamp, t2i date);

insert into t2
values
  ('t2a', 6, 12, 14, float(15), 20, 20E2, timestamp('2014-04-04 01:01:00.000'), date('2014-04-04')),
  ('t1b', 10, 12, 19, float(17), 25, 26E2, timestamp('2014-05-04 01:01:00.000'), date('2014-05-04')),
  ('t1b', 8, 16, 119, float(17), 25, 26E2, timestamp('2015-05-04 01:01:00.000'), date('2015-05-04')),
  ('t1c', 12, 16, 219, float(17), 25, 26E2, timestamp('2016-05-04 01:01:00.000'), date('2016-05-04')),
  ('t1b', null, 16, 319, float(17), 25, 26E2, timestamp('2017-05-04 01:01:00.000'), null),
  ('t2e', 8, null, 419, float(17), 25, 26E2, timestamp('2014-06-04 01:01:00.000'), date('2014-06-04')),
  ('t1f', 19, null, 519, float(17), 25, 26E2, timestamp('2014-05-04 01:01:00.000'), date('2014-05-04')),
  ('t1b', 10, 12, 19, float(17), 25, 26E2, timestamp('2014-06-04 01:01:00.000'), date('2014-06-04')),
  ('t1b', 8, 16, 19, float(17), 25, 26E2, timestamp('2014-07-04 01:01:00.000'), date('2014-07-04')),
  ('t1c', 12, 16, 19, float(17), 25, 26E2, timestamp('2014-08-04 01:01:00.000'), date('2014-08-05')),
  ('t1e', 8, null, 19, float(17), 25, 26E2, timestamp('2014-09-04 01:01:00.000'), date('2014-09-04')),
  ('t1f', 19, null, 19, float(17), 25, 26E2, timestamp('2014-10-04 01:01:00.000'), date('2014-10-04')),
  ('t1b', null, 16, 19, float(17), 25, 26E2, timestamp('2014-05-04 01:01:00.000'), null)
;

create table t3(
  t3a varchar(10), t3b smallint, t3c int, t3d bigint,
  t3e float, t3f double, t3g decimal(9,2), t3h timestamp, t3i date);

insert into t3
values
  ('t3a', 6, 12, 110, float(15), 20, 20E2, timestamp('2014-04-04 01:02:00.000'), date('2014-04-04')),
  ('t3a', 6, 12, 10, float(15), 20, 20E2, timestamp('2014-05-04 01:02:00.000'), date('2014-05-04')),
  ('t1b', 10, 12, 219, float(17), 25, 26E2, timestamp('2014-05-04 01:02:00.000'), date('2014-05-04')),
  ('t1b', 10, 12, 19, float(17), 25, 26E2, timestamp('2014-05-04 01:02:00.000'), date('2014-05-04')),
  ('t1b', 8, 16, 319, float(17), 25, 26E2, timestamp('2014-06-04 01:02:00.000'), date('2014-06-04')),
  ('t1b', 8, 16, 19, float(17), 25, 26E2, timestamp('2014-07-04 01:02:00.000'), date('2014-07-04')),
  ('t3c', 17, 16, 519, float(17), 25, 26E2, timestamp('2014-08-04 01:02:00.000'), date('2014-08-04')),
  ('t3c', 17, 16, 19, float(17), 25, 26E2, timestamp('2014-09-04 01:02:00.000'), date('2014-09-05')),
  ('t1b', null, 16, 419, float(17), 25, 26E2, timestamp('2014-10-04 01:02:00.000'), null),
  ('t1b', null, 16, 19, float(17), 25, 26E2, timestamp('2014-11-04 01:02:00.000'), null),
  ('t3b', 8, null, 719, float(17), 25, 26E2, timestamp('2014-05-04 01:02:00.000'), date('2014-05-04')),
  ('t3b', 8, null, 19, float(17), 25, 26E2, timestamp('2015-05-04 01:02:00.000'), date('2015-05-04'))
;

-- Group 1: scalar subquery in SELECT clause
--          no correlation
-- TC 01.01
-- more than one scalar subquery
SELECT (SELECT min(t3d) FROM t3) min_t3d,
       (SELECT max(t2h) FROM t2) max_t2h
FROM   t1
WHERE  t1a = 't1c'
;

-- TC 01.02
-- scalar subquery in an IN subquery
SELECT   t1a, count(*)
FROM     t1
WHERE    t1c IN (SELECT   (SELECT min(t3c) FROM t3)
                 FROM     t2
                 GROUP BY t2g
                 HAVING   count(*) > 1)
GROUP BY t1a
;

-- TC 01.03
-- under a set op
SELECT (SELECT min(t3d) FROM t3) min_t3d,
       null
FROM   t1
WHERE  t1a = 't1c'
UNION 
SELECT null,
       (SELECT max(t2h) FROM t2) max_t2h
FROM   t1
WHERE  t1a = 't1c'
;

-- TC 01.04
SELECT (SELECT min(t3c) FROM t3) min_t3d
FROM   t1
WHERE  t1a = 't1a'
INTERSECT 
SELECT (SELECT min(t2c) FROM t2) min_t2d
FROM   t1
WHERE  t1a = 't1d'
;

-- TC 01.05
SELECT q1.t1a, q2.t2a, q1.min_t3d, q2.avg_t3d
FROM   (SELECT t1a, (SELECT min(t3d) FROM t3) min_t3d
        FROM   t1
        WHERE  t1a IN ('t1e', 't1c')) q1
       FULL OUTER JOIN
       (SELECT t2a, (SELECT avg(cast(t3d as double)) FROM t3) avg_t3d
        FROM   t2
        WHERE  t2a IN ('t1c', 't2a')) q2
ON     q1.t1a = q2.t2a
AND    q1.min_t3d < q2.avg_t3d
;

-- Group 2: scalar subquery in SELECT clause
--          with correlation
-- TC 02.01
SELECT (SELECT min(t3d) FROM t3 WHERE t3.t3a = t1.t1a) min_t3d,
       (SELECT max(t2h) FROM t2 WHERE t2.t2a = t1.t1a) max_t2h
FROM   t1
WHERE  t1a = 't1b'
;

-- TC 02.02
SELECT (SELECT min(t3d) FROM t3 WHERE t3a = t1a) min_t3d
FROM   t1
WHERE  t1a = 't1b'
MINUS 
SELECT (SELECT min(t3d) FROM t3) abs_min_t3d
FROM   t1
WHERE  t1a = 't1b'
;

-- TC 02.03
SELECT t1a, t1b
FROM   t1
WHERE  NOT EXISTS (SELECT (SELECT max(t2b)
                           FROM   t2 LEFT JOIN t1
                           ON     t2a = t1a
                           WHERE  t2c = t3c) dummy
                   FROM   t3
                   WHERE  t3b < (SELECT max(t2b)
                                 FROM   t2 LEFT JOIN t1
                                 ON     t2a = t1a
                                 WHERE  t2c = t3c)
                   AND    t3a = t1a)

;