You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by nsyca <gi...@git.apache.org> on 2017/02/04 01:16:48 UTC
[GitHub] spark pull request #16798: [SPARK-18873][SQL][TEST] New test cases for scala...
GitHub user nsyca opened a pull request:
https://github.com/apache/spark/pull/16798
[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.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/nsyca/spark 18873-2
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/spark/pull/16798.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #16798
----
commit b98865127a39bde885f9b1680cfe608629d59d51
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-07-29T21:43:56Z
[SPARK-16804][SQL] Correlated subqueries containing LIMIT return incorrect results
## What changes were proposed in this pull request?
This patch fixes the incorrect results in the rule ResolveSubquery in Catalyst's Analysis phase.
## How was this patch tested?
./dev/run-tests
a new unit test on the problematic pattern.
commit 069ed8f8e5f14dca7a15701945d42fc27fe82f3c
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-07-29T21:50:02Z
[SPARK-16804][SQL] Correlated subqueries containing LIMIT return incorrect results
## What changes were proposed in this pull request?
This patch fixes the incorrect results in the rule ResolveSubquery in Catalyst's Analysis phase.
## How was this patch tested?
./dev/run-tests
a new unit test on the problematic pattern.
commit edca333c081e6d4e53a91b496fba4a3ef4ee89ac
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-07-30T00:28:15Z
New positive test cases
commit 64184fdb77c1a305bb2932e82582da28bb4c0e53
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-01T13:20:09Z
Fix unit test case failure
commit 29f82b05c9e40e7934397257c674b260a8e8a996
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-05T17:42:01Z
blocking TABLESAMPLE
commit ac43ab47907a1ccd6d22f920415fbb4de93d4720
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-05T21:10:19Z
Fixing code styling
commit 631d396031e8bf627eb1f4872a4d3a17c144536c
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-07T18:39:44Z
Correcting Scala test style
commit 7eb9b2dbba3633a1958e38e0019e3ce816300514
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-08T02:31:09Z
One (last) attempt to correct the Scala style tests
commit 1387cf51541408ac20048064fa5e559836af932c
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-12T20:11:50Z
Merge remote-tracking branch 'upstream/master'
commit 3faa2d5edc030495f8b870d2c017cb714c17b6a7
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-12-14T16:35:52Z
Merge remote-tracking branch 'upstream/master'
commit a30863457ef49f99aff001b1987da75093c20f86
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-12-30T17:18:18Z
Merge remote-tracking branch 'upstream/master'
commit f1524b99aff70e688e4763db7898da53286a321e
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2017-01-03T22:08:03Z
Merge remote-tracking branch 'upstream/master'
commit 5c36dce4df0051cdf1957ac448b354db0ee22e2d
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2017-01-13T01:05:45Z
Merge remote-tracking branch 'upstream/master'
commit 862b2b8a96a76331bde6225805ae207c9e99bb28
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2017-01-25T01:38:55Z
Merge remote-tracking branch 'upstream/master'
commit 211e325c14a945349ca42d5237c7a8bfa3efffae
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2017-01-25T21:38:09Z
Merge remote-tracking branch 'upstream/master'
commit 05119efa1a6283ec6a23b09003218ee12a920fd3
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2017-02-03T20:03:44Z
Merge remote-tracking branch 'upstream/master'
commit 092f2a585fa5d58054cf59a204acc728dffc66b4
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2017-02-04T01:11:59Z
new test file
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16798: [SPARK-18873][SQL][TEST] New test cases for scala...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on a diff in the pull request:
https://github.com/apache/spark/pull/16798#discussion_r99480006
--- Diff: sql/core/src/test/resources/sql-tests/inputs/subquery/scalar-subquery/scalar-subquery-predicate.sql ---
@@ -0,0 +1,255 @@
+-- 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)
--- End diff --
Nit. I like this indentation.
For the other examples, `AND` seems to be aligned with `t1b` at line 190.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16798
**[Test build #72416 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72416/testReport)** for PR 16798 at commit [`044d6a4`](https://github.com/apache/spark/commit/044d6a4927f77bb50631d74830a7a999a6d6fd26).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/16798
@dilipbiswal Could you please cross-check the results from both sources?
@gatorsmile, @hvanhovell Could you please review?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/16798
Below are a modified version of the test cases to run on DB2 and the result from DB2, as a second source to compare to the result from Spark.
[Modified test file to run on DB2](https://github.com/apache/spark/files/751915/scalar-subquery-predicate.sql.db2.txt)
[Result from DB2](https://github.com/apache/spark/files/751918/scalar-subquery-predicate.sql.db2.out.txt)
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16798
Merged build finished. Test PASSed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16798: [SPARK-18873][SQL][TEST] New test cases for scala...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/16798#discussion_r99453763
--- Diff: sql/core/src/test/resources/sql-tests/inputs/scalar-subquery.sql ---
@@ -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));
--- End diff --
Merged the test cases with the new test cases and placed under the directory "scalar-subquery".
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16798
Merged build finished. Test PASSed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16798: [SPARK-18873][SQL][TEST] New test cases for scala...
Posted by dilipbiswal <gi...@git.apache.org>.
Github user dilipbiswal commented on a diff in the pull request:
https://github.com/apache/spark/pull/16798#discussion_r99454377
--- Diff: sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-predicate.sql.out ---
@@ -0,0 +1,407 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 25
+
+
+-- !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)
+MINUS
+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
+
+
+-- !query 24
+SELECT t1a
+FROM t1
+GROUP BY t1a, t1c
+HAVING max(t1b) <= (SELECT max(t2b)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+-- !query 24 schema
+struct<t1a:string>
+-- !query 24 output
+val1b
+val1c
--- End diff --
I have compared the result set matched with the result from DB2.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16798: [SPARK-18873][SQL][TEST] New test cases for scala...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on a diff in the pull request:
https://github.com/apache/spark/pull/16798#discussion_r99480025
--- Diff: sql/core/src/test/resources/sql-tests/inputs/subquery/scalar-subquery/scalar-subquery-predicate.sql ---
@@ -0,0 +1,255 @@
+-- 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
+-- set op
+SELECT t1a
+FROM t1
+WHERE t1a <= (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+UNION ALL
--- End diff --
Can we have `UNION` here too?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16798
Merged build finished. Test FAILed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16798
**[Test build #72356 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72356/testReport)** for PR 16798 at commit [`092f2a5`](https://github.com/apache/spark/commit/092f2a585fa5d58054cf59a204acc728dffc66b4).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16798: [SPARK-18873][SQL][TEST] New test cases for scala...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/16798#discussion_r99499561
--- Diff: sql/core/src/test/resources/sql-tests/inputs/subquery/scalar-subquery/scalar-subquery-predicate.sql ---
@@ -0,0 +1,255 @@
+-- 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
+-- set op
+SELECT t1a
+FROM t1
+WHERE t1a <= (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+UNION ALL
--- End diff --
@dongjoon-hyun Thank you for your comment. I have added another test case using UNION DISTINCT.
I would appreciate if you could share your insight on what you think the UNION test case will process differently from the UNION ALL test case with respect to the testing of scalar subquery.
Note that the correctness of the result of UNION DISTINCT can be inferred from applying the "uniqueness" operator on the result of the existing UNION test case.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16798
**[Test build #72337 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72337/testReport)** for PR 16798 at commit [`092f2a5`](https://github.com/apache/spark/commit/092f2a585fa5d58054cf59a204acc728dffc66b4).
* This patch **fails SparkR unit tests**.
* This patch merges cleanly.
* This patch adds no public classes.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16798
**[Test build #72416 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72416/testReport)** for PR 16798 at commit [`044d6a4`](https://github.com/apache/spark/commit/044d6a4927f77bb50631d74830a7a999a6d6fd26).
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16798
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/72356/
Test PASSed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/16798
LGTM - merging to master
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16798
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/72416/
Test PASSed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16798
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/72337/
Test FAILed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16798: [SPARK-18873][SQL][TEST] New test cases for scala...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/spark/pull/16798
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16798
**[Test build #72337 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72337/testReport)** for PR 16798 at commit [`092f2a5`](https://github.com/apache/spark/commit/092f2a585fa5d58054cf59a204acc728dffc66b4).
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the issue:
https://github.com/apache/spark/pull/16798
retest this please
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16798: [SPARK-18873][SQL][TEST] New test cases for scalar subqu...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16798
**[Test build #72356 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72356/testReport)** for PR 16798 at commit [`092f2a5`](https://github.com/apache/spark/commit/092f2a585fa5d58054cf59a204acc728dffc66b4).
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org