Gouping error on deleting
-------------------------
Key: CORE-3862
URL: http://tracker.firebirdsql.org/browse/CORE-3862
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.1
Environment: Windows (desktop machine)
Reporter: Marcelo R Vellame
Priority: Minor
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(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(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)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
