You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by GitBox <gi...@apache.org> on 2021/04/05 18:04:51 UTC

[GitHub] [spark] allisonwang-db opened a new pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

allisonwang-db opened a new pull request #32054:
URL: https://github.com/apache/spark/pull/32054


   ### What changes were proposed in this pull request?
   This PR adds two additional checks in `CheckAnalysis` for correlated scalar subquery in Aggregate. It blocks the cases that Spark do not currently support based on the rewrite logic in `RewriteCorrelatedScalarSubquery`:
   https://github.com/apache/spark/blob/aff6c0febb40d9713895ba00d8c77ba00f04bd16/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala#L618-L624
   
   ### Why are the changes needed?
   It can be confusing to users when their queries pass the check analysis but cannot be executed. Also, the error messages are confusing:
   
   #### Case 1: correlated scalar subquery in the grouping expressions but not in aggregate expressions 
   
   ```sql
   SELECT SUM(c2) FROM t t1 GROUP BY (SELECT SUM(c2) FROM t t2 WHERE t1.c1 = t2.c1)
   ```
   We get this error:
   ```
   java.lang.AssertionError: assertion failed: Expects 1 field, but got 2; something went wrong in analysis  
   ```
   because the correlated scalar subquery is not rewritten properly: 
   ```scala
   == Optimized Logical Plan ==
   Aggregate [scalar-subquery#5 [(c1#6 = c1#6#93)]], [sum(c2#7) AS sum(c2)#11L]
   :  +- Aggregate [c1#6], [sum(c2#7) AS sum(c2)#15L, c1#6 AS c1#6#93]
   :     +- LocalRelation [c1#6, c2#7]
   +- LocalRelation [c1#6, c2#7]
   ``` 
   
   #### Case 2: correlated scalar subquery in the aggregate expressions but not in the grouping expressions 
   
   ```sql
   SELECT (SELECT SUM(c2) FROM t t2 WHERE t1.c1 = t2.c1), SUM(c2) FROM t t1 GROUP BY c1
   ```
   We get this error:
   ```
   java.lang.IllegalStateException: Couldn't find sum(c2)#69L in [c1#60,sum(c2#61)#64L]
   ```
   because the transformed correlated scalar subquery output is not present in the grouping expression of the Aggregate:
   ```scala
   == Optimized Logical Plan ==
   Aggregate [c1#60], [sum(c2)#69L AS scalarsubquery(c1)#70L, sum(c2#61) AS sum(c2)#65L]
   +- Project [c1#60, c2#61, sum(c2)#69L]
      +- Join LeftOuter, (c1#60 = c1#60#95)
         :- LocalRelation [c1#60, c2#61]
         +- Aggregate [c1#60], [sum(c2#61) AS sum(c2)#69L, c1#60 AS c1#60#95]
            +- LocalRelation [c1#60, c2#61]
   ```
   
   ### Does this PR introduce _any_ user-facing change?
   Yes
   
   ### How was this patch tested?
   New unit tests
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813546822


   **[Test build #136914 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/136914/testReport)** for PR 32054 at commit [`c33075f`](https://github.com/apache/spark/commit/c33075fa98847fc7dd4422b047d876ffa19705bc).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814468087


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/136960/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815357626


   **[Test build #137040 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137040/testReport)** for PR 32054 at commit [`34c7dd6`](https://github.com/apache/spark/commit/34c7dd645942105cbbc5bf5cd08ba19b53d3b0aa).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815588018


   **[Test build #137055 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137055/testReport)** for PR 32054 at commit [`711f408`](https://github.com/apache/spark/commit/711f408eb1c72c3ae3956689e19474e3e8e5a45b).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] allisonwang-db commented on a change in pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32054:
URL: https://github.com/apache/spark/pull/32054#discussion_r609152666



##########
File path: sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala
##########
@@ -1765,4 +1765,35 @@ class SubquerySuite extends QueryTest with SharedSparkSession with AdaptiveSpark
       }
     }
   }
