--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>
wrote:

> I've made a testcase, since I was curious about whether it

affected

> all unique columns or just those with the primary key constraint.

It

> seems to be the PK constraint that reveals the bug, because a

column

> having the UNIQUE constraint on it doesn't exhibit the problem.

CUT

> Normally, one doesn't issue a production query for a distinct

count

> on occurrences of a primary key value, for obvious reasons -
> duplications can't occur in a PK column.

I think there's also a problem with foreign keys:

CREATE TABLE tbl1(k INTEGER NOT NULL, number INTEGER);
CREATE TABLE tbl2(number INTEGER NOT NULL PRIMARY KEY);

ALTER TABLE tbl1 ADD CONSTRAINT fk1 FOREIGN KEY (number) REFERENCES
tbl2(number) ON DELETE NO ACTION ON UPDATE CASCADE;

INSERT INTO tbl2 VALUES(2147483647);
INSERT INTO tbl1 VALUES(0,2147483647);

and now:

SELECT COUNT(DISTINCT number) from tbl1;

returns 0, while:

SELECT COUNT(number) from tbl1;

returns 1.

In more realistic situations, Firebird sometimes reports SQLCODE -
902.

> btw, I made my testcase using your starting example, but under

1.5.2,

> so this isn't just a recent regression. I'll re-run it in 1.5.3,
> just to make it so that my conditions are the same as yours, and

I'll

> post the testcase in the bugtracker to see what our gurus can make

of it.

Thank you for your answer.


Manlio Morini

--
EOS Development
Soluzioni informatiche per l'impresa

http://eos.pi.it