-- A test suite for IN HAVING in parent side, subquery, and both predicate subquery
-- It includes correlated cases.


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
  (‘val1a', 6, 8, 10, float(15.0), 20, 20E2, timestamp('2014-04-04 01:00:00.000'), date('2014-04-04')),
  ('val1b', 8, 16, 19, float(17.0), 25, 26E2, timestamp('2014-05-04 01:01:00.000'), date('2014-05-04')),
  ('val1a', 16, 12, 21, float(15.0), 20, 20E2, timestamp('2014-06-04 01:02:00.001'), date('2014-06-04')),
  ('val1a', 16, 12, 10, float(15.0), 20, 20E2, timestamp('2014-07-04 01:01:00.000'), date('2014-07-04')),
  ('val1c', 8, 16, 19, float(17.0), 25, 26E2, timestamp('2014-05-04 01:02:00.001'), date('2014-05-05')),
  ('val1d', null, 16, 22, float(17.0), 25, 26E2, timestamp('2014-06-04 01:01:00.000'), null),
  ('val1d', null, 16, 19, float(17.0), 25, 26E2, timestamp('2014-07-04 01:02:00.001'), null),
  ('val1e', 10, null, 25, float(17.0), 25, 26E2, timestamp('2014-08-04 01:01:00.000'), date('2014-08-04')),
  ('val1e', 10, null, 19, float(17.0), 25, 26E2, timestamp('2014-09-04 01:02:00.001'), date('2014-09-04')),
  ('val1d', 10, null, 12, float(17.0), 25, 26E2, timestamp('2015-05-04 01:01:00.000'), date('2015-05-04')),
  ('val1a', 6, 8, 10, float(15.0), 20, 20E2, timestamp('2014-04-04 01:02:00.001'), date('2014-04-04')),
  ('val1e', 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
  ('val2a', 6, 12, 14, float(15), 20, 20E2, timestamp('2014-04-04 01:01:00.000'), date('2014-04-04')),
  ('val1b', 10, 12, 19, float(17), 25, 26E2, timestamp('2014-05-04 01:01:00.000'), date('2014-05-04')),
  ('val1b', 8, 16, 119, float(17), 25, 26E2, timestamp('2015-05-04 01:01:00.000'), date('2015-05-04')),
  ('val1c', 12, 16, 219, float(17), 25, 26E2, timestamp('2016-05-04 01:01:00.000'), date('2016-05-04')),
  ('val1b', null, 16, 319, float(17), 25, 26E2, timestamp('2017-05-04 01:01:00.000'), null),
  ('val2e', 8, null, 419, float(17), 25, 26E2, timestamp('2014-06-04 01:01:00.000'), date('2014-06-04')),
  ('val1f', 19, null, 519, float(17), 25, 26E2, timestamp('2014-05-04 01:01:00.000'), date('2014-05-04')),
  ('val1b', 10, 12, 19, float(17), 25, 26E2, timestamp('2014-06-04 01:01:00.000'), date('2014-06-04')),
  ('val1b', 8, 16, 19, float(17), 25, 26E2, timestamp('2014-07-04 01:01:00.000'), date('2014-07-04')),
  ('val1c', 12, 16, 19, float(17), 25, 26E2, timestamp('2014-08-04 01:01:00.000'), date('2014-08-05')),
  ('val1e', 8, null, 19, float(17), 25, 26E2, timestamp('2014-09-04 01:01:00.000'), date('2014-09-04')),
  ('val1f', 19, null, 19, float(17), 25, 26E2, timestamp('2014-10-04 01:01:00.000'), date('2014-10-04')),
  ('val1b', 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
  ('val3a', 6, 12, 110, float(15), 20, 20E2, timestamp('2014-04-04 01:02:00.000'), date('2014-04-04')),
  ('val3a', 6, 12, 10, float(15), 20, 20E2, timestamp('2014-05-04 01:02:00.000'), date('2014-05-04')),
  ('val1b', 10, 12, 219, float(17), 25, 26E2, timestamp('2014-05-04 01:02:00.000'), date('2014-05-04')),
  ('val1b', 10, 12, 19, float(17), 25, 26E2, timestamp('2014-05-04 01:02:00.000'), date('2014-05-04')),
  ('val1b', 8, 16, 319, float(17), 25, 26E2, timestamp('2014-06-04 01:02:00.000'), date('2014-06-04')),
  ('val1b', 8, 16, 19, float(17), 25, 26E2, timestamp('2014-07-04 01:02:00.000'), date('2014-07-04')),
  ('val3c', 17, 16, 519, float(17), 25, 26E2, timestamp('2014-08-04 01:02:00.000'), date('2014-08-04')),
  ('val3c', 17, 16, 19, float(17), 25, 26E2, timestamp('2014-09-04 01:02:00.000'), date('2014-09-05')),
  ('val1b', null, 16, 419, float(17), 25, 26E2, timestamp('2014-10-04 01:02:00.000'), null),
  ('val1b', null, 16, 19, float(17), 25, 26E2, timestamp('2014-11-04 01:02:00.000'), null),
  ('val3b', 8, null, 719, float(17), 25, 26E2, timestamp('2014-05-04 01:02:00.000'), date('2014-05-04')),
  ('val3b', 8, null, 19, float(17), 25, 26E2, timestamp('2015-05-04 01:02:00.000'), date('2015-05-04'))
;

-- correlated IN subquery
-- HAVING in the subquery
-- TC 01.01
SELECT t1a,
       t1b,
       t1h
FROM   t1
WHERE  t1b IN (SELECT t2b
               FROM   t2
               GROUP BY t2b
               HAVING t2b < 10);

-- TC 01.02
SELECT t1a,
       t1b,
       t1c
FROM   t1
WHERE  t1b IN (SELECT Min(t2b)
               FROM   t2
               WHERE  t1a = t2a
               GROUP  BY t2b
               HAVING t2b > 1);

-- HAVING in the parent
-- TC 01.03
SELECT t1a, t1b, t1c
FROM   t1
WHERE  t1b IN (SELECT t2b
               FROM   t2
               WHERE t1c < t2c)
GROUP BY t1a, t1b, t1c
HAVING t1b < 10;

-- TC 01.04
SELECT t1a, t1b, t1c
FROM   t1
WHERE  t1b IN (SELECT t2b
               FROM   t2
               WHERE t1c = t2c)
GROUP BY t1a, t1b, t1c
HAVING COUNT (DISTINCT t1b) < 10;

-- BOTH
-- TC 01.05
SELECT Count(DISTINCT( t1a )),
       t1b
FROM   t1
WHERE  t1c IN (SELECT t2c
               FROM   t2
               WHERE  t1a = t2a
               GROUP BY t2c
               HAVING t2c > 10)
GROUP  BY t1b
HAVING t1b >= 8;

-- TC 01.06
SELECT t1a,
       Max(t1b)
FROM   t1
WHERE  t1b > 0
GROUP  BY t1a
HAVING t1a IN (SELECT t2a
               FROM   t2
               WHERE  t2b IN (SELECT t3b
                              FROM   t3
                              WHERE  t2c = t3c)
               );

-- HAVING clause with NOT IN
-- TC 01.07
SELECT t1a,
       t1c,
       Min(t1d)
FROM   t1
WHERE  t1a NOT IN (SELECT t2a
                   FROM   t2
                   GROUP BY t2a
                   HAVING t2a > 'val2a')
GROUP BY t1a, t1c
HAVING Min(t1d) > t1c;

-- TC 01.08
SELECT t1a,
       t1b
FROM   t1
WHERE  t1d NOT IN (SELECT t2d
                   FROM   t2
                   WHERE  t1a = t2ai
                   GROUP BY t2c, t2d
                   HAVING t2c > 8)
GROUP  BY t1a, t1b
HAVING t1b < 10;

-- TC 01.09
SELECT t1a,
       Max(t1b)
FROM   t1
WHERE  t1b > 0
GROUP  BY t1a
HAVING t1a NOT IN (SELECT t2a
                   FROM   t2
                   WHERE  t2b > 3);