[SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN subquery 3rd batch

## What changes were proposed in this pull request?

This is 3ird batch of test case for IN/NOT IN subquery. In this PR, it has these test files:

`in-having.sql`
`in-joins.sql`
`in-multiple-columns.sql`

These are the queries and results from running on DB2.
[in-having DB2 version](https://github.com/apache/spark/files/772668/in-having.sql.db2.txt)
[output of in-having](https://github.com/apache/spark/files/772670/in-having.sql.db2.out.txt)
[in-joins DB2 version](https://github.com/apache/spark/files/772672/in-joins.sql.db2.txt)
[output of in-joins](https://github.com/apache/spark/files/772673/in-joins.sql.db2.out.txt)
[in-multiple-columns DB2 version](https://github.com/apache/spark/files/772678/in-multiple-columns.sql.db2.txt)
[output of in-multiple-columns](https://github.com/apache/spark/files/772680/in-multiple-columns.sql.db2.out.txt)

## How was this patch tested?
This pr is adding new test cases. We compare the result from spark with the result from another RDBMS(We used DB2 LUW). If the results are the same, we assume the result is correct.

Author: Kevin Yu <qyu@us.ibm.com>

Closes #16841 from kevinyu98/spark-18871-33.

6 files changed, 1297 insertions, 0 deletions

diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-having.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-having.sql
new file mode 100644
index 0000000000..8f98ae1155
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-having.sql
@@ -0,0 +1,152 @@
+-- A test suite for IN HAVING in parent side, subquery, and both predicate subquery
+-- It includes correlated cases.
+
+create temporary view t1 as select * from values
+  ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
+  ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
+  ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+  ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
+  ("val1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
+  ("val1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
+  ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
+  ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
+  ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+  ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
+  ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
+  as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i);
+
+create temporary view t2 as select * from values
+  ("val2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+  ("val1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
+  ("val1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
+  ("val2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+  ("val1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+  ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+  ("val1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
+  ("val1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
+  ("val1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
+  ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
+  as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i);
+
+create temporary view t3 as select * from values
+  ("val3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
+  ("val3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
+  ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
+  ("val3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
+  ("val3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
+  ("val1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
+  ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
+  ("val3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val3b", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
+  as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i);
+
+-- 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 = t2a
+                   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);
+
diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-joins.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-joins.sql
new file mode 100644
index 0000000000..b10c41929c
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-joins.sql
@@ -0,0 +1,270 @@
+-- A test suite for IN JOINS in parent side, subquery, and both predicate subquery
+-- It includes correlated cases.
+
+create temporary view t1 as select * from values
+  ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
+  ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
+  ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+  ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
+  ("val1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
+  ("val1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
+  ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
+  ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
+  ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+  ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
+  ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
+  as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i);
+
+create temporary view t2 as select * from values
+  ("val2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+  ("val1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
+  ("val1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
+  ("val2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+  ("val1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+  ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+  ("val1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
+  ("val1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
+  ("val1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
+  ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
+  as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i);
+
+create temporary view t3 as select * from values
+  ("val3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
+  ("val3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
+  ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
+  ("val3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
+  ("val3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
+  ("val1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
+  ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
+  ("val3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val3b", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
+  as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i);
+
+-- correlated IN subquery
+-- different JOIN in parent side
+-- TC 01.01
+SELECT t1a, t1b, t1c, t3a, t3b, t3c
+FROM   t1 natural JOIN t3
+WHERE  t1a IN (SELECT t2a
+               FROM   t2
+               WHERE t1a = t2a)
+       AND t1b = t3b
+       AND t1a = t3a
+ORDER  BY t1a,
+          t1b,
+          t1c DESC nulls first;
+
+-- TC 01.02
+SELECT    Count(DISTINCT(t1a)),
+          t1b,
+          t3a,
+          t3b,
+          t3c
+FROM      t1 natural left JOIN t3
+WHERE     t1a IN
+          (
+                 SELECT t2a
+                 FROM   t2
+                 WHERE t1d = t2d)
+AND       t1b > t3b
+GROUP BY  t1a,
+          t1b,
+          t3a,
+          t3b,
+          t3c
+ORDER BY  t1a DESC;
+
+-- TC 01.03
+SELECT     Count(DISTINCT(t1a))
+FROM       t1 natural right JOIN t3
+WHERE      t1a IN
+           (
+                  SELECT t2a
+                  FROM   t2
+                  WHERE  t1b = t2b)
+AND        t1d IN
+           (
+                  SELECT t2d
+                  FROM   t2
+                  WHERE  t1c > t2c)
+AND        t1a = t3a
+GROUP BY   t1a
+ORDER BY   t1a;
+
+-- TC 01.04
+SELECT          t1a,
+                t1b,
+                t1c,
+                t3a,
+                t3b,
+                t3c
+FROM            t1 FULL OUTER JOIN t3
+where           t1a IN
+                (
+                       SELECT t2a
+                       FROM   t2
+                       WHERE t2c IS NOT NULL)
+AND             t1b != t3b
+AND             t1a = 'val1b'
+ORDER BY        t1a;
+
+-- TC 01.05
+SELECT     Count(DISTINCT(t1a)),
+           t1b
+FROM       t1 RIGHT JOIN t3
+where      t1a IN
+           (
+                  SELECT t2a
+                  FROM   t2
+                  WHERE  t2h > t3h)
+AND        t3a IN
+           (
+                  SELECT t2a
+                  FROM   t2
+                  WHERE  t2c > t3c)
+AND        t1h >= t3h
+GROUP BY   t1a,
+           t1b
+HAVING     t1b > 8
+ORDER BY   t1a;
+
+-- TC 01.06
+SELECT   Count(DISTINCT(t1a))
+FROM     t1 LEFT OUTER
+JOIN     t3
+ON t1a = t3a
+WHERE    t1a IN
+         (
+                SELECT t2a
+                FROM   t2
+                WHERE  t1h < t2h )
+GROUP BY t1a
+ORDER BY t1a;
+
+-- TC 01.07
+SELECT   Count(DISTINCT(t1a)),
+         t1b
+FROM     t1 INNER JOIN     t2
+ON       t1a > t2a
+WHERE    t1b IN
+         (
+                SELECT t2b
+                FROM   t2
+                WHERE  t2h > t1h)
+OR       t1a IN
+         (
+                SELECT t2a
+                FROM   t2
+                WHERE  t2h < t1h)
+GROUP BY t1b
+HAVING   t1b > 6;
+
+-- different JOIN in the subquery
+-- TC 01.08
+SELECT   Count(DISTINCT(t1a)),
+         t1b
+FROM     t1
+WHERE    t1a IN
+         (
+                    SELECT     t2a
+                    FROM       t2
+                    JOIN t1
+                    WHERE      t2b <> t1b)
+AND      t1h IN
+         (
+                    SELECT     t2h
+                    FROM       t2
+                    RIGHT JOIN t3
+                    where      t2b = t3b)
+GROUP BY t1b
+HAVING t1b > 8;
+
+-- TC 01.09
+SELECT   Count(DISTINCT(t1a)),
+         t1b
+FROM     t1
+WHERE    t1a IN
+         (
+                    SELECT     t2a
+                    FROM       t2
+                    JOIN t1
+                    WHERE      t2b <> t1b)
+AND      t1h IN
+         (
+                    SELECT     t2h
+                    FROM       t2
+                    RIGHT JOIN t3
+                    where      t2b = t3b)
+AND       t1b IN
+         (
+                    SELECT     t2b
+                    FROM       t2
+                    FULL OUTER JOIN t3
+                    where      t2b = t3b)
+
+GROUP BY t1b
+HAVING   t1b > 8;
+
+-- JOIN in the parent and subquery
+-- TC 01.10
+SELECT     Count(DISTINCT(t1a)),
+           t1b
+FROM       t1
+INNER JOIN t2 on t1b = t2b
+RIGHT JOIN t3 ON t1a = t3a
+where      t1a IN
+           (
+                           SELECT          t2a
+                           FROM            t2
+                           FULL OUTER JOIN t3
+                           WHERE           t2b > t3b)
+AND        t1c IN
+           (
+                           SELECT          t3c
+                           FROM            t3
+                           LEFT OUTER JOIN t2
+                           ON              t3a = t2a )
+AND        t1b IN
+           (
+                  SELECT t3b
+                  FROM   t3 LEFT OUTER
+                  JOIN   t1
+                  WHERE  t3c = t1c)
+
+AND        t1a = t2a
+GROUP BY   t1b
+ORDER BY   t1b DESC;
+
+-- TC 01.11
+SELECT    t1a,
+          t1b,
+          t1c,
+          count(distinct(t2a)),
+          t2b,
+          t2c
+FROM      t1
+FULL JOIN t2  on t1a = t2a
+RIGHT JOIN t3 on t1a = t3a
+where     t1a IN
+          (
+                 SELECT t2a
+                 FROM   t2 INNER
+                 JOIN   t3
+                 ON     t2b < t3b
+                 WHERE  t2c IN
+                        (
+                               SELECT t1c
+                               FROM   t1
+                               WHERE  t1a = t2a))
+and t1a = t2a
+Group By t1a, t1b, t1c, t2a, t2b, t2c
+HAVING t2c IS NOT NULL
+ORDER By t2b DESC nulls last;
+
diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-multiple-columns.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-multiple-columns.sql
new file mode 100644
index 0000000000..4643605148
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-multiple-columns.sql
@@ -0,0 +1,127 @@
+-- A test suite for multiple columns in predicate in parent side, subquery, and both predicate subquery
+-- It includes correlated cases.
+
+create temporary view t1 as select * from values
+  ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
+  ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
+  ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+  ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
+  ("val1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
+  ("val1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
+  ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
+  ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
+  ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+  ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
+  ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
+  as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i);
+
+create temporary view t2 as select * from values
+  ("val2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+  ("val1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
+  ("val1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
+  ("val2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+  ("val1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+  ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+  ("val1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
+  ("val1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
+  ("val1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
+  ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
+  as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i);
+
+create temporary view t3 as select * from values
+  ("val3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
+  ("val3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
+  ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
+  ("val3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
+  ("val3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
+  ("val1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
+  ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
+  ("val3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val3b", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
+  as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i);
+
+-- 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 DISTINCT
+        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;
+
diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-having.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-having.sql.out
new file mode 100644
index 0000000000..b90ebf57e7
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-having.sql.out
@@ -0,0 +1,217 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 12
+
+
+-- !query 0
+create temporary view t1 as select * from values
+  ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
+  ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
+  ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+  ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
+  ("val1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
+  ("val1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
+  ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
+  ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
+  ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+  ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
+  ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
+  as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+create temporary view t2 as select * from values
+  ("val2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+  ("val1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
+  ("val1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
+  ("val2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+  ("val1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+  ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+  ("val1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
+  ("val1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
+  ("val1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
+  ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
+  as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+create temporary view t3 as select * from values
+  ("val3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
+  ("val3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
+  ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
+  ("val3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
+  ("val3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
+  ("val1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
+  ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
+  ("val3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val3b", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
+  as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+SELECT t1a,
+       t1b,
+       t1h
+FROM   t1
+WHERE  t1b IN (SELECT t2b
+               FROM   t2
+               GROUP BY t2b
+               HAVING t2b < 10)
+-- !query 3 schema
+struct<t1a:string,t1b:smallint,t1h:timestamp>
+-- !query 3 output
+val1a	6	2014-04-04 01:00:00
+val1a	6	2014-04-04 01:02:00.001
+val1b	8	2014-05-04 01:01:00
+val1c	8	2014-05-04 01:02:00.001
+
+
+-- !query 4
+SELECT t1a,
+       t1b,
+       t1c
+FROM   t1
+WHERE  t1b IN (SELECT Min(t2b)
+               FROM   t2
+               WHERE  t1a = t2a
+               GROUP  BY t2b
+               HAVING t2b > 1)
+-- !query 4 schema
+struct<t1a:string,t1b:smallint,t1c:int>
+-- !query 4 output
+val1b	8	16
+
+
+-- !query 5
+SELECT t1a, t1b, t1c
+FROM   t1
+WHERE  t1b IN (SELECT t2b
+               FROM   t2
+               WHERE t1c < t2c)
+GROUP BY t1a, t1b, t1c
+HAVING t1b < 10
+-- !query 5 schema
+struct<t1a:string,t1b:smallint,t1c:int>
+-- !query 5 output
+val1a	6	8
+
+
+-- !query 6
+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
+-- !query 6 schema
+struct<t1a:string,t1b:smallint,t1c:int>
+-- !query 6 output
+val1b	8	16
+val1c	8	16
+
+
+-- !query 7
+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
+-- !query 7 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 7 output
+2	8
+
+
+-- !query 8
+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)
+               )
+-- !query 8 schema
+struct<t1a:string,max(t1b):smallint>
+-- !query 8 output
+val1b	8
+
+
+-- !query 9
+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
+-- !query 9 schema
+struct<t1a:string,t1c:int,min(t1d):bigint>
+-- !query 9 output
+val1a	8	10
+val1b	16	19
+val1c	16	19
+val1d	16	19
+
+
+-- !query 10
+SELECT t1a,
+       t1b
+FROM   t1
+WHERE  t1d NOT IN (SELECT t2d
+                   FROM   t2
+                   WHERE  t1a = t2a
+                   GROUP BY t2c, t2d
+                   HAVING t2c > 8)
+GROUP  BY t1a, t1b
+HAVING t1b < 10
+-- !query 10 schema
+struct<t1a:string,t1b:smallint>
+-- !query 10 output
+val1a	6
+
+
+-- !query 11
+SELECT t1a,
+       Max(t1b)
+FROM   t1
+WHERE  t1b > 0
+GROUP  BY t1a
+HAVING t1a NOT IN (SELECT t2a
+                   FROM   t2
+                   WHERE  t2b > 3)
+-- !query 11 schema
+struct<t1a:string,max(t1b):smallint>
+-- !query 11 output
+val1a	16
+val1d	10
diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-joins.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-joins.sql.out
new file mode 100644
index 0000000000..7258bcfc6a
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-joins.sql.out
@@ -0,0 +1,353 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 14
+
+
+-- !query 0
+create temporary view t1 as select * from values
+  ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
+  ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
+  ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+  ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
+  ("val1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
+  ("val1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
+  ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
+  ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
+  ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+  ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
+  ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
+  as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+create temporary view t2 as select * from values
+  ("val2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+  ("val1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
+  ("val1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
+  ("val2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+  ("val1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+  ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+  ("val1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
+  ("val1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
+  ("val1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
+  ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
+  as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+create temporary view t3 as select * from values
+  ("val3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
+  ("val3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
+  ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
+  ("val3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
+  ("val3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
+  ("val1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
+  ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
+  ("val3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val3b", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
+  as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+SELECT t1a, t1b, t1c, t3a, t3b, t3c
+FROM   t1 natural JOIN t3
+WHERE  t1a IN (SELECT t2a
+               FROM   t2
+               WHERE t1a = t2a)
+       AND t1b = t3b
+       AND t1a = t3a
+ORDER  BY t1a,
+          t1b,
+          t1c DESC nulls first
+-- !query 3 schema
+struct<t1a:string,t1b:smallint,t1c:int,t3a:string,t3b:smallint,t3c:int>
+-- !query 3 output
+val1b	8	16	val1b	8	16
+val1b	8	16	val1b	8	16
+
+
+-- !query 4
+SELECT    Count(DISTINCT(t1a)),
+          t1b,
+          t3a,
+          t3b,
+          t3c
+FROM      t1 natural left JOIN t3
+WHERE     t1a IN
+          (
+                 SELECT t2a
+                 FROM   t2
+                 WHERE t1d = t2d)
+AND       t1b > t3b
+GROUP BY  t1a,
+          t1b,
+          t3a,
+          t3b,
+          t3c
+ORDER BY  t1a DESC
+-- !query 4 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint,t3a:string,t3b:smallint,t3c:int>
+-- !query 4 output
+1	10	val3b	8	NULL
+1	10	val1b	8	16
+1	10	val3a	6	12
+1	8	val3a	6	12
+1	8	val3a	6	12
+
+
+-- !query 5
+SELECT     Count(DISTINCT(t1a))
+FROM       t1 natural right JOIN t3
+WHERE      t1a IN
+           (
+                  SELECT t2a
+                  FROM   t2
+                  WHERE  t1b = t2b)
+AND        t1d IN
+           (
+                  SELECT t2d
+                  FROM   t2
+                  WHERE  t1c > t2c)
+AND        t1a = t3a
+GROUP BY   t1a
+ORDER BY   t1a
+-- !query 5 schema
+struct<count(DISTINCT t1a):bigint>
+-- !query 5 output
+1
+
+
+-- !query 6
+SELECT          t1a,
+                t1b,
+                t1c,
+                t3a,
+                t3b,
+                t3c
+FROM            t1 FULL OUTER JOIN t3
+where           t1a IN
+                (
+                       SELECT t2a
+                       FROM   t2
+                       WHERE t2c IS NOT NULL)
+AND             t1b != t3b
+AND             t1a = 'val1b'
+ORDER BY        t1a
+-- !query 6 schema
+struct<t1a:string,t1b:smallint,t1c:int,t3a:string,t3b:smallint,t3c:int>
+-- !query 6 output
+val1b	8	16	val3a	6	12
+val1b	8	16	val3a	6	12
+val1b	8	16	val1b	10	12
+val1b	8	16	val1b	10	12
+val1b	8	16	val3c	17	16
+val1b	8	16	val3c	17	16
+
+
+-- !query 7
+SELECT     Count(DISTINCT(t1a)),
+           t1b
+FROM       t1 RIGHT JOIN t3
+where      t1a IN
+           (
+                  SELECT t2a
+                  FROM   t2
+                  WHERE  t2h > t3h)
+AND        t3a IN
+           (
+                  SELECT t2a
+                  FROM   t2
+                  WHERE  t2c > t3c)
+AND        t1h >= t3h
+GROUP BY   t1a,
+           t1b
+HAVING     t1b > 8
+ORDER BY   t1a
+-- !query 7 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 7 output
+1	10
+
+
+-- !query 8
+SELECT   Count(DISTINCT(t1a))
+FROM     t1 LEFT OUTER
+JOIN     t3
+ON t1a = t3a
+WHERE    t1a IN
+         (
+                SELECT t2a
+                FROM   t2
+                WHERE  t1h < t2h )
+GROUP BY t1a
+ORDER BY t1a
+-- !query 8 schema
+struct<count(DISTINCT t1a):bigint>
+-- !query 8 output
+1
+1
+1
+
+
+-- !query 9
+SELECT   Count(DISTINCT(t1a)),
+         t1b
+FROM     t1 INNER JOIN     t2
+ON       t1a > t2a
+WHERE    t1b IN
+         (
+                SELECT t2b
+                FROM   t2
+                WHERE  t2h > t1h)
+OR       t1a IN
+         (
+                SELECT t2a
+                FROM   t2
+                WHERE  t2h < t1h)
+GROUP BY t1b
+HAVING   t1b > 6
+-- !query 9 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 9 output
+1	10
+1	8
+
+
+-- !query 10
+SELECT   Count(DISTINCT(t1a)),
+         t1b
+FROM     t1
+WHERE    t1a IN
+         (
+                    SELECT     t2a
+                    FROM       t2
+                    JOIN t1
+                    WHERE      t2b <> t1b)
+AND      t1h IN
+         (
+                    SELECT     t2h
+                    FROM       t2
+                    RIGHT JOIN t3
+                    where      t2b = t3b)
+GROUP BY t1b
+HAVING t1b > 8
+-- !query 10 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 10 output
+1	10
+
+
+-- !query 11
+SELECT   Count(DISTINCT(t1a)),
+         t1b
+FROM     t1
+WHERE    t1a IN
+         (
+                    SELECT     t2a
+                    FROM       t2
+                    JOIN t1
+                    WHERE      t2b <> t1b)
+AND      t1h IN
+         (
+                    SELECT     t2h
+                    FROM       t2
+                    RIGHT JOIN t3
+                    where      t2b = t3b)
+AND       t1b IN
+         (
+                    SELECT     t2b
+                    FROM       t2
+                    FULL OUTER JOIN t3
+                    where      t2b = t3b)
+
+GROUP BY t1b
+HAVING   t1b > 8
+-- !query 11 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 11 output
+1	10
+
+
+-- !query 12
+SELECT     Count(DISTINCT(t1a)),
+           t1b
+FROM       t1
+INNER JOIN t2 on t1b = t2b
+RIGHT JOIN t3 ON t1a = t3a
+where      t1a IN
+           (
+                           SELECT          t2a
+                           FROM            t2
+                           FULL OUTER JOIN t3
+                           WHERE           t2b > t3b)
+AND        t1c IN
+           (
+                           SELECT          t3c
+                           FROM            t3
+                           LEFT OUTER JOIN t2
+                           ON              t3a = t2a )
+AND        t1b IN
+           (
+                  SELECT t3b
+                  FROM   t3 LEFT OUTER
+                  JOIN   t1
+                  WHERE  t3c = t1c)
+
+AND        t1a = t2a
+GROUP BY   t1b
+ORDER BY   t1b DESC
+-- !query 12 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 12 output
+1	8
+
+
+-- !query 13
+SELECT    t1a,
+          t1b,
+          t1c,
+          count(distinct(t2a)),
+          t2b,
+          t2c
+FROM      t1
+FULL JOIN t2  on t1a = t2a
+RIGHT JOIN t3 on t1a = t3a
+where     t1a IN
+          (
+                 SELECT t2a
+                 FROM   t2 INNER
+                 JOIN   t3
+                 ON     t2b < t3b
+                 WHERE  t2c IN
+                        (
+                               SELECT t1c
+                               FROM   t1
+                               WHERE  t1a = t2a))
+and t1a = t2a
+Group By t1a, t1b, t1c, t2a, t2b, t2c
+HAVING t2c IS NOT NULL
+ORDER By t2b DESC nulls last
+-- !query 13 schema
+struct<t1a:string,t1b:smallint,t1c:int,count(DISTINCT t2a):bigint,t2b:smallint,t2c:int>
+-- !query 13 output
+val1b	8	16	1	10	12
+val1b	8	16	1	8	16
+val1b	8	16	1	NULL	16
diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-multiple-columns.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-multiple-columns.sql.out
new file mode 100644
index 0000000000..7a96c4bc5a
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-multiple-columns.sql.out
@@ -0,0 +1,178 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 8
+
+
+-- !query 0
+create temporary view t1 as select * from values
+  ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
+  ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
+  ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+  ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
+  ("val1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
+  ("val1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
+  ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
+  ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
+  ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+  ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
+  ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
+  as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+create temporary view t2 as select * from values
+  ("val2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+  ("val1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
+  ("val1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
+  ("val2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+  ("val1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+  ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+  ("val1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
+  ("val1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
+  ("val1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
+  ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
+  as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+create temporary view t3 as select * from values
+  ("val3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
+  ("val3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
+  ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
+  ("val3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
+  ("val3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
+  ("val1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
+  ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
+  ("val3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ("val3b", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
+  as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+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'
+-- !query 3 schema
+struct<t1a:string,t1b:smallint,t1h:timestamp>
+-- !query 3 output
+val1a	16	2014-06-04 01:02:00.001
+val1a	16	2014-07-04 01:01:00
+val1a	6	2014-04-04 01:00:00
+val1a	6	2014-04-04 01:02:00.001
+
+
+-- !query 4
+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))
+-- !query 4 schema
+struct<t1a:string,t1b:smallint,t1d:bigint>
+-- !query 4 output
+val1e	10	19
+val1e	10	19
+
+
+-- !query 5
+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'
+-- !query 5 schema
+struct<t1a:string,t1b:smallint,t1d:bigint>
+-- !query 5 output
+val1a	16	10
+val1a	16	21
+val1a	6	10
+val1a	6	10
+
+
+-- !query 6
+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 DISTINCT
+        SELECT t2a
+        FROM   t2
+        WHERE  ( t2a, t2b ) IN (SELECT t3a,
+                                       t3b
+                                FROM   t3)) AS t4
+-- !query 6 schema
+struct<t2a:string>
+-- !query 6 output
+val1b
+
+
+-- !query 7
+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
+-- !query 7 schema
+struct<t1a:string,t1b:smallint,t1a:string,t1b:smallint>
+-- !query 7 output
+val1b	8	val1b	8
+val1b	8	val1c	8
+val1c	8	val1b	8
+val1c	8	val1c	8