[jira] [Created] (SPARK-59954) DECLARE VARIABLE fails with INTERNAL_ERROR when the default value applies NULLIF or BETWEEN to a non-foldable operand

["Yang Jie (Jira)" <ji...@apache.org>]

Yang Jie created SPARK-59954:
--------------------------------

             Summary: DECLARE VARIABLE fails with INTERNAL_ERROR when the default value applies NULLIF or BETWEEN to a non-foldable operand
                 Key: SPARK-59954
                 URL: https://issues.apache.org/jira/browse/SPARK-59954
             Project: Spark
          Issue Type: Bug
          Components: SQL
    Affects Versions: 5.0.0
            Reporter: Yang Jie


A DECLARE VARIABLE whose default value applies NULLIF or BETWEEN to a non-foldable operand fails at planning:

{code:sql}
DECLARE OR REPLACE VARIABLE v1 DOUBLE DEFAULT nullif(rand(), 0.5);
DECLARE OR REPLACE VARIABLE v2 BOOLEAN DEFAULT rand() BETWEEN 0.1 AND 0.9;
{code}

{noformat}
[INTERNAL_ERROR] The Spark SQL phase planning failed with an internal error. ... SQLSTATE: XX000
Caused by: java.lang.AssertionError: assertion failed: No plan for CreateVariable default(if ((_common_expr_0#0 = 0.5)) null else _common_expr_0#0) ...
+- Project [rand(...) AS _common_expr_0#0]
   +- ResolvedIdentifier ..., session.v1
{noformat}

A deterministic Scala UDF in place of rand() fails the same way. nullif(1, 3) runs, since its operand is foldable and the With is inlined.

NULLIF and BETWEEN become a With that references its operand twice. RewriteWithExpression pre-evaluates such a definition in a Project over the first child whose output covers the definition's references. The children of CreateVariable are the ResolvedIdentifier of each variable name, and a default value references no column, so the definition is put in a Project over the first ResolvedIdentifier. V2CommandStrategy only plans a CreateVariable whose children are all ResolvedIdentifier, so the planner finds no plan for it.

Reproduced on master and on branch-4.2.




--
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