Hi,

> Here's a particularly nasty query (at least I think it is)

> delete from t t1
> where t1.id in (select first 5 t t2
> from t order by t2.id);

<snip>

> If we applied the logic I described above (assuming it can be applied)
> here's what happens. Still assuming an index on t.id, the engine starts
> reading the index and finds that the first five entries are all valid,
> and that the first one matches the value of t.id from the outer loop so
> that record is deleted. It gets the next value of t.id and goes to the
> index to find the first 5 records. The first record appears, at first
> glance, to be deleted, but it is also on the undo list for the delete.
> The engine goes to the previous version of that record, finds that it is
> legitimate for the current transaction, and keeps it in the list of 5.
> Again, the non-indexed case has the same behavior at greater cost.

What is the list of 5?
What happends when the statement is executed twice in the same transaction? Are 5 or 10 records
deleted?

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info