--- In firebird-support@yahoogroups.com, Ann Harrison wrote:

> Trust the right side, Luke. Maybe someone has fixed this in a more
> recent version of Firebird, but it's a
> characteristic bug of databases that Jim Starkey writes. Unless
> there's a sort (e.g. order by, distinct,
> grouped) they get records one a time rather than finding all
> qualifying rows first as the standard anticipates.
> Rdb/Eln, InterBase, Firebird, Netfrastructure, Falcon, and, until
> recently NuoDB all go into an infinite loop
> if you have any rows in table A an do something like this:
>
> insert into A select * from A
>
> Retrieving all the rows and data first is really inefficient.
> Retrieving the rows and retaining only the db-keys
> (or equivalent) does the same amount of I/O as retrieving data first,
> but doesn't use as much memory or
> temporary disk space. The solution that NuoDB implemented is to keep
> a sparse bitmap of the db-keys of
> the inserted records and not reinsert them when they come back from
> the select.

But this way updates and deletes are not addressed. Probably NuoDB's
implementation is more complex than just bitmap of inserted record
numbers and really addressed all cases of unstable cursor...

> Note that db-keys are not
> necessarily monotonically increasing, so the query has to run until it
> exhausts the result set, not just stop
> when it finds the first "new" record. And, of course, you can't just
> ignore records created by your transaction,
> or running the query twice wouldn't get the right answer.
>
> It's possible to do the same thing using the information in the
> savepoint undo list, I think, and maybe
> Firebird implemented that after 2.0.

Yes, it is done in FB3 (still pre-alpha)

Regards,
Vlad