Is this expected Firebird behaviour?

17 views
Skip to first unread message

Tomasz Dubiel

unread,
May 11, 2023, 6:21:25 AM5/11/23
to firebird-support
Firebird 3.0.10, Debian.
delete from table1
  where id in (select x.id
                       from table2 x
                       where x.id1 = 422682
                         and x.id2 = 2451685);

subselect gives indexed reads
delete from table without subselect, but with already given id, gives also indexed reads
However, the original delete causes nonindexed reads from table1

Best regards.

Tomasz Tyrakowski

unread,
May 11, 2023, 6:38:03 AM5/11/23
to firebird...@googlegroups.com, Tomasz Dubiel
It happens with IN and subselects. Try this equivalent:

DELETE FROM table1 t1
WHERE EXISTS (
SELECT * FROM table2 t2
WHERE t1.id=t2.id and t2.id1=422682 and t2.id2=422682
);

It should result in indexed reads (that's at least what my own
observations suggest), 'cause internally it's probably implemented as a
JOIN.
It would be nice to have DELETE with JOIN (like for example MySQL has)
in such scenarios, but FB doesn't support this syntax AFAIK.

regards
Tomasz





Dimitry Sibiryakov

unread,
May 11, 2023, 6:41:19 AM5/11/23
to firebird...@googlegroups.com
Tomasz Tyrakowski wrote 11.05.2023 12:38:
> Try this equivalent

FOR SELECT table2 ... DO
DELETE FROM table1

would be more effective and guarantee optimal plan. AFAIU this code is in a
trigger anyway.

--
WBR, SD.

Mark Rotteveel

unread,
May 11, 2023, 7:04:04 AM5/11/23
to firebird...@googlegroups.com
On 11-05-2023 12:38, Tomasz Tyrakowski wrote:
> It would be nice to have DELETE with JOIN (like for example MySQL has)
> in such scenarios, but FB doesn't support this syntax AFAIK.

Firebird has the MERGE statement to do that.

Mark
--
Mark Rotteveel

Tomasz Tyrakowski

unread,
May 11, 2023, 7:13:23 AM5/11/23
to firebird...@googlegroups.com
On 11.05.2023 at 13:03, Mark Rotteveel wrote:
> On 11-05-2023 12:38, Tomasz Tyrakowski wrote:
>> It would be nice to have DELETE with JOIN (like for example MySQL has)
>> in such scenarios, but FB doesn't support this syntax AFAIK.
>
> Firebird has the MERGE statement to do that.

Would you be so kind and provide a short example how to use MERGE to
delete rows based on condition requiring a JOIN? E.g. the OP's original
query rewritten using MERGE?
I've skimmed
https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-dml-merge
and can't see any obvious ways to do that.
Thanks in advance.

regards
Tomasz

Mark Rotteveel

unread,
May 11, 2023, 7:21:11 AM5/11/23
to firebird...@googlegroups.com
Sure:

```
merge into TABLE1
using (
select ID
from TABLE2
where ID1 = 422682 and ID2 = 2451685
) as SRC
on SRC.ID = TABLE1.ID
when matched then delete;
```

For a running example: https://dbfiddle.uk/ILa87NKj

Mark
--
Mark Rotteveel

Tomasz Tyrakowski

unread,
May 11, 2023, 7:30:21 AM5/11/23
to firebird...@googlegroups.com
On 11.05.2023 at 13:21, Mark Rotteveel wrote:
> Sure:
>
> ```
> merge into TABLE1
> using (
>   select ID
>   from TABLE2
>   where ID1 = 422682 and ID2 = 2451685
> ) as SRC
> on SRC.ID = TABLE1.ID
> when matched then delete;
> ```
>
> For a running example: https://dbfiddle.uk/ILa87NKj
>

Neat! WHEN MATCHED THEN DELETE was the part I somehow missed in the
manual (and I've always used DELETE + EXISTS 'cause I din't know better).
Thanks a lot!

regards
Tomasz

Mark Rotteveel

unread,
May 11, 2023, 7:34:20 AM5/11/23
to firebird...@googlegroups.com
The `when matched then delete` option was introduced in Firebird 3.0, so
it didn't exist in Firebird 2.1 and 2.5, maybe that is why you didn't
notice it before.

Mark
--
Mark Rotteveel

Tomasz Dubiel

unread,
May 11, 2023, 7:36:48 AM5/11/23
to firebird-support
Thanks as well!

Tomasz Dubiel

unread,
May 11, 2023, 8:11:03 AM5/11/23
to firebird-support
Just to be sure - this merge example does exactly the same what does the delete which I mentioned in the first post? This command is new to me.

Mark Rotteveel

unread,
May 11, 2023, 8:33:26 AM5/11/23
to firebird...@googlegroups.com
On 11-05-2023 14:11, Tomasz Dubiel wrote:
> Just to be sure - this merge example does exactly the same what does the
> delete which I mentioned in the first post? This command is new to me.

The end result is the same, yes.

Mark
--
Mark Rotteveel

Tomasz Dubiel

unread,
May 11, 2023, 8:42:32 AM5/11/23
to firebird-support
OK, thanks.
Reply all
Reply to author
Forward
0 new messages