Submitted by: Marcelo R Vellame (marcelorv)

Duplicates CORE634

When i tryed to delete some records in table "a" by a subselect on table "a" join "b" join "c" dicovered that the grouping of function min() (and avg() too) is not working on delete for the same table.

To exemplify there goes a simple script:

create table iceSling (
id integer,
yearOfBirth integer,
name varchar(30),
CONSTRAINT someUniqueConstrain PRIMARY KEY (ID)
)
-- execute and commit the create

INSERT INTO ICESLING (ID, YEAROFBIRTH, NAME) VALUES ('1', '1980', 'ZECA');
INSERT INTO ICESLING (ID, YEAROFBIRTH, NAME) VALUES ('2', '1980', 'CHICO');
INSERT INTO ICESLING (ID, YEAROFBIRTH, NAME) VALUES ('3', '1983', 'LEILA');
-- execute and commit the inserts

--Now execute this selection: (this works just fine)
select *
from ICESLING where ID in (
select min(http://i.ID) from ICESLING i
where i.YEAROFBIRTH=1980
)

--Now execute try this:
--Notice that this is the same SQL form select, only changed "select *" for "delete"
--(here is the problem: the min() grouping is ignored and 2 records are deleted, only one shoud be deleted)
delete
from ICESLING where ID in (
select min(http://i.ID) from ICESLING i
where i.YEAROFBIRTH=1980
)

Obs. This happens for avg() too;
Obs.2 Max() works ok;
Obs.3 This happens only if is the selected and deleted table are the same
Obs.4 IceSling knocked FireBird :) (Oracle and Postgre worked just fine)

Commits: cdca8e3 91cdb8e 170b799 5b76e52 addfee3 FirebirdSQL/fbt-repository@a5ae04e

====== Test Details ======

Test case from this ticket was added into core-3362.fbt ("Cursors should ignore changes made by the same statement")