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]

Reply via email to