Hi

I guess I found a bug with the follwoing statement:

select Relation.ParentID, sum(TestResult.TestVal)
from Relation join TestResult on Relation.ChildID = TestResult.ChildID
GROUP BY Relation.ParentID

Relation is a table, TestResult a stored procedure.

I would expect to get back each ParentID once - but Firebird returns a
different result set. Below you find a script for a test-database,
which returns this wrong result set.

I found that it is somehow related to the indexes I defined. When I
drop them I get a correct result.

Any hints about that problem?

Cheers
Daniel Achermann



CREATE DATABASE 'C:\Temp\Test.gdb' USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE = 4096;


CREATE GENERATOR ValueGen;

create table Val (
ValueID Numeric(18,0) not null,
ChildID Numeric(18,0),
TestVal Numeric(18,4),
constraint PK_Val primary key (ValueID));

CREATE INDEX Val_I1 ON Val(ChildID);

CREATE TABLE Relation (
RelationID Numeric(18,0) not null,
ParentID Numeric(18,0) not null,
ChildID Numeric(18,0) not null,
RelTypeID Numeric(18,0) not null,
constraint PK_Relation primary key (RelationID));


CREATE INDEX Relation_I1 ON Relation(ParentID, ChildID, RelTypeID);

SET TERM ^;

Create Procedure TestResult
Returns (ChildID Numeric(18,0), TestVal Integer)
As
DECLARE VARIABLE vTestVal Numeric(18,4);
Begin
FOR SELECT DISTINCT ChildID FROM Val
INTO :ChildID
DO BEGIN
SELECT Sum(TestVal) FROM Val WHERE ChildID = :ChildID
INTO :TestVal;
Suspend;
END
end^

SET TERM ;^

INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 4, 44493);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 3, 66899);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 2, 35721);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 3, 98596);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 2, 63824);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 2, 93935);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 4, 81306);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 2, 8432);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 2, 38419);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 3, 4243);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 5, 80307);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 2, 85419);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 2, 11573);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 5, 23953);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 4, 13515);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 3, 70773);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 5, 3661);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 5, 83509);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 4, 85585);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 3, 14442);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 3, 50033);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 5, 66260);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 4, 19564);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 4, 379);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 3, 98138);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 3, 40117);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 1, 72515);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 5, 86509);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 2, 62012);
INSERT INTO VAL VALUES (Gen_ID(ValueGen, 1), 3, 88092);


INSERT INTO Relation VALUES(Gen_ID(ValueGen, 1), 3, 1, 1);
INSERT INTO Relation VALUES(Gen_ID(ValueGen, 1), 2, 2, 1);
INSERT INTO Relation VALUES(Gen_ID(ValueGen, 1), 3, 3, 1);
INSERT INTO Relation VALUES(Gen_ID(ValueGen, 1), 3, 4, 1);
INSERT INTO Relation VALUES(Gen_ID(ValueGen, 1), 2, 5, 1);