+
+  test("SPARK-34946: correlated scalar subquery in grouping expressions only") {

Review comment:
       Agree. And it would also be easier to check for all subquery-related errors if we have the tests in one test suite.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814604209


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/136972/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813676377


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/41495/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813619354


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/136914/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813671335


   Kubernetes integration test status failure
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/41495/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813571915


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/41491/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813676377


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/41495/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan closed pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
cloud-fan closed pull request #32054:
URL: https://github.com/apache/spark/pull/32054


   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814330393


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/41537/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814468087


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/136960/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815376437


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/41618/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814286381


   **[Test build #136960 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/136960/testReport)** for PR 32054 at commit [`0f29ff0`](https://github.com/apache/spark/commit/0f29ff04d64524c38b64168daef67c0992b6582c).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813759216


   **[Test build #136918 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/136918/testReport)** for PR 32054 at commit [`cd7bc70`](https://github.com/apache/spark/commit/cd7bc706bd065f5ec73e8d383b069ad471bcceb2).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814454799


   **[Test build #136960 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/136960/testReport)** for PR 32054 at commit [`0f29ff0`](https://github.com/apache/spark/commit/0f29ff04d64524c38b64168daef67c0992b6582c).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815376404






-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] maropu commented on a change in pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #32054:
URL: https://github.com/apache/spark/pull/32054#discussion_r608289035



##########
File path: sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala
##########
@@ -1765,4 +1765,35 @@ class SubquerySuite extends QueryTest with SharedSparkSession with AdaptiveSpark
       }
     }
   }
+
+  test("SPARK-34946: correlated scalar subquery in grouping expressions only") {

Review comment:
       Could you move these tests into `AnalysisErrorSuite`?

##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -756,7 +767,15 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
           // Only certain operators are allowed to host subquery expression containing
           // outer references.
           plan match {
-            case _: Filter | _: Aggregate | _: Project | _: SupportsSubquery => // Ok
+            case _: Filter | _: Project | _: SupportsSubquery => // Ok
+            case a: Aggregate =>
+              // If the correlated scalar subquery is in the grouping expressions of an Aggregate,
+              // it must also be in the aggregate expressions to be rewritten in the optimization
+              // phase.
+              if (containsExpr(a.groupingExpressions) && !containsExpr(a.aggregateExpressions)) {
+                failAnalysis(s"Correlated scalar subqueries in the group by clause " +

Review comment:
       nit: remove `s`.

##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -305,6 +305,12 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
                     s"nor is it an aggregate function. " +
                     "Add to group by or wrap in first() (or first_value) if you don't care " +
                     "which value you get.")
+              case s: ScalarSubquery
+                  if s.children.nonEmpty && !groupingExprs.exists(_.semanticEquals(s)) =>
+                failAnalysis(s"Correlated scalar subquery '${s.sql}' is neither " +
+                  s"present in the group by, nor in an aggregate function. Add it to group by " +
+                  s"using ordinal position or wrap it in first() (or first_value) if you don't " +
+                  s"care which value you get.")

Review comment:
       nit: remove `s`

##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -305,6 +305,12 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
                     s"nor is it an aggregate function. " +
                     "Add to group by or wrap in first() (or first_value) if you don't care " +
                     "which value you get.")
+              case s: ScalarSubquery
+                  if s.children.nonEmpty && !groupingExprs.exists(_.semanticEquals(s)) =>
+                failAnalysis(s"Correlated scalar subquery '${s.sql}' is neither " +
+                  s"present in the group by, nor in an aggregate function. Add it to group by " +
+                  s"using ordinal position or wrap it in first() (or first_value) if you don't " +
+                  s"care which value you get.")

Review comment:
       Could you add an explicit assert to check if group-by has the same subquery with agg exprs?
   https://github.com/apache/spark/blob/aff6c0febb40d9713895ba00d8c77ba00f04bd16/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala#L618-L624




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] allisonwang-db commented on a change in pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32054:
URL: https://github.com/apache/spark/pull/32054#discussion_r609152666



##########
File path: sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala
##########
@@ -1765,4 +1765,35 @@ class SubquerySuite extends QueryTest with SharedSparkSession with AdaptiveSpark
       }
     }
   }
+
+  test("SPARK-34946: correlated scalar subquery in grouping expressions only") {

Review comment:
       Agree. And it would also be easier to check for all analysis errors if we have the tests in one test suite.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32054:
URL: https://github.com/apache/spark/pull/32054#discussion_r608309552



##########
File path: sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala
##########
@@ -1765,4 +1765,35 @@ class SubquerySuite extends QueryTest with SharedSparkSession with AdaptiveSpark
       }
     }
   }
