You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by viirya <gi...@git.apache.org> on 2017/03/31 04:12:08 UTC
[GitHub] spark pull request #17491: [SQL][WIP] Exists should not be evaluated in Join...
GitHub user viirya opened a pull request:
https://github.com/apache/spark/pull/17491
[SQL][WIP] Exists should not be evaluated in Join operator
## What changes were proposed in this pull request?
Similar to `ListQuery`, `Exists` should not be evaluated in `Join` operator too.
For the `Exists` subquery without correlated reference, this patch converts it to scalar subquery with a count `Aggregate` operator.
## How was this patch tested?
Jenkins tests.
Please review http://spark.apache.org/contributing.html before opening a pull request.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/viirya/spark-1 dont-push-exists-to-join
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/spark/pull/17491.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 #17491
----
commit b012550d6981d3b49aac78432dc42e2974fd3649
Author: Liang-Chi Hsieh <vi...@gmail.com>
Date: 2017-03-31T04:07:38Z
Exists should not be evaluated in Join operator 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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/17491
**[Test build #75687 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/75687/testReport)** for PR 17491 at commit [`329f067`](https://github.com/apache/spark/commit/329f067d1a38469675367f1e29330034f6d923e8).
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r110858379
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala ---
@@ -498,3 +498,32 @@ object RewriteCorrelatedScalarSubquery extends Rule[LogicalPlan] {
}
}
}
+
+/**
+ * This rule rewrites a EXISTS predicate sub-queries into an Aggregate with count.
+ * So it doesn't be converted to a JOIN later.
+ */
+object RewriteEmptyExists extends Rule[LogicalPlan] with PredicateHelper {
+ private def containsAgg(plan: LogicalPlan): Boolean = {
+ plan.collect {
+ case a: Aggregate => a
+ }.nonEmpty
+ }
+
+ def apply(plan: LogicalPlan): LogicalPlan = plan transform {
+ case Filter(condition, child) =>
+ val (withSubquery, withoutSubquery) =
+ splitConjunctivePredicates(condition).partition(SubqueryExpression.hasInOrExistsSubquery)
+ val newWithSubquery = withSubquery.map(_.transform {
+ case e @ Exists(sub, conditions, exprId) if conditions.isEmpty && !containsAgg(sub) =>
+ val countExpr = Alias(Count(Literal(1)).toAggregateExpression(), "count")()
+ val expr = Alias(GreaterThan(countExpr.toAttribute, Literal(0)), e.toString)()
+ ScalarSubquery(
+ Project(Seq(expr),
+ Aggregate(Nil, Seq(countExpr), LocalLimit(Literal(1), sub))),
--- End diff --
We can address the early-out in other work.
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r109192716
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/predicates.scala ---
@@ -90,11 +90,12 @@ trait PredicateHelper {
* Returns true iff `expr` could be evaluated as a condition within join.
*/
protected def canEvaluateWithinJoin(expr: Expression): Boolean = expr match {
- case l: ListQuery =>
+ case _: ListQuery | _: Exists =>
// A ListQuery defines the query which we want to search in an IN subquery expression.
// Currently the only way to evaluate an IN subquery is to convert it to a
// LeftSemi/LeftAnti/ExistenceJoin by `RewritePredicateSubquery` rule.
// It cannot be evaluated as part of a Join operator.
+ // An Exists shouldn't be push into a Join operator too.
--- End diff --
I am not sure. The name of this is `def canEvaluateWithinJoin` so I assume it asks whether an input `Expression` can be processed as part of a Join operator. Can a `ScalarSubquery` be processed inside a 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 pull request #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r109181709
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala ---
@@ -498,3 +498,31 @@ object RewriteCorrelatedScalarSubquery extends Rule[LogicalPlan] {
}
}
}
+
+/**
+ * This rule rewrites a EXISTS predicate sub-queries into an Aggregate with count.
+ * So it doesn't be converted to a JOIN later.
+ */
+object RewriteEmptyExists extends Rule[LogicalPlan] with PredicateHelper {
+ private def containsAgg(plan: LogicalPlan): Boolean = {
+ plan.collect {
+ case a: Aggregate => a
+ }.nonEmpty
+ }
+
+ def apply(plan: LogicalPlan): LogicalPlan = plan transform {
+ case Filter(condition, child) =>
+ val (withSubquery, withoutSubquery) =
+ splitConjunctivePredicates(condition).partition(SubqueryExpression.hasInOrExistsSubquery)
+ val newWithSubquery = withSubquery.map(_.transform {
+ case e @ Exists(sub, conditions, exprId) if conditions.isEmpty && !containsAgg(sub) =>
--- End diff --
@viirya what we should do is to mark the outer join as an "early out" in the run-time join processing. An aggregation is not cheap as it needs to read the entire table to give the (first) answer. An "early out" logic is just like what we currently have in the LeftSemi join where only the first match of the join value is returned (and the subsequent matches are discarded). A LeftSemi join is a special case of an "early out" inner join where the columns of the right table are not permitted in the output of the join.
IMO, a step forward is to implement the "early out" mechanism in all the run-time join operators, nested-loop, sorted-merge, and hash; and inner, left outer, right outer. Then LeftSemi and LeftAnti will just be special cases of one of those operators.
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r109183735
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/predicates.scala ---
@@ -90,11 +90,12 @@ trait PredicateHelper {
* Returns true iff `expr` could be evaluated as a condition within join.
*/
protected def canEvaluateWithinJoin(expr: Expression): Boolean = expr match {
- case l: ListQuery =>
+ case _: ListQuery | _: Exists =>
// A ListQuery defines the query which we want to search in an IN subquery expression.
// Currently the only way to evaluate an IN subquery is to convert it to a
// LeftSemi/LeftAnti/ExistenceJoin by `RewritePredicateSubquery` rule.
// It cannot be evaluated as part of a Join operator.
+ // An Exists shouldn't be push into a Join operator too.
--- End diff --
@dilipbiswal Here is another case of a regression from https://github.com/apache/spark/pull/16954. Would you think we should just say the following?
`case SubqueryExpression => false`
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by dilipbiswal <gi...@git.apache.org>.
Github user dilipbiswal commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r109191711
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala ---
@@ -498,3 +498,31 @@ object RewriteCorrelatedScalarSubquery extends Rule[LogicalPlan] {
}
}
}
+
+/**
+ * This rule rewrites a EXISTS predicate sub-queries into an Aggregate with count.
+ * So it doesn't be converted to a JOIN later.
+ */
+object RewriteEmptyExists extends Rule[LogicalPlan] with PredicateHelper {
+ private def containsAgg(plan: LogicalPlan): Boolean = {
+ plan.collect {
+ case a: Aggregate => a
+ }.nonEmpty
+ }
+
+ def apply(plan: LogicalPlan): LogicalPlan = plan transform {
+ case Filter(condition, child) =>
+ val (withSubquery, withoutSubquery) =
+ splitConjunctivePredicates(condition).partition(SubqueryExpression.hasInOrExistsSubquery)
+ val newWithSubquery = withSubquery.map(_.transform {
+ case e @ Exists(sub, conditions, exprId) if conditions.isEmpty && !containsAgg(sub) =>
--- End diff --
@viirya @nsyca I am still looking at the code. If we do go the route to turn this into a scalar subquery, would a "limit" help prevent a scan of the inner table (assuming its very large). So something like "select Count(1) from (original subquery + limit 1)" ?
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r110858303
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala ---
@@ -498,3 +498,32 @@ object RewriteCorrelatedScalarSubquery extends Rule[LogicalPlan] {
}
}
}
+
+/**
+ * This rule rewrites a EXISTS predicate sub-queries into an Aggregate with count.
+ * So it doesn't be converted to a JOIN later.
+ */
+object RewriteEmptyExists extends Rule[LogicalPlan] with PredicateHelper {
+ private def containsAgg(plan: LogicalPlan): Boolean = {
+ plan.collect {
+ case a: Aggregate => a
+ }.nonEmpty
+ }
+
+ def apply(plan: LogicalPlan): LogicalPlan = plan transform {
+ case Filter(condition, child) =>
+ val (withSubquery, withoutSubquery) =
+ splitConjunctivePredicates(condition).partition(SubqueryExpression.hasInOrExistsSubquery)
+ val newWithSubquery = withSubquery.map(_.transform {
+ case e @ Exists(sub, conditions, exprId) if conditions.isEmpty && !containsAgg(sub) =>
+ val countExpr = Alias(Count(Literal(1)).toAggregateExpression(), "count")()
+ val expr = Alias(GreaterThan(countExpr.toAttribute, Literal(0)), e.toString)()
+ ScalarSubquery(
+ Project(Seq(expr),
+ Aggregate(Nil, Seq(countExpr), LocalLimit(Literal(1), sub))),
--- End diff --
I think it is a special case. Then I will remove this optimization and minimize this pr's change.
---
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 #17491: [SQL][WIP] Exists should not be evaluated in Join operat...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/17491
**[Test build #75424 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/75424/testReport)** for PR 17491 at commit [`b012550`](https://github.com/apache/spark/commit/b012550d6981d3b49aac78432dc42e2974fd3649).
* 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 #17491: [SQL][WIP] Exists should not be evaluated in Join operat...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/17491
Merged build finished. Test FAILed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r109272800
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/predicates.scala ---
@@ -90,11 +90,12 @@ trait PredicateHelper {
* Returns true iff `expr` could be evaluated as a condition within join.
*/
protected def canEvaluateWithinJoin(expr: Expression): Boolean = expr match {
- case l: ListQuery =>
+ case _: ListQuery | _: Exists =>
// A ListQuery defines the query which we want to search in an IN subquery expression.
// Currently the only way to evaluate an IN subquery is to convert it to a
// LeftSemi/LeftAnti/ExistenceJoin by `RewritePredicateSubquery` rule.
// It cannot be evaluated as part of a Join operator.
+ // An Exists shouldn't be push into a Join operator too.
--- End diff --
> I am not sure. The name of this is def canEvaluateWithinJoin so I assume it asks whether an input Expression can be processed as part of a Join operator. Can a ScalarSubquery be processed inside a Join?
I remember `ScalarSubquery` without correlated reference will be evaluated as individual query plan and get its result back as an expression. So it should not no difference in run time compared with other expressions.
A `Limit` looks good to me for now. I can't think a negative side effect prevents possible optimization for the subquery plan. Doesn't it just like a re-written query with a limit clause added?
I think this is a corner usage case. To address this in run-time like the introduction of "early out" into physical join operators works, but it may involve a lot of code changes.
> 2) one more step further: cache the result of the RHS without a rescan because the next row from the parent table will always get the same answer from rescanning the subquery.
I quickly scan physical `SortMergeJoin` operator. If the streamed row matches the scanned group of rows, it will reuse the scanned group. Sounds it does what you said, if I don't miss something.
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r109222591
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/predicates.scala ---
@@ -90,11 +90,12 @@ trait PredicateHelper {
* Returns true iff `expr` could be evaluated as a condition within join.
*/
protected def canEvaluateWithinJoin(expr: Expression): Boolean = expr match {
- case l: ListQuery =>
+ case _: ListQuery | _: Exists =>
// A ListQuery defines the query which we want to search in an IN subquery expression.
// Currently the only way to evaluate an IN subquery is to convert it to a
// LeftSemi/LeftAnti/ExistenceJoin by `RewritePredicateSubquery` rule.
// It cannot be evaluated as part of a Join operator.
+ // An Exists shouldn't be push into a Join operator too.
--- End diff --
What this code does is around the idea of treating an uncorrelated subquery as a black box. The subquery is processed as a self-contained operation and a list of values is returned. After that, the code evaluates as if this is an IN list predicate like <col> IN (<list-of-literals>). In your code above, <col> is represented as a "true" literal. That means the returned values from the subquery must be in Boolean type too.
Putting a LIMIT does help to short-circuit the processing to the first row. I still think putting a LIMIT explicitly as an extra LogicalPlan operator may have some negative side effect in the way that it prevents other Optimizer rules to further optimize the query.
I feel this optimization could be done better in the run-time area, rather than trying to shoehorn it in the Optimizer phase. What we can do is 1) propagate the notion of "early out" deeper to the operator on the RHS of the outer join. If it's a scan, stop scanning on the first row. 2) one more step further: cache the result of the RHS without a rescan because the next row from the parent table will always get the same answer from rescanning the subquery.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/17491
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 #17491: [SQL][WIP] Exists should not be evaluated in Join operat...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/17491
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/75423/
Test FAILed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by dilipbiswal <gi...@git.apache.org>.
Github user dilipbiswal commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r109211310
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/predicates.scala ---
@@ -90,11 +90,12 @@ trait PredicateHelper {
* Returns true iff `expr` could be evaluated as a condition within join.
*/
protected def canEvaluateWithinJoin(expr: Expression): Boolean = expr match {
- case l: ListQuery =>
+ case _: ListQuery | _: Exists =>
// A ListQuery defines the query which we want to search in an IN subquery expression.
// Currently the only way to evaluate an IN subquery is to convert it to a
// LeftSemi/LeftAnti/ExistenceJoin by `RewritePredicateSubquery` rule.
// It cannot be evaluated as part of a Join operator.
+ // An Exists shouldn't be push into a Join operator too.
--- End diff --
@nsyca Looking at this further, there is a SubqueryExec operator that can execute a ScalarSubquery and InSubquery (PlanSubqueries). As part of my change, i had removed the case for PredicateSubquery as we removed PredicateSubquery all together. I just quickly tried the following and got the query to work. I haven't verified the semantics but just tried something quickly. Basically if we were to keep the Exists expression as it is and push it down as a join condition and execute it as a InSubquery (possibly with a additional limit clause) there seems to be an infrastructure for it already. Or perhaps we may want to introduce a ExistSubquery exec operator that can work more efficiently.
```scala
case subquery: expressions.Exists =>
val executedPlan = new QueryExecution(sparkSession, subquery.plan).executedPlan
InSubquery(Literal.TrueLiteral,
SubqueryExec(s"subquery${subquery.exprId.id}", executedPlan), subquery.exprId)
```
What do you think Natt ?
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on the issue:
https://github.com/apache/spark/pull/17491
cc @cloud-fan
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/spark/pull/17491
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/17491
**[Test build #75439 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/75439/testReport)** for PR 17491 at commit [`88016bf`](https://github.com/apache/spark/commit/88016bfc5a4fa72f4513fd2e8ec9a439c2d77ee6).
* 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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/17491
**[Test build #75683 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/75683/testReport)** for PR 17491 at commit [`329f067`](https://github.com/apache/spark/commit/329f067d1a38469675367f1e29330034f6d923e8).
---
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 #17491: [SQL][WIP] Exists should not be evaluated in Join operat...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/17491
**[Test build #75419 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/75419/testReport)** for PR 17491 at commit [`b012550`](https://github.com/apache/spark/commit/b012550d6981d3b49aac78432dc42e2974fd3649).
* This patch **fails PySpark unit tests**.
* This patch merges cleanly.
* This patch adds no public classes.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by dilipbiswal <gi...@git.apache.org>.
Github user dilipbiswal commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r109203721
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/predicates.scala ---
@@ -90,11 +90,12 @@ trait PredicateHelper {
* Returns true iff `expr` could be evaluated as a condition within join.
*/
protected def canEvaluateWithinJoin(expr: Expression): Boolean = expr match {
- case l: ListQuery =>
+ case _: ListQuery | _: Exists =>
// A ListQuery defines the query which we want to search in an IN subquery expression.
// Currently the only way to evaluate an IN subquery is to convert it to a
// LeftSemi/LeftAnti/ExistenceJoin by `RewritePredicateSubquery` rule.
// It cannot be evaluated as part of a Join operator.
+ // An Exists shouldn't be push into a Join operator too.
--- End diff --
@nsyca @viirya I just verified and the exists test fails the same way in 2.1. So its not a regression.
---
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 #17491: [SQL][WIP] Exists should not be evaluated in Join operat...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on the issue:
https://github.com/apache/spark/pull/17491
retest this please.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #17491: [SQL][WIP] Exists should not be evaluated in Join operat...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/17491
**[Test build #75419 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/75419/testReport)** for PR 17491 at commit [`b012550`](https://github.com/apache/spark/commit/b012550d6981d3b49aac78432dc42e2974fd3649).
---
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 #17491: [SQL][WIP] Exists should not be evaluated in Join operat...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on the issue:
https://github.com/apache/spark/pull/17491
cc @wzhfy @hvanhovell @nsyca
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/17491
**[Test build #75439 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/75439/testReport)** for PR 17491 at commit [`88016bf`](https://github.com/apache/spark/commit/88016bfc5a4fa72f4513fd2e8ec9a439c2d77ee6).
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/17491
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 #17491: [SQL][WIP] Exists should not be evaluated in Join operat...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/17491
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/17491
**[Test build #75687 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/75687/testReport)** for PR 17491 at commit [`329f067`](https://github.com/apache/spark/commit/329f067d1a38469675367f1e29330034f6d923e8).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r109271419
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala ---
@@ -498,3 +498,31 @@ object RewriteCorrelatedScalarSubquery extends Rule[LogicalPlan] {
}
}
}
+
+/**
+ * This rule rewrites a EXISTS predicate sub-queries into an Aggregate with count.
+ * So it doesn't be converted to a JOIN later.
+ */
+object RewriteEmptyExists extends Rule[LogicalPlan] with PredicateHelper {
+ private def containsAgg(plan: LogicalPlan): Boolean = {
+ plan.collect {
+ case a: Aggregate => a
+ }.nonEmpty
+ }
+
+ def apply(plan: LogicalPlan): LogicalPlan = plan transform {
+ case Filter(condition, child) =>
+ val (withSubquery, withoutSubquery) =
+ splitConjunctivePredicates(condition).partition(SubqueryExpression.hasInOrExistsSubquery)
+ val newWithSubquery = withSubquery.map(_.transform {
+ case e @ Exists(sub, conditions, exprId) if conditions.isEmpty && !containsAgg(sub) =>
--- End diff --
Yeah, that is a good idea. I think it definitely will help with an additional `Limit` operator.
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r109186662
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/predicates.scala ---
@@ -90,11 +90,12 @@ trait PredicateHelper {
* Returns true iff `expr` could be evaluated as a condition within join.
*/
protected def canEvaluateWithinJoin(expr: Expression): Boolean = expr match {
- case l: ListQuery =>
+ case _: ListQuery | _: Exists =>
// A ListQuery defines the query which we want to search in an IN subquery expression.
// Currently the only way to evaluate an IN subquery is to convert it to a
// LeftSemi/LeftAnti/ExistenceJoin by `RewritePredicateSubquery` rule.
// It cannot be evaluated as part of a Join operator.
+ // An Exists shouldn't be push into a Join operator too.
--- End diff --
I think ScalarSubquery without correlated references can be pushed. Doesn't it?
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by cloud-fan <gi...@git.apache.org>.
Github user cloud-fan commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r110852640
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala ---
@@ -498,3 +498,32 @@ object RewriteCorrelatedScalarSubquery extends Rule[LogicalPlan] {
}
}
}
+
+/**
+ * This rule rewrites a EXISTS predicate sub-queries into an Aggregate with count.
+ * So it doesn't be converted to a JOIN later.
+ */
+object RewriteEmptyExists extends Rule[LogicalPlan] with PredicateHelper {
+ private def containsAgg(plan: LogicalPlan): Boolean = {
+ plan.collect {
+ case a: Aggregate => a
+ }.nonEmpty
+ }
+
+ def apply(plan: LogicalPlan): LogicalPlan = plan transform {
+ case Filter(condition, child) =>
+ val (withSubquery, withoutSubquery) =
+ splitConjunctivePredicates(condition).partition(SubqueryExpression.hasInOrExistsSubquery)
+ val newWithSubquery = withSubquery.map(_.transform {
+ case e @ Exists(sub, conditions, exprId) if conditions.isEmpty && !containsAgg(sub) =>
+ val countExpr = Alias(Count(Literal(1)).toAggregateExpression(), "count")()
+ val expr = Alias(GreaterThan(countExpr.toAttribute, Literal(0)), e.toString)()
+ ScalarSubquery(
+ Project(Seq(expr),
+ Aggregate(Nil, Seq(countExpr), LocalLimit(Literal(1), sub))),
--- End diff --
How useful is this optimization? It only works when `Exists` has no condition, is that a common case?
I do agree with @nsyca that we should implement "early-out" mechanism, which is more general
---
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 #17491: [SQL][WIP] Exists should not be evaluated in Join operat...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/17491
Merged build finished. Test FAILed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #17491: [SQL][WIP] Exists should not be evaluated in Join...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r109091537
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala ---
@@ -498,3 +498,31 @@ object RewriteCorrelatedScalarSubquery extends Rule[LogicalPlan] {
}
}
}
+
+/**
+ * This rule rewrites a EXISTS predicate sub-queries into an Aggregate with count.
+ * So it doesn't be converted to a JOIN later.
+ */
+object RewriteEmptyExists extends Rule[LogicalPlan] with PredicateHelper {
+ private def containsAgg(plan: LogicalPlan): Boolean = {
+ plan.collect {
+ case a: Aggregate => a
+ }.nonEmpty
+ }
+
+ def apply(plan: LogicalPlan): LogicalPlan = plan transform {
+ case Filter(condition, child) =>
+ val (withSubquery, withoutSubquery) =
+ splitConjunctivePredicates(condition).partition(SubqueryExpression.hasInOrExistsSubquery)
+ val newWithSubquery = withSubquery.map(_.transform {
+ case e @ Exists(sub, conditions, exprId) if conditions.isEmpty && !containsAgg(sub) =>
--- End diff --
Currently take a conservative way, if there is already `Aggregate`, it skips the conversion.
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on the issue:
https://github.com/apache/spark/pull/17491
retest this please.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #17491: [SQL][WIP] Exists should not be evaluated in Join operat...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/17491
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/75419/
Test FAILed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #17491: [SQL][WIP] Exists should not be evaluated in Join operat...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/17491
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/75424/
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/17491
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/75439/
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/17491
@cloud-fan wrote: "How useful is this optimization? It only works when Exists has no condition, is that a common case?"
One of the common cases of this usage is an application of ACL where the application asks the database whether the user has a proper authority to access a certain set of data or not.
Ex:
select ... from controlled_table where exists (select 1 from acl_table where user = CURRENT_USER and role = ...)
From a runtime perspective, an optimal access plan is placing the ACL_TABLE as an outer of a nested-loop join with a semantic to fetch only the first qualified row, once the row exists, continue to process the inner table, CONTROLLED_TABLE, or avoiding access the inner completely if no qualified row from the outer.
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by cloud-fan <gi...@git.apache.org>.
Github user cloud-fan commented on the issue:
https://github.com/apache/spark/pull/17491
LGTM, merging to master!
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/17491
**[Test build #75703 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/75703/testReport)** for PR 17491 at commit [`24ae5ce`](https://github.com/apache/spark/commit/24ae5ce866f82641470ed9598fad9fece450313c).
* 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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/17491
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/75683/
Test FAILed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #17491: [SQL][WIP] Exists should not be evaluated in Join...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r109091845
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala ---
@@ -498,3 +498,31 @@ object RewriteCorrelatedScalarSubquery extends Rule[LogicalPlan] {
}
}
}
+
+/**
+ * This rule rewrites a EXISTS predicate sub-queries into an Aggregate with count.
+ * So it doesn't be converted to a JOIN later.
+ */
+object RewriteEmptyExists extends Rule[LogicalPlan] with PredicateHelper {
+ private def containsAgg(plan: LogicalPlan): Boolean = {
+ plan.collect {
+ case a: Aggregate => a
+ }.nonEmpty
+ }
+
+ def apply(plan: LogicalPlan): LogicalPlan = plan transform {
+ case Filter(condition, child) =>
+ val (withSubquery, withoutSubquery) =
+ splitConjunctivePredicates(condition).partition(SubqueryExpression.hasInOrExistsSubquery)
+ val newWithSubquery = withSubquery.map(_.transform {
+ case e @ Exists(sub, conditions, exprId) if conditions.isEmpty && !containsAgg(sub) =>
--- End diff --
The reason for this conversion is, I assume a count aggregation is cheaper than an existence 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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/17491
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/75687/
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/17491
Merged build finished. Test FAILed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on the issue:
https://github.com/apache/spark/pull/17491
I think the current approach will have a LeftSemi join for this Exists subquery. Is it far from the optimal access plan you said?
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/17491
Merged build finished. Test PASSed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r109190009
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala ---
@@ -498,3 +498,31 @@ object RewriteCorrelatedScalarSubquery extends Rule[LogicalPlan] {
}
}
}
+
+/**
+ * This rule rewrites a EXISTS predicate sub-queries into an Aggregate with count.
+ * So it doesn't be converted to a JOIN later.
+ */
+object RewriteEmptyExists extends Rule[LogicalPlan] with PredicateHelper {
+ private def containsAgg(plan: LogicalPlan): Boolean = {
+ plan.collect {
+ case a: Aggregate => a
+ }.nonEmpty
+ }
+
+ def apply(plan: LogicalPlan): LogicalPlan = plan transform {
+ case Filter(condition, child) =>
+ val (withSubquery, withoutSubquery) =
+ splitConjunctivePredicates(condition).partition(SubqueryExpression.hasInOrExistsSubquery)
+ val newWithSubquery = withSubquery.map(_.transform {
+ case e @ Exists(sub, conditions, exprId) if conditions.isEmpty && !containsAgg(sub) =>
--- End diff --
For a simple count aggregation, I think it is cheap because it should prune columns and no data from the table will be shuffled.
As the Exists has no correlated reference, so the join doesn't have keys and so shuffling required, I think, but as there is no meaningful condition, it will be recognized as cartesian product by optimizer.
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on the issue:
https://github.com/apache/spark/pull/17491
@cloud-fan The optimization rule is removed now. This patch now is just making `Exists` subquery without correlated references work. Please take a look again. Thanks.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r109198895
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala ---
@@ -498,3 +498,31 @@ object RewriteCorrelatedScalarSubquery extends Rule[LogicalPlan] {
}
}
}
+
+/**
+ * This rule rewrites a EXISTS predicate sub-queries into an Aggregate with count.
+ * So it doesn't be converted to a JOIN later.
+ */
+object RewriteEmptyExists extends Rule[LogicalPlan] with PredicateHelper {
+ private def containsAgg(plan: LogicalPlan): Boolean = {
+ plan.collect {
+ case a: Aggregate => a
+ }.nonEmpty
+ }
+
+ def apply(plan: LogicalPlan): LogicalPlan = plan transform {
+ case Filter(condition, child) =>
+ val (withSubquery, withoutSubquery) =
+ splitConjunctivePredicates(condition).partition(SubqueryExpression.hasInOrExistsSubquery)
+ val newWithSubquery = withSubquery.map(_.transform {
+ case e @ Exists(sub, conditions, exprId) if conditions.isEmpty && !containsAgg(sub) =>
--- End diff --
A simple test will tell. If you try to do a count on a 1-billion row table versus `SELECT 1 FROM <tbl> LIMIT 1`. Which one is better?
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/17491
**[Test build #75683 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/75683/testReport)** for PR 17491 at commit [`329f067`](https://github.com/apache/spark/commit/329f067d1a38469675367f1e29330034f6d923e8).
* This patch **fails Spark unit tests**.
* This patch merges cleanly.
* This patch adds no public classes.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #17491: [SQL][WIP] Exists should not be evaluated in Join operat...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/17491
**[Test build #75423 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/75423/testReport)** for PR 17491 at commit [`b012550`](https://github.com/apache/spark/commit/b012550d6981d3b49aac78432dc42e2974fd3649).
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/17491
**[Test build #75703 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/75703/testReport)** for PR 17491 at commit [`24ae5ce`](https://github.com/apache/spark/commit/24ae5ce866f82641470ed9598fad9fece450313c).
---
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 #17491: [SPARK-20175][SQL] Exists should not be evaluated in Joi...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/17491
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/75703/
Test PASSed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #17491: [SPARK-20175][SQL] Exists should not be evaluated...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on a diff in the pull request:
https://github.com/apache/spark/pull/17491#discussion_r110859934
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala ---
@@ -498,3 +498,32 @@ object RewriteCorrelatedScalarSubquery extends Rule[LogicalPlan] {
}
}
}
+
+/**
+ * This rule rewrites a EXISTS predicate sub-queries into an Aggregate with count.
+ * So it doesn't be converted to a JOIN later.
+ */
+object RewriteEmptyExists extends Rule[LogicalPlan] with PredicateHelper {
+ private def containsAgg(plan: LogicalPlan): Boolean = {
+ plan.collect {
+ case a: Aggregate => a
+ }.nonEmpty
+ }
+
+ def apply(plan: LogicalPlan): LogicalPlan = plan transform {
+ case Filter(condition, child) =>
+ val (withSubquery, withoutSubquery) =
+ splitConjunctivePredicates(condition).partition(SubqueryExpression.hasInOrExistsSubquery)
+ val newWithSubquery = withSubquery.map(_.transform {
+ case e @ Exists(sub, conditions, exprId) if conditions.isEmpty && !containsAgg(sub) =>
+ val countExpr = Alias(Count(Literal(1)).toAggregateExpression(), "count")()
+ val expr = Alias(GreaterThan(countExpr.toAttribute, Literal(0)), e.toString)()
+ ScalarSubquery(
+ Project(Seq(expr),
+ Aggregate(Nil, Seq(countExpr), LocalLimit(Literal(1), sub))),
--- End diff --
Btw, I am not very sure this early-out can benefit the general usage, except for this kind of special case.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #17491: [SQL][WIP] Exists should not be evaluated in Join operat...
Posted by viirya <gi...@git.apache.org>.
Github user viirya commented on the issue:
https://github.com/apache/spark/pull/17491
retest this please.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #17491: [SQL][WIP] Exists should not be evaluated in Join operat...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/17491
**[Test build #75424 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/75424/testReport)** for PR 17491 at commit [`b012550`](https://github.com/apache/spark/commit/b012550d6981d3b49aac78432dc42e2974fd3649).
---
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