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

## What changes were proposed in this pull request?

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

`in-set-operations.sql`
`in-with-cte.sql`
`not-in-joins.sql`

Here are the queries and results from running on DB2.

[in-set-operations DB2 version](https://github.com/apache/spark/files/772846/in-set-operations.sql.db2.txt)
[Output of in-set-operations](https://github.com/apache/spark/files/772848/in-set-operations.sql.db2.out.txt)
[in-with-cte DB2 version](https://github.com/apache/spark/files/772849/in-with-cte.sql.db2.txt)
[Output of in-with-cte](https://github.com/apache/spark/files/772856/in-with-cte.sql.db2.out.txt)
[not-in-joins DB2 version](https://github.com/apache/spark/files/772851/not-in-joins.sql.db2.txt)
[Output of not-in-joins](https://github.com/apache/spark/files/772852/not-in-joins.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 #16915 from kevinyu98/spark-18871-44.

1 files changed, 364 insertions, 0 deletions

diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-with-cte.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-with-cte.sql.out
new file mode 100644
index 0000000000..7d3943e376
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-with-cte.sql.out
@@ -0,0 +1,364 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 13
+
+
+-- !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
+WITH cte1
+     AS (SELECT t1a,
+                t1b
+         FROM   t1
+         WHERE  t1a = "val1a")
+SELECT t1a,
+       t1b,
+       t1c,
+       t1d,
+       t1h
+FROM   t1
+WHERE  t1b IN (SELECT cte1.t1b
+               FROM   cte1
+               WHERE  cte1.t1b > 0)
+-- !query 3 schema
+struct<t1a:string,t1b:smallint,t1c:int,t1d:bigint,t1h:timestamp>
+-- !query 3 output
+val1a	16	12	10	2014-07-04 01:01:00
+val1a	16	12	21	2014-06-04 01:02:00.001
+val1a	6	8	10	2014-04-04 01:00:00
+val1a	6	8	10	2014-04-04 01:02:00.001
+
+
+-- !query 4
+WITH cte1 AS
+(
+       SELECT t1a,
+              t1b
+       FROM   t1)
+SELECT count(distinct(t1a)), t1b, t1c
+FROM   t1
+WHERE  t1b IN
+       (
+              SELECT cte1.t1b
+              FROM   cte1
+              WHERE  cte1.t1b > 0
+              UNION
+              SELECT cte1.t1b
+              FROM   cte1
+              WHERE  cte1.t1b > 5
+              UNION ALL
+              SELECT cte1.t1b
+              FROM   cte1
+              INTERSECT
+              SELECT cte1.t1b
+              FROM   cte1
+              UNION
+              SELECT cte1.t1b
+              FROM   cte1 )
+GROUP BY t1a, t1b, t1c
+HAVING t1c IS NOT NULL
+-- !query 4 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint,t1c:int>
+-- !query 4 output
+1	16	12
+1	6	8
+1	8	16
+1	8	16
+
+
+-- !query 5
+WITH cte1 AS
+(
+       SELECT t1a,
+              t1b,
+              t1c,
+              t1d,
+              t1e
+       FROM   t1)
+SELECT t1a,
+       t1b,
+       t1c,
+       t1h
+FROM   t1
+WHERE  t1c IN
+       (
+              SELECT          cte1.t1c
+              FROM            cte1
+              JOIN            cte1 cte2
+              on              cte1.t1b > cte2.t1b
+              FULL OUTER JOIN cte1 cte3
+              ON              cte1.t1c = cte3.t1c
+              LEFT JOIN       cte1 cte4
+              ON              cte1.t1d = cte4.t1d
+              INNER JOIN  cte1 cte5
+              ON              cte1.t1b < cte5.t1b
+              LEFT OUTER JOIN  cte1 cte6
+              ON              cte1.t1d > cte6.t1d)
+-- !query 5 schema
+struct<t1a:string,t1b:smallint,t1c:int,t1h:timestamp>
+-- !query 5 output
+val1b	8	16	2014-05-04 01:01:00
+val1c	8	16	2014-05-04 01:02:00.001
+val1d	NULL	16	2014-06-04 01:01:00
+val1d	NULL	16	2014-07-04 01:02:00.001
+
+
+-- !query 6
+WITH cte1
+     AS (SELECT t1a,
+                t1b
+         FROM   t1
+         WHERE  t1b IN (SELECT t2b
+                        FROM   t2
+                               RIGHT JOIN t1
+                                       ON t1c = t2c
+                               LEFT JOIN t3
+                                      ON t2d = t3d)
+                AND t1a = "val1b")
+SELECT *
+FROM   (SELECT *
+        FROM   cte1
+               JOIN cte1 cte2
+                 ON cte1.t1b > 5
+                    AND cte1.t1a = cte2.t1a
+               FULL OUTER JOIN cte1 cte3
+                            ON cte1.t1a = cte3.t1a
+               INNER JOIN cte1 cte4
+                       ON cte1.t1b = cte4.t1b) s
+-- !query 6 schema
+struct<t1a:string,t1b:smallint,t1a:string,t1b:smallint,t1a:string,t1b:smallint,t1a:string,t1b:smallint>
+-- !query 6 output
+val1b	8	val1b	8	val1b	8	val1b	8
+
+
+-- !query 7
+WITH cte1 AS
+(
+       SELECT t1a,
+              t1b,
+              t1h
+       FROM   t1
+       WHERE  t1a IN
+              (
+                     SELECT t2a
+                     FROM   t2
+                     WHERE  t1b < t2b))
+SELECT   Count(DISTINCT t1a),
+         t1b
+FROM     (
+                    SELECT     cte1.t1a,
+                               cte1.t1b
+                    FROM       cte1
+                    JOIN       cte1 cte2
+                    on         cte1.t1h >= cte2.t1h) s
+WHERE    t1b IN
+         (
+                SELECT t1b
+                FROM   t1)
+GROUP BY t1b
+-- !query 7 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 7 output
+2	8
+
+
+-- !query 8
+WITH cte1 AS
+(
+       SELECT t1a,
+              t1b,
+              t1c
+       FROM   t1
+       WHERE  t1b IN
+              (
+                     SELECT t2b
+                     FROM   t2 FULL OUTER JOIN T3 on t2a = t3a
+                     WHERE  t1c = t2c) AND
+              t1a = "val1b")
+SELECT *
+FROM            (
+                       SELECT *
+                       FROM   cte1
+                       INNER JOIN   cte1 cte2 ON cte1.t1a = cte2.t1a
+                       RIGHT OUTER JOIN cte1 cte3  ON cte1.t1b = cte3.t1b
+                       LEFT OUTER JOIN cte1 cte4 ON cte1.t1c = cte4.t1c
+                       ) s
+-- !query 8 schema
+struct<t1a:string,t1b:smallint,t1c:int,t1a:string,t1b:smallint,t1c:int,t1a:string,t1b:smallint,t1c:int,t1a:string,t1b:smallint,t1c:int>
+-- !query 8 output
+val1b	8	16	val1b	8	16	val1b	8	16	val1b	8	16
+
+
+-- !query 9
+WITH cte1
+     AS (SELECT t1a,
+                t1b
+         FROM   t1
+         WHERE  t1b IN (SELECT t2b
+                        FROM   t2
+                        WHERE  t1c = t2c))
+SELECT Count(DISTINCT( s.t1a )),
+       s.t1b
+FROM   (SELECT cte1.t1a,
+               cte1.t1b
+        FROM   cte1
+               RIGHT OUTER JOIN cte1 cte2
+                             ON cte1.t1a = cte2.t1a) s
+GROUP  BY s.t1b
+-- !query 9 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 9 output
+2	8
+
+
+-- !query 10
+WITH cte1 AS
+(
+       SELECT t1a,
+              t1b
+       FROM   t1
+       WHERE  t1b IN
+              (
+                     SELECT t2b
+                     FROM   t2
+                     WHERE  t1c = t2c))
+SELECT DISTINCT(s.t1b)
+FROM            (
+                                SELECT          cte1.t1b
+                                FROM            cte1
+                                LEFT OUTER JOIN cte1 cte2
+                                ON              cte1.t1b = cte2.t1b) s
+WHERE           s.t1b IN
+                (
+                       SELECT t1.t1b
+                       FROM   t1 INNER
+                       JOIN   cte1
+                       ON     t1.t1a = cte1.t1a)
+-- !query 10 schema
+struct<t1b:smallint>
+-- !query 10 output
+8
+
+
+-- !query 11
+WITH cte1
+     AS (SELECT t1a,
+                t1b
+         FROM   t1
+         WHERE  t1a = "val1d")
+SELECT t1a,
+       t1b,
+       t1c,
+       t1h
+FROM   t1
+WHERE  t1b NOT IN (SELECT cte1.t1b
+                   FROM   cte1
+                   WHERE  cte1.t1b < 0) AND
+       t1c > 10
+-- !query 11 schema
+struct<t1a:string,t1b:smallint,t1c:int,t1h:timestamp>
+-- !query 11 output
+val1a	16	12	2014-06-04 01:02:00.001
+val1a	16	12	2014-07-04 01:01:00
+val1b	8	16	2014-05-04 01:01:00
+val1c	8	16	2014-05-04 01:02:00.001
+val1d	NULL	16	2014-06-04 01:01:00
+val1d	NULL	16	2014-07-04 01:02:00.001
+
+
+-- !query 12
+WITH cte1 AS
+(
+       SELECT t1a,
+              t1b,
+              t1c,
+              t1d,
+              t1h
+       FROM   t1
+       WHERE  t1d NOT IN
+              (
+                              SELECT          t2d
+                              FROM            t2
+                              FULL OUTER JOIN t3 ON t2a = t3a
+                              JOIN t1 on t1b = t2b))
+SELECT   t1a,
+         t1b,
+         t1c,
+         t1d,
+         t1h
+FROM     t1
+WHERE    t1b NOT IN
+         (
+                    SELECT     cte1.t1b
+                    FROM       cte1 INNER
+                    JOIN       cte1 cte2 ON cte1.t1a = cte2.t1a
+                    RIGHT JOIN cte1 cte3 ON cte1.t1b = cte3.t1b
+                    JOIN cte1 cte4 ON cte1.t1c = cte4.t1c) AND
+         t1c IS NOT NULL
+ORDER BY t1c DESC
+-- !query 12 schema
+struct<t1a:string,t1b:smallint,t1c:int,t1d:bigint,t1h:timestamp>
+-- !query 12 output
+val1b	8	16	19	2014-05-04 01:01:00
+val1c	8	16	19	2014-05-04 01:02:00.001