Wenchen Fan created SPARK-61979:
-----------------------------------
Summary: Do not substitute DELETE, UPDATE and MERGE targets with a
same-named CTE
Key: SPARK-61979
URL: https://issues.apache.org/jira/browse/SPARK-61979
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 5.0.0
Reporter: Wenchen Fan
When a statement-level WITH defines a CTE with the same name as the unqualified
target of DELETE FROM, UPDATE or MERGE INTO, CTESubstitution replaces the
target relation itself with the CTE body. If the CTE body is a bare table
reference, the statement silently modifies that other table; otherwise it fails
with an internal or unsupported-operation error. An INSERT target is already
never substituted with a same-named CTE (SPARK-58933), and PostgreSQL always
writes the table the statement names.
{code:sql}
-- t: (1, 100, 'hr'), (2, 200, 'software'), (3, 300, 'hr')
-- other: (1, 10, 'hr'), (2, 20, 'software')
WITH t AS (TABLE other)
DELETE FROM t WHERE pk IN (SELECT pk FROM t WHERE salary = 10);
-- actual: the row with pk = 1 is deleted from `other`; `t` is unchanged; no
error.
-- expected: the row with pk = 1 is deleted from `t`.
WITH t AS (SELECT * FROM t WHERE salary > 250)
DELETE FROM t WHERE pk IN (SELECT pk FROM t);
-- actual: [INTERNAL_ERROR] Unexpected table relation: Filter ...
{code}
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]