You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by nsyca <gi...@git.apache.org> on 2016/11/04 04:30:44 UTC
[GitHub] spark pull request #15763: [SPARK-17348][SQL] Incorrect results from subquer...
GitHub user nsyca opened a pull request:
https://github.com/apache/spark/pull/15763
[SPARK-17348][SQL] Incorrect results from subquery transformation
## What changes were proposed in this pull request?
Return an Analysis exception when there is a correlated non-equality predicate in a subquery and the correlated column from the outer reference is not from the immediate parent operator of the subquery. This PR prevents incorrect results from subquery transformation in such case.
Test cases, both positive and negative tests, are added.
## How was this patch tested?
sql/test, catalyst/test, hive/test, and scenarios that will produce incorrect results without this PR and product correct results when subquery transformation does happen.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/nsyca/spark spark-17348
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/spark/pull/15763.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 #15763
----
commit b98865127a39bde885f9b1680cfe608629d59d51
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-07-29T21:43:56Z
[SPARK-16804][SQL] Correlated subqueries containing LIMIT return incorrect results
## What changes were proposed in this pull request?
This patch fixes the incorrect results in the rule ResolveSubquery in Catalyst's Analysis phase.
## How was this patch tested?
./dev/run-tests
a new unit test on the problematic pattern.
commit 069ed8f8e5f14dca7a15701945d42fc27fe82f3c
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-07-29T21:50:02Z
[SPARK-16804][SQL] Correlated subqueries containing LIMIT return incorrect results
## What changes were proposed in this pull request?
This patch fixes the incorrect results in the rule ResolveSubquery in Catalyst's Analysis phase.
## How was this patch tested?
./dev/run-tests
a new unit test on the problematic pattern.
commit edca333c081e6d4e53a91b496fba4a3ef4ee89ac
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-07-30T00:28:15Z
New positive test cases
commit 64184fdb77c1a305bb2932e82582da28bb4c0e53
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-01T13:20:09Z
Fix unit test case failure
commit 29f82b05c9e40e7934397257c674b260a8e8a996
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-05T17:42:01Z
blocking TABLESAMPLE
commit ac43ab47907a1ccd6d22f920415fbb4de93d4720
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-05T21:10:19Z
Fixing code styling
commit 631d396031e8bf627eb1f4872a4d3a17c144536c
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-07T18:39:44Z
Correcting Scala test style
commit 7eb9b2dbba3633a1958e38e0019e3ce816300514
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-08T02:31:09Z
One (last) attempt to correct the Scala style tests
commit 1387cf51541408ac20048064fa5e559836af932c
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-12T20:11:50Z
Merge remote-tracking branch 'upstream/master'
commit 6d9bade4df8954987078c479274d90a7612cc772
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-04T03:51:54Z
Merge remote-tracking branch 'upstream/master'
commit baf0e6084a838ce2d72eeeac9d7618ae4536ffb6
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-04T03:54:24Z
First version of code+test cases
----
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15763
@hvanhovell could you please review the PR again? Hopefully this is the last one.
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on a diff in the pull request:
https://github.com/apache/spark/pull/15763#discussion_r86600741
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1044,6 +1044,34 @@ class Analyzer(
failOnOuterReference(p)
p
}
+
+ // SPARK-17348
+ // Looking for a potential incorrect result case.
+ // When a correlated predicate is a non-equality predicate
+ // it must be placed at the immediate child operator.
+ // Otherwise, the pull up of the correlated predicate
+ // will generate a plan with a different semantics
+ // which could return incorrect result.
+ var continue : Boolean = true
--- End diff --
those are not aggregates right? I am saying that we should ban cases in which we pull out a predicate from an aggregate for example `t1.c2 >= t2.c2` in the following example:
```sql
select t1.c1
from t1
where t1.c1 in (select max(t2.c1)
from t2
where t1.c2 >= t2.c2)
```
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/68142/
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15763
**[Test build #68142 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/68142/consoleFull)** for PR 15763 at commit [`217c0e9`](https://github.com/apache/spark/commit/217c0e955b55d10dd462e077d67097704ab86f61).
* 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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/68147/
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15763
**[Test build #68621 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/68621/consoleFull)** for PR 15763 at commit [`84ef128`](https://github.com/apache/spark/commit/84ef1281d64dc327e77c1ee1172751cde2d1ee9f).
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15763
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 pull request #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/15763#discussion_r86790230
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1044,6 +1044,34 @@ class Analyzer(
failOnOuterReference(p)
p
}
+
+ // SPARK-17348
+ // Looking for a potential incorrect result case.
+ // When a correlated predicate is a non-equality predicate
+ // it must be placed at the immediate child operator.
+ // Otherwise, the pull up of the correlated predicate
+ // will generate a plan with a different semantics
+ // which could return incorrect result.
+ var continue : Boolean = true
--- End diff --
@rxin, Shall we continue the discussion of the general algorithm to resolve the correlated subquery issue in a new thread? Would you be interested in putting this item into 2.2?
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15763
**[Test build #68122 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/68122/consoleFull)** for PR 15763 at commit [`baf0e60`](https://github.com/apache/spark/commit/baf0e6084a838ce2d72eeeac9d7618ae4536ffb6).
* 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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
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 #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on a diff in the pull request:
https://github.com/apache/spark/pull/15763#discussion_r86859510
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1044,6 +1044,34 @@ class Analyzer(
failOnOuterReference(p)
p
}
+
+ // SPARK-17348
+ // Looking for a potential incorrect result case.
+ // When a correlated predicate is a non-equality predicate
+ // it must be placed at the immediate child operator.
+ // Otherwise, the pull up of the correlated predicate
+ // will generate a plan with a different semantics
+ // which could return incorrect result.
+ var continue : Boolean = true
--- End diff --
@nsyca thanks for the explanation of how DB2 works with subqueries. A different perspective or approach can be very helpful; we all suffer from myopia at some point. It most certainly has merit to add a general node for subquery processing to Spark. Do you have time to work on this for Spark 2.2?
I would also like to take the opportunity to explain why we do so much rewriting during analysis. We wanted support the following use case:
```sql
-- hive: subquery_exists_having.q
select b.key, min(b.value)
from src b
group by b.key
having exists ( select a.key
from src a
where a.value > 'val_9' and a.value = min(b.value)
)
```
The difficulty here is that we need to evaluate the `min(b.value)` in the aggregate. So we needed a way to extract the entire `min(b.value)` expression. The most straightforward way was to extract the entire predicate and rewrite the tree in the process. This is quite an aggressive approach, and it breaks as soon as you cannot/should not move the predicate. In hindsight it might have been better to isolate the entire outer expression instead of only isolating the outer reference, and to do the rewriting in a later stage.
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15763
Hmm. don't know what's wrong with the test failures reported in hive/test here. I will address @rxin's comment and submit a new version. That should trigger a new build.
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/15763
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 pull request #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/15763#discussion_r86911547
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1044,6 +1044,34 @@ class Analyzer(
failOnOuterReference(p)
p
}
+
+ // SPARK-17348
+ // Looking for a potential incorrect result case.
+ // When a correlated predicate is a non-equality predicate
+ // it must be placed at the immediate child operator.
+ // Otherwise, the pull up of the correlated predicate
+ // will generate a plan with a different semantics
+ // which could return incorrect result.
+ var continue : Boolean = true
+ for (pm <- predicateMap if continue) {
+ assert(pm._2.nonEmpty, "Correlated predicate(s) does not exist.")
+ for (p <- pm._2 if continue)
+ p match {
+ case EqualTo(_, _) | EqualNullSafe(_, _) =>
+ None
+ case _ =>
+ assert(transformed.children.nonEmpty)
+ if (!(transformed.isInstanceOf[Project]) ||
--- End diff --
@hvanhovell The key point of this PR is on the second condition, not the check for the `Project`. Its intention is if the correlated predicate is not at the immediate child, we are about to pull the correlated predicate up through a number of operations. It is very hard to prove that which operations we can pull the predicate through without changing the meaning of the original SQL. Yes, there are cases that it may regress, like the example @srinathshankar posted.
I do not understand your second comment on putting in the previous block. Could you please explain more?
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on a diff in the pull request:
https://github.com/apache/spark/pull/15763#discussion_r87814081
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1069,11 +1110,19 @@ class Analyzer(
case a @ Aggregate(grouping, expressions, child) =>
failOnOuterReference(a)
val referencesToAdd = missingReferences(a)
+ if (foundNonEqualCorrelatedPred) {
--- End diff --
Move the `if (foundNonEqualCorrelatedPred)` into the `failOnNonEqualCorrelatedPredicate` function
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15763
**[Test build #68142 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/68142/consoleFull)** for PR 15763 at commit [`217c0e9`](https://github.com/apache/spark/commit/217c0e955b55d10dd462e077d67097704ab86f61).
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15763
Thanks for the tip on the code. I will work on 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 issue #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15763
**[Test build #68623 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/68623/consoleFull)** for PR 15763 at commit [`9def9a7`](https://github.com/apache/spark/commit/9def9a70cf9dc5517d3cfb9d50611f68521e0854).
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on a diff in the pull request:
https://github.com/apache/spark/pull/15763#discussion_r87813912
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1041,12 +1070,24 @@ class Analyzer(
localPredicateReferences -- p.outputSet
}
+ var foundNonEqualCorrelatedPred : Boolean = false
+
// Simplify the predicates before pulling them out.
val transformed = BooleanSimplification(sub) transformUp {
case f @ Filter(cond, child) =>
// Find all predicates with an outer reference.
val (correlated, local) = splitConjunctivePredicates(cond).partition(containsOuter)
+ // Find any non-equality correlated predicates
--- End diff --
This is a bit more concise and will terminate as soon as we find a non-equal predicate.
```scala
foundNonEqualCorrelatedPred = foundNonEqualCorrelatedPred || correlated.exists {
case _: EqualTo | _: EqualNullSafe => false
case _ => true
}
```
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/15763
@nsyca you can retrigger builds by issuing `retest this please` this works as soon as an `ok to test` has been issued by an admin.
It might be a good idea to extend the logic in CheckAnalysis to also cover this case, that might make this more concise.
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/68623/
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/15763
I think it is also better that we start whitelisting operators instead blacklisting them.
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15763
I'd like to a note that a piece of existing code that closes to address this problem but only done for the scalar subquery context can be found by searching this pattern in CheckAnalysis.scala (line 126):
_"The correlated scalar subquery can only contain equality predicates:"_
An existing test coverage for that scenario is at
_SubquerySuite/non-equal correlated scalar subquery_
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/15763#discussion_r86603248
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1044,6 +1044,34 @@ class Analyzer(
failOnOuterReference(p)
p
}
+
+ // SPARK-17348
+ // Looking for a potential incorrect result case.
+ // When a correlated predicate is a non-equality predicate
+ // it must be placed at the immediate child operator.
+ // Otherwise, the pull up of the correlated predicate
+ // will generate a plan with a different semantics
+ // which could return incorrect result.
+ var continue : Boolean = true
--- End diff --
That scenario above is banned with this PR (and added as a new, negative, test case). However, the PR has a greater scope, Window function is another operator, we cannot pull up any correlated predicates, such as the example below:
// Case 2: window func
Seq((2,1)).toDF("c1","c2").createOrReplaceTempView("t1")
Seq((1,1),(2,2)).toDF("c1","c2").createOrReplaceTempView("t2")
sql("select c1 from t1 where c1 in (select max(t2.c1) over () from t2 where t1.c2 >= t2.c2)").show
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by rxin <gi...@git.apache.org>.
Github user rxin commented on a diff in the pull request:
https://github.com/apache/spark/pull/15763#discussion_r86659812
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1044,6 +1044,34 @@ class Analyzer(
failOnOuterReference(p)
p
}
+
+ // SPARK-17348
+ // Looking for a potential incorrect result case.
+ // When a correlated predicate is a non-equality predicate
+ // it must be placed at the immediate child operator.
+ // Otherwise, the pull up of the correlated predicate
+ // will generate a plan with a different semantics
+ // which could return incorrect result.
+ var continue : Boolean = true
--- End diff --
Well there is also this http://www.btw-2015.de/res/proceedings/Hauptband/Wiss/Neumann-Unnesting_Arbitrary_Querie.pdf
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15763
**[Test build #68147 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/68147/consoleFull)** for PR 15763 at commit [`217c0e9`](https://github.com/apache/spark/commit/217c0e955b55d10dd462e077d67097704ab86f61).
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/68551/
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/68170/
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/15763
@nsyca it should be relatively straighforward to implement this here: https://github.com/apache/spark/blob/master/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala#L1045-L1111
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/15763#discussion_r87823864
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1069,11 +1110,19 @@ class Analyzer(
case a @ Aggregate(grouping, expressions, child) =>
failOnOuterReference(a)
val referencesToAdd = missingReferences(a)
+ if (foundNonEqualCorrelatedPred) {
+ failOnNonEqualCorrelatedPredicate(a)
+ }
if (referencesToAdd.nonEmpty) {
Aggregate(grouping ++ referencesToAdd, expressions ++ referencesToAdd, child)
} else {
a
}
+ case w : Window =>
+ if (foundNonEqualCorrelatedPred) {
+ failOnNonEqualCorrelatedPredicate(w)
+ }
+ w
--- End diff --
We could do that if all the correlated predicates are equality predicates. It is as if the computation of the subquery is bound by each value from the outer reference and therefore can be bound in the PARTITION BY clause of a Window function.
I guess we want to implement this transformation outside of this PR, preferrably in Optimizer phase.
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on a diff in the pull request:
https://github.com/apache/spark/pull/15763#discussion_r86859677
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1044,6 +1044,34 @@ class Analyzer(
failOnOuterReference(p)
p
}
+
+ // SPARK-17348
+ // Looking for a potential incorrect result case.
+ // When a correlated predicate is a non-equality predicate
+ // it must be placed at the immediate child operator.
+ // Otherwise, the pull up of the correlated predicate
+ // will generate a plan with a different semantics
+ // which could return incorrect result.
+ var continue : Boolean = true
+ for (pm <- predicateMap if continue) {
+ assert(pm._2.nonEmpty, "Correlated predicate(s) does not exist.")
+ for (p <- pm._2 if continue)
+ p match {
+ case EqualTo(_, _) | EqualNullSafe(_, _) =>
+ None
+ case _ =>
+ assert(transformed.children.nonEmpty)
+ if (!(transformed.isInstanceOf[Project]) ||
--- End diff --
Do you think we can put this in the previous block? That might be easier.
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/15763
ok to test
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/15763
LGTM. Merging to master/2.1/2.0. Thanks!
@nsyca could you open a JIRA discuss a generic subquery 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 issue #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
Can one of the admins verify this patch?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15763
@hvanhovell Then we will need to walk from the top of the operator hosting the outer reference to the operator hosting the correlation to ensure there is no Aggregate or Window operator.
If this is the algorithm you have in mind, I will see how I could implement it in an effective way.
Examples are:
````
// Case 1: agg func
Seq((1,1)).toDF("c1","c2").createOrReplaceTempView("t1")
Seq((1,1),(2,0)).toDF("c1","c2").createOrReplaceTempView("t2")
// Case 1A: Simplest case Aggregate is immediately under Project and over the correlation point
sql("""
| select t1.c1
| from t1
| where t1.c1 in (select max(t2.c1)
| from t2 where t1.c2 >= t2.c2)
""".stripMargin).show
// Case 1B: Project - Filter - Aggregate - [correlation point]
// Project is the operator immediately over T1, the source of the outer reference
// There is a Filter (hosting the HAVING logic) in between
sql("""
| select t1.c1
| from t1
| where t1.c1 in (select max(t2.c1)
| from t2
| where t1.c2 >= t2.c2
| having count(*) > 0 )
""".stripMargin).show
// And the list can go on and on such as this one that
// Case 1C: Insert a layer of SELECT on top of Case 1B
sql("""
| select t1.c1
| from t1
| where t1.c1 in (select maxc+1
| from (select max(t2.c1) as maxc
| from t2
| where t1.c2 >= t2.c2
| having count(*) > 0 ))
""".stripMargin).show
````
````
// Here is the LogicalPlan at the point of checking for Case 1C
Project [(maxc#161 + 1) AS (maxc + 1)#167, c2#103] <-- This is the Project hosting the outer reference T1.C1
+- Project [maxc#161, c2#103]
+- Filter (count(1)#166L > cast(0 as bigint))
+- Aggregate [c2#103], [max(c1#102) AS maxc#161, count(1) AS count(1)#166L, c2#103]
+- SubqueryAlias t2, `t2` <-- The boundary of Subquery
+- Project [_1#99 AS c1#102, _2#100 AS c2#103]
+- LocalRelation [_1#99, _2#100]
````
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by srinathshankar <gi...@git.apache.org>.
Github user srinathshankar commented on the issue:
https://github.com/apache/spark/pull/15763
As it stands the check seems overly aggressive. The following works:
```sql
select t1.c1
from t1
where t1.c1 in (
select c1 + 1 as col
from t2
where t1.c2 < t2.c2)
```
```
scala> sql("select t1.c1 from t1 where t1.c1 in (select c1 + 1 as col from t2 where t1.c2 < t2.c2)").show()
+---+
| c1|
+---+
+---+
```
But the following, when I wrap the subselect with a project, does not
```sql
select t1.c1
from t1
where t1.c1 in (
select col + 1
from (
select c1 as col
from t2
where t1.c2 < t2.c2))
```
```
scala> sql("select t1.c1 from t1 where t1.c1 in (select col + 1 from (select c1 as col from t2 where t1.c2 < t2.c2))").show()
org.apache.spark.sql.AnalysisException: Correlated column is not allowed in a non-equality predicate:
Project [(col#29 + 1) AS (col + 1)#32]
+- Project [(c1#16 + 1) AS col#29]
+- Filter (outer(c2#6) < c2#17)
+- SubqueryAlias t2
+- Project [_1#13 AS c1#16, _2#14 AS c2#17]
+- LocalRelation [_1#13, _2#14]
;
```
Is this intentional ?
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/15763
@nsyca I feel that the current approach is to restrictive. I would prefer to just close the gap for Window and Aggregate.
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15763
**[Test build #68551 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/68551/consoleFull)** for PR 15763 at commit [`7722503`](https://github.com/apache/spark/commit/7722503e68981f340e8a44aa91d5738b6d6a6b2a).
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca closed the pull request at:
https://github.com/apache/spark/pull/15763
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15763
**[Test build #68147 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/68147/consoleFull)** for PR 15763 at commit [`217c0e9`](https://github.com/apache/spark/commit/217c0e955b55d10dd462e077d67097704ab86f61).
* 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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15763
**[Test build #68621 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/68621/consoleFull)** for PR 15763 at commit [`84ef128`](https://github.com/apache/spark/commit/84ef1281d64dc327e77c1ee1172751cde2d1ee9f).
* 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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/68621/
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15763
@hvanhovell ping... Are there anything I need to do to close this incorrect result problem?
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by nsyca <gi...@git.apache.org>.
GitHub user nsyca reopened a pull request:
https://github.com/apache/spark/pull/15763
[SPARK-17348][SQL] Incorrect results from subquery transformation
## What changes were proposed in this pull request?
Return an Analysis exception when there is a correlated non-equality predicate in a subquery and the correlated column from the outer reference is not from the immediate parent operator of the subquery. This PR prevents incorrect results from subquery transformation in such case.
Test cases, both positive and negative tests, are added.
## How was this patch tested?
sql/test, catalyst/test, hive/test, and scenarios that will produce incorrect results without this PR and product correct results when subquery transformation does happen.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/nsyca/spark spark-17348
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/spark/pull/15763.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 #15763
----
commit b98865127a39bde885f9b1680cfe608629d59d51
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-07-29T21:43:56Z
[SPARK-16804][SQL] Correlated subqueries containing LIMIT return incorrect results
## What changes were proposed in this pull request?
This patch fixes the incorrect results in the rule ResolveSubquery in Catalyst's Analysis phase.
## How was this patch tested?
./dev/run-tests
a new unit test on the problematic pattern.
commit 069ed8f8e5f14dca7a15701945d42fc27fe82f3c
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-07-29T21:50:02Z
[SPARK-16804][SQL] Correlated subqueries containing LIMIT return incorrect results
## What changes were proposed in this pull request?
This patch fixes the incorrect results in the rule ResolveSubquery in Catalyst's Analysis phase.
## How was this patch tested?
./dev/run-tests
a new unit test on the problematic pattern.
commit edca333c081e6d4e53a91b496fba4a3ef4ee89ac
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-07-30T00:28:15Z
New positive test cases
commit 64184fdb77c1a305bb2932e82582da28bb4c0e53
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-01T13:20:09Z
Fix unit test case failure
commit 29f82b05c9e40e7934397257c674b260a8e8a996
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-05T17:42:01Z
blocking TABLESAMPLE
commit ac43ab47907a1ccd6d22f920415fbb4de93d4720
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-05T21:10:19Z
Fixing code styling
commit 631d396031e8bf627eb1f4872a4d3a17c144536c
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-07T18:39:44Z
Correcting Scala test style
commit 7eb9b2dbba3633a1958e38e0019e3ce816300514
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-08T02:31:09Z
One (last) attempt to correct the Scala style tests
commit 1387cf51541408ac20048064fa5e559836af932c
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-08-12T20:11:50Z
Merge remote-tracking branch 'upstream/master'
commit 6d9bade4df8954987078c479274d90a7612cc772
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-04T03:51:54Z
Merge remote-tracking branch 'upstream/master'
commit baf0e6084a838ce2d72eeeac9d7618ae4536ffb6
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-04T03:54:24Z
First version of code+test cases
----
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15763
**[Test build #68170 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/68170/consoleFull)** for PR 15763 at commit [`1c1864c`](https://github.com/apache/spark/commit/1c1864caa764130f947be9ccd2b132d4ac75ec2d).
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15763
I think I have addressed all the pending comments on this PR. Would be there anything left to do?
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15763
@hvanhovell
I removed the redundant checking in `ScalarSubquery` as pointed out in your earlier comment (copied below).
"I was also wondering if we still need the following checkAnalysis lines: https://github.com/apache/spark/blob/master/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala#L122-L127"
I will submit another PR for this work, including updating some existing test result.
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15763
**[Test build #68623 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/68623/consoleFull)** for PR 15763 at commit [`9def9a7`](https://github.com/apache/spark/commit/9def9a70cf9dc5517d3cfb9d50611f68521e0854).
* 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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15763
**[Test build #68122 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/68122/consoleFull)** for PR 15763 at commit [`baf0e60`](https://github.com/apache/spark/commit/baf0e6084a838ce2d72eeeac9d7618ae4536ffb6).
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/68122/
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15763
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15763
**[Test build #68170 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/68170/consoleFull)** for PR 15763 at commit [`1c1864c`](https://github.com/apache/spark/commit/1c1864caa764130f947be9ccd2b132d4ac75ec2d).
* 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 #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by rxin <gi...@git.apache.org>.
Github user rxin commented on a diff in the pull request:
https://github.com/apache/spark/pull/15763#discussion_r86489433
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -977,6 +977,13 @@ class Analyzer(
localPredicateReferences -- p.outputSet
}
+ // SPARK-17348
+ // Report a non-supported case where there exists a correlated predicate
+ // that is not an equality predicate and the outer reference of the correlated predicate
+ // is not at the immediate parent operator.
+ def failOnNonEqualityPredicate(p: LogicalPlan): Unit = {
--- End diff --
why define this since it is used only once?
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15763
@hvanhovell thank you for the trick.
Exploring your idea further, I looked at CheckAnalysis. This is a trait that Analyzer extends from. The checks in def CheckAnalysis of trait CheckAnalysis walk the operators in a LogicalPlan tree and look for violations specified to each operator type. The checks in Analyzer/ResolveSubquery has the context of a subquery, specifically the connect of those correlated predicates in the subquery, and look at everything from the subquery's viewpoint to see if there exists any violation then report an exception. I guess finding a correlated subquery for each operator type and flagging a violation is more expensive than here we having a correlated subquery, making sure no violation.
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on a diff in the pull request:
https://github.com/apache/spark/pull/15763#discussion_r87814868
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1069,11 +1110,19 @@ class Analyzer(
case a @ Aggregate(grouping, expressions, child) =>
failOnOuterReference(a)
val referencesToAdd = missingReferences(a)
+ if (foundNonEqualCorrelatedPred) {
+ failOnNonEqualCorrelatedPredicate(a)
+ }
if (referencesToAdd.nonEmpty) {
Aggregate(grouping ++ referencesToAdd, expressions ++ referencesToAdd, child)
} else {
a
}
+ case w : Window =>
+ if (foundNonEqualCorrelatedPred) {
+ failOnNonEqualCorrelatedPredicate(w)
+ }
+ w
--- End diff --
Should we rewrite the aggregates in the window function? For example:
```sql
select *
from tbl_x
where value in (select max() over() from tbl_y where tbl_y.id = tbl_x.id)
```
Should we rewrite `max() over()` into `max() over(partition by tbl_y.id)`?
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15763
@srinathshankar It is intentional. It is impossible to do an analysis on what in-between operations we can allow and what we cannot. Correlated predicates can be placed in any arbitrary level of depth in a subquery. Spark may not support more than one level of correlations today but it may in the next version. So my argument is if we cannot proof that pulling up a correlated predicate thru any operations will still preserve its original semantics, then we should not do it. The paper @rxin mentioned in the previous note (http://www.btw-2015.de/res/proceedings/Hauptband/Wiss/Neumann-Unnesting_Arbitrary_Querie.pdf) makes this claim in the paragraph after Q2 in page 2.
This PR is not a full solution. It is intended to be a temporary stop-gap solution to close off the incorrect result cases Spark exposes today. It could be argued that your example above is a regression but the statement can be rewritten to make it work by collapsing the 2 levels of subselects. It is harder to implement a solution that will walk the whole plan tree of a subquery to determine which operations are fine to let the correlated predicate pulled through.
A permanent solution, as I proposed in one of my comments above, is to separate the transformation of any correlated predicates to Optimizer phase and leave Analyzer phase to just resolve the references and validate the input SQL is valid. This way, the two subselects in your example will probably be merged and then the correlated predicate pull up follows.
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on a diff in the pull request:
https://github.com/apache/spark/pull/15763#discussion_r86612914
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1044,6 +1044,34 @@ class Analyzer(
failOnOuterReference(p)
p
}
+
+ // SPARK-17348
+ // Looking for a potential incorrect result case.
+ // When a correlated predicate is a non-equality predicate
+ // it must be placed at the immediate child operator.
+ // Otherwise, the pull up of the correlated predicate
+ // will generate a plan with a different semantics
+ // which could return incorrect result.
+ var continue : Boolean = true
--- End diff --
I am wondering if we should support `Window` at all. We can pull equals through `Window`, but that sounds pretty bizarre. What does DB2 support?
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15763
**[Test build #68551 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/68551/consoleFull)** for PR 15763 at commit [`7722503`](https://github.com/apache/spark/commit/7722503e68981f340e8a44aa91d5738b6d6a6b2a).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds the following public classes _(experimental)_:
* `public class AesCipher `
* `public class AesConfigMessage implements Encodable `
* `public class ByteArrayReadableChannel implements ReadableByteChannel `
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/15763#discussion_r86653844
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1044,6 +1044,34 @@ class Analyzer(
failOnOuterReference(p)
p
}
+
+ // SPARK-17348
+ // Looking for a potential incorrect result case.
+ // When a correlated predicate is a non-equality predicate
+ // it must be placed at the immediate child operator.
+ // Otherwise, the pull up of the correlated predicate
+ // will generate a plan with a different semantics
+ // which could return incorrect result.
+ var continue : Boolean = true
--- End diff --
One technique that I know of being used to transform correlation queries to queries with no correlation is outlined in this 1996 IEEE Data Engineering paper.
Complex query decorrelation
P. Seshadri; H. Pirahesh; T. Y. C. Leung
Data Engineering, 1996. Proceedings of the Twelfth International Conference on
Pages: 450 - 458
Distributed systems aggravate the performance impact of correlated queries from the movement of the entire data set of the subqueries to where the data of the outer tables reside. This processing is similar to the `BroadcastNestedLoopJoinExec` in Spark.
The idea behind the paper is to build a duplicate portion of the outer tables and de-correlate the original subquery by joining the duplicate portion within the subquery. The algorithm is claimed to be generic and can be applied to all forms of correlations, both shallow correlation where the correlated point is immediately below the operation over the outer table(s), and deep correlation, where the correlated point is at arbitrary level below the operation over the outer tables.
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquer...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/spark/pull/15763
---
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 #15763: [SPARK-17348][SQL] Incorrect results from subquery trans...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15763
@hvanhovell could you please review the latest PR?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org