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/08 04:36:42 UTC
spark git commit: [SPARK-18873][SQL][TEST] New test cases for scalar
subquery (part 1 of 2) - scalar subquery in SELECT clause
Repository: spark
Updated Branches:
refs/heads/master d4cd97571 -> 266c1e730
[SPARK-18873][SQL][TEST] New test cases for scalar subquery (part 1 of 2) - scalar subquery in SELECT clause
## What changes were proposed in this pull request?
This PR adds new test cases for scalar subquery in SELECT clause.
## 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 #16712 from nsyca/18873.
Project: http://git-wip-us.apache.org/repos/asf/spark/repo
Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/266c1e73
Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/266c1e73
Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/266c1e73
Branch: refs/heads/master
Commit: 266c1e730975de59c534fbb7e52aafda60c562fa
Parents: d4cd975
Author: Nattavut Sutyanyong <ns...@gmail.com>
Authored: Tue Feb 7 23:36:34 2017 -0500
Committer: gatorsmile <ga...@gmail.com>
Committed: Tue Feb 7 23:36:34 2017 -0500
----------------------------------------------------------------------
.../scalar-subquery/scalar-subquery-select.sql | 130 ++++++++++++
.../scalar-subquery-select.sql.out | 198 +++++++++++++++++++
2 files changed, 328 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/spark/blob/266c1e73/sql/core/src/test/resources/sql-tests/inputs/subquery/scalar-subquery/scalar-subquery-select.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/scalar-subquery/scalar-subquery-select.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/scalar-subquery/scalar-subquery-select.sql
new file mode 100644
index 0000000..eabbd0a
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/scalar-subquery/scalar-subquery-select.sql
@@ -0,0 +1,130 @@
+-- A test suite for scalar subquery in SELECT clause
+
+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 SELECT clause
+-- no correlation
+-- TC 01.01
+-- more than one scalar subquery
+SELECT (SELECT min(t3d) FROM t3) min_t3d,
+ (SELECT max(t2h) FROM t2) max_t2h
+FROM t1
+WHERE t1a = 'val1c';
+
+-- TC 01.02
+-- scalar subquery in an IN subquery
+SELECT t1a, count(*)
+FROM t1
+WHERE t1c IN (SELECT (SELECT min(t3c) FROM t3)
+ FROM t2
+ GROUP BY t2g
+ HAVING count(*) > 1)
+GROUP BY t1a;
+
+-- TC 01.03
+-- under a set op
+SELECT (SELECT min(t3d) FROM t3) min_t3d,
+ null
+FROM t1
+WHERE t1a = 'val1c'
+UNION
+SELECT null,
+ (SELECT max(t2h) FROM t2) max_t2h
+FROM t1
+WHERE t1a = 'val1c';
+
+-- TC 01.04
+SELECT (SELECT min(t3c) FROM t3) min_t3d
+FROM t1
+WHERE t1a = 'val1a'
+INTERSECT
+SELECT (SELECT min(t2c) FROM t2) min_t2d
+FROM t1
+WHERE t1a = 'val1d';
+
+-- TC 01.05
+SELECT q1.t1a, q2.t2a, q1.min_t3d, q2.avg_t3d
+FROM (SELECT t1a, (SELECT min(t3d) FROM t3) min_t3d
+ FROM t1
+ WHERE t1a IN ('val1e', 'val1c')) q1
+ FULL OUTER JOIN
+ (SELECT t2a, (SELECT avg(t3d) FROM t3) avg_t3d
+ FROM t2
+ WHERE t2a IN ('val1c', 'val2a')) q2
+ON q1.t1a = q2.t2a
+AND q1.min_t3d < q2.avg_t3d;
+
+-- Group 2: scalar subquery in SELECT clause
+-- with correlation
+-- TC 02.01
+SELECT (SELECT min(t3d) FROM t3 WHERE t3.t3a = t1.t1a) min_t3d,
+ (SELECT max(t2h) FROM t2 WHERE t2.t2a = t1.t1a) max_t2h
+FROM t1
+WHERE t1a = 'val1b';
+
+-- TC 02.02
+SELECT (SELECT min(t3d) FROM t3 WHERE t3a = t1a) min_t3d
+FROM t1
+WHERE t1a = 'val1b'
+MINUS
+SELECT (SELECT min(t3d) FROM t3) abs_min_t3d
+FROM t1
+WHERE t1a = 'val1b';
+
+-- TC 02.03
+SELECT t1a, t1b
+FROM t1
+WHERE NOT EXISTS (SELECT (SELECT max(t2b)
+ FROM t2 LEFT JOIN t1
+ ON t2a = t1a
+ WHERE t2c = t3c) dummy
+ FROM t3
+ WHERE t3b < (SELECT max(t2b)
+ FROM t2 LEFT JOIN t1
+ ON t2a = t1a
+ WHERE t2c = t3c)
+ AND t3a = t1a);
http://git-wip-us.apache.org/repos/asf/spark/blob/266c1e73/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-select.sql.out
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-select.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-select.sql.out
new file mode 100644
index 0000000..807bb47
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-select.sql.out
@@ -0,0 +1,198 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 11
+
+
+-- !query 0
+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 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 (SELECT min(t3d) FROM t3) min_t3d,
+ (SELECT max(t2h) FROM t2) max_t2h
+FROM t1
+WHERE t1a = 'val1c'
+-- !query 3 schema
+struct<min_t3d:bigint,max_t2h:timestamp>
+-- !query 3 output
+10 2017-05-04 01:01:00
+
+
+-- !query 4
+SELECT t1a, count(*)
+FROM t1
+WHERE t1c IN (SELECT (SELECT min(t3c) FROM t3)
+ FROM t2
+ GROUP BY t2g
+ HAVING count(*) > 1)
+GROUP BY t1a
+-- !query 4 schema
+struct<t1a:string,count(1):bigint>
+-- !query 4 output
+val1a 2
+
+
+-- !query 5
+SELECT (SELECT min(t3d) FROM t3) min_t3d,
+ null
+FROM t1
+WHERE t1a = 'val1c'
+UNION
+SELECT null,
+ (SELECT max(t2h) FROM t2) max_t2h
+FROM t1
+WHERE t1a = 'val1c'
+-- !query 5 schema
+struct<min_t3d:bigint,NULL:timestamp>
+-- !query 5 output
+10 NULL
+NULL 2017-05-04 01:01:00
+
+
+-- !query 6
+SELECT (SELECT min(t3c) FROM t3) min_t3d
+FROM t1
+WHERE t1a = 'val1a'
+INTERSECT
+SELECT (SELECT min(t2c) FROM t2) min_t2d
+FROM t1
+WHERE t1a = 'val1d'
+-- !query 6 schema
+struct<min_t3d:int>
+-- !query 6 output
+12
+
+
+-- !query 7
+SELECT q1.t1a, q2.t2a, q1.min_t3d, q2.avg_t3d
+FROM (SELECT t1a, (SELECT min(t3d) FROM t3) min_t3d
+ FROM t1
+ WHERE t1a IN ('val1e', 'val1c')) q1
+ FULL OUTER JOIN
+ (SELECT t2a, (SELECT avg(t3d) FROM t3) avg_t3d
+ FROM t2
+ WHERE t2a IN ('val1c', 'val2a')) q2
+ON q1.t1a = q2.t2a
+AND q1.min_t3d < q2.avg_t3d
+-- !query 7 schema
+struct<t1a:string,t2a:string,min_t3d:bigint,avg_t3d:double>
+-- !query 7 output
+NULL val2a NULL 200.83333333333334
+val1c val1c 10 200.83333333333334
+val1c val1c 10 200.83333333333334
+val1e NULL 10 NULL
+val1e NULL 10 NULL
+val1e NULL 10 NULL
+
+
+-- !query 8
+SELECT (SELECT min(t3d) FROM t3 WHERE t3.t3a = t1.t1a) min_t3d,
+ (SELECT max(t2h) FROM t2 WHERE t2.t2a = t1.t1a) max_t2h
+FROM t1
+WHERE t1a = 'val1b'
+-- !query 8 schema
+struct<min_t3d:bigint,max_t2h:timestamp>
+-- !query 8 output
+19 2017-05-04 01:01:00
+
+
+-- !query 9
+SELECT (SELECT min(t3d) FROM t3 WHERE t3a = t1a) min_t3d
+FROM t1
+WHERE t1a = 'val1b'
+MINUS
+SELECT (SELECT min(t3d) FROM t3) abs_min_t3d
+FROM t1
+WHERE t1a = 'val1b'
+-- !query 9 schema
+struct<min_t3d:bigint>
+-- !query 9 output
+19
+
+
+-- !query 10
+SELECT t1a, t1b
+FROM t1
+WHERE NOT EXISTS (SELECT (SELECT max(t2b)
+ FROM t2 LEFT JOIN t1
+ ON t2a = t1a
+ WHERE t2c = t3c) dummy
+ FROM t3
+ WHERE t3b < (SELECT max(t2b)
+ FROM t2 LEFT JOIN t1
+ ON t2a = t1a
+ WHERE t2c = t3c)
+ AND t3a = t1a)
+-- !query 10 schema
+struct<t1a:string,t1b:smallint>
+-- !query 10 output
+val1a 16
+val1a 16
+val1a 6
+val1a 6
+val1c 8
+val1d 10
+val1d NULL
+val1d NULL
+val1e 10
+val1e 10
+val1e 10
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org