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