David wrote:
I am seeking advice on whether this is possible:
insert into table_X (mydatetime, taskcode, sitecode)
Select x.mydatetime, x.taskcode, ss.sitecode
From table_X x
inner join sites ss on (ss.siteparentcode=x.sitecode)
Where (x.mydatetime between '30 nov 2010' and '1 nov 2011');
/* table_X has no keys */
In other words a table inserting into itself. Essentially for a particular sitecode the records are duplicated for each sitecode relationship.
On the left side of my brain, I say firebird would collect all records from the select first and then perform inserts with no problems.
On the right side, I say as records are inserted these very same records my be gathered into the select and also be duplicated with horrifying results.
Any thoughts would be appreciated.
Ann W. Harrison answers:
Trust the right side, Luke. Maybe someone has fixed this in a more recent version of Firebird (Vlad Khorsun comments: Yes, it is done in FB3), 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. 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.