+
+  test("SPARK-34946: correlated scalar subquery in grouping expressions only") {

Review comment:
       That test suite needs to build logical plans using DSL and the test would be hard to read with subqueries.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813579590


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/41491/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814604209


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/136972/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814495351


   **[Test build #136972 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/136972/testReport)** for PR 32054 at commit [`140fb72`](https://github.com/apache/spark/commit/140fb72dcc82ffd7d98acfeef6d8fa0d5db05970).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815596449


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/137055/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814286381


   **[Test build #136960 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/136960/testReport)** for PR 32054 at commit [`0f29ff0`](https://github.com/apache/spark/commit/0f29ff04d64524c38b64168daef67c0992b6582c).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815466094


   **[Test build #137040 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137040/testReport)** for PR 32054 at commit [`34c7dd6`](https://github.com/apache/spark/commit/34c7dd645942105cbbc5bf5cd08ba19b53d3b0aa).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] allisonwang-db commented on a change in pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32054:
URL: https://github.com/apache/spark/pull/32054#discussion_r609265541



##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -305,6 +305,12 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
                     s"nor is it an aggregate function. " +
                     "Add to group by or wrap in first() (or first_value) if you don't care " +
                     "which value you get.")
+              case s: ScalarSubquery
+                  if s.children.nonEmpty && !groupingExprs.exists(_.semanticEquals(s)) =>
+                failAnalysis(s"Correlated scalar subquery '${s.sql}' is neither " +
+                  s"present in the group by, nor in an aggregate function. Add it to group by " +
+                  s"using ordinal position or wrap it in first() (or first_value) if you don't " +
+                  s"care which value you get.")

Review comment:
       Sounds good. Thanks for letting me know!




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] maropu commented on a change in pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #32054:
URL: https://github.com/apache/spark/pull/32054#discussion_r608333976



##########
File path: sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala
##########
@@ -1765,4 +1765,35 @@ class SubquerySuite extends QueryTest with SharedSparkSession with AdaptiveSpark
       }
     }
   }
+
+  test("SPARK-34946: correlated scalar subquery in grouping expressions only") {

Review comment:
       It seems it has some tests using a SQL string, e.g., 
   https://github.com/apache/spark/blob/7c8dc5e0b54731c5790bb94274eb9308774772ff/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnalysisErrorSuite.scala#L184-L187




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815376437


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/41618/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] maropu commented on a change in pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
maropu commented on a change in pull request #32054:
URL: https://github.com/apache/spark/pull/32054#discussion_r609151406



##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -305,6 +305,12 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
                     s"nor is it an aggregate function. " +
                     "Add to group by or wrap in first() (or first_value) if you don't care " +
                     "which value you get.")
+              case s: ScalarSubquery
+                  if s.children.nonEmpty && !groupingExprs.exists(_.semanticEquals(s)) =>
+                failAnalysis(s"Correlated scalar subquery '${s.sql}' is neither " +
+                  s"present in the group by, nor in an aggregate function. Add it to group by " +
+                  s"using ordinal position or wrap it in first() (or first_value) if you don't " +
+                  s"care which value you get.")

