You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by GitBox <gi...@apache.org> on 2020/04/09 02:45:55 UTC
[GitHub] [spark] maropu opened a new pull request #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
maropu opened a new pull request #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158
<!--
Thanks for sending a pull request! Here are some tips for you:
1. If this is your first time, please read our contributor guidelines: https://spark.apache.org/contributing.html
2. Ensure you have added or run the appropriate tests for your PR: https://spark.apache.org/developer-tools.html
3. If the PR is unfinished, add '[WIP]' in your PR title, e.g., '[WIP][SPARK-XXXX] Your PR title ...'.
4. Be sure to keep the PR description updated to reflect all changes.
5. Please write your PR title to summarize what this PR proposes.
6. If possible, provide a concise example to reproduce the issue for a faster review.
7. If you want to add a new configuration, please read the guideline first for naming configurations in
'core/src/main/scala/org/apache/spark/internal/config/ConfigEntry.scala'.
-->
### What changes were proposed in this pull request?
<!--
Please clarify what changes you are proposing. The purpose of this section is to outline the changes and how this PR fixes the issue.
If possible, please consider writing useful notes for better and faster reviews in your PR. See the examples below.
1. If you refactor some codes with changing classes, showing the class hierarchy will help reviewers.
2. If you fix some SQL features, you can provide some references of other DBMSes.
3. If there is design documentation, please add the link.
4. If there is a discussion in the mailing list, please add the link.
-->
Currently NOT IN subqueries (predicated null aware subquery) are not allowed inside OR expressions. We currently catch this condition in checkAnalysis and throw an error.
This PR enhances the subquery rewrite to support this type of queries.
Query
```SQL
SELECT * FROM s1 WHERE a > 5 or b NOT IN (SELECT c FROM s2);
```
Optimized Plan
```SQL
== Optimized Logical Plan ==
Project [a#3, b#4]
+- Filter ((a#3 > 5) || NOT exists#7)
+- Join ExistenceJoin(exists#7), ((b#4 = c#5) || isnull((b#4 = c#5)))
:- HiveTableRelation `default`.`s1`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [a#3, b#4]
+- Project [c#5]
+- HiveTableRelation `default`.`s2`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [c#5, d#6]
```
This is rework from #22141.
The original author of this PR is @dilipbiswal.
Closes #22141
### Why are the changes needed?
<!--
Please clarify why the changes are needed. For instance,
1. If you propose a new API, clarify the use case for a new API.
2. If you fix a bug, you can clarify why it is a bug.
-->
For better usability.
### Does this PR introduce any user-facing change?
<!--
If yes, please clarify the previous behavior and the change this PR proposes - provide the console output, description and/or an example to show the behavior difference if possible.
If no, write 'No'.
-->
No.
### How was this patch tested?
<!--
If tests were added, say they were added here. Please make sure to add some test cases that check the changes thoroughly including negative and positive cases if possible.
If it was tested in a way different from regular unit tests, please clarify how you tested step by step, ideally copy and paste-able, so that other reviewers can test and check, and descendants can verify in the future.
If tests were not added, please describe why they were not added and/or why it was difficult to add.
-->
Added new tests in SQLQueryTestSuite, RewriteSubquerySuite and SubquerySuite.
Output from DB2 as a reference:
[nested-not-db2.txt](https://github.com/apache/spark/files/2299945/nested-not-db2.txt)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611922328
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611367417
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/120996/
Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611297868
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/25684/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611491501
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611950973
**[Test build #121065 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121065/testReport)** for PR 28158 at commit [`02f788f`](https://github.com/apache/spark/commit/02f788faff9dc911b408a2a9bf9230da1f45b649).
* This patch **fails Spark unit tests**.
* This patch merges cleanly.
* This patch adds no public classes.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611490793
**[Test build #121011 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121011/testReport)** for PR 28158 at commit [`9b54235`](https://github.com/apache/spark/commit/9b542356c672768a312a9ab10c79c0e33c86e5db).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611367845
**[Test build #121011 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121011/testReport)** for PR 28158 at commit [`9b54235`](https://github.com/apache/spark/commit/9b542356c672768a312a9ab10c79c0e33c86e5db).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
maropu commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611367504
retest this please
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#discussion_r406159513
##########
File path: sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/nested-not-in.sql.out
##########
@@ -0,0 +1,332 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 21
+
+
+-- !query
+CREATE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
+ (100, "emp 1", 10),
+ (200, "emp 2", NULL),
+ (300, "emp 3", 20),
+ (400, "emp 4", 30),
+ (500, "emp 5", NULL),
+ (600, "emp 6", 100),
+ (800, "emp 8", 70)
+AS EMP(id, emp_name, dept_id)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
+ (10, "dept 1", "CA"),
+ (20, "dept 2", "NY"),
+ (30, "dept 3", "TX"),
+ (40, "dept 4 - unassigned", "OR"),
+ (50, "dept 5 - unassigned", "NJ"),
+ (70, "dept 7", "FL")
+AS DEPT(dept_id, dept_name, state)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW BONUS AS SELECT * FROM VALUES
+ ("emp 1", 10.00D),
+ ("emp 1", 20.00D),
+ ("emp 2", 300.00D),
+ ("emp 2", 100.00D),
+ ("emp 3", 300.00D),
+ ("emp 4", 100.00D),
+ ("emp 5", 1000.00D),
+ ("emp 6 - no dept", 500.00D)
+AS BONUS(emp_name, bonus_amt)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW ADDRESS AS SELECT * FROM VALUES
+ (100, "emp 1", "addr1"),
+ (200, null, "addr2"),
+ (null, "emp 3", "addr3"),
+ (null, null, "addr4"),
+ (600, "emp 6", "addr6"),
+ (800, "emp 8", "addr8")
+AS ADDRESS(id, emp_name, address)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW S1 AS SELECT * FROM VALUES
+ (null, null), (5, 5), (8, 8), (11, 11) AS s1(a, b)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW S2 AS SELECT * FROM VALUES
+ (7, 7), (8, 8), (11, 11), (null, null) AS s2(c, d)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT id,
+ dept_id
+FROM emp
+WHERE id = 600
+ OR id = 500
+ OR dept_id NOT IN (SELECT dept_id
+ FROM emp)
+-- !query schema
+struct<id:int,dept_id:int>
+-- !query output
+500 NULL
Review comment:
I've checked that the output is the same with the PostgreSQL answers.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-612027240
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611298992
Merged build finished. Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611491501
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#discussion_r406655034
##########
File path: sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/RewriteSubquerySuite.scala
##########
@@ -52,4 +52,21 @@ class RewriteSubquerySuite extends PlanTest {
comparePlans(optimized, correctAnswer)
}
+ test("NOT-IN subquery nested inside OR") {
+ val relation1 = LocalRelation('a.int, 'b.int)
+ val relation2 = LocalRelation('c.int, 'd.int)
+ val exists = 'exists.boolean.notNull
+
+ val query = relation1.where('b === 1 || Not('a.in(ListQuery(relation2.select('c))))).select('a)
+
+ val plan = relation1.select('a).where('b === 1 || Not('exists))
Review comment:
where do we use `plan`?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611909520
Merged build finished. Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611317293
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/25687/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611909532
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/121063/
Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611938533
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611867529
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/25748/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611367409
Merged build finished. Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611316977
**[Test build #120996 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/120996/testReport)** for PR 28158 at commit [`9b54235`](https://github.com/apache/spark/commit/9b542356c672768a312a9ab10c79c0e33c86e5db).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611368427
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611909497
**[Test build #121063 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121063/testReport)** for PR 28158 at commit [`02f788f`](https://github.com/apache/spark/commit/02f788faff9dc911b408a2a9bf9230da1f45b649).
* This patch **fails due to an unknown error code, -9**.
* This patch merges cleanly.
* This patch adds no public classes.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611907007
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611317288
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611367417
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/120996/
Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611951050
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/121065/
Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-612027240
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#discussion_r406592071
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala
##########
@@ -162,12 +162,21 @@ object RewritePredicateSubquery extends Rule[LogicalPlan] with PredicateHelper {
plan: LogicalPlan): (Option[Expression], LogicalPlan) = {
var newPlan = plan
val newExprs = exprs.map { e =>
- e transformUp {
+ e transformDown {
case Exists(sub, conditions, _) =>
val exists = AttributeReference("exists", BooleanType, nullable = false)()
newPlan =
buildJoin(newPlan, sub, ExistenceJoin(exists), conditions.reduceLeftOption(And))
exists
+ case Not(InSubquery(values, ListQuery(sub, conditions, _, _))) =>
+ val exists = AttributeReference("exists", BooleanType, nullable = false)()
+ // Deduplicate conflicting attributes if any.
+ val newSub = dedupSubqueryOnSelfJoin(newPlan, sub, Some(values))
+ val inConditions = values.zip(sub.output).map(EqualTo.tupled)
+ val nullAwareJoinConds = inConditions.map(c => Or(c, IsNull(c)))
Review comment:
Added.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611867529
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/25748/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611951050
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/121065/
Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611317288
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611906671
**[Test build #121063 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121063/testReport)** for PR 28158 at commit [`02f788f`](https://github.com/apache/spark/commit/02f788faff9dc911b408a2a9bf9230da1f45b649).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611317293
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/25687/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611951045
Merged build finished. Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611922333
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/25756/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611491512
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/121011/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611909619
Merged build finished. Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611367168
**[Test build #120996 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/120996/testReport)** for PR 28158 at commit [`9b54235`](https://github.com/apache/spark/commit/9b542356c672768a312a9ab10c79c0e33c86e5db).
* This patch **fails due to an unknown error code, -9**.
* This patch merges cleanly.
* This patch adds no public classes.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611297621
**[Test build #120993 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/120993/testReport)** for PR 28158 at commit [`1f30d08`](https://github.com/apache/spark/commit/1f30d0821d5f21aaecca34125de296677fab4d3b).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#discussion_r406592034
##########
File path: sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/nested-not-in.sql.out
##########
@@ -0,0 +1,332 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 21
+
+
+-- !query
+CREATE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
+ (100, "emp 1", 10),
+ (200, "emp 2", NULL),
+ (300, "emp 3", 20),
+ (400, "emp 4", 30),
+ (500, "emp 5", NULL),
+ (600, "emp 6", 100),
+ (800, "emp 8", 70)
+AS EMP(id, emp_name, dept_id)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
+ (10, "dept 1", "CA"),
+ (20, "dept 2", "NY"),
+ (30, "dept 3", "TX"),
+ (40, "dept 4 - unassigned", "OR"),
+ (50, "dept 5 - unassigned", "NJ"),
+ (70, "dept 7", "FL")
+AS DEPT(dept_id, dept_name, state)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW BONUS AS SELECT * FROM VALUES
+ ("emp 1", 10.00D),
+ ("emp 1", 20.00D),
+ ("emp 2", 300.00D),
+ ("emp 2", 100.00D),
+ ("emp 3", 300.00D),
+ ("emp 4", 100.00D),
+ ("emp 5", 1000.00D),
+ ("emp 6 - no dept", 500.00D)
+AS BONUS(emp_name, bonus_amt)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW ADDRESS AS SELECT * FROM VALUES
+ (100, "emp 1", "addr1"),
+ (200, null, "addr2"),
+ (null, "emp 3", "addr3"),
+ (null, null, "addr4"),
+ (600, "emp 6", "addr6"),
+ (800, "emp 8", "addr8")
+AS ADDRESS(id, emp_name, address)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW S1 AS SELECT * FROM VALUES
+ (null, null), (5, 5), (8, 8), (11, 11) AS s1(a, b)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW S2 AS SELECT * FROM VALUES
+ (7, 7), (8, 8), (11, 11), (null, null) AS s2(c, d)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT id,
+ dept_id
+FROM emp
+WHERE id = 600
+ OR id = 500
+ OR dept_id NOT IN (SELECT dept_id
+ FROM emp)
+-- !query schema
+struct<id:int,dept_id:int>
+-- !query output
+500 NULL
Review comment:
I checked the ported tests don't have those queries. Actually, the original regression tests in PostgreSQL don't have, too.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611299003
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/120993/
Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611298992
Merged build finished. Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#discussion_r406624994
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala
##########
@@ -162,12 +162,32 @@ object RewritePredicateSubquery extends Rule[LogicalPlan] with PredicateHelper {
plan: LogicalPlan): (Option[Expression], LogicalPlan) = {
var newPlan = plan
val newExprs = exprs.map { e =>
- e transformUp {
+ e transformDown {
case Exists(sub, conditions, _) =>
val exists = AttributeReference("exists", BooleanType, nullable = false)()
newPlan =
buildJoin(newPlan, sub, ExistenceJoin(exists), conditions.reduceLeftOption(And))
exists
+ case Not(InSubquery(values, ListQuery(sub, conditions, _, _))) =>
+ val exists = AttributeReference("exists", BooleanType, nullable = false)()
+ // Deduplicate conflicting attributes if any.
+ val newSub = dedupSubqueryOnSelfJoin(newPlan, sub, Some(values))
+ val inConditions = values.zip(sub.output).map(EqualTo.tupled)
+ // To handle null-aware predicate sub-queries in nested conditions,
Review comment:
Yea, IIUC `null-aware predicate sub-queries` just means `InSubquery` judging from the `hasNullAwarePredicateWithinNot` implementation. https://github.com/apache/spark/pull/28158/files#diff-34d3859f7c9fd82a4b286686c21d26c2L112
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611367409
Merged build finished. Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
maropu commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611494369
cc: @cloud-fan @dongjoon-hyun
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611491512
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/121011/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611907016
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/25755/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611938536
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/25763/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611867523
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611921953
**[Test build #121065 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121065/testReport)** for PR 28158 at commit [`02f788f`](https://github.com/apache/spark/commit/02f788faff9dc911b408a2a9bf9230da1f45b649).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611297857
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-612027249
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/121072/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611909627
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/121056/
Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611316977
**[Test build #120996 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/120996/testReport)** for PR 28158 at commit [`9b54235`](https://github.com/apache/spark/commit/9b542356c672768a312a9ab10c79c0e33c86e5db).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu closed pull request #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
maropu closed pull request #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611938533
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#discussion_r406525352
##########
File path: sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/nested-not-in.sql.out
##########
@@ -0,0 +1,332 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 21
+
+
+-- !query
+CREATE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
+ (100, "emp 1", 10),
+ (200, "emp 2", NULL),
+ (300, "emp 3", 20),
+ (400, "emp 4", 30),
+ (500, "emp 5", NULL),
+ (600, "emp 6", 100),
+ (800, "emp 8", 70)
+AS EMP(id, emp_name, dept_id)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
+ (10, "dept 1", "CA"),
+ (20, "dept 2", "NY"),
+ (30, "dept 3", "TX"),
+ (40, "dept 4 - unassigned", "OR"),
+ (50, "dept 5 - unassigned", "NJ"),
+ (70, "dept 7", "FL")
+AS DEPT(dept_id, dept_name, state)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW BONUS AS SELECT * FROM VALUES
+ ("emp 1", 10.00D),
+ ("emp 1", 20.00D),
+ ("emp 2", 300.00D),
+ ("emp 2", 100.00D),
+ ("emp 3", 300.00D),
+ ("emp 4", 100.00D),
+ ("emp 5", 1000.00D),
+ ("emp 6 - no dept", 500.00D)
+AS BONUS(emp_name, bonus_amt)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW ADDRESS AS SELECT * FROM VALUES
+ (100, "emp 1", "addr1"),
+ (200, null, "addr2"),
+ (null, "emp 3", "addr3"),
+ (null, null, "addr4"),
+ (600, "emp 6", "addr6"),
+ (800, "emp 8", "addr8")
+AS ADDRESS(id, emp_name, address)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW S1 AS SELECT * FROM VALUES
+ (null, null), (5, 5), (8, 8), (11, 11) AS s1(a, b)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW S2 AS SELECT * FROM VALUES
+ (7, 7), (8, 8), (11, 11), (null, null) AS s2(c, d)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT id,
+ dept_id
+FROM emp
+WHERE id = 600
+ OR id = 500
+ OR dept_id NOT IN (SELECT dept_id
+ FROM emp)
+-- !query schema
+struct<id:int,dept_id:int>
+-- !query output
+500 NULL
Review comment:
Ah, yea, we need to check that. I'll do later.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611368433
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/25703/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611297621
**[Test build #120993 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/120993/testReport)** for PR 28158 at commit [`1f30d08`](https://github.com/apache/spark/commit/1f30d0821d5f21aaecca34125de296677fab4d3b).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611938119
**[Test build #121072 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121072/testReport)** for PR 28158 at commit [`be9dc0a`](https://github.com/apache/spark/commit/be9dc0abc0ac6fcac42472721c1cb355c70e52f8).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
maropu commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611920809
retest this please
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611922328
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611909619
Merged build finished. Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611938536
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/25763/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611909520
Merged build finished. Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611299003
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/120993/
Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on a change in pull request #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#discussion_r406525134
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala
##########
@@ -162,12 +162,21 @@ object RewritePredicateSubquery extends Rule[LogicalPlan] with PredicateHelper {
plan: LogicalPlan): (Option[Expression], LogicalPlan) = {
var newPlan = plan
val newExprs = exprs.map { e =>
- e transformUp {
+ e transformDown {
case Exists(sub, conditions, _) =>
val exists = AttributeReference("exists", BooleanType, nullable = false)()
newPlan =
buildJoin(newPlan, sub, ExistenceJoin(exists), conditions.reduceLeftOption(And))
exists
+ case Not(InSubquery(values, ListQuery(sub, conditions, _, _))) =>
+ val exists = AttributeReference("exists", BooleanType, nullable = false)()
+ // Deduplicate conflicting attributes if any.
+ val newSub = dedupSubqueryOnSelfJoin(newPlan, sub, Some(values))
+ val inConditions = values.zip(sub.output).map(EqualTo.tupled)
+ val nullAwareJoinConds = inConditions.map(c => Or(c, IsNull(c)))
Review comment:
ok
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611867523
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611297857
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611938119
**[Test build #121072 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121072/testReport)** for PR 28158 at commit [`be9dc0a`](https://github.com/apache/spark/commit/be9dc0abc0ac6fcac42472721c1cb355c70e52f8).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611907016
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/25755/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611368433
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/25703/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611906671
**[Test build #121063 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121063/testReport)** for PR 28158 at commit [`02f788f`](https://github.com/apache/spark/commit/02f788faff9dc911b408a2a9bf9230da1f45b649).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611951045
Merged build finished. Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611922333
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/25756/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611921953
**[Test build #121065 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121065/testReport)** for PR 28158 at commit [`02f788f`](https://github.com/apache/spark/commit/02f788faff9dc911b408a2a9bf9230da1f45b649).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611867283
**[Test build #121056 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121056/testReport)** for PR 28158 at commit [`f65e68a`](https://github.com/apache/spark/commit/f65e68a0d5e3fe69f87da55d54856a8b4cbed2f8).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] maropu commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
maropu commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-612264416
Thanks, @cloud-fan @dilipbiswal ! Merged to master.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611867283
**[Test build #121056 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121056/testReport)** for PR 28158 at commit [`f65e68a`](https://github.com/apache/spark/commit/f65e68a0d5e3fe69f87da55d54856a8b4cbed2f8).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611367845
**[Test build #121011 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121011/testReport)** for PR 28158 at commit [`9b54235`](https://github.com/apache/spark/commit/9b542356c672768a312a9ab10c79c0e33c86e5db).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-612026200
**[Test build #121072 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121072/testReport)** for PR 28158 at commit [`be9dc0a`](https://github.com/apache/spark/commit/be9dc0abc0ac6fcac42472721c1cb355c70e52f8).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611909627
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/121056/
Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#discussion_r406622100
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala
##########
@@ -162,12 +162,32 @@ object RewritePredicateSubquery extends Rule[LogicalPlan] with PredicateHelper {
plan: LogicalPlan): (Option[Expression], LogicalPlan) = {
var newPlan = plan
val newExprs = exprs.map { e =>
- e transformUp {
+ e transformDown {
case Exists(sub, conditions, _) =>
val exists = AttributeReference("exists", BooleanType, nullable = false)()
newPlan =
buildJoin(newPlan, sub, ExistenceJoin(exists), conditions.reduceLeftOption(And))
exists
+ case Not(InSubquery(values, ListQuery(sub, conditions, _, _))) =>
+ val exists = AttributeReference("exists", BooleanType, nullable = false)()
+ // Deduplicate conflicting attributes if any.
+ val newSub = dedupSubqueryOnSelfJoin(newPlan, sub, Some(values))
+ val inConditions = values.zip(sub.output).map(EqualTo.tupled)
+ // To handle null-aware predicate sub-queries in nested conditions,
Review comment:
where do we define "null-aware predicate sub-queries"? It's only `InSubquery`?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-612027249
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/121072/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611909532
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/121063/
Test FAILed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611368427
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611298980
**[Test build #120993 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/120993/testReport)** for PR 28158 at commit [`1f30d08`](https://github.com/apache/spark/commit/1f30d0821d5f21aaecca34125de296677fab4d3b).
* This patch **fails to build**.
* This patch merges cleanly.
* This patch adds no public classes.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611297868
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/25684/
Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#discussion_r406188675
##########
File path: sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/nested-not-in.sql.out
##########
@@ -0,0 +1,332 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 21
+
+
+-- !query
+CREATE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
+ (100, "emp 1", 10),
+ (200, "emp 2", NULL),
+ (300, "emp 3", 20),
+ (400, "emp 4", 30),
+ (500, "emp 5", NULL),
+ (600, "emp 6", 100),
+ (800, "emp 8", 70)
+AS EMP(id, emp_name, dept_id)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
+ (10, "dept 1", "CA"),
+ (20, "dept 2", "NY"),
+ (30, "dept 3", "TX"),
+ (40, "dept 4 - unassigned", "OR"),
+ (50, "dept 5 - unassigned", "NJ"),
+ (70, "dept 7", "FL")
+AS DEPT(dept_id, dept_name, state)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW BONUS AS SELECT * FROM VALUES
+ ("emp 1", 10.00D),
+ ("emp 1", 20.00D),
+ ("emp 2", 300.00D),
+ ("emp 2", 100.00D),
+ ("emp 3", 300.00D),
+ ("emp 4", 100.00D),
+ ("emp 5", 1000.00D),
+ ("emp 6 - no dept", 500.00D)
+AS BONUS(emp_name, bonus_amt)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW ADDRESS AS SELECT * FROM VALUES
+ (100, "emp 1", "addr1"),
+ (200, null, "addr2"),
+ (null, "emp 3", "addr3"),
+ (null, null, "addr4"),
+ (600, "emp 6", "addr6"),
+ (800, "emp 8", "addr8")
+AS ADDRESS(id, emp_name, address)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW S1 AS SELECT * FROM VALUES
+ (null, null), (5, 5), (8, 8), (11, 11) AS s1(a, b)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW S2 AS SELECT * FROM VALUES
+ (7, 7), (8, 8), (11, 11), (null, null) AS s2(c, d)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT id,
+ dept_id
+FROM emp
+WHERE id = 600
+ OR id = 500
+ OR dept_id NOT IN (SELECT dept_id
+ FROM emp)
+-- !query schema
+struct<id:int,dept_id:int>
+-- !query output
+500 NULL
Review comment:
does our ported pgsql test include "nested not in" queries?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a change in pull request #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#discussion_r406187806
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala
##########
@@ -162,12 +162,21 @@ object RewritePredicateSubquery extends Rule[LogicalPlan] with PredicateHelper {
plan: LogicalPlan): (Option[Expression], LogicalPlan) = {
var newPlan = plan
val newExprs = exprs.map { e =>
- e transformUp {
+ e transformDown {
case Exists(sub, conditions, _) =>
val exists = AttributeReference("exists", BooleanType, nullable = false)()
newPlan =
buildJoin(newPlan, sub, ExistenceJoin(exists), conditions.reduceLeftOption(And))
exists
+ case Not(InSubquery(values, ListQuery(sub, conditions, _, _))) =>
+ val exists = AttributeReference("exists", BooleanType, nullable = false)()
+ // Deduplicate conflicting attributes if any.
+ val newSub = dedupSubqueryOnSelfJoin(newPlan, sub, Some(values))
+ val inConditions = values.zip(sub.output).map(EqualTo.tupled)
+ val nullAwareJoinConds = inConditions.map(c => Or(c, IsNull(c)))
Review comment:
can we add some comments to explain it?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] AmplabJenkins removed a comment on issue #28158:
[SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611907007
Merged build finished. Test PASSed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] SparkQA commented on issue #28158: [SPARK-25154][SQL]
Support NOT IN sub-queries inside nested OR conditions
Posted by GitBox <gi...@apache.org>.
SparkQA commented on issue #28158: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions
URL: https://github.com/apache/spark/pull/28158#issuecomment-611909495
**[Test build #121056 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/121056/testReport)** for PR 28158 at commit [`f65e68a`](https://github.com/apache/spark/commit/f65e68a0d5e3fe69f87da55d54856a8b4cbed2f8).
* This patch **fails due to an unknown error code, -9**.
* This patch merges cleanly.
* This patch adds no public classes.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org