Hello,

We ran into a problem where the order in which the rows are inserted
in the table affects the values set by an update statement.

Consider the following table MY_TAB { id bigint, sibling_id bigint,
age bigint }. Where id is the primary key, sibling_id is a self
referencing foreign key and age is a property.

Suppose we insert two sibling rows in the table, i.e. sibling_id of
each row points to the id of another row. We then run an update
statement, which sets the age of each row to the age of its sibling.
The expected result of this update is that rows swap their ages.
However, in Firebird the post-state of the update is that both rows
have the same age, which is the age of the row that was inserted last.

We tested this with another database vendor and got the expected result.

Is this a bug or there is a reasonable explanation for this behavior?

Here is the script that reproduces the problem.

Create table MY_TAB ( id bigint, sibling_id bigint, age bigint );

INSERT INTO MY_TAB (ID, SIBLING_ID, AGE) VALUES (1,2,10);
INSERT INTO MY_TAB (ID, SIBLING_ID, AGE) VALUES (2,1,20);

update MY_TAB set age = (select t.age from MY_TAB t where t.id =
MY_TAB.sibling_id);

select * from MY_TAB

Thanks