Hi,

At August 16, 2004, 05:43, Martijn Tonies wrote:

>> >> > > the statement
>> >> > > INSERT INTO T1 SELECT * FROM T1 (duplicating T1)
>> >> > > causes (in Firebird 1.5.1, maybe in IB too) closed loop.
>> >> > >
>> >> > > PostgreSQL works correctly!
>> >>
>> >> At 12:53 PM 15/08/2004 -0400, Sean Leyne wrote:
>> >>
>> >> >Yes, this an old/long standing bug with FB and IB (all versions).
>> >>
>> >> ...while others would say it was a crippling bug if database rules
>> >> prevented a transaction from seeing its own uncommitted work.
>>
>> MT> Then again - what is the SQL standard behaviour for this?
>>
>> IIRC the standard says Firebird is wrong. :-(

> In that case, it's bug and should be fixed. Period.

This is what is mentioned in the SQL-99/2003 docs, in regards to the
INSERT statement:

[Start quote]
1) Without Feature F781, ‘‘Self-referencing operations’’, no leaf
generally underlying table of T shall be generally contained in the
<query expression> immediately contained in the <insert columns and
source> except as the <table or query name> or <correlation name> of a
column reference.
[End quote]

I think when using sub-queries as the source for an INSERT should
be executed in the following order by the engine:

1. Execute the sub-query and retrieve all the rows for the result set
(snapshot of the data only)
2. Perform the insert into the table using the sub-query's result set.

This way if it's a self-referencing INSERT, the worst that can happen
is duplicating the rows of the table or a constraint violation.


--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)