Let me explain it more clearly. If you have any optimization rules that
change a source select like this:
`SELECT * from tab1 b where exists (select 1 from tab2 a where a.c2=2 and
a.c1=b.c1);`
to something like this:
`SELECT DISTINCT a FROM tab1 b LEFT JOIN tab2 a ON a.c2 = 2 AND a.c1 = b.c1`
or in any other way where the top-level query loses the WHERE part, then
it’s possible that the WHERE clause gets dropped in the delete statement .
Again, Please create a Jira ticket and include as many details as possible.
Also, the plan you mentioned ("I have attached the query plan for
reference") is missing in your email. Please attach it to the Jira ticket
as well.
On Fri, Oct 24, 2025 at 3:07 PM Dmitry Sysolyatin <[email protected]>
wrote:
> 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
>>>
>>>
>>>
