You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by kevinyu98 <gi...@git.apache.org> on 2017/02/07 19:34:55 UTC
[GitHub] spark pull request #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/N...
GitHub user kevinyu98 opened a pull request:
https://github.com/apache/spark/pull/16841
[SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN subquery 3ird batch
## What changes were proposed in this pull request?
This is 3ird batch of test case for IN/NOT IN subquery. In this PR, it has these test files:
`in-having.sql`
`in-joins.sql`
`in-multiple-columns.sql`
These are the queries and results from running on DB2.
[in-having DB2 version](https://github.com/apache/spark/files/758763/in-having.sql.db2.txt)
[output of in-having](https://github.com/apache/spark/files/758766/in-having.sql.db2.out.txt)
[in-joins DB2 version](https://github.com/apache/spark/files/758772/in-joins.sql.db2.txt)
[output of in-joins.](https://github.com/apache/spark/files/758773/in-joins.sql.db2.out.txt)
[in-multiple-columns DB2 version](https://github.com/apache/spark/files/758777/in-multiple-columns.sql.db2.txt)
[output of in-multiple-columns](https://github.com/apache/spark/files/758779/in-multiple-columns.sql.db2.out.txt)
## How was this patch tested?
This pr is adding new test cases. We compare the result from spark with the result from another RDBMS(We used DB2 LUW). If the results are the same, we assume the result is correct.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/kevinyu98/spark spark-18871-33
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/spark/pull/16841.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 #16841
----
commit 3b44c5978bd44db986621d3e8511e9165b66926b
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-20T18:06:30Z
adding testcase
commit 18b4a31c687b264b50aa5f5a74455956911f738a
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-22T21:48:00Z
Merge remote-tracking branch 'upstream/master'
commit 4f4d1c8f2801b1e662304ab2b33351173e71b427
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-23T16:50:19Z
Merge remote-tracking branch 'upstream/master'
get latest code from upstream
commit f5f0cbed1eb5754c04c36933b374c3b3d2ae4f4e
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-23T22:20:53Z
Merge remote-tracking branch 'upstream/master'
adding trim characters support
commit d8b2edbd13ee9a4f057bca7dcb0c0940e8e867b8
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-25T20:24:33Z
Merge remote-tracking branch 'upstream/master'
get latest code for pr12646
commit 196b6c66b0d55232f427c860c0e7c6876c216a67
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-25T23:45:57Z
Merge remote-tracking branch 'upstream/master'
merge latest code
commit f37a01e005f3e27ae2be056462d6eb6730933ba5
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-27T14:15:06Z
Merge remote-tracking branch 'upstream/master'
merge upstream/master
commit bb5b01fd3abeea1b03315eccf26762fcc23f80c0
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-30T23:49:31Z
Merge remote-tracking branch 'upstream/master'
commit bde5820a181cf84e0879038ad8c4cebac63c1e24
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-04T03:52:31Z
Merge remote-tracking branch 'upstream/master'
commit 5f7cd96d495f065cd04e8e4cc58461843e45bc8d
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-10T21:14:50Z
Merge remote-tracking branch 'upstream/master'
commit 893a49af0bfd153ccb59ba50b63a232660e0eada
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-13T18:20:39Z
Merge remote-tracking branch 'upstream/master'
commit 4bbe1fd4a3ebd50338ccbe07dc5887fe289cd53d
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-17T21:58:14Z
Merge remote-tracking branch 'upstream/master'
commit b2dd795e23c36cbbd022f07a10c0cf21c85eb421
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-18T06:37:13Z
Merge remote-tracking branch 'upstream/master'
commit 8c3e5da458dbff397ed60fcb68f2a46d87ab7ba4
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-18T16:18:16Z
Merge remote-tracking branch 'upstream/master'
commit a0eaa408e847fbdc3ac5b26348588ee0a1e276c7
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-19T04:28:20Z
Merge remote-tracking branch 'upstream/master'
commit d03c940ed89795fa7fe1d1e9f511363b22cdf19d
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-19T21:24:33Z
Merge remote-tracking branch 'upstream/master'
commit d728d5e002082e571ac47292226eb8b2614f479f
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-24T20:32:57Z
Merge remote-tracking branch 'upstream/master'
commit ea104ddfbf7d180ed1bc53dd9a1005010264aa1f
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-25T22:52:57Z
Merge remote-tracking branch 'upstream/master'
commit 6ab1215b781ad0cccf1752f3a625b4e4e371c38e
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-27T17:18:46Z
Merge remote-tracking branch 'upstream/master'
commit 0c566533705331697eb1b287b30c8b16111f6fa2
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-01T06:48:57Z
Merge remote-tracking branch 'upstream/master'
commit d7a187490b31185d0a803cbbdeda67cb26c40056
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-01T22:55:17Z
Merge remote-tracking branch 'upstream/master'
commit 85d35002ce864d5ce6fd3be7215a868a8867caf9
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-02T14:08:30Z
Merge remote-tracking branch 'upstream/master'
commit c056f91036ec75d1e2c93f6f47ad842eb28a3e0b
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-03T06:06:51Z
Merge remote-tracking branch 'upstream/master'
commit 0b8189dd454897ae73bb3a5ffc245b2c65f6b226
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-03T23:33:39Z
Merge remote-tracking branch 'upstream/master'
commit c2ea31de654bd8c32ab0dd9a0362ee90b47c8756
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-06T21:04:09Z
Merge remote-tracking branch 'upstream/master'
commit a2d3056e148a1b38b1b2bf84eabf13962a452e36
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-08T02:52:43Z
Merge remote-tracking branch 'upstream/master'
commit 39e564887667b57ec15b87f9ee6c760fd1938a15
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-08T18:05:50Z
Merge remote-tracking branch 'upstream/master'
commit b9370a35acbc47d1ed30417d843284ac0cf1a6d5
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-07-25T18:41:03Z
Merge remote-tracking branch 'upstream/master'
commit 01224a4c133fa2b47b921ff9fff170ff22f5de44
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-08-03T05:21:29Z
Merge remote-tracking branch 'upstream/master'
commit d05d39a0e51d087ff2102a0685b66a85d0e5a984
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-08-19T21:55:21Z
Merge remote-tracking branch 'upstream/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 pull request #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/N...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on a diff in the pull request:
https://github.com/apache/spark/pull/16841#discussion_r100907233
--- Diff: sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-multiple-columns.sql.out ---
@@ -0,0 +1,178 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 8
+
+
+-- !query 0
+create temporary view t1 as select * from values
+ ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
+ ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
+ ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+ ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
+ ("val1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
+ ("val1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
+ ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
+ ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
+ ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+ ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
+ ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
+ as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+create temporary view t2 as select * from values
+ ("val2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+ ("val1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
+ ("val1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
+ ("val2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+ ("val1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+ ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+ ("val1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
+ ("val1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
+ ("val1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
+ ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
+ as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+create temporary view t3 as select * from values
+ ("val3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
+ ("val3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
+ ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
+ ("val3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
+ ("val3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
+ ("val1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
+ ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
+ ("val3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val3b", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
+ as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+SELECT t1a,
+ t1b,
+ t1h
+FROM t1
+WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
+ t2h
+ FROM t2
+ WHERE t2a = t1a
+ ORDER BY t2a)
+AND t1a = 'val1a'
+-- !query 3 schema
+struct<t1a:string,t1b:smallint,t1h:timestamp>
+-- !query 3 output
+val1a 16 2014-06-04 01:02:00.001
+val1a 16 2014-07-04 01:01:00
+val1a 6 2014-04-04 01:00:00
--- End diff --
This does not match the DB2 output.
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the issue:
https://github.com/apache/spark/pull/16841
test 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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16841
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/72984/
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/N...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/16841#discussion_r100076749
--- Diff: sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-having.sql.out ---
@@ -0,0 +1,217 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 12
+
+
+-- !query 0
+create temporary view t1 as select * from values
+ ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
+ ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
+ ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+ ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
+ ("val1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
+ ("val1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
+ ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
+ ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
+ ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+ ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
+ ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
+ as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+create temporary view t2 as select * from values
+ ("val2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+ ("val1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
+ ("val1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
+ ("val2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+ ("val1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+ ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+ ("val1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
+ ("val1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
+ ("val1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
+ ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
+ as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+create temporary view t3 as select * from values
+ ("val3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
+ ("val3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
+ ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
+ ("val3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
+ ("val3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
+ ("val1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
+ ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
+ ("val3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val3b", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
+ as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+SELECT t1a,
+ t1b,
+ t1h
+FROM t1
+WHERE t1b IN (SELECT t2b
+ FROM t2
+ GROUP BY t2b
+ HAVING t2b < 10)
+-- !query 3 schema
+struct<t1a:string,t1b:smallint,t1h:timestamp>
+-- !query 3 output
+val1a 6 2014-04-04 01:00:00
+val1a 6 2014-04-04 01:02:00.001
+val1b 8 2014-05-04 01:01:00
+val1c 8 2014-05-04 01:02:00.001
+
+
+-- !query 4
+SELECT t1a,
+ t1b,
+ t1c
+FROM t1
+WHERE t1b IN (SELECT Min(t2b)
+ FROM t2
+ WHERE t1a = t2a
+ GROUP BY t2b
+ HAVING t2b > 1)
+-- !query 4 schema
+struct<t1a:string,t1b:smallint,t1c:int>
+-- !query 4 output
+val1b 8 16
+
+
+-- !query 5
+SELECT t1a, t1b, t1c
+FROM t1
+WHERE t1b IN (SELECT t2b
+ FROM t2
+ WHERE t1c < t2c)
+GROUP BY t1a, t1b, t1c
+HAVING t1b < 10
+-- !query 5 schema
+struct<t1a:string,t1b:smallint,t1c:int>
+-- !query 5 output
+val1a 6 8
+
+
+-- !query 6
+SELECT t1a, t1b, t1c
+FROM t1
+WHERE t1b IN (SELECT t2b
+ FROM t2
+ WHERE t1c = t2c)
+GROUP BY t1a, t1b, t1c
+HAVING COUNT (DISTINCT t1b) < 10
+-- !query 6 schema
+struct<t1a:string,t1b:smallint,t1c:int>
+-- !query 6 output
+val1b 8 16
+val1c 8 16
+
+
+-- !query 7
+SELECT Count(DISTINCT( t1a )),
+ t1b
+FROM t1
+WHERE t1c IN (SELECT t2c
+ FROM t2
+ WHERE t1a = t2a
+ GROUP BY t2c
+ HAVING t2c > 10)
+GROUP BY t1b
+HAVING t1b >= 8
+-- !query 7 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 7 output
+2 8
+
+
+-- !query 8
+SELECT t1a,
+ Max(t1b)
+FROM t1
+WHERE t1b > 0
+GROUP BY t1a
+HAVING t1a IN (SELECT t2a
+ FROM t2
+ WHERE t2b IN (SELECT t3b
+ FROM t3
+ WHERE t2c = t3c)
+ )
+-- !query 8 schema
+struct<t1a:string,max(t1b):smallint>
+-- !query 8 output
+val1b 8
+
+
+-- !query 9
+SELECT t1a,
+ t1c,
+ Min(t1d)
+FROM t1
+WHERE t1a NOT IN (SELECT t2a
+ FROM t2
+ GROUP BY t2a
+ HAVING t2a > 'val2a')
+GROUP BY t1a, t1c
+HAVING Min(t1d) > t1c
+-- !query 9 schema
+struct<t1a:string,t1c:int,min(t1d):bigint>
+-- !query 9 output
+val1a 8 10
+val1b 16 19
+val1c 16 19
+val1d 16 19
+
+
+-- !query 10
+SELECT t1a,
+ t1b
+FROM t1
+WHERE t1d NOT IN (SELECT t2d
+ FROM t2
+ WHERE t1a = t2a
+ GROUP BY t2c, t2d
+ HAVING t2c > 8)
+GROUP BY t1a, t1b
+HAVING t1b < 10
+-- !query 10 schema
+struct<t1a:string,t1b:smallint>
+-- !query 10 output
+val1a 6
+
+
+-- !query 11
+SELECT t1a,
+ Max(t1b)
+FROM t1
+WHERE t1b > 0
+GROUP BY t1a
+HAVING t1a NOT IN (SELECT t2a
+ FROM t2
+ WHERE t2b > 3)
+-- !query 11 schema
+struct<t1a:string,max(t1b):smallint>
+-- !query 11 output
+val1a 16
+val1d 10
--- End diff --
The results match with the ones 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 issue #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16841
**[Test build #72984 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72984/testReport)** for PR 16841 at commit [`850aacd`](https://github.com/apache/spark/commit/850aacdec86a1dc1ffc4c4f1b77f828f4aa1078f).
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by kevinyu98 <gi...@git.apache.org>.
Github user kevinyu98 commented on the issue:
https://github.com/apache/spark/pull/16841
@gatorsmile sure, I will do that. Thanks.
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the issue:
https://github.com/apache/spark/pull/16841
ok to test
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16841
Can one of the admins verify this patch?
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/N...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/16841#discussion_r100077423
--- Diff: sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-multiple-columns.sql.out ---
@@ -0,0 +1,178 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 8
+
+
+-- !query 0
+create temporary view t1 as select * from values
+ ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
+ ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
+ ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+ ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
+ ("val1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
+ ("val1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
+ ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
+ ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
+ ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+ ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
+ ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
+ as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+create temporary view t2 as select * from values
+ ("val2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+ ("val1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
+ ("val1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
+ ("val2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+ ("val1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+ ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+ ("val1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
+ ("val1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
+ ("val1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
+ ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
+ as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+create temporary view t3 as select * from values
+ ("val3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
+ ("val3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
+ ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
+ ("val3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
+ ("val3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
+ ("val1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
+ ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
+ ("val3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val3b", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
+ as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+SELECT t1a,
+ t1b,
+ t1h
+FROM t1
+WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
+ t2h
+ FROM t2
+ WHERE t2a = t1a
+ ORDER BY t2a)
+AND t1a = 'val1a'
+-- !query 3 schema
+struct<t1a:string,t1b:smallint,t1h:timestamp>
+-- !query 3 output
+val1a 16 2014-06-04 01:02:00.001
+val1a 16 2014-07-04 01:01:00
+val1a 6 2014-04-04 01:00:00
+val1a 6 2014-04-04 01:02:00.001
+
+
+-- !query 4
+SELECT t1a,
+ t1b,
+ t1d
+FROM t1
+WHERE ( t1b, t1d ) IN (SELECT t2b,
+ t2d
+ FROM t2
+ WHERE t2i IN (SELECT t3i
+ FROM t3
+ WHERE t2b > t3b))
+-- !query 4 schema
+struct<t1a:string,t1b:smallint,t1d:bigint>
+-- !query 4 output
+val1e 10 19
+val1e 10 19
+
+
+-- !query 5
+SELECT t1a,
+ t1b,
+ t1d
+FROM t1
+WHERE ( t1b, t1d ) NOT IN (SELECT t2b,
+ t2d
+ FROM t2
+ WHERE t2h IN (SELECT t3h
+ FROM t3
+ WHERE t2b > t3b))
+AND t1a = 'val1a'
+-- !query 5 schema
+struct<t1a:string,t1b:smallint,t1d:bigint>
+-- !query 5 output
+val1a 16 10
+val1a 16 21
+val1a 6 10
+val1a 6 10
+
+
+-- !query 6
+SELECT t2a
+FROM (SELECT t2a
+ FROM t2
+ WHERE ( t2a, t2b ) IN (SELECT t1a,
+ t1b
+ FROM t1)
+ UNION ALL
+ SELECT t2a
+ FROM t2
+ WHERE ( t2a, t2b ) IN (SELECT t1a,
+ t1b
+ FROM t1)
+ UNION DISTINCT
+ SELECT t2a
+ FROM t2
+ WHERE ( t2a, t2b ) IN (SELECT t3a,
+ t3b
+ FROM t3)) AS t4
+-- !query 6 schema
+struct<t2a:string>
+-- !query 6 output
+val1b
+
+
+-- !query 7
+WITH cte1 AS
+(
+ SELECT t1a,
+ t1b
+ FROM t1
+ WHERE (
+ t1b, t1d) IN
+ (
+ SELECT t2b,
+ t2d
+ FROM t2
+ WHERE t1c = t2c))
+SELECT *
+FROM (
+ SELECT *
+ FROM cte1
+ JOIN cte1 cte2
+ on cte1.t1b = cte2.t1b) s
+-- !query 7 schema
+struct<t1a:string,t1b:smallint,t1a:string,t1b:smallint>
+-- !query 7 output
+val1b 8 val1b 8
+val1b 8 val1c 8
+val1c 8 val1b 8
+val1c 8 val1c 8
--- End diff --
The results match with the ones 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 issue #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the issue:
https://github.com/apache/spark/pull/16841
OK to test
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/N...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/16841#discussion_r100077204
--- Diff: sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-joins.sql.out ---
@@ -0,0 +1,353 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 14
+
+
+-- !query 0
+create temporary view t1 as select * from values
+ ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
+ ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
+ ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+ ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
+ ("val1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
+ ("val1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
+ ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
+ ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
+ ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+ ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
+ ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
+ as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+create temporary view t2 as select * from values
+ ("val2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+ ("val1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
+ ("val1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
+ ("val2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+ ("val1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+ ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+ ("val1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
+ ("val1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
+ ("val1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
+ ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
+ as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+create temporary view t3 as select * from values
+ ("val3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
+ ("val3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
+ ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
+ ("val3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
+ ("val3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
+ ("val1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
+ ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
+ ("val3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val3b", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
+ as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+SELECT t1a, t1b, t1c, t3a, t3b, t3c
+FROM t1 natural JOIN t3
+WHERE t1a IN (SELECT t2a
+ FROM t2
+ WHERE t1a = t2a)
+ AND t1b = t3b
+ AND t1a = t3a
+ORDER BY t1a,
+ t1b,
+ t1c DESC nulls first
+-- !query 3 schema
+struct<t1a:string,t1b:smallint,t1c:int,t3a:string,t3b:smallint,t3c:int>
+-- !query 3 output
+val1b 8 16 val1b 8 16
+val1b 8 16 val1b 8 16
+
+
+-- !query 4
+SELECT Count(DISTINCT(t1a)),
+ t1b,
+ t3a,
+ t3b,
+ t3c
+FROM t1 natural left JOIN t3
+WHERE t1a IN
+ (
+ SELECT t2a
+ FROM t2
+ WHERE t1d = t2d)
+AND t1b > t3b
+GROUP BY t1a,
+ t1b,
+ t3a,
+ t3b,
+ t3c
+ORDER BY t1a DESC
+-- !query 4 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint,t3a:string,t3b:smallint,t3c:int>
+-- !query 4 output
+1 10 val3b 8 NULL
+1 10 val1b 8 16
+1 10 val3a 6 12
+1 8 val3a 6 12
+1 8 val3a 6 12
+
+
+-- !query 5
+SELECT Count(DISTINCT(t1a))
+FROM t1 natural right JOIN t3
+WHERE t1a IN
+ (
+ SELECT t2a
+ FROM t2
+ WHERE t1b = t2b)
+AND t1d IN
+ (
+ SELECT t2d
+ FROM t2
+ WHERE t1c > t2c)
+AND t1a = t3a
+GROUP BY t1a
+ORDER BY t1a
+-- !query 5 schema
+struct<count(DISTINCT t1a):bigint>
+-- !query 5 output
+1
+
+
+-- !query 6
+SELECT t1a,
+ t1b,
+ t1c,
+ t3a,
+ t3b,
+ t3c
+FROM t1 FULL OUTER JOIN t3
+where t1a IN
+ (
+ SELECT t2a
+ FROM t2
+ WHERE t2c IS NOT NULL)
+AND t1b != t3b
+AND t1a = 'val1b'
+ORDER BY t1a
+-- !query 6 schema
+struct<t1a:string,t1b:smallint,t1c:int,t3a:string,t3b:smallint,t3c:int>
+-- !query 6 output
+val1b 8 16 val3a 6 12
+val1b 8 16 val3a 6 12
+val1b 8 16 val1b 10 12
+val1b 8 16 val1b 10 12
+val1b 8 16 val3c 17 16
+val1b 8 16 val3c 17 16
+
+
+-- !query 7
+SELECT Count(DISTINCT(t1a)),
+ t1b
+FROM t1 RIGHT JOIN t3
+where t1a IN
+ (
+ SELECT t2a
+ FROM t2
+ WHERE t2h > t3h)
+AND t3a IN
+ (
+ SELECT t2a
+ FROM t2
+ WHERE t2c > t3c)
+AND t1h >= t3h
+GROUP BY t1a,
+ t1b
+HAVING t1b > 8
+ORDER BY t1a
+-- !query 7 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 7 output
+1 10
+
+
+-- !query 8
+SELECT Count(DISTINCT(t1a))
+FROM t1 LEFT OUTER
+JOIN t3
+ON t1a = t3a
+WHERE t1a IN
+ (
+ SELECT t2a
+ FROM t2
+ WHERE t1h < t2h )
+GROUP BY t1a
+ORDER BY t1a
+-- !query 8 schema
+struct<count(DISTINCT t1a):bigint>
+-- !query 8 output
+1
+1
+1
+
+
+-- !query 9
+SELECT Count(DISTINCT(t1a)),
+ t1b
+FROM t1 INNER JOIN t2
+ON t1a > t2a
+WHERE t1b IN
+ (
+ SELECT t2b
+ FROM t2
+ WHERE t2h > t1h)
+OR t1a IN
+ (
+ SELECT t2a
+ FROM t2
+ WHERE t2h < t1h)
+GROUP BY t1b
+HAVING t1b > 6
+-- !query 9 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 9 output
+1 10
+1 8
+
+
+-- !query 10
+SELECT Count(DISTINCT(t1a)),
+ t1b
+FROM t1
+WHERE t1a IN
+ (
+ SELECT t2a
+ FROM t2
+ JOIN t1
+ WHERE t2b <> t1b)
+AND t1h IN
+ (
+ SELECT t2h
+ FROM t2
+ RIGHT JOIN t3
+ where t2b = t3b)
+GROUP BY t1b
+HAVING t1b > 8
+-- !query 10 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 10 output
+1 10
+
+
+-- !query 11
+SELECT Count(DISTINCT(t1a)),
+ t1b
+FROM t1
+WHERE t1a IN
+ (
+ SELECT t2a
+ FROM t2
+ JOIN t1
+ WHERE t2b <> t1b)
+AND t1h IN
+ (
+ SELECT t2h
+ FROM t2
+ RIGHT JOIN t3
+ where t2b = t3b)
+AND t1b IN
+ (
+ SELECT t2b
+ FROM t2
+ FULL OUTER JOIN t3
+ where t2b = t3b)
+
+GROUP BY t1b
+HAVING t1b > 8
+-- !query 11 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 11 output
+1 10
+
+
+-- !query 12
+SELECT Count(DISTINCT(t1a)),
+ t1b
+FROM t1
+INNER JOIN t2 on t1b = t2b
+RIGHT JOIN t3 ON t1a = t3a
+where t1a IN
+ (
+ SELECT t2a
+ FROM t2
+ FULL OUTER JOIN t3
+ WHERE t2b > t3b)
+AND t1c IN
+ (
+ SELECT t3c
+ FROM t3
+ LEFT OUTER JOIN t2
+ ON t3a = t2a )
+AND t1b IN
+ (
+ SELECT t3b
+ FROM t3 LEFT OUTER
+ JOIN t1
+ WHERE t3c = t1c)
+
+AND t1a = t2a
+GROUP BY t1b
+ORDER BY t1b DESC
+-- !query 12 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 12 output
+1 8
+
+
+-- !query 13
+SELECT t1a,
+ t1b,
+ t1c,
+ count(distinct(t2a)),
+ t2b,
+ t2c
+FROM t1
+FULL JOIN t2 on t1a = t2a
+RIGHT JOIN t3 on t1a = t3a
+where t1a IN
+ (
+ SELECT t2a
+ FROM t2 INNER
+ JOIN t3
+ ON t2b < t3b
+ WHERE t2c IN
+ (
+ SELECT t1c
+ FROM t1
+ WHERE t1a = t2a))
+and t1a = t2a
+Group By t1a, t1b, t1c, t2a, t2b, t2c
+HAVING t2c IS NOT NULL
+ORDER By t2b DESC nulls last
+-- !query 13 schema
+struct<t1a:string,t1b:smallint,t1c:int,count(DISTINCT t2a):bigint,t2b:smallint,t2c:int>
+-- !query 13 output
+val1b 8 16 1 10 12
+val1b 8 16 1 8 16
+val1b 8 16 1 NULL 16
--- End diff --
The results match with the ones 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 issue #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16841
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 issue #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the issue:
https://github.com/apache/spark/pull/16841
test 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 pull request #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/N...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on a diff in the pull request:
https://github.com/apache/spark/pull/16841#discussion_r100907556
--- Diff: sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-multiple-columns.sql ---
@@ -0,0 +1,127 @@
+-- A test suite for multiple columns in predicate in parent side, subquery, and both predicate subquery
+-- It includes correlated cases.
+
+create temporary view t1 as select * from values
+ ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
--- End diff --
The values of timestamp do not match what you insert to 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 issue #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the issue:
https://github.com/apache/spark/pull/16841
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the issue:
https://github.com/apache/spark/pull/16841
Thanks! 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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16841
**[Test build #72833 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72833/testReport)** for PR 16841 at commit [`850aacd`](https://github.com/apache/spark/commit/850aacdec86a1dc1ffc4c4f1b77f828f4aa1078f).
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/N...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/spark/pull/16841
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/N...
Posted by kevinyu98 <gi...@git.apache.org>.
Github user kevinyu98 commented on a diff in the pull request:
https://github.com/apache/spark/pull/16841#discussion_r100924238
--- Diff: sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-multiple-columns.sql ---
@@ -0,0 +1,127 @@
+-- A test suite for multiple columns in predicate in parent side, subquery, and both predicate subquery
+-- It includes correlated cases.
+
+create temporary view t1 as select * from values
+ ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
--- End diff --
Hello Sean: Thank for catching this. The db2 data is
insert into t1 values ('val1a', 6, 8, 10, float(15.0), 20, 20E2, timestamp('2014-04-04 00:00:00.000'), date('2014-04-04'))..
I have verified that is the only difference in the data. I have updated the db2 data, and run against the db2 for all the test files, and verify the result are the same. I upload the updated db2 files.
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16841
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 issue #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the issue:
https://github.com/apache/spark/pull/16841
To make the results consistent between big endian and small endian, we can improve the queries with the extra order by clauses.
@robbinspg Which queries failed? @kevinyu98 Can you collect the failed cases and submit another PR for resolving the issues? Thanks!
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by robbinspg <gi...@git.apache.org>.
Github user robbinspg commented on the issue:
https://github.com/apache/spark/pull/16841
OK I'll raise a separate Jira, document the differences and submit a PR
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/N...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on a diff in the pull request:
https://github.com/apache/spark/pull/16841#discussion_r101462455
--- Diff: sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-multiple-columns.sql ---
@@ -0,0 +1,127 @@
+-- A test suite for multiple columns in predicate in parent side, subquery, and both predicate subquery
+-- It includes correlated cases.
+
+create temporary view t1 as select * from values
+ ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
--- End diff --
Thanks!
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by robbinspg <gi...@git.apache.org>.
Github user robbinspg commented on the issue:
https://github.com/apache/spark/pull/16841
@kevinyu98 Several of the new tests fail on Big Endian platforms. It appears that rows are returned in a slightly different order but are still a correct output from the query. For example in-joins query 4:
-- !query 4
SELECT Count(DISTINCT(t1a)),
t1b,
t3a,
t3b,
t3c
FROM t1 natural left JOIN t3
WHERE t1a IN
(
SELECT t2a
FROM t2
WHERE t1d = t2d)
AND t1b > t3b
GROUP BY t1a,
t1b,
t3a,
t3b,
t3c
ORDER BY t1a DESC
on Little Endian returns
1 10 val3b 8 NULL
1 10 val1b 8 16
1 10 val3a 6 12
1 8 val3a 6 12
1 8 val3a 6 12
wheras on big endian returns:
1 10 val3a 6 12
1 10 val3b 8 NULL
1 10 val1b 8 16
1 8 val3a 6 12
1 8 val3a 6 12
I believe GROUP BY does not define any ordering so both of these outputs are valid for the query as the ORDER BY is only on t1a but obviously the big endian output does not match your expected output so fails.
I'm trying to determine why the execution on big endian returns the rows in a different order.
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16841
**[Test build #72833 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72833/testReport)** for PR 16841 at commit [`850aacd`](https://github.com/apache/spark/commit/850aacdec86a1dc1ffc4c4f1b77f828f4aa1078f).
* 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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16841
**[Test build #72984 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72984/testReport)** for PR 16841 at commit [`850aacd`](https://github.com/apache/spark/commit/850aacdec86a1dc1ffc4c4f1b77f828f4aa1078f).
* 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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by kevinyu98 <gi...@git.apache.org>.
Github user kevinyu98 commented on the issue:
https://github.com/apache/spark/pull/16841
Hello Pete: Thanks for running the test case. Can you send the failing test case file to me? Also I can provide new test files with the output files, can you help test on your platforms? thanks.
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/N...
Posted by kevinyu98 <gi...@git.apache.org>.
Github user kevinyu98 commented on a diff in the pull request:
https://github.com/apache/spark/pull/16841#discussion_r100924389
--- Diff: sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-multiple-columns.sql.out ---
@@ -0,0 +1,178 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 8
+
+
+-- !query 0
+create temporary view t1 as select * from values
+ ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
+ ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
+ ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+ ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
+ ("val1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
+ ("val1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
+ ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
+ ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
+ ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+ ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
+ ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
+ as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+create temporary view t2 as select * from values
+ ("val2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+ ("val1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
+ ("val1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
+ ("val2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+ ("val1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+ ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+ ("val1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
+ ("val1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
+ ("val1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
+ ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
+ as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+create temporary view t3 as select * from values
+ ("val3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
+ ("val3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
+ ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
+ ("val3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
+ ("val3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
+ ("val1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
+ ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
+ ("val3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+ ("val3b", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
+ as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+SELECT t1a,
+ t1b,
+ t1h
+FROM t1
+WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
+ t2h
+ FROM t2
+ WHERE t2a = t1a
+ ORDER BY t2a)
+AND t1a = 'val1a'
+-- !query 3 schema
+struct<t1a:string,t1b:smallint,t1h:timestamp>
+-- !query 3 output
+val1a 16 2014-06-04 01:02:00.001
+val1a 16 2014-07-04 01:01:00
+val1a 6 2014-04-04 01:00:00
--- End diff --
This is the same issue for the input data. After I updated the data for db2 test cases, the result is same now. I have upload the db2 files
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the issue:
https://github.com/apache/spark/pull/16841
LGTM
---
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 #16841: [SPARK-18871][SQL][TESTS] New test cases for IN/NOT IN s...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16841
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/72833/
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