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]