[jira] [Created] (SPARK-58966) SQL variables cannot be used in UPDATE or MERGE INTO statements

["Joel Robin (Jira)" <ji...@apache.org>]

Joel Robin created SPARK-58966:
----------------------------------

             Summary: SQL variables cannot be used in UPDATE or MERGE INTO statements
                 Key: SPARK-58966
                 URL: https://issues.apache.org/jira/browse/SPARK-58966
             Project: Spark
          Issue Type: Bug
          Components: SQL
    Affects Versions: 5.0.0
            Reporter: Joel Robin


SQL variables declared with DECLARE cannot be referenced anywhere in an UPDATE or MERGE INTO statement against a table that uses Spark's built-in DataSource V2 row-level operation framework. They resolve correctly in INSERT and DELETE, so the restriction is inconsistent and undocumented.

 

SPARK-57260 fixed the same root cause for the REPLACE WHERE clause of INSERT INTO. It is still present in every clause of UPDATE and MERGE INTO.
h2. Reproduction

Reproduced on master (commit b51665875a3) using the built-in InMemoryRowLevelOperationTableCatalog:
{noformat}
  DELETE ... WHERE dep = var                        -> WORKS
  INSERT ... REPLACE WHERE dep = var                -> WORKS   (SPARK-57260)
  UPDATE ... WHERE dep = var                        -> FAILS
  UPDATE ... SET salary = var                       -> FAILS
  MERGE ... ON t.pk = s.pk AND t.dep = var          -> FAILS
  MERGE ... WHEN MATCHED AND s.salary < var         -> FAILS
  MERGE ... WHEN NOT MATCHED BY SOURCE AND t.x > var -> FAILS
  MERGE ... WHEN MATCHED THEN UPDATE SET col = var  -> FAILS
  MERGE ... WHEN NOT MATCHED THEN INSERT VALUES (var) -> FAILS
{noformat}
Every failure is:
{noformat}
[UNRESOLVED_COLUMN.WITH_SUGGESTION] A column, variable, or function parameter with name `v_dep` cannot be resolved. Did you mean one of the following? [`dep`, `pk`, `salary`]. SQLSTATE: 42703 
{noformat}
Note the message itself offers "A column, {*}variable{*}, or function parameter", i.e. the analyzer reports that variable resolution was attempted. The same failure occurs for SQL scripting local variables:
{code:sql}
  BEGIN
    DECLARE lv STRING DEFAULT 'hr';
    UPDATE t SET salary = 5 WHERE dep = lv;
  END
{code}
h2. Scope

This affects connectors that rely on Spark's built-in resolution of UpdateTable and MergeIntoTable. Delta is NOT affected because it supplies its own analyzer rules for UPDATE/MERGE and never reaches the code below; consequently this does not reproduce on Delta tables. Apache Iceberg is expected to be affected since it uses the built-in row-level operation framework, but I have not verified Iceberg directly.



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