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/18 22:40:46 UTC
[GitHub] spark pull request #15936: [SPARK-18504][SQL] Scalar subquery with extra gro...
GitHub user nsyca opened a pull request:
https://github.com/apache/spark/pull/15936
[SPARK-18504][SQL] Scalar subquery with extra group by columns returning incorrect result
## What changes were proposed in this pull request?
This PR blocks an incorrect result scenario in scalar subquery where there are GROUP BY column(s)
that are not part of the correlated predicate(s).
Example:
// Incorrect result
Seq(1).toDF("c1").createOrReplaceTempView("t1")
Seq((1,1),(1,2)).toDF("c1","c2").createOrReplaceTempView("t2")
sql("select (select sum(-1) from t2 where t1.c1=t2.c1 group by t2.c2) from t1").show
// How can selecting a scalar subquery from a 1-row table return 2 rows?
## How was this patch tested?
sql/test, catalyst/test
new test case covering the reported problem is added to SubquerySuite.scala
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/nsyca/spark scalarSubqueryIncorrect-1
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/spark/pull/15936.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 #15936
----
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
commit 4e6d99b92cd0908856371569479debc72e03703c
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-04T16:14:03Z
Address rxin's comment: inline the call to report Analyzer exception
commit 9a1f80b12cdc9857f4b906688f8691a2db502fa5
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-04T16:14:25Z
Merge remote-tracking branch 'upstream/master'
commit 217c0e955b55d10dd462e077d67097704ab86f61
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-04T16:14:41Z
Merge branch 'master' into spark-17348
commit 3fe9429c009eb156ac89ef6732e9230d583ed5d0
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-05T00:52:45Z
Merge remote-tracking branch 'upstream/master'
commit 1c1864caa764130f947be9ccd2b132d4ac75ec2d
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-05T00:56:34Z
Merge with master
commit 0757b8134316f8b5c87ef1c023966304228a0eeb
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-11T16:31:36Z
Merge remote-tracking branch 'upstream/master'
commit 89bb31c10314cca2473568716649f80f5e28781f
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-11T16:31:55Z
Merge branch 'master' into spark-17348
commit 35b77f0ca477bf6427e18588c4514a3f0209f426
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-12T03:36:14Z
Merge remote-tracking branch 'upstream/master'
commit c63b8c627cb13253b3776aec57b8a73d685d7bd1
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-14T15:29:09Z
Merge remote-tracking branch 'upstream/master'
commit f3351d5aba8b5b52f5e1b12a8e068e0d4a4ece08
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-18T17:03:23Z
Merge remote-tracking branch 'upstream/master'
commit 61880e42a5ca9be84b488ff05cc98cc74a31ba9f
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-18T17:03:48Z
Merge branch 'master' into scalarSubqueryIncorrect-1
commit b05353ebe55cf8fee4d3f2e10f291a662c43909e
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-18T21:15:48Z
Fix scalar subquery bug (SPARK-18504)
commit 9fc5c3305f7c23593b1ef93a43fd266b2d5bed5a
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-18T22:31:30Z
Merge remote-tracking branch 'upstream/master'
commit 452f5d08c9017c2f08455c48bd76bf14c2f1d5fe
Author: Nattavut Sutyanyong <ns...@gmail.com>
Date: 2016-11-18T22:31:49Z
Merge branch 'master' into scalarSubqueryIncorrect-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 issue #15936: [SPARK-18504][SQL] Scalar subquery with extra group by c...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15936
Should I say `retest this please` to kick off the regression test?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #15936: [SPARK-18504][SQL] Scalar subquery with extra gro...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/15936#discussion_r89155790
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala ---
@@ -117,19 +117,36 @@ trait CheckAnalysis extends PredicateHelper {
failAnalysis(s"Window specification $s is not valid because $m")
case None => w
}
+ case s @ ScalarSubquery(query, conditions, _)
+ // If no correlation, the output must be exactly one column
+ if (conditions.isEmpty && query.output.size != 1) =>
+ failAnalysis(
+ s"Scalar subquery must return only one column, but got ${query.output.size}")
case s @ ScalarSubquery(query, conditions, _) if conditions.nonEmpty =>
- // Make sure correlated scalar subqueries contain one row for every outer row by
- // enforcing that they are aggregates which contain exactly one aggregate expressions.
- // The analyzer has already checked that subquery contained only one output column, and
- // added all the grouping expressions to the aggregate.
- def checkAggregate(a: Aggregate): Unit = {
- val aggregates = a.expressions.flatMap(_.collect {
+ def checkAggregate(agg: Aggregate): Unit = {
+ // Make sure correlated scalar subqueries contain one row for every outer row by
+ // enforcing that they are aggregates which contain exactly one aggregate expressions.
+ // The analyzer has already checked that subquery contained only one output column,
+ // and added all the grouping expressions to the aggregate.
+ val aggregates = agg.expressions.flatMap(_.collect {
case a: AggregateExpression => a
})
if (aggregates.isEmpty) {
failAnalysis("The output of a correlated scalar subquery must be aggregated")
}
+
+ // SPARK-18504: block cases where GROUP BY columns
+ // are not part of the correlated columns
+ val groupByCols = ExpressionSet.apply(agg.groupingExpressions.flatMap(_.references))
+ val predicateCols = ExpressionSet.apply(conditions.flatMap(_.references))
+ val invalidCols = groupByCols.diff(predicateCols)
+ // GROUP BY columns must be a subset of columns in the predicates
+ if (invalidCols.nonEmpty) {
+ failAnalysis(s"""
+ |GROUP BY column(s) in scalar subquery must exist in the WHERE clause:
+ |${invalidCols.toString}""".stripMargin.replaceAll("\n", " "))
--- End diff --
Right. Thank you.
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra group by c...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/15936
LGTM. Merging to master/2.1/2.0.
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra gro...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/15936#discussion_r89159852
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala ---
@@ -117,19 +117,36 @@ trait CheckAnalysis extends PredicateHelper {
failAnalysis(s"Window specification $s is not valid because $m")
case None => w
}
+ case s @ ScalarSubquery(query, conditions, _)
+ // If no correlation, the output must be exactly one column
+ if (conditions.isEmpty && query.output.size != 1) =>
+ failAnalysis(
+ s"Scalar subquery must return only one column, but got ${query.output.size}")
case s @ ScalarSubquery(query, conditions, _) if conditions.nonEmpty =>
- // Make sure correlated scalar subqueries contain one row for every outer row by
- // enforcing that they are aggregates which contain exactly one aggregate expressions.
- // The analyzer has already checked that subquery contained only one output column, and
- // added all the grouping expressions to the aggregate.
- def checkAggregate(a: Aggregate): Unit = {
- val aggregates = a.expressions.flatMap(_.collect {
+ def checkAggregate(agg: Aggregate): Unit = {
+ // Make sure correlated scalar subqueries contain one row for every outer row by
+ // enforcing that they are aggregates which contain exactly one aggregate expressions.
+ // The analyzer has already checked that subquery contained only one output column,
+ // and added all the grouping expressions to the aggregate.
+ val aggregates = agg.expressions.flatMap(_.collect {
case a: AggregateExpression => a
})
if (aggregates.isEmpty) {
failAnalysis("The output of a correlated scalar subquery must be aggregated")
}
+
+ // SPARK-18504: block cases where GROUP BY columns
+ // are not part of the correlated columns
+ val groupByCols = ExpressionSet.apply(agg.groupingExpressions.flatMap(_.references))
+ val predicateCols = ExpressionSet.apply(conditions.flatMap(_.references))
+ val invalidCols = groupByCols.diff(predicateCols)
+ // GROUP BY columns must be a subset of columns in the predicates
+ if (invalidCols.nonEmpty) {
+ failAnalysis(
+ "GROUP BY column(s) in scalar subquery must exist in the WHERE clause: " +
+ s"${invalidCols.toString}")
--- End diff --
Oop! missed this comment. 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 pull request #15936: [SPARK-18504][SQL] Scalar subquery with extra gro...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on a diff in the pull request:
https://github.com/apache/spark/pull/15936#discussion_r88947150
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1244,6 +1244,23 @@ class Analyzer(
case s @ ScalarSubquery(sub, conditions, exprId)
if sub.resolved && conditions.isEmpty && sub.output.size != 1 =>
failAnalysis(s"Scalar subquery must return only one column, but got ${sub.output.size}")
+ // SPARK-1854: block cases where GROUP BY columns
+ // are not part of the correlated columns
+ case s @ ScalarSubquery(sub, conditions, exprId)
--- End diff --
BTW: we should not assume that an aggregate is always the top-level node for a correlated scalar subquery. We add a projection to the subquery plan when it shares attributes with the outer plan. This is solved in CheckAnalysis.
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra group by c...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/15936
ok to test
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #15936: [SPARK-18504][SQL] Scalar subquery with extra gro...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on a diff in the pull request:
https://github.com/apache/spark/pull/15936#discussion_r89156723
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala ---
@@ -117,19 +117,36 @@ trait CheckAnalysis extends PredicateHelper {
failAnalysis(s"Window specification $s is not valid because $m")
case None => w
}
+ case s @ ScalarSubquery(query, conditions, _)
+ // If no correlation, the output must be exactly one column
+ if (conditions.isEmpty && query.output.size != 1) =>
+ failAnalysis(
+ s"Scalar subquery must return only one column, but got ${query.output.size}")
case s @ ScalarSubquery(query, conditions, _) if conditions.nonEmpty =>
- // Make sure correlated scalar subqueries contain one row for every outer row by
- // enforcing that they are aggregates which contain exactly one aggregate expressions.
- // The analyzer has already checked that subquery contained only one output column, and
- // added all the grouping expressions to the aggregate.
- def checkAggregate(a: Aggregate): Unit = {
- val aggregates = a.expressions.flatMap(_.collect {
+ def checkAggregate(agg: Aggregate): Unit = {
+ // Make sure correlated scalar subqueries contain one row for every outer row by
+ // enforcing that they are aggregates which contain exactly one aggregate expressions.
+ // The analyzer has already checked that subquery contained only one output column,
+ // and added all the grouping expressions to the aggregate.
+ val aggregates = agg.expressions.flatMap(_.collect {
case a: AggregateExpression => a
})
if (aggregates.isEmpty) {
failAnalysis("The output of a correlated scalar subquery must be aggregated")
}
+
+ // SPARK-18504: block cases where GROUP BY columns
+ // are not part of the correlated columns
+ val groupByCols = ExpressionSet.apply(agg.groupingExpressions.flatMap(_.references))
+ val predicateCols = ExpressionSet.apply(conditions.flatMap(_.references))
+ val invalidCols = groupByCols.diff(predicateCols)
+ // GROUP BY columns must be a subset of columns in the predicates
+ if (invalidCols.nonEmpty) {
+ failAnalysis(
+ "GROUP BY column(s) in scalar subquery must exist in the WHERE clause: " +
+ s"${invalidCols.toString}")
--- End diff --
`invalidCols.mkString(",")`? (No need for string interpolation)
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra gro...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on a diff in the pull request:
https://github.com/apache/spark/pull/15936#discussion_r88945647
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1244,6 +1244,23 @@ class Analyzer(
case s @ ScalarSubquery(sub, conditions, exprId)
if sub.resolved && conditions.isEmpty && sub.output.size != 1 =>
failAnalysis(s"Scalar subquery must return only one column, but got ${sub.output.size}")
+ // SPARK-1854: block cases where GROUP BY columns
+ // are not part of the correlated columns
+ case s @ ScalarSubquery(sub, conditions, exprId)
--- End diff --
We also do a part of the scalar subquery checks in CheckAnalysis. Lets move this code there.
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra gro...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on a diff in the pull request:
https://github.com/apache/spark/pull/15936#discussion_r88946394
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---
@@ -1244,6 +1244,23 @@ class Analyzer(
case s @ ScalarSubquery(sub, conditions, exprId)
if sub.resolved && conditions.isEmpty && sub.output.size != 1 =>
failAnalysis(s"Scalar subquery must return only one column, but got ${sub.output.size}")
+ // SPARK-1854: block cases where GROUP BY columns
+ // are not part of the correlated columns
+ case s @ ScalarSubquery(sub, conditions, exprId)
+ if sub.resolved && sub.isInstanceOf[Aggregate] =>
--- End diff --
Nit (for a next time): It is easier to do this in the pattern match. For example:
`case s @ ScalarSubquery(agg: Aggregate, conditions, exprId) if agg.resolved => `
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra gro...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/spark/pull/15936
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra group by c...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/15936
add to whitelist
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra group by c...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15936
The latest push (3181a08) of the PR has the following changes from its previous version:
1. Consolidate existing checking from Analyzer.scala to CheckAnalysis.scala
2. Make use of `ExpressionSet` class to compare the canonical form of expressions between two ExpressionSet
3. Revise the test case to be more concise (using only one table) and, more importantly, exercise the `ExpressionSet` code in the comparison of the columns in the GROUP BY clause and the columns in the WHERE clause at line 143 of the file CheckAnalysis.scala
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra group by c...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/15936
It should pick it up automatically.
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra group by c...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on the issue:
https://github.com/apache/spark/pull/15936
I think I have contaminated the PR with some of my old code. I will push a new PR soon.
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra group by c...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15936
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra group by c...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15936
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/69009/
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra group by c...
Posted by ericl <gi...@git.apache.org>.
Github user ericl commented on the issue:
https://github.com/apache/spark/pull/15936
This seems to be causing https://issues.apache.org/jira/browse/SPARK-18814
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra group by c...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15936
**[Test build #69009 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/69009/consoleFull)** for PR 15936 at commit [`2fe6a64`](https://github.com/apache/spark/commit/2fe6a64c159f90fc8f0c5ddf4deebe59fd9e1458).
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra gro...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/15936#discussion_r89158982
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala ---
@@ -117,19 +117,36 @@ trait CheckAnalysis extends PredicateHelper {
failAnalysis(s"Window specification $s is not valid because $m")
case None => w
}
+ case s @ ScalarSubquery(query, conditions, _)
+ // If no correlation, the output must be exactly one column
+ if (conditions.isEmpty && query.output.size != 1) =>
+ failAnalysis(
+ s"Scalar subquery must return only one column, but got ${query.output.size}")
case s @ ScalarSubquery(query, conditions, _) if conditions.nonEmpty =>
- // Make sure correlated scalar subqueries contain one row for every outer row by
- // enforcing that they are aggregates which contain exactly one aggregate expressions.
- // The analyzer has already checked that subquery contained only one output column, and
- // added all the grouping expressions to the aggregate.
- def checkAggregate(a: Aggregate): Unit = {
- val aggregates = a.expressions.flatMap(_.collect {
+ def checkAggregate(agg: Aggregate): Unit = {
+ // Make sure correlated scalar subqueries contain one row for every outer row by
+ // enforcing that they are aggregates which contain exactly one aggregate expressions.
+ // The analyzer has already checked that subquery contained only one output column,
+ // and added all the grouping expressions to the aggregate.
+ val aggregates = agg.expressions.flatMap(_.collect {
case a: AggregateExpression => a
})
if (aggregates.isEmpty) {
failAnalysis("The output of a correlated scalar subquery must be aggregated")
}
+
+ // SPARK-18504: block cases where GROUP BY columns
+ // are not part of the correlated columns
+ val groupByCols = ExpressionSet.apply(agg.groupingExpressions.flatMap(_.references))
+ val predicateCols = ExpressionSet.apply(conditions.flatMap(_.references))
+ val invalidCols = groupByCols.diff(predicateCols)
+ // GROUP BY columns must be a subset of columns in the predicates
+ if (invalidCols.nonEmpty) {
+ failAnalysis(
+ "GROUP BY column(s) in scalar subquery must exist in the WHERE clause: " +
--- End diff --
It is.
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra gro...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on a diff in the pull request:
https://github.com/apache/spark/pull/15936#discussion_r89151615
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala ---
@@ -117,19 +117,36 @@ trait CheckAnalysis extends PredicateHelper {
failAnalysis(s"Window specification $s is not valid because $m")
case None => w
}
+ case s @ ScalarSubquery(query, conditions, _)
+ // If no correlation, the output must be exactly one column
+ if (conditions.isEmpty && query.output.size != 1) =>
+ failAnalysis(
+ s"Scalar subquery must return only one column, but got ${query.output.size}")
case s @ ScalarSubquery(query, conditions, _) if conditions.nonEmpty =>
- // Make sure correlated scalar subqueries contain one row for every outer row by
- // enforcing that they are aggregates which contain exactly one aggregate expressions.
- // The analyzer has already checked that subquery contained only one output column, and
- // added all the grouping expressions to the aggregate.
- def checkAggregate(a: Aggregate): Unit = {
- val aggregates = a.expressions.flatMap(_.collect {
+ def checkAggregate(agg: Aggregate): Unit = {
+ // Make sure correlated scalar subqueries contain one row for every outer row by
+ // enforcing that they are aggregates which contain exactly one aggregate expressions.
+ // The analyzer has already checked that subquery contained only one output column,
+ // and added all the grouping expressions to the aggregate.
+ val aggregates = agg.expressions.flatMap(_.collect {
case a: AggregateExpression => a
})
if (aggregates.isEmpty) {
failAnalysis("The output of a correlated scalar subquery must be aggregated")
}
+
+ // SPARK-18504: block cases where GROUP BY columns
+ // are not part of the correlated columns
+ val groupByCols = ExpressionSet.apply(agg.groupingExpressions.flatMap(_.references))
+ val predicateCols = ExpressionSet.apply(conditions.flatMap(_.references))
+ val invalidCols = groupByCols.diff(predicateCols)
+ // GROUP BY columns must be a subset of columns in the predicates
+ if (invalidCols.nonEmpty) {
+ failAnalysis(s"""
+ |GROUP BY column(s) in scalar subquery must exist in the WHERE clause:
+ |${invalidCols.toString}""".stripMargin.replaceAll("\n", " "))
--- End diff --
BTW if you use regular strings and regular concatenation instead of raw strings, you don't have to use `replaceAll("\n", " ")`.
---
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 #15936: [SPARK-18504][SQL] Scalar subquery with extra group by c...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/15936
**[Test build #69009 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/69009/consoleFull)** for PR 15936 at commit [`2fe6a64`](https://github.com/apache/spark/commit/2fe6a64c159f90fc8f0c5ddf4deebe59fd9e1458).
* 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 #15936: [SPARK-18504][SQL] Scalar subquery with extra group by c...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/15936
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