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: [email protected]
For additional commands, e-mail: [email protected]
