Repository: spark Updated Branches: refs/heads/master fc02ef95c -> 8487902a9
[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 <q...@us.ibm.com> Closes #16915 from kevinyu98/spark-18871-44. Project: http://git-wip-us.apache.org/repos/asf/spark/repo Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/8487902a Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/8487902a Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/8487902a Branch: refs/heads/master Commit: 8487902a98caf727ba3f9820452b01276d20ede3 Parents: fc02ef9 Author: Kevin Yu <q...@us.ibm.com> Authored: Wed Feb 15 21:29:28 2017 -0800 Committer: Xiao Li <gatorsm...@gmail.com> Committed: Wed Feb 15 21:29:28 2017 -0800 ---------------------------------------------------------------------- .../subquery/in-subquery/in-set-operations.sql | 472 +++++++++++++++ .../inputs/subquery/in-subquery/in-with-cte.sql | 287 +++++++++ .../subquery/in-subquery/not-in-joins.sql | 167 ++++++ .../in-subquery/in-set-operations.sql.out | 595 +++++++++++++++++++ .../subquery/in-subquery/in-with-cte.sql.out | 364 ++++++++++++ .../subquery/in-subquery/not-in-joins.sql.out | 229 +++++++ 6 files changed, 2114 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/spark/blob/8487902a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-set-operations.sql ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-set-operations.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-set-operations.sql new file mode 100644 index 0000000..6b9e8bf --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-set-operations.sql @@ -0,0 +1,472 @@ +-- A test suite for set-operations 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 +-- UNION, UNION ALL, UNION DISTINCT, INTERSECT and EXCEPT in the parent +-- TC 01.01 +SELECT t2a, + t2b, + t2c, + t2h, + t2i +FROM (SELECT * + FROM t2 + WHERE t2a IN (SELECT t1a + FROM t1) + UNION ALL + SELECT * + FROM t3 + WHERE t3a IN (SELECT t1a + FROM t1)) AS t3 +WHERE t2i IS NOT NULL AND + 2 * t2b = t2c +ORDER BY t2c DESC nulls first; + +-- TC 01.02 +SELECT t2a, + t2b, + t2d, + Count(DISTINCT( t2h )), + t2i +FROM (SELECT * + FROM t2 + WHERE t2a IN (SELECT t1a + FROM t1 + WHERE t2b = t1b) + UNION + SELECT * + FROM t1 + WHERE t1a IN (SELECT t3a + FROM t3 + WHERE t1c = t3c)) AS t3 +GROUP BY t2a, + t2b, + t2d, + t2i +ORDER BY t2d DESC; + +-- TC 01.03 +SELECT t2a, + t2b, + t2c, + Min(t2d) +FROM t2 +WHERE t2a IN (SELECT t1a + FROM t1 + WHERE t1b = t2b) +GROUP BY t2a, t2b, t2c +UNION ALL +SELECT t2a, + t2b, + t2c, + Max(t2d) +FROM t2 +WHERE t2a IN (SELECT t1a + FROM t1 + WHERE t2c = t1c) +GROUP BY t2a, t2b, t2c +UNION +SELECT t3a, + t3b, + t3c, + Min(t3d) +FROM t3 +WHERE t3a IN (SELECT t2a + FROM t2 + WHERE t3c = t2c) +GROUP BY t3a, t3b, t3c +UNION DISTINCT +SELECT t1a, + t1b, + t1c, + Max(t1d) +FROM t1 +WHERE t1a IN (SELECT t3a + FROM t3 + WHERE t3d = t1d) +GROUP BY t1a, t1b, t1c; + +-- TC 01.04 +SELECT DISTINCT( t2a ), + t2b, + Count(t2c), + t2d, + t2h, + t2i +FROM t2 +WHERE t2a IN (SELECT t1a + FROM t1 + WHERE t1b = t2b) +GROUP BY t2a, + t2b, + t2c, + t2d, + t2h, + t2i +UNION +SELECT DISTINCT( t2a ), + t2b, + Count(t2c), + t2d, + t2h, + t2i +FROM t2 +WHERE t2a IN (SELECT t1a + FROM t1 + WHERE t2c = t1c) +GROUP BY t2a, + t2b, + t2c, + t2d, + t2h, + t2i +HAVING t2b IS NOT NULL; + +-- TC 01.05 +SELECT t2a, + t2b, + Count(t2c), + t2d, + t2h, + t2i +FROM t2 +WHERE t2a IN (SELECT DISTINCT(t1a) + FROM t1 + WHERE t1b = t2b) +GROUP BY t2a, + t2b, + t2c, + t2d, + t2h, + t2i + +UNION +SELECT DISTINCT( t2a ), + t2b, + Count(t2c), + t2d, + t2h, + t2i +FROM t2 +WHERE t2b IN (SELECT Max(t1b) + FROM t1 + WHERE t2c = t1c) +GROUP BY t2a, + t2b, + t2c, + t2d, + t2h, + t2i +HAVING t2b IS NOT NULL +UNION DISTINCT +SELECT t2a, + t2b, + t2c, + t2d, + t2h, + t2i +FROM t2 +WHERE t2d IN (SELECT min(t1d) + FROM t1 + WHERE t2c = t1c); + +-- TC 01.06 +SELECT t2a, + t2b, + t2c, + t2d +FROM t2 +WHERE t2a IN (SELECT t1a + FROM t1 + WHERE t1b = t2b AND + t1d < t2d) +INTERSECT +SELECT t2a, + t2b, + t2c, + t2d +FROM t2 +WHERE t2b IN (SELECT Max(t1b) + FROM t1 + WHERE t2c = t1c) +EXCEPT +SELECT t2a, + t2b, + t2c, + t2d +FROM t2 +WHERE t2d IN (SELECT Min(t3d) + FROM t3 + WHERE t2c = t3c) +UNION ALL +SELECT t2a, + t2b, + t2c, + t2d +FROM t2 +WHERE t2c IN (SELECT Max(t1c) + FROM t1 + WHERE t1d = t2d); + +-- UNION, UNION ALL, UNION DISTINCT, INTERSECT and EXCEPT in the subquery +-- TC 01.07 +SELECT DISTINCT(t1a), + t1b, + t1c, + t1d +FROM t1 +WHERE t1a IN (SELECT t3a + FROM (SELECT t2a t3a + FROM t2 + UNION ALL + SELECT t2a t3a + FROM t2) AS t3 + UNION + SELECT t2a + FROM (SELECT t2a + FROM t2 + WHERE t2b > 6 + UNION + SELECT t2a + FROM t2 + WHERE t2b > 6) AS t4 + UNION DISTINCT + SELECT t2a + FROM (SELECT t2a + FROM t2 + WHERE t2b > 6 + UNION DISTINCT + SELECT t1a + FROM t1 + WHERE t1b > 6) AS t5) +GROUP BY t1a, t1b, t1c, t1d +HAVING t1c IS NOT NULL AND t1b IS NOT NULL +ORDER BY t1c DESC; + +-- TC 01.08 +SELECT t1a, + t1b, + t1c +FROM t1 +WHERE t1b IN (SELECT t2b + FROM (SELECT t2b + FROM t2 + WHERE t2b > 6 + INTERSECT + SELECT t1b + FROM t1 + WHERE t1b > 6) AS t3 + WHERE t2b = t1b); + +-- TC 01.09 +SELECT t1a, + t1b, + t1c +FROM t1 +WHERE t1h IN (SELECT t2h + FROM (SELECT t2h + FROM t2 + EXCEPT + SELECT t3h + FROM t3) AS t3) +ORDER BY t1b DESC NULLs first, t1c DESC NULLs last; + +-- UNION, UNION ALL, UNION DISTINCT, INTERSECT and EXCEPT in the parent and subquery +-- TC 01.10 +SELECT t1a, + t1b, + t1c +FROM t1 +WHERE t1b IN + ( + SELECT t2b + FROM ( + SELECT t2b + FROM t2 + WHERE t2b > 6 + INTERSECT + SELECT t1b + FROM t1 + WHERE t1b > 6) AS t3) +UNION DISTINCT +SELECT t1a, + t1b, + t1c +FROM t1 +WHERE t1b IN + ( + SELECT t2b + FROM ( + SELECT t2b + FROM t2 + WHERE t2b > 6 + EXCEPT + SELECT t1b + FROM t1 + WHERE t1b > 6) AS t4 + WHERE t2b = t1b) +ORDER BY t1c DESC NULLS last; + +-- TC 01.11 +SELECT * +FROM (SELECT * + FROM (SELECT * + FROM t2 + WHERE t2h IN (SELECT t1h + FROM t1 + WHERE t1a = t2a) + UNION DISTINCT + SELECT * + FROM t1 + WHERE t1h IN (SELECT t3h + FROM t3 + UNION + SELECT t1h + FROM t1) + UNION + SELECT * + FROM t3 + WHERE t3a IN (SELECT t2a + FROM t2 + UNION ALL + SELECT t1a + FROM t1 + WHERE t1b > 0) + INTERSECT + SELECT * + FROM T1 + WHERE t1b IN (SELECT t3b + FROM t3 + UNION DISTINCT + SELECT t2b + FROM t2 + ) + EXCEPT + SELECT * + FROM t2 + WHERE t2h IN (SELECT t1i + FROM t1)) t4 + WHERE t4.t2b IN (SELECT Min(t3b) + FROM t3 + WHERE t4.t2a = t3a)); + +-- UNION, UNION ALL, UNION DISTINCT, INTERSECT and EXCEPT for NOT IN +-- TC 01.12 +SELECT t2a, + t2b, + t2c, + t2i +FROM (SELECT * + FROM t2 + WHERE t2a NOT IN (SELECT t1a + FROM t1 + UNION + SELECT t3a + FROM t3) + UNION ALL + SELECT * + FROM t2 + WHERE t2a NOT IN (SELECT t1a + FROM t1 + INTERSECT + SELECT t2a + FROM t2)) AS t3 +WHERE t3.t2a NOT IN (SELECT t1a + FROM t1 + INTERSECT + SELECT t2a + FROM t2) + AND t2c IS NOT NULL +ORDER BY t2a; + +-- TC 01.13 +SELECT Count(DISTINCT(t1a)), + t1b, + t1c, + t1i +FROM t1 +WHERE t1b NOT IN + ( + SELECT t2b + FROM ( + SELECT t2b + FROM t2 + WHERE t2b NOT IN + ( + SELECT t1b + FROM t1) + UNION + SELECT t1b + FROM t1 + WHERE t1b NOT IN + ( + SELECT t3b + FROM t3) + UNION + distinct SELECT t3b + FROM t3 + WHERE t3b NOT IN + ( + SELECT t2b + FROM t2)) AS t3 + WHERE t2b = t1b) +GROUP BY t1a, + t1b, + t1c, + t1i +HAVING t1b NOT IN + ( + SELECT t2b + FROM t2 + WHERE t2c IS NULL + EXCEPT + SELECT t3b + FROM t3) +ORDER BY t1c DESC NULLS LAST; + http://git-wip-us.apache.org/repos/asf/spark/blob/8487902a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-with-cte.sql ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-with-cte.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-with-cte.sql new file mode 100644 index 0000000..e65cb91 --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-with-cte.sql @@ -0,0 +1,287 @@ +-- A test suite for in with cte 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 +-- outside CTE +-- TC 01.01 +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); + +-- TC 01.02 +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; + +-- TC 01.03 +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); + +-- CTE inside and outside +-- TC 01.04 +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; + +-- TC 01.05 +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; + +-- TC 01.06 +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 +; + +-- TC 01.07 +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; + +-- TC 01.08 +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); + +-- CTE with NOT IN +-- TC 01.09 +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; + +-- TC 01.10 +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; + http://git-wip-us.apache.org/repos/asf/spark/blob/8487902a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/not-in-joins.sql ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/not-in-joins.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/not-in-joins.sql new file mode 100644 index 0000000..505366b --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/not-in-joins.sql @@ -0,0 +1,167 @@ +-- A test suite for not-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 not JOIN in parent side +-- TC 01.01 +SELECT t1a, + t1b, + t1c, + t3a, + t3b, + t3c +FROM t1 + JOIN t3 +WHERE t1a NOT IN (SELECT t2a + FROM t2) + AND t1b = t3b; + +-- TC 01.02 +SELECT t1a, + t1b, + t1c, + count(distinct(t3a)), + t3b, + t3c +FROM t1 +FULL OUTER JOIN t3 on t1b != t3b +RIGHT JOIN t2 on t1c = t2c +where t1a NOT IN + ( + SELECT t2a + FROM t2 + WHERE t2c NOT IN + ( + SELECT t1c + FROM t1 + WHERE t1a = t2a)) +AND t1b != t3b +AND t1d = t2d +GROUP BY t1a, t1b, t1c, t3a, t3b, t3c +HAVING count(distinct(t3a)) >= 1 +ORDER BY t1a; + +-- TC 01.03 +SELECT t1a, + t1b, + t1c, + t1d, + t1h +FROM t1 +WHERE t1a NOT IN + ( + SELECT t2a + FROM t2 + LEFT JOIN t3 on t2b = t3b + WHERE t1d = t2d + ) +AND t1d NOT IN + ( + SELECT t2d + FROM t2 + RIGHT JOIN t1 on t2e = t1e + WHERE t1a = t2a); + +-- TC 01.04 +SELECT Count(DISTINCT( t1a )), + t1b, + t1c, + t1d +FROM t1 +WHERE t1a NOT IN (SELECT t2a + FROM t2 + JOIN t1 + WHERE t2b <> t1b) +GROUP BY t1b, + t1c, + t1d +HAVING t1d NOT IN (SELECT t2d + FROM t2 + WHERE t1d = t2d) +ORDER BY t1b DESC; + +-- TC 01.05 +SELECT COUNT(DISTINCT(t1a)), + t1b, + t1c, + t1d +FROM t1 +WHERE t1a NOT IN + ( + SELECT t2a + FROM t2 INNER + JOIN t1 ON t1a = t2a) +GROUP BY t1b, + t1c, + t1d +HAVING t1b < sum(t1c); + +-- TC 01.06 +SELECT COUNT(DISTINCT(t1a)), + t1b, + t1c, + t1d +FROM t1 +WHERE t1a NOT IN + ( + SELECT t2a + FROM t2 INNER + JOIN t1 + ON t1a = t2a) +AND t1d NOT IN + ( + SELECT t2d + FROM t2 + INNER JOIN t3 + ON t2b = t3b ) +GROUP BY t1b, + t1c, + t1d +HAVING t1b < sum(t1c); + http://git-wip-us.apache.org/repos/asf/spark/blob/8487902a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-set-operations.sql.out ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-set-operations.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-set-operations.sql.out new file mode 100644 index 0000000..878bc75 --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-set-operations.sql.out @@ -0,0 +1,595 @@ +-- Automatically generated by SQLQueryTestSuite +-- Number of queries: 16 + + +-- !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 t2a, + t2b, + t2c, + t2h, + t2i +FROM (SELECT * + FROM t2 + WHERE t2a IN (SELECT t1a + FROM t1) + UNION ALL + SELECT * + FROM t3 + WHERE t3a IN (SELECT t1a + FROM t1)) AS t3 +WHERE t2i IS NOT NULL AND + 2 * t2b = t2c +ORDER BY t2c DESC nulls first +-- !query 3 schema +struct<t2a:string,t2b:smallint,t2c:int,t2h:timestamp,t2i:date> +-- !query 3 output +val1b 8 16 2015-05-04 01:01:00 2015-05-04 +val1b 8 16 2014-07-04 01:01:00 2014-07-04 +val1b 8 16 2014-06-04 01:02:00 2014-06-04 +val1b 8 16 2014-07-04 01:02:00 2014-07-04 + + +-- !query 4 +SELECT t2a, + t2b, + t2d, + Count(DISTINCT( t2h )), + t2i +FROM (SELECT * + FROM t2 + WHERE t2a IN (SELECT t1a + FROM t1 + WHERE t2b = t1b) + UNION + SELECT * + FROM t1 + WHERE t1a IN (SELECT t3a + FROM t3 + WHERE t1c = t3c)) AS t3 +GROUP BY t2a, + t2b, + t2d, + t2i +ORDER BY t2d DESC +-- !query 4 schema +struct<t2a:string,t2b:smallint,t2d:bigint,count(DISTINCT t2h):bigint,t2i:date> +-- !query 4 output +val1b 8 119 1 2015-05-04 +val1b 8 19 1 2014-07-04 +val1b 8 19 1 2014-05-04 + + +-- !query 5 +SELECT t2a, + t2b, + t2c, + Min(t2d) +FROM t2 +WHERE t2a IN (SELECT t1a + FROM t1 + WHERE t1b = t2b) +GROUP BY t2a, t2b, t2c +UNION ALL +SELECT t2a, + t2b, + t2c, + Max(t2d) +FROM t2 +WHERE t2a IN (SELECT t1a + FROM t1 + WHERE t2c = t1c) +GROUP BY t2a, t2b, t2c +UNION +SELECT t3a, + t3b, + t3c, + Min(t3d) +FROM t3 +WHERE t3a IN (SELECT t2a + FROM t2 + WHERE t3c = t2c) +GROUP BY t3a, t3b, t3c +UNION DISTINCT +SELECT t1a, + t1b, + t1c, + Max(t1d) +FROM t1 +WHERE t1a IN (SELECT t3a + FROM t3 + WHERE t3d = t1d) +GROUP BY t1a, t1b, t1c +-- !query 5 schema +struct<t2a:string,t2b:smallint,t2c:int,min(t2d):bigint> +-- !query 5 output +val1b 10 12 19 +val1b 8 16 119 +val1b 8 16 19 +val1b NULL 16 19 +val1b NULL 16 319 +val1c 12 16 219 + + +-- !query 6 +SELECT DISTINCT( t2a ), + t2b, + Count(t2c), + t2d, + t2h, + t2i +FROM t2 +WHERE t2a IN (SELECT t1a + FROM t1 + WHERE t1b = t2b) +GROUP BY t2a, + t2b, + t2c, + t2d, + t2h, + t2i +UNION +SELECT DISTINCT( t2a ), + t2b, + Count(t2c), + t2d, + t2h, + t2i +FROM t2 +WHERE t2a IN (SELECT t1a + FROM t1 + WHERE t2c = t1c) +GROUP BY t2a, + t2b, + t2c, + t2d, + t2h, + t2i +HAVING t2b IS NOT NULL +-- !query 6 schema +struct<t2a:string,t2b:smallint,count(t2c):bigint,t2d:bigint,t2h:timestamp,t2i:date> +-- !query 6 output +val1b 8 1 119 2015-05-04 01:01:00 2015-05-04 +val1b 8 1 19 2014-07-04 01:01:00 2014-07-04 +val1c 12 1 19 2014-08-04 01:01:00 2014-08-05 +val1c 12 1 219 2016-05-04 01:01:00 2016-05-04 + + +-- !query 7 +SELECT t2a, + t2b, + Count(t2c), + t2d, + t2h, + t2i +FROM t2 +WHERE t2a IN (SELECT DISTINCT(t1a) + FROM t1 + WHERE t1b = t2b) +GROUP BY t2a, + t2b, + t2c, + t2d, + t2h, + t2i + +UNION +SELECT DISTINCT( t2a ), + t2b, + Count(t2c), + t2d, + t2h, + t2i +FROM t2 +WHERE t2b IN (SELECT Max(t1b) + FROM t1 + WHERE t2c = t1c) +GROUP BY t2a, + t2b, + t2c, + t2d, + t2h, + t2i +HAVING t2b IS NOT NULL +UNION DISTINCT +SELECT t2a, + t2b, + t2c, + t2d, + t2h, + t2i +FROM t2 +WHERE t2d IN (SELECT min(t1d) + FROM t1 + WHERE t2c = t1c) +-- !query 7 schema +struct<t2a:string,t2b:smallint,count(t2c):bigint,t2d:bigint,t2h:timestamp,t2i:date> +-- !query 7 output +val1b 8 1 119 2015-05-04 01:01:00 2015-05-04 +val1b 8 1 19 2014-07-04 01:01:00 2014-07-04 +val1b 8 16 19 2014-07-04 01:01:00 2014-07-04 +val1b NULL 16 19 2014-05-04 01:01:00 NULL +val1c 12 16 19 2014-08-04 01:01:00 2014-08-05 + + +-- !query 8 +SELECT t2a, + t2b, + t2c, + t2d +FROM t2 +WHERE t2a IN (SELECT t1a + FROM t1 + WHERE t1b = t2b AND + t1d < t2d) +INTERSECT +SELECT t2a, + t2b, + t2c, + t2d +FROM t2 +WHERE t2b IN (SELECT Max(t1b) + FROM t1 + WHERE t2c = t1c) +EXCEPT +SELECT t2a, + t2b, + t2c, + t2d +FROM t2 +WHERE t2d IN (SELECT Min(t3d) + FROM t3 + WHERE t2c = t3c) +UNION ALL +SELECT t2a, + t2b, + t2c, + t2d +FROM t2 +WHERE t2c IN (SELECT Max(t1c) + FROM t1 + WHERE t1d = t2d) +-- !query 8 schema +struct<t2a:string,t2b:smallint,t2c:int,t2d:bigint> +-- !query 8 output +val1b 8 16 119 +val1b 8 16 19 +val1b NULL 16 19 +val1c 12 16 19 + + +-- !query 9 +SELECT DISTINCT(t1a), + t1b, + t1c, + t1d +FROM t1 +WHERE t1a IN (SELECT t3a + FROM (SELECT t2a t3a + FROM t2 + UNION ALL + SELECT t2a t3a + FROM t2) AS t3 + UNION + SELECT t2a + FROM (SELECT t2a + FROM t2 + WHERE t2b > 6 + UNION + SELECT t2a + FROM t2 + WHERE t2b > 6) AS t4 + UNION DISTINCT + SELECT t2a + FROM (SELECT t2a + FROM t2 + WHERE t2b > 6 + UNION DISTINCT + SELECT t1a + FROM t1 + WHERE t1b > 6) AS t5) +GROUP BY t1a, t1b, t1c, t1d +HAVING t1c IS NOT NULL AND t1b IS NOT NULL +ORDER BY t1c DESC +-- !query 9 schema +struct<t1a:string,t1b:smallint,t1c:int,t1d:bigint> +-- !query 9 output +val1c 8 16 19 +val1b 8 16 19 +val1a 16 12 21 +val1a 16 12 10 +val1a 6 8 10 + + +-- !query 10 +SELECT t1a, + t1b, + t1c +FROM t1 +WHERE t1b IN (SELECT t2b + FROM (SELECT t2b + FROM t2 + WHERE t2b > 6 + INTERSECT + SELECT t1b + FROM t1 + WHERE t1b > 6) AS t3 + WHERE t2b = t1b) +-- !query 10 schema +struct<t1a:string,t1b:smallint,t1c:int> +-- !query 10 output +val1b 8 16 +val1c 8 16 +val1d 10 NULL +val1e 10 NULL +val1e 10 NULL +val1e 10 NULL + + +-- !query 11 +SELECT t1a, + t1b, + t1c +FROM t1 +WHERE t1h IN (SELECT t2h + FROM (SELECT t2h + FROM t2 + EXCEPT + SELECT t3h + FROM t3) AS t3) +ORDER BY t1b DESC NULLs first, t1c DESC NULLs last +-- !query 11 schema +struct<t1a:string,t1b:smallint,t1c:int> +-- !query 11 output +val1d NULL 16 +val1a 16 12 +val1e 10 NULL +val1d 10 NULL +val1e 10 NULL +val1b 8 16 + + +-- !query 12 +SELECT t1a, + t1b, + t1c +FROM t1 +WHERE t1b IN + ( + SELECT t2b + FROM ( + SELECT t2b + FROM t2 + WHERE t2b > 6 + INTERSECT + SELECT t1b + FROM t1 + WHERE t1b > 6) AS t3) +UNION DISTINCT +SELECT t1a, + t1b, + t1c +FROM t1 +WHERE t1b IN + ( + SELECT t2b + FROM ( + SELECT t2b + FROM t2 + WHERE t2b > 6 + EXCEPT + SELECT t1b + FROM t1 + WHERE t1b > 6) AS t4 + WHERE t2b = t1b) +ORDER BY t1c DESC NULLS last +-- !query 12 schema +struct<t1a:string,t1b:smallint,t1c:int> +-- !query 12 output +val1c 8 16 +val1b 8 16 +val1e 10 NULL +val1d 10 NULL + + +-- !query 13 +SELECT * +FROM (SELECT * + FROM (SELECT * + FROM t2 + WHERE t2h IN (SELECT t1h + FROM t1 + WHERE t1a = t2a) + UNION DISTINCT + SELECT * + FROM t1 + WHERE t1h IN (SELECT t3h + FROM t3 + UNION + SELECT t1h + FROM t1) + UNION + SELECT * + FROM t3 + WHERE t3a IN (SELECT t2a + FROM t2 + UNION ALL + SELECT t1a + FROM t1 + WHERE t1b > 0) + INTERSECT + SELECT * + FROM T1 + WHERE t1b IN (SELECT t3b + FROM t3 + UNION DISTINCT + SELECT t2b + FROM t2 + ) + EXCEPT + SELECT * + FROM t2 + WHERE t2h IN (SELECT t1i + FROM t1)) t4 + WHERE t4.t2b IN (SELECT Min(t3b) + FROM t3 + WHERE t4.t2a = t3a)) +-- !query 13 schema +struct<t2a:string,t2b:smallint,t2c:int,t2d:bigint,t2e:float,t2f:double,t2g:decimal(2,-2),t2h:timestamp,t2i:date> +-- !query 13 output +val1b 8 16 19 17.0 25.0 2600 2014-05-04 01:01:00 2014-05-04 + + +-- !query 14 +SELECT t2a, + t2b, + t2c, + t2i +FROM (SELECT * + FROM t2 + WHERE t2a NOT IN (SELECT t1a + FROM t1 + UNION + SELECT t3a + FROM t3) + UNION ALL + SELECT * + FROM t2 + WHERE t2a NOT IN (SELECT t1a + FROM t1 + INTERSECT + SELECT t2a + FROM t2)) AS t3 +WHERE t3.t2a NOT IN (SELECT t1a + FROM t1 + INTERSECT + SELECT t2a + FROM t2) + AND t2c IS NOT NULL +ORDER BY t2a +-- !query 14 schema +struct<t2a:string,t2b:smallint,t2c:int,t2i:date> +-- !query 14 output +val2a 6 12 2014-04-04 +val2a 6 12 2014-04-04 + + +-- !query 15 +SELECT Count(DISTINCT(t1a)), + t1b, + t1c, + t1i +FROM t1 +WHERE t1b NOT IN + ( + SELECT t2b + FROM ( + SELECT t2b + FROM t2 + WHERE t2b NOT IN + ( + SELECT t1b + FROM t1) + UNION + SELECT t1b + FROM t1 + WHERE t1b NOT IN + ( + SELECT t3b + FROM t3) + UNION + distinct SELECT t3b + FROM t3 + WHERE t3b NOT IN + ( + SELECT t2b + FROM t2)) AS t3 + WHERE t2b = t1b) +GROUP BY t1a, + t1b, + t1c, + t1i +HAVING t1b NOT IN + ( + SELECT t2b + FROM t2 + WHERE t2c IS NULL + EXCEPT + SELECT t3b + FROM t3) +ORDER BY t1c DESC NULLS LAST +-- !query 15 schema +struct<count(DISTINCT t1a):bigint,t1b:smallint,t1c:int,t1i:date> +-- !query 15 output +1 8 16 2014-05-05 +1 8 16 2014-05-04 +1 16 12 2014-06-04 +1 16 12 2014-07-04 +1 6 8 2014-04-04 +1 10 NULL 2014-08-04 +1 10 NULL 2014-09-04 +1 10 NULL 2015-05-04 +1 10 NULL 2014-05-04 http://git-wip-us.apache.org/repos/asf/spark/blob/8487902a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-with-cte.sql.out ---------------------------------------------------------------------- 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 0000000..7d3943e --- /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 http://git-wip-us.apache.org/repos/asf/spark/blob/8487902a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/not-in-joins.sql.out ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/not-in-joins.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/not-in-joins.sql.out new file mode 100644 index 0000000..db01fa4 --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/not-in-joins.sql.out @@ -0,0 +1,229 @@ +-- Automatically generated by SQLQueryTestSuite +-- Number of queries: 9 + + +-- !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 + JOIN t3 +WHERE t1a NOT IN (SELECT t2a + FROM t2) + AND t1b = t3b +-- !query 3 schema +struct<t1a:string,t1b:smallint,t1c:int,t3a:string,t3b:smallint,t3c:int> +-- !query 3 output +val1a 6 8 val3a 6 12 +val1a 6 8 val3a 6 12 +val1a 6 8 val3a 6 12 +val1a 6 8 val3a 6 12 +val1d 10 NULL val1b 10 12 +val1d 10 NULL val1b 10 12 + + +-- !query 4 +SELECT t1a, + t1b, + t1c, + count(distinct(t3a)), + t3b, + t3c +FROM t1 +FULL OUTER JOIN t3 on t1b != t3b +RIGHT JOIN t2 on t1c = t2c +where t1a NOT IN + ( + SELECT t2a + FROM t2 + WHERE t2c NOT IN + ( + SELECT t1c + FROM t1 + WHERE t1a = t2a)) +AND t1b != t3b +AND t1d = t2d +GROUP BY t1a, t1b, t1c, t3a, t3b, t3c +HAVING count(distinct(t3a)) >= 1 +ORDER BY t1a +-- !query 4 schema +struct<t1a:string,t1b:smallint,t1c:int,count(DISTINCT t3a):bigint,t3b:smallint,t3c:int> +-- !query 4 output +val1c 8 16 1 10 12 +val1c 8 16 1 6 12 +val1c 8 16 1 17 16 + + +-- !query 5 +SELECT t1a, + t1b, + t1c, + t1d, + t1h +FROM t1 +WHERE t1a NOT IN + ( + SELECT t2a + FROM t2 + LEFT JOIN t3 on t2b = t3b + WHERE t1d = t2d + ) +AND t1d NOT IN + ( + SELECT t2d + FROM t2 + RIGHT JOIN t1 on t2e = t1e + WHERE t1a = t2a) +-- !query 5 schema +struct<t1a:string,t1b:smallint,t1c:int,t1d:bigint,t1h:timestamp> +-- !query 5 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 +val1d 10 NULL 12 2015-05-04 01:01:00 +val1d NULL 16 22 2014-06-04 01:01:00 +val1e 10 NULL 25 2014-08-04 01:01:00 + + +-- !query 6 +SELECT Count(DISTINCT( t1a )), + t1b, + t1c, + t1d +FROM t1 +WHERE t1a NOT IN (SELECT t2a + FROM t2 + JOIN t1 + WHERE t2b <> t1b) +GROUP BY t1b, + t1c, + t1d +HAVING t1d NOT IN (SELECT t2d + FROM t2 + WHERE t1d = t2d) +ORDER BY t1b DESC +-- !query 6 schema +struct<count(DISTINCT t1a):bigint,t1b:smallint,t1c:int,t1d:bigint> +-- !query 6 output +1 16 12 10 +1 16 12 21 +1 10 NULL 12 +1 6 8 10 +1 NULL 16 22 + + +-- !query 7 +SELECT COUNT(DISTINCT(t1a)), + t1b, + t1c, + t1d +FROM t1 +WHERE t1a NOT IN + ( + SELECT t2a + FROM t2 INNER + JOIN t1 ON t1a = t2a) +GROUP BY t1b, + t1c, + t1d +HAVING t1b < sum(t1c) +-- !query 7 schema +struct<count(DISTINCT t1a):bigint,t1b:smallint,t1c:int,t1d:bigint> +-- !query 7 output +1 6 8 10 + + +-- !query 8 +SELECT COUNT(DISTINCT(t1a)), + t1b, + t1c, + t1d +FROM t1 +WHERE t1a NOT IN + ( + SELECT t2a + FROM t2 INNER + JOIN t1 + ON t1a = t2a) +AND t1d NOT IN + ( + SELECT t2d + FROM t2 + INNER JOIN t3 + ON t2b = t3b ) +GROUP BY t1b, + t1c, + t1d +HAVING t1b < sum(t1c) +-- !query 8 schema +struct<count(DISTINCT t1a):bigint,t1b:smallint,t1c:int,t1d:bigint> +-- !query 8 output +1 6 8 10 --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org
