You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by hv...@apache.org on 2017/02/15 16:31:03 UTC
spark git commit: [SPARK-18873][SQL][TEST] New test cases for scalar
subquery (part 2 of 2) - scalar subquery in predicate context
Repository: spark
Updated Branches:
refs/heads/master d22db6278 -> 5ad10c531
[SPARK-18873][SQL][TEST] New test cases for scalar subquery (part 2 of 2) - scalar subquery in predicate context
## What changes were proposed in this pull request?
This PR adds new test cases for scalar subquery in predicate context
## How was this patch tested?
The test result is compared with the result run from another SQL engine (in this case is IBM DB2). If the result are equivalent, we assume the result is correct.
Author: Nattavut Sutyanyong <ns...@gmail.com>
Closes #16798 from nsyca/18873-2.
Project: http://git-wip-us.apache.org/repos/asf/spark/repo
Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/5ad10c53
Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/5ad10c53
Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/5ad10c53
Branch: refs/heads/master
Commit: 5ad10c53102ac2f77c47bfd8c977e7beef55ea10
Parents: d22db62
Author: Nattavut Sutyanyong <ns...@gmail.com>
Authored: Wed Feb 15 17:30:55 2017 +0100
Committer: Herman van Hovell <hv...@databricks.com>
Committed: Wed Feb 15 17:30:55 2017 +0100
----------------------------------------------------------------------
.../sql-tests/inputs/scalar-subquery.sql | 20 -
.../scalar-subquery-predicate.sql | 271 ++++++++++++
.../sql-tests/results/scalar-subquery.sql.out | 46 --
.../scalar-subquery-predicate.sql.out | 430 +++++++++++++++++++
4 files changed, 701 insertions(+), 66 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/spark/blob/5ad10c53/sql/core/src/test/resources/sql-tests/inputs/scalar-subquery.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/sql-tests/inputs/scalar-subquery.sql b/sql/core/src/test/resources/sql-tests/inputs/scalar-subquery.sql
deleted file mode 100644
index 3acc9db..0000000
--- a/sql/core/src/test/resources/sql-tests/inputs/scalar-subquery.sql
+++ /dev/null
@@ -1,20 +0,0 @@
-CREATE OR REPLACE TEMPORARY VIEW p AS VALUES (1, 1) AS T(pk, pv);
-CREATE OR REPLACE TEMPORARY VIEW c AS VALUES (1, 1) AS T(ck, cv);
-
--- SPARK-18814.1: Simplified version of TPCDS-Q32
-SELECT pk, cv
-FROM p, c
-WHERE p.pk = c.ck
-AND c.cv = (SELECT avg(c1.cv)
- FROM c c1
- WHERE c1.ck = p.pk);
-
--- SPARK-18814.2: Adding stack of aggregates
-SELECT pk, cv
-FROM p, c
-WHERE p.pk = c.ck
-AND c.cv = (SELECT max(avg)
- FROM (SELECT c1.cv, avg(c1.cv) avg
- FROM c c1
- WHERE c1.ck = p.pk
- GROUP BY c1.cv));
http://git-wip-us.apache.org/repos/asf/spark/blob/5ad10c53/sql/core/src/test/resources/sql-tests/inputs/subquery/scalar-subquery/scalar-subquery-predicate.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/scalar-subquery/scalar-subquery-predicate.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/scalar-subquery/scalar-subquery-predicate.sql
new file mode 100644
index 0000000..fb0d07f
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/scalar-subquery/scalar-subquery-predicate.sql
@@ -0,0 +1,271 @@
+-- A test suite for scalar subquery in predicate context
+
+CREATE OR REPLACE TEMPORARY VIEW p AS VALUES (1, 1) AS T(pk, pv);
+CREATE OR REPLACE TEMPORARY VIEW c AS VALUES (1, 1) AS T(ck, cv);
+
+-- SPARK-18814.1: Simplified version of TPCDS-Q32
+SELECT pk, cv
+FROM p, c
+WHERE p.pk = c.ck
+AND c.cv = (SELECT avg(c1.cv)
+ FROM c c1
+ WHERE c1.ck = p.pk);
+
+-- SPARK-18814.2: Adding stack of aggregates
+SELECT pk, cv
+FROM p, c
+WHERE p.pk = c.ck
+AND c.cv = (SELECT max(avg)
+ FROM (SELECT c1.cv, avg(c1.cv) avg
+ FROM c c1
+ WHERE c1.ck = p.pk
+ GROUP BY c1.cv));
+
+create temporary view t1 as select * from values
+ ('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 00: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);
+
+-- Group 1: scalar subquery in predicate context
+-- no correlation
+-- TC 01.01
+SELECT t1a, t1b
+FROM t1
+WHERE t1c = (SELECT max(t2c)
+ FROM t2);
+
+-- TC 01.02
+SELECT t1a, t1d, t1f
+FROM t1
+WHERE t1c = (SELECT max(t2c)
+ FROM t2)
+AND t1b > (SELECT min(t3b)
+ FROM t3);
+
+-- TC 01.03
+SELECT t1a, t1h
+FROM t1
+WHERE t1c = (SELECT max(t2c)
+ FROM t2)
+OR t1b = (SELECT min(t3b)
+ FROM t3
+ WHERE t3b > 10);
+
+-- TC 01.04
+-- scalar subquery over outer join
+SELECT t1a, t1b, t2d
+FROM t1 LEFT JOIN t2
+ ON t1a = t2a
+WHERE t1b = (SELECT min(t3b)
+ FROM t3);
+
+-- TC 01.05
+-- test casting
+SELECT t1a, t1b, t1g
+FROM t1
+WHERE t1c + 5 = (SELECT max(t2e)
+ FROM t2);
+
+-- TC 01.06
+-- test casting
+SELECT t1a, t1h
+FROM t1
+WHERE date(t1h) = (SELECT min(t2i)
+ FROM t2);
+
+-- TC 01.07
+-- same table, expressions in scalar subquery
+SELECT t2d, t1a
+FROM t1, t2
+WHERE t1b = t2b
+AND t2c + 1 = (SELECT max(t2c) + 1
+ FROM t2, t1
+ WHERE t2b = t1b);
+
+-- TC 01.08
+-- same table
+SELECT DISTINCT t2a, max_t1g
+FROM t2, (SELECT max(t1g) max_t1g, t1a
+ FROM t1
+ GROUP BY t1a) t1
+WHERE t2a = t1a
+AND max_t1g = (SELECT max(t1g)
+ FROM t1);
+
+-- TC 01.09
+-- more than one scalar subquery
+SELECT t3b, t3c
+FROM t3
+WHERE (SELECT max(t3c)
+ FROM t3
+ WHERE t3b > 10) >=
+ (SELECT min(t3b)
+ FROM t3
+ WHERE t3c > 0)
+AND (t3b is null or t3c is null);
+
+-- Group 2: scalar subquery in predicate context
+-- with correlation
+-- TC 02.01
+SELECT t1a
+FROM t1
+WHERE t1a < (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c);
+
+-- TC 02.02
+SELECT t1a, t1c
+FROM t1
+WHERE (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c) IS NULL;
+
+-- TC 02.03
+SELECT t1a
+FROM t1
+WHERE t1a = (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c
+ HAVING count(*) >= 0)
+OR t1i > '2014-12-31';
+
+-- TC 02.04
+-- t1 on the right of an outer join
+-- can be reduced to inner join
+SELECT count(t1a)
+FROM t1 RIGHT JOIN t2
+ON t1d = t2d
+WHERE t1a < (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c);
+
+-- TC 02.05
+SELECT t1a
+FROM t1
+WHERE t1b <= (SELECT max(t2b)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+AND t1b >= (SELECT min(t2b)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c);
+
+-- TC 02.06
+-- set op
+SELECT t1a
+FROM t1
+WHERE t1a <= (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+INTERSECT
+SELECT t1a
+FROM t1
+WHERE t1a >= (SELECT min(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c);
+
+-- TC 02.07.01
+-- set op
+SELECT t1a
+FROM t1
+WHERE t1a <= (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+UNION ALL
+SELECT t1a
+FROM t1
+WHERE t1a >= (SELECT min(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c);
+
+-- TC 02.07.02
+-- set op
+SELECT t1a
+FROM t1
+WHERE t1a <= (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+UNION DISTINCT
+SELECT t1a
+FROM t1
+WHERE t1a >= (SELECT min(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c);
+
+-- TC 02.08
+-- set op
+SELECT t1a
+FROM t1
+WHERE t1a <= (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+MINUS
+SELECT t1a
+FROM t1
+WHERE t1a >= (SELECT min(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c);
+
+-- TC 02.09
+-- in HAVING clause
+SELECT t1a
+FROM t1
+GROUP BY t1a, t1c
+HAVING max(t1b) <= (SELECT max(t2b)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c);
http://git-wip-us.apache.org/repos/asf/spark/blob/5ad10c53/sql/core/src/test/resources/sql-tests/results/scalar-subquery.sql.out
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/sql-tests/results/scalar-subquery.sql.out b/sql/core/src/test/resources/sql-tests/results/scalar-subquery.sql.out
deleted file mode 100644
index c249329..0000000
--- a/sql/core/src/test/resources/sql-tests/results/scalar-subquery.sql.out
+++ /dev/null
@@ -1,46 +0,0 @@
--- Automatically generated by SQLQueryTestSuite
--- Number of queries: 4
-
-
--- !query 0
-CREATE OR REPLACE TEMPORARY VIEW p AS VALUES (1, 1) AS T(pk, pv)
--- !query 0 schema
-struct<>
--- !query 0 output
-
-
-
--- !query 1
-CREATE OR REPLACE TEMPORARY VIEW c AS VALUES (1, 1) AS T(ck, cv)
--- !query 1 schema
-struct<>
--- !query 1 output
-
-
-
--- !query 2
-SELECT pk, cv
-FROM p, c
-WHERE p.pk = c.ck
-AND c.cv = (SELECT avg(c1.cv)
- FROM c c1
- WHERE c1.ck = p.pk)
--- !query 2 schema
-struct<pk:int,cv:int>
--- !query 2 output
-1 1
-
-
--- !query 3
-SELECT pk, cv
-FROM p, c
-WHERE p.pk = c.ck
-AND c.cv = (SELECT max(avg)
- FROM (SELECT c1.cv, avg(c1.cv) avg
- FROM c c1
- WHERE c1.ck = p.pk
- GROUP BY c1.cv))
--- !query 3 schema
-struct<pk:int,cv:int>
--- !query 3 output
-1 1
http://git-wip-us.apache.org/repos/asf/spark/blob/5ad10c53/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-predicate.sql.out
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-predicate.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-predicate.sql.out
new file mode 100644
index 0000000..8b29300
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-predicate.sql.out
@@ -0,0 +1,430 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 26
+
+
+-- !query 0
+CREATE OR REPLACE TEMPORARY VIEW p AS VALUES (1, 1) AS T(pk, pv)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+CREATE OR REPLACE TEMPORARY VIEW c AS VALUES (1, 1) AS T(ck, cv)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+SELECT pk, cv
+FROM p, c
+WHERE p.pk = c.ck
+AND c.cv = (SELECT avg(c1.cv)
+ FROM c c1
+ WHERE c1.ck = p.pk)
+-- !query 2 schema
+struct<pk:int,cv:int>
+-- !query 2 output
+1 1
+
+
+-- !query 3
+SELECT pk, cv
+FROM p, c
+WHERE p.pk = c.ck
+AND c.cv = (SELECT max(avg)
+ FROM (SELECT c1.cv, avg(c1.cv) avg
+ FROM c c1
+ WHERE c1.ck = p.pk
+ GROUP BY c1.cv))
+-- !query 3 schema
+struct<pk:int,cv:int>
+-- !query 3 output
+1 1
+
+
+-- !query 4
+create temporary view t1 as select * from values
+ ('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 00: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 4 schema
+struct<>
+-- !query 4 output
+
+
+
+-- !query 5
+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 5 schema
+struct<>
+-- !query 5 output
+
+
+
+-- !query 6
+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 6 schema
+struct<>
+-- !query 6 output
+
+
+
+-- !query 7
+SELECT t1a, t1b
+FROM t1
+WHERE t1c = (SELECT max(t2c)
+ FROM t2)
+-- !query 7 schema
+struct<t1a:string,t1b:smallint>
+-- !query 7 output
+val1b 8
+val1c 8
+val1d NULL
+val1d NULL
+
+
+-- !query 8
+SELECT t1a, t1d, t1f
+FROM t1
+WHERE t1c = (SELECT max(t2c)
+ FROM t2)
+AND t1b > (SELECT min(t3b)
+ FROM t3)
+-- !query 8 schema
+struct<t1a:string,t1d:bigint,t1f:double>
+-- !query 8 output
+val1b 19 25.0
+val1c 19 25.0
+
+
+-- !query 9
+SELECT t1a, t1h
+FROM t1
+WHERE t1c = (SELECT max(t2c)
+ FROM t2)
+OR t1b = (SELECT min(t3b)
+ FROM t3
+ WHERE t3b > 10)
+-- !query 9 schema
+struct<t1a:string,t1h:timestamp>
+-- !query 9 output
+val1b 2014-05-04 01:01:00
+val1c 2014-05-04 01:02:00.001
+val1d 2014-06-04 01:01:00
+val1d 2014-07-04 01:02:00.001
+
+
+-- !query 10
+SELECT t1a, t1b, t2d
+FROM t1 LEFT JOIN t2
+ ON t1a = t2a
+WHERE t1b = (SELECT min(t3b)
+ FROM t3)
+-- !query 10 schema
+struct<t1a:string,t1b:smallint,t2d:bigint>
+-- !query 10 output
+val1a 6 NULL
+val1a 6 NULL
+
+
+-- !query 11
+SELECT t1a, t1b, t1g
+FROM t1
+WHERE t1c + 5 = (SELECT max(t2e)
+ FROM t2)
+-- !query 11 schema
+struct<t1a:string,t1b:smallint,t1g:decimal(2,-2)>
+-- !query 11 output
+val1a 16 2000
+val1a 16 2000
+
+
+-- !query 12
+SELECT t1a, t1h
+FROM t1
+WHERE date(t1h) = (SELECT min(t2i)
+ FROM t2)
+-- !query 12 schema
+struct<t1a:string,t1h:timestamp>
+-- !query 12 output
+val1a 2014-04-04 00:00:00
+val1a 2014-04-04 01:02:00.001
+
+
+-- !query 13
+SELECT t2d, t1a
+FROM t1, t2
+WHERE t1b = t2b
+AND t2c + 1 = (SELECT max(t2c) + 1
+ FROM t2, t1
+ WHERE t2b = t1b)
+-- !query 13 schema
+struct<t2d:bigint,t1a:string>
+-- !query 13 output
+119 val1b
+119 val1c
+19 val1b
+19 val1c
+
+
+-- !query 14
+SELECT DISTINCT t2a, max_t1g
+FROM t2, (SELECT max(t1g) max_t1g, t1a
+ FROM t1
+ GROUP BY t1a) t1
+WHERE t2a = t1a
+AND max_t1g = (SELECT max(t1g)
+ FROM t1)
+-- !query 14 schema
+struct<t2a:string,max_t1g:decimal(2,-2)>
+-- !query 14 output
+val1b 2600
+val1c 2600
+val1e 2600
+
+
+-- !query 15
+SELECT t3b, t3c
+FROM t3
+WHERE (SELECT max(t3c)
+ FROM t3
+ WHERE t3b > 10) >=
+ (SELECT min(t3b)
+ FROM t3
+ WHERE t3c > 0)
+AND (t3b is null or t3c is null)
+-- !query 15 schema
+struct<t3b:smallint,t3c:int>
+-- !query 15 output
+8 NULL
+8 NULL
+NULL 16
+NULL 16
+
+
+-- !query 16
+SELECT t1a
+FROM t1
+WHERE t1a < (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+-- !query 16 schema
+struct<t1a:string>
+-- !query 16 output
+val1a
+val1a
+val1b
+
+
+-- !query 17
+SELECT t1a, t1c
+FROM t1
+WHERE (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c) IS NULL
+-- !query 17 schema
+struct<t1a:string,t1c:int>
+-- !query 17 output
+val1a 8
+val1a 8
+val1d NULL
+val1e NULL
+val1e NULL
+val1e NULL
+
+
+-- !query 18
+SELECT t1a
+FROM t1
+WHERE t1a = (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c
+ HAVING count(*) >= 0)
+OR t1i > '2014-12-31'
+-- !query 18 schema
+struct<t1a:string>
+-- !query 18 output
+val1c
+val1d
+
+
+-- !query 19
+SELECT count(t1a)
+FROM t1 RIGHT JOIN t2
+ON t1d = t2d
+WHERE t1a < (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+-- !query 19 schema
+struct<count(t1a):bigint>
+-- !query 19 output
+7
+
+
+-- !query 20
+SELECT t1a
+FROM t1
+WHERE t1b <= (SELECT max(t2b)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+AND t1b >= (SELECT min(t2b)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+-- !query 20 schema
+struct<t1a:string>
+-- !query 20 output
+val1b
+val1c
+
+
+-- !query 21
+SELECT t1a
+FROM t1
+WHERE t1a <= (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+INTERSECT
+SELECT t1a
+FROM t1
+WHERE t1a >= (SELECT min(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+-- !query 21 schema
+struct<t1a:string>
+-- !query 21 output
+val1b
+val1c
+
+
+-- !query 22
+SELECT t1a
+FROM t1
+WHERE t1a <= (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+UNION ALL
+SELECT t1a
+FROM t1
+WHERE t1a >= (SELECT min(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+-- !query 22 schema
+struct<t1a:string>
+-- !query 22 output
+val1a
+val1a
+val1b
+val1b
+val1c
+val1c
+val1d
+val1d
+
+
+-- !query 23
+SELECT t1a
+FROM t1
+WHERE t1a <= (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+UNION DISTINCT
+SELECT t1a
+FROM t1
+WHERE t1a >= (SELECT min(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+-- !query 23 schema
+struct<t1a:string>
+-- !query 23 output
+val1a
+val1b
+val1c
+val1d
+
+
+-- !query 24
+SELECT t1a
+FROM t1
+WHERE t1a <= (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+MINUS
+SELECT t1a
+FROM t1
+WHERE t1a >= (SELECT min(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+-- !query 24 schema
+struct<t1a:string>
+-- !query 24 output
+val1a
+
+
+-- !query 25
+SELECT t1a
+FROM t1
+GROUP BY t1a, t1c
+HAVING max(t1b) <= (SELECT max(t2b)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+-- !query 25 schema
+struct<t1a:string>
+-- !query 25 output
+val1b
+val1c
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org