[
https://issues.apache.org/jira/browse/SPARK-58966?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-58966:
-----------------------------------
Labels: pull-request-available (was: )
> 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
> 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: [email protected]
For additional commands, e-mail: [email protected]
