tomjanczkadao wrote:

> But I still wonder why select doesn't take a snapshot :)
>

The problem could be corrected without having Firebird take a
snapshot of the existing table using the "undo list" for the statement.

When any statement fails, including statements that affect many rows,
all its changes must be undone. In order to undo multi-row statements,
Firebird keeps a list of the records created or modified by each
statement. The list is discarded when the statement completes.

During an insert ... select statement when the source and target are
the same, Firebird should compare the record number of the input
record with the record numbers on its undo list. If the number
is there, then the record was created by the current statement and
should not be used to create a new record.

The actual code would be somewhat complicated to avoid breaking
the layering between the compile/optimize phase and execution,
and would require special execution handling of a pretty dumb
looking statement - it's a testing/building trick, not likely
to show up in a real application - but the use of insert ... select
to populate testing tables is very common. And the standard is
quite clear that what we have is a bug.

Cheers,

Ann