Raviston Thanasekar created SPARK-61444:
-------------------------------------------

             Summary: Correlated scalar subquery fails during optimization when 
its result on zero rows fails to evaluate
                 Key: SPARK-61444
                 URL: https://issues.apache.org/jira/browse/SPARK-61444
             Project: Spark
          Issue Type: Bug
          Components: SQL
    Affects Versions: 5.0.0
            Reporter: Raviston Thanasekar


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