[jira] [Commented] (SPARK-50091) Query fails when aggregate expression is in left-hand operand of IN-subquery

["Dongjoon Hyun (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/SPARK-50091?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17916887#comment-17916887 ] 

Dongjoon Hyun commented on SPARK-50091:
---------------------------------------

This landed at branch-3.5 via 
- https://github.com/apache/spark/pull/49663

> Query fails when aggregate expression is in left-hand operand of IN-subquery
> ----------------------------------------------------------------------------
>
>                 Key: SPARK-50091
>                 URL: https://issues.apache.org/jira/browse/SPARK-50091
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 3.5.2
>            Reporter: Bruce Robbins
>            Assignee: Bruce Robbins
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 4.0.0, 3.5.5
>
>
> Consider this query:
> {noformat}
> create or replace temp view v1(c1, c2) as values
> (1, 2), (1, 3), (2, 2), (3, 7), (3, 1);
> create or replace temp view v2(col1, col2) as values
> (1, 2), (1, 3), (2, 2), (3, 7), (3, 1);
> select col1, sum(col2) in (select c2 from v1)
> from v2 group by col1;
> {noformat}
> It fails with the following error:
> {noformat}
> [INTERNAL_ERROR] Cannot generate code for expression: sum(input[1, int, false]) SQLSTATE: XX000
> {noformat}
> With SPARK_TESTING=1, it fails with
> {noformat}
> [PLAN_VALIDATION_FAILED_RULE_IN_BATCH] Rule org.apache.spark.sql.catalyst.optimizer.RewritePredicateSubquery in batch RewriteSubquery generated an invalid plan: Special expressions are placed in the wrong plan: Aggregate [col1#11], [col1#11, first(exists#20, false) AS (sum(col2) IN (listquery()))#19]
> +- Join ExistenceJoin(exists#20), (sum(col2#12) = c2#18L)
>    :- LocalRelation [col1#11, col2#12]
>    +- LocalRelation [c2#18L]
> {noformat}
> The {{Join}} is invalid because it contains an aggregate expression in the join condition.
> The bug is in the handler for UnaryNode in {{RewritePredicateSubquery#apply,}} which adds a {{Join}} below the {{Aggregate}} and assumes that the left-hand operand of IN-subquery can be used in the join condition. This works fine for most cases, but not when the left-hand operand is an aggregate expression.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

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