[ 
https://issues.apache.org/jira/browse/SPARK-61444?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Kousuke Saruta reassigned SPARK-61444:
--------------------------------------

    Assignee: Raviston Thanasekar

> Keep the result on zero rows of a correlated subquery as an expression if it 
> fails to fold
> ------------------------------------------------------------------------------------------
>
>                 Key: SPARK-61444
>                 URL: https://issues.apache.org/jira/browse/SPARK-61444
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 3.5.9, 4.3.0, 5.0.0, 4.2.1, 4.0.5, 4.1.4
>            Reporter: Raviston Thanasekar
>            Assignee: Raviston Thanasekar
>            Priority: Major
>              Labels: pull-request-available
>
> With ANSI mode on (the default), a correlated scalar subquery fails while the 
> query is optimized when its result on zero rows throws, even if every outer 
> row has matching rows and the result on zero rows is never used:
> {code:sql}
> CREATE OR REPLACE TEMP VIEW o(k) AS VALUES (1), (2);
> CREATE OR REPLACE TEMP VIEW i(k, v) AS VALUES (1, 100), (1, 200), (2, 300);
> SELECT k, (SELECT 10 / COALESCE(MAX(v), 0) FROM i WHERE i.k = o.k) FROM o;
> -- [DIVIDE_BY_ZERO] Division by zero. Expected (1, 0.05), (2, 0.0333...)
> {code}
> With spark.sql.ansi.enabled=false the query returns the expected rows.
> To handle the COUNT bug, 
> RewriteCorrelatedScalarSubquery.evalSubqueryOnZeroTups (and 
> DecorrelateInnerQuery, through evalAggregateOnZeroTups) evaluates the 
> subquery on zero rows while the query is optimized, and puts the result into 
> If(IsNull(alwaysTrue), <result on zero rows>, <subquery result>), which only 
> uses it for outer rows without matching rows. tryEvalExpr calls eval() on 
> every foldable expression without catching its errors, so an error that would 
> only happen for such an outer row fails the whole query. ConstantFolding 
> keeps an expression that fails to evaluate in a conditional branch for the 
> same reason (SPARK-39106).
> Other examples that fail in the same way:
> {code:sql}
> SELECT k, (SELECT CAST(COALESCE(MAX(CAST(v AS STRING)), 'x') AS INT) FROM i 
> WHERE i.k = o.k) FROM o;  -- CAST_INVALID_INPUT
> SELECT k, (SELECT COUNT(*) FROM i WHERE i.k = o.k HAVING 10 / COUNT(*) > 0) 
> FROM o;                   -- DIVIDE_BY_ZERO
> SELECT k, (SELECT MAX(c) FROM (SELECT 10 / COUNT(*) c FROM i WHERE i.k = 
> o.k)) FROM o;                -- DIVIDE_BY_ZERO
> SELECT k FROM o WHERE (SELECT 10 / COALESCE(MAX(v), 0) FROM i WHERE i.k = 
> o.k) > 0.04;               -- DIVIDE_BY_ZERO
> {code}
> The code is the same in branch-3.5 and later. ANSI mode is on by default 
> since 4.0.



--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to