--test suite for multiple columns in predicate 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
-- TC 01.01
SELECT t1a,
       t1b,
       t1h
FROM   t1
WHERE  ( t1a, t1h ) NOT IN (SELECT t2a,
                                   t2h
                            FROM   t2
                            WHERE  t2a = t1a
                            ORDER  BY t2a)
AND t1a = 'val1a';

-- TC 01.02
SELECT t1a,
       t1b,
       t1d
FROM   t1
WHERE  ( t1b, t1d ) IN (SELECT t2b,
                               t2d
                        FROM   t2
                        WHERE  t2i IN (SELECT t3i
                                       FROM   t3
                                       WHERE  t2b > t3b));
-- TC 01.03
SELECT t1a,
       t1b,
       t1d
FROM   t1
WHERE  ( t1b, t1d ) NOT IN (SELECT t2b,
                                   t2d
                            FROM   t2
                            WHERE  t2h IN (SELECT t3h
                                           FROM   t3
                                           WHERE  t2b > t3b))
AND t1a = 'val1a';

-- TC 01.04
SELECT t2a
FROM   (SELECT t2a
        FROM   t2
        WHERE  ( t2a, t2b ) IN (SELECT t1a,
                                       t1b
                                FROM   t1)
        UNION ALL
        SELECT t2a
        FROM   t2
        WHERE  ( t2a, t2b ) IN (SELECT t1a,
                                       t1b
                                FROM   t1)
        UNION 
        SELECT t2a
        FROM   t2
        WHERE  ( t2a, t2b ) IN (SELECT t3a,
                                       t3b
                                FROM   t3)) AS t4;

-- TC 01.05
WITH cte1 AS
(
       SELECT t1a,
              t1b
       FROM   t1
       WHERE  (
                     t1b, t1d) IN
              (
                     SELECT t2b,
                            t2d
                     FROM   t2
                     WHERE  t1c = t2c))
SELECT *
FROM            (
                           SELECT     *
                           FROM       cte1
                           JOIN       cte1 cte2
                           on         cte1.t1b = cte2.t1b) s;