Sorry did a typo: ``` SELECT * from tab1 b INNER JOIN tab2 a ON a.c2=2 and a.c1=b.c1 ```
On Fri, Oct 24, 2025 at 3:05 PM Dmitry Sysolyatin <[email protected]> wrote: > It would also be helpful if you could create a Jira task for this. I think > the issue is as follows: > > The DELETE statement WHERE part is generated from the source SELECT > [1]. If the source SELECT looks like this: > SELECT * from tab1 b where exists (select 1 from tab2 a where a.c2=2 and > a.c1=b.c1); > > hen the WHERE part will be empty, and the generated statement will be: > > DELETE FROM "tab1" as you mentioned. > > [1] > https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L1164 > > > On Fri, Oct 24, 2025 at 2:33 AM Mihai Budiu <[email protected]> wrote: > >> Calcite is really a framework to build query execution engines, it does >> not store data or execute statements by itself. You have to describe more >> about how your setup uses Calcite. >> >> Mihai >> >> ________________________________ >> From: Jordin Catanzaro <[email protected]> >> Sent: Thursday, October 23, 2025 2:54 PM >> To: [email protected] <[email protected]> >> Subject: Potential Bug, delete statement with correlated subquery and >> conditionals unexpectedly removes all records >> >> >> Hi! >> >> >> >> We have encountered a delete query with subquery containing conditionals >> that deletes all records from the target table (Apache Calcite, version: >> 1.40.0). The same query with postgres driver removes the single matching >> record as expected >> `delete from tab1 b where exists (select 1 from tab2 a where a.c2=2 and >> a.c1=b.c1);` >> >> >> >> Here is how to reproduce the issue: >> ```sql >> -- 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" >> ``` >> >> >> >> I have attached the query plan for reference. It appears 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. >> Is this a known issue, and are there any workarounds? I didn't see >> anything when I browsed through the Jira - happy to report a bug if this is >> unexpected behavior. >> >> >> >> Please let me know if you need more details. >> Thank you! >> >> >> >> Jordin >> >> >>