Review comment:
       Ah, okay. I see. Thanks for your explanation. If so, its okay to leave it as it is. Probably, After #31913 resolved, I think we might be able to handle this case more safely. That's because the PR is proposing to replace group-by exprs with ordinals to aggr exprs.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813648673


   **[Test build #136918 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/136918/testReport)** for PR 32054 at commit [`cd7bc70`](https://github.com/apache/spark/commit/cd7bc706bd065f5ec73e8d383b069ad471bcceb2).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815432387


   **[Test build #137055 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137055/testReport)** for PR 32054 at commit [`711f408`](https://github.com/apache/spark/commit/711f408eb1c72c3ae3956689e19474e3e8e5a45b).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815806464


   thanks, merging to master!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815462255






-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815478467






-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813619354


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/136914/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814594965


   **[Test build #136972 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/136972/testReport)** for PR 32054 at commit [`140fb72`](https://github.com/apache/spark/commit/140fb72dcc82ffd7d98acfeef6d8fa0d5db05970).
    * This patch passes all tests.
    * This patch merges cleanly.
    * This patch adds no public classes.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813546822


   **[Test build #136914 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/136914/testReport)** for PR 32054 at commit [`c33075f`](https://github.com/apache/spark/commit/c33075fa98847fc7dd4422b047d876ffa19705bc).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814495351


   **[Test build #136972 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/136972/testReport)** for PR 32054 at commit [`140fb72`](https://github.com/apache/spark/commit/140fb72dcc82ffd7d98acfeef6d8fa0d5db05970).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814529281


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/41549/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] allisonwang-db commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814495572


   cc @cloud-fan 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815432387


   **[Test build #137055 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137055/testReport)** for PR 32054 at commit [`711f408`](https://github.com/apache/spark/commit/711f408eb1c72c3ae3956689e19474e3e8e5a45b).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813768988


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/136918/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813768988


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/136918/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813669545


   Kubernetes integration test starting
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/41495/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815357626


   **[Test build #137040 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/137040/testReport)** for PR 32054 at commit [`34c7dd6`](https://github.com/apache/spark/commit/34c7dd645942105cbbc5bf5cd08ba19b53d3b0aa).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813648673


   **[Test build #136918 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/136918/testReport)** for PR 32054 at commit [`cd7bc70`](https://github.com/apache/spark/commit/cd7bc706bd065f5ec73e8d383b069ad471bcceb2).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813618803


   **[Test build #136914 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/136914/testReport)** for PR 32054 at commit [`c33075f`](https://github.com/apache/spark/commit/c33075fa98847fc7dd4422b047d876ffa19705bc).
    * This patch **fails Spark unit tests**.
    * This patch merges cleanly.
    * This patch adds no public classes.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins removed a comment on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814330393


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/41537/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814520695






-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] allisonwang-db commented on a change in pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
allisonwang-db commented on a change in pull request #32054:
URL: https://github.com/apache/spark/pull/32054#discussion_r609148331



##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -305,6 +305,12 @@ trait CheckAnalysis extends PredicateHelper with LookupCatalog {
                     s"nor is it an aggregate function. " +
                     "Add to group by or wrap in first() (or first_value) if you don't care " +
                     "which value you get.")
+              case s: ScalarSubquery
+                  if s.children.nonEmpty && !groupingExprs.exists(_.semanticEquals(s)) =>
+                failAnalysis(s"Correlated scalar subquery '${s.sql}' is neither " +
+                  s"present in the group by, nor in an aggregate function. Add it to group by " +
+                  s"using ordinal position or wrap it in first() (or first_value) if you don't " +
+                  s"care which value you get.")

Review comment:
       This is a good point, but there is actually another bug that is harder to resolve right now (explained below). We can add an assertion in this PR so the code will fail during query optimization instead of execution time.
   ```sql
   CREATE VIEW t(c1, c2) AS VALUES (0,1), (0,2);
   SELECT (SELECT SUM(c2) FROM t t2 WHERE t1.c1 = t2.c1) FROM t t1 GROUP BY 1
   ```
   This will fail even if the query can pass CheckAnalysis because the subquery in the grouping expressions and in the aggregate expressions are the same before the optimization phase.
   ```scala
   Aggregate [scalar-subquery#18 [c1#19]], [scalar-subquery#18 [c1#19] AS scalarsubquery(c1)#27L]
   ```
    But after the `PullupCorrelatedPredicates` rule, and the subquery outputs are deduplicated, these two subqueries are no longer semantically equal:
   ```scala
   Aggregate [scalar-subquery#18 [(c1#19 = c1#19#31)]], [scalar-subquery#18 [(c1#19 = c1#19#32)] AS scalarsubquery(c1)#27L]
   ``` 
   Notice the expression `c1#19 = c1#19#31` differs from `c1#19 = c1#19#32`.
   So the optimized plan still has an un-rewritten correlated scalar subquery.
   ```scala
   == Optimized Logical Plan ==
   Aggregate [scalar-subquery#18 [(c1#19 = c1#19#31)]], [sum(c2)#26L AS scalarsubquery(c1)#27L]
   :  +- Aggregate [c1#19], [sum(c2#20) AS sum(c2)#26L, c1#19 AS c1#19#31]
   :     +- LocalRelation [c1#19, c2#20]
   +- Project [c1#19, sum(c2)#26L]
      +- Join LeftOuter, (c1#19 = c1#19#32)
         :- LocalRelation [c1#19]
         +- Aggregate [c1#19], [sum(c2#20) AS sum(c2)#26L, c1#19 AS c1#19#32]
            +- LocalRelation [c1#19, c2#20]
   ```
   Note we cannot just compare the ID of the subquery expression because this case won't work:
   ```sql
   SELECT (SELECT SUM(c2) FROM t t2 WHERE t1.c1 = t2.c1) FROM t t1
   GROUP BY (SELECT SUM(c2) FROM t t2 WHERE t1.c1 = t2.c1)
   ```




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815478467






-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814330346






-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813579590


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/41491/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-814529281


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/41549/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] cloud-fan commented on a change in pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
cloud-fan commented on a change in pull request #32054:
URL: https://github.com/apache/spark/pull/32054#discussion_r608352820



##########
File path: sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala
##########
@@ -1765,4 +1765,35 @@ class SubquerySuite extends QueryTest with SharedSparkSession with AdaptiveSpark
       }
     }
   }
+
+  test("SPARK-34946: correlated scalar subquery in grouping expressions only") {

Review comment:
       ah I see, then +1!




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] AmplabJenkins commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-815596449


   
   Refer to this link for build results (access rights to CI server needed): 
   https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/137055/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


[GitHub] [spark] SparkQA commented on pull request #32054: [SPARK-34946][SQL] Block unsupported correlated scalar subquery in Aggregate

Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #32054:
URL: https://github.com/apache/spark/pull/32054#issuecomment-813574524


   Kubernetes integration test status failure
   URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/41491/
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org