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

["Wenchen Fan (Jira)" <ji...@apache.org>]

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

Wenchen Fan reassigned SPARK-58966:
-----------------------------------

    Assignee: Joel Robin

> 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
>            Assignee: Joel Robin
>            Priority: Major
>              Labels: pull-request-available
>
> 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