You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by hvanhovell <gi...@git.apache.org> on 2016/11/28 00:30:39 UTC
[GitHub] spark pull request #16026: [SPARK-18597][SQL] Do push-down predicates to rig...
GitHub user hvanhovell opened a pull request:
https://github.com/apache/spark/pull/16026
[SPARK-18597][SQL] Do push-down predicates to right side for a LEFT ANTI join
## What changes were proposed in this pull request?
We currently push down join predicates for a Left Anti join to both sides of the join if we can. This is similar to Inner, Left Semi and Existence (specialized left semi) join. The problem is that this changes the semantics of join; a left anti join filters out rows that matches the join condition.
This PR fixes this by only allowing the push down to the left hand side of the join. This is similar to the behavior of left outer join.
## How was this patch tested?
Added tests to `FilterPushdownSuite.scala` and created a SQLQueryTestSuite file for left anti joins with a regression test.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/hvanhovell/spark SPARK-18597
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/spark/pull/16026.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #16026
----
commit 7696cf2cf67ed8ea541c0b8a90901f15ae393cbe
Author: Herman van Hovell <hv...@databricks.com>
Date: 2016-11-20T21:42:35Z
Fix 2.10 compilation
commit d51b63583670d274acf0fda2042915a29b174d2f
Author: Herman van Hovell <hv...@databricks.com>
Date: 2016-11-21T10:11:56Z
Merge remote-tracking branch 'apache-github/master'
commit 7ccb680ebe8a2939e0b81c31be6703c3927dc7bb
Author: Herman van Hovell <hv...@databricks.com>
Date: 2016-11-25T14:35:09Z
Merge remote-tracking branch 'apache-github/master'
commit e386fed62ad992eedc06e63aaeaf3524fff19b54
Author: Herman van Hovell <hv...@databricks.com>
Date: 2016-11-27T20:48:40Z
Merge remote-tracking branch 'apache-github/master'
commit 4d6b18361828e56fbf1e18c1b694eb318b0851f2
Author: Herman van Hovell <hv...@databricks.com>
Date: 2016-11-28T00:18:16Z
Do not change left anti join semantics by pushing down to the right side of the join.
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16026: [SPARK-18597][SQL] Do not push-down join conditions to t...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16026
**[Test build #69242 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/69242/consoleFull)** for PR 16026 at commit [`47bbdff`](https://github.com/apache/spark/commit/47bbdffdc78ce72b4b1b9cdf0d4ce139d04e16fa).
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16026: [SPARK-18597][SQL] Do push-down predicates to rig...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on a diff in the pull request:
https://github.com/apache/spark/pull/16026#discussion_r89715298
--- Diff: sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/FilterPushdownSuite.scala ---
@@ -514,6 +514,39 @@ class FilterPushdownSuite extends PlanTest {
comparePlans(optimized, analysis.EliminateSubqueryAliases(correctAnswer))
}
+ test("joins: push down where clause into left anti join") {
+ val x = testRelation.subquery('x)
+ val y = testRelation.subquery('y)
+ val originalQuery =
+ x.join(y, LeftAnti, Some("x.b".attr === "y.b".attr))
+ .where("x.a".attr > 10)
+ .analyze
+ val optimized = Optimize.execute(originalQuery)
+ val correctAnswer =
+ x.where("x.a".attr > 10)
+ .join(y, LeftAnti, Some("x.b".attr === "y.b".attr))
+ .analyze
+ comparePlans(optimized, analysis.EliminateSubqueryAliases(correctAnswer))
+ }
+
+ test("joins: only push down to the right of a left anti join") {
--- End diff --
Do we need a JIRA issue number here?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16026: [SPARK-18597][SQL] Do not push-down join conditio...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on a diff in the pull request:
https://github.com/apache/spark/pull/16026#discussion_r89865122
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala ---
@@ -932,7 +932,7 @@ object PushPredicateThroughJoin extends Rule[LogicalPlan] with PredicateHelper {
split(joinCondition.map(splitConjunctivePredicates).getOrElse(Nil), left, right)
joinType match {
- case _: InnerLike | LeftExistence(_) =>
+ case _: InnerLike | LeftSemi | ExistenceJoin(_) =>
--- End diff --
@nsyca Have you tried the above example using the latest master?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16026: [SPARK-18597][SQL] Do push-down predicates to right side...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the issue:
https://github.com/apache/spark/pull/16026
The current PR title is a little bit confusing. Could you please update the PR title from `push-down predicates` to `push-down join conditions`?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16026: [SPARK-18597][SQL] Do push-down predicates to rig...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on a diff in the pull request:
https://github.com/apache/spark/pull/16026#discussion_r89713119
--- Diff: sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/FilterPushdownSuite.scala ---
@@ -514,6 +514,39 @@ class FilterPushdownSuite extends PlanTest {
comparePlans(optimized, analysis.EliminateSubqueryAliases(correctAnswer))
}
+ test("joins: push down where clause into left anti join") {
+ val x = testRelation.subquery('x)
+ val y = testRelation.subquery('y)
+ val originalQuery =
+ x.join(y, LeftAnti, Some("x.b".attr === "y.b".attr))
+ .where("x.a".attr > 10)
+ .analyze
+ val optimized = Optimize.execute(originalQuery)
+ val correctAnswer =
+ x.where("x.a".attr > 10)
+ .join(y, LeftAnti, Some("x.b".attr === "y.b".attr))
+ .analyze
+ comparePlans(optimized, analysis.EliminateSubqueryAliases(correctAnswer))
+ }
+
+ test("joins: only push down to the right of a left anti join") {
--- End diff --
How about `joins: only push down join conditions to the right of a left anti join`?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16026: [SPARK-18597][SQL] Do not push-down join conditions to t...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16026
**[Test build #69242 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/69242/consoleFull)** for PR 16026 at commit [`47bbdff`](https://github.com/apache/spark/commit/47bbdffdc78ce72b4b1b9cdf0d4ce139d04e16fa).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16026: [SPARK-18597][SQL] Do push-down predicates to right side...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on the issue:
https://github.com/apache/spark/pull/16026
Thank you for pinging me, @hvanhovell . I'll take a look, too.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16026: [SPARK-18597][SQL] Do push-down predicates to right side...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16026
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/69211/
Test PASSed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16026: [SPARK-18597][SQL] Do not push-down join conditions to t...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16026
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/69242/
Test PASSed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16026: [SPARK-18597][SQL] Do push-down predicates to right side...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16026
**[Test build #69211 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/69211/consoleFull)** for PR 16026 at commit [`4d6b183`](https://github.com/apache/spark/commit/4d6b18361828e56fbf1e18c1b694eb318b0851f2).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16026: [SPARK-18597][SQL] Do not push-down join conditions to t...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16026
Merged build finished. Test PASSed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16026: [SPARK-18597][SQL] Do not push-down join conditions to t...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/16026
I am merging this to master/2.1. Thanks for the reviews!
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16026: [SPARK-18597][SQL] Do not push-down join conditio...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/16026#discussion_r89830125
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala ---
@@ -932,7 +932,7 @@ object PushPredicateThroughJoin extends Rule[LogicalPlan] with PredicateHelper {
split(joinCondition.map(splitConjunctivePredicates).getOrElse(Nil), left, right)
joinType match {
- case _: InnerLike | LeftExistence(_) =>
+ case _: InnerLike | LeftSemi | ExistenceJoin(_) =>
--- End diff --
The semantics of `ExistenceJoin` says we need to preserve all the rows from the left table through the join operation as if it is a regular `LeftOuter` join. The `ExistenceJoin` augments the `LeftOuter` operation with a new column called exists, set to true when the join condition in the ON clause is true and false otherwise. The filter of any rows will happen in the Filter operation above the `ExistenceJoin`.
Example:
A(c1, c2):
{ (1, 1), (1, 2) }
B(c1):
{ (NULL) }
// can be any value as it is irrelevant in this example
````
select A.*
from A
where exists (select 1 from B where A.c1 = A.c2)
or A.c2=2
````
In this example, the correct result is all the rows from A. If the pattern `ExistenceJoin` at line 935 in `Optimizer.scala` added by the PR of this JIRA is indeed active, the code will push down the predicate `A.c1 = A.c2` to be a Filter on relation A, which will filter the row (1,2) from A.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16026: [SPARK-18597][SQL] Do push-down predicates to right side...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16026
Merged build finished. Test PASSed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16026: [SPARK-18597][SQL] Do push-down predicates to right side...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16026
**[Test build #69211 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/69211/consoleFull)** for PR 16026 at commit [`4d6b183`](https://github.com/apache/spark/commit/4d6b18361828e56fbf1e18c1b694eb318b0851f2).
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16026: [SPARK-18597][SQL] Do not push-down join conditio...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/spark/pull/16026
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16026: [SPARK-18597][SQL] Do push-down predicates to right side...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on the issue:
https://github.com/apache/spark/pull/16026
LGTM, @hvanhovell .
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16026: [SPARK-18597][SQL] Do push-down predicates to right side...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/16026
cc @dongjoon-hyun could you take a look at this?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16026: [SPARK-18597][SQL] Do push-down predicates to right side...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the issue:
https://github.com/apache/spark/pull/16026
LGTM except a minor comment.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org