You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by li...@apache.org on 2017/02/16 08:02:20 UTC
spark git commit: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT
IN subquery 3rd batch
Repository: spark
Updated Branches:
refs/heads/master f041e55ee -> 3871d94a6
[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 <qy...@us.ibm.com>
Closes #16841 from kevinyu98/spark-18871-33.
Project: http://git-wip-us.apache.org/repos/asf/spark/repo
Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/3871d94a
Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/3871d94a
Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/3871d94a
Branch: refs/heads/master
Commit: 3871d94a695d47169720e877f77ff1e4bede43ee
Parents: f041e55
Author: Kevin Yu <qy...@us.ibm.com>
Authored: Thu Feb 16 00:02:15 2017 -0800
Committer: Xiao Li <ga...@gmail.com>
Committed: Thu Feb 16 00:02:15 2017 -0800
----------------------------------------------------------------------
.../inputs/subquery/in-subquery/in-having.sql | 152 ++++++++
.../inputs/subquery/in-subquery/in-joins.sql | 270 ++++++++++++++
.../in-subquery/in-multiple-columns.sql | 127 +++++++
.../subquery/in-subquery/in-having.sql.out | 217 ++++++++++++
.../subquery/in-subquery/in-joins.sql.out | 353 +++++++++++++++++++
.../in-subquery/in-multiple-columns.sql.out | 178 ++++++++++
6 files changed, 1297 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/spark/blob/3871d94a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-having.sql
----------------------------------------------------------------------
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 0000000..8f98ae1
--- /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);
+
http://git-wip-us.apache.org/repos/asf/spark/blob/3871d94a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-joins.sql
----------------------------------------------------------------------
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 0000000..b10c419
--- /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;
+
http://git-wip-us.apache.org/repos/asf/spark/blob/3871d94a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-multiple-columns.sql
----------------------------------------------------------------------
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 0000000..4643605
--- /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;
+
http://git-wip-us.apache.org/repos/asf/spark/blob/3871d94a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-having.sql.out
----------------------------------------------------------------------
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 0000000..b90ebf5
--- /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
http://git-wip-us.apache.org/repos/asf/spark/blob/3871d94a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-joins.sql.out
----------------------------------------------------------------------
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 0000000..7258bcf
--- /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
http://git-wip-us.apache.org/repos/asf/spark/blob/3871d94a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-multiple-columns.sql.out
----------------------------------------------------------------------
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 0000000..7a96c4b
--- /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
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org