At 07:23 PM 18/02/2006, you wrote:

>--- 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.

Sorry, I don't see any difference here from the original report.

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

Could that be because you are trying to add constraints/data to
tables that you haven't committed?

./heLen