[
https://issues.apache.org/jira/browse/CALCITE-7250?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18033280#comment-18033280
]
Jordin Catanzaro commented on CALCITE-7250:
-------------------------------------------
We tried in older version too, 1.35 and it did the same, thanks.
> Unexpected deletion of all table records for DELETE ... WHERE EXISTS
> <subquery>
> -------------------------------------------------------------------------------
>
> Key: CALCITE-7250
> URL: https://issues.apache.org/jira/browse/CALCITE-7250
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.40.0
> Reporter: Jordin Catanzaro
> Priority: Critical
> Attachments: planner-2025-10-22-1.log
>
>
> We discovered a delete query with subquery containing conditionals that
> deletes _all records from the target table_ (tested in Apache Calcite,
> version: 1.40.0). The same query with postgres driver removes the single
> matching record as expected. Here is the offending query:
> {code:java}
> delete from tab1 b where exists (select 1 from tab2 a where a.c2=2 and
> a.c1=b.c1);{code}
> Here is how to reproduce the issue:
> {code:java}
> -- Setup tables and data in postgres
> create table tab1 (c1 int, c2 int);
> create table tab2 (c1 int,c2 int);
> insert into tab1 values (1,1);
> insert into tab1 values (2,2);
> insert into tab2 values (2,2);
>
> -- Delete query
> delete from tab1 b where exists (
> select 1 from tab2 a where a.c2=2 and a.c1=b.c1
> );
>
> -- Check results
> select * from tab1;
>
> -- Expected (postgres): (1,1)
> -- Actual (calcite): (empty set)
> -- Calcite generated query: DELETE FROM "tab1"
> {code}
> I have attached the query plan for reference. Perhaps there is an issue when
> applying both `a.c2=2 and a.c1=b.c1`? Removing `a.c=2` allows `a.c1=b.c1` to
> resolve correctly.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
