At 10:17 AM 18/02/2006, you wrote:

>A short example (Firebird v1.5.3):
>
>CREATE TABLE tbl(number INTEGER NOT NULL PRIMARY KEY);
>INSERT INTO tbl VALUES(2147483647);
>
>Now:
>
>(1)
>SELECT COUNT(DISTINCT number) FROM tbl;
>
>returns this result:
>
> COUNT
>============
> 0
>
>while:
>
>(2)
>SELECT COUNT(number) FROM tbl;
>
>is ok (COUNT==1). If number < 2147483647 (2^31-1) everything is
>right.
>
>What am I missing? Is this a known bug?

It's looks *DISTINCTly* like a bug, but not (that I can think of)
known previously.

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.

It's an interesting find, since we've seen some reports recently of
cases where PK violations were showing up, yet SELECT COUNT(DISTINCT
blah) wasn't able to confirm that the value already existed. Now we know why.

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. But when troubleshooting a
table whose keys have been corrupted by an old bug, now cured, where
it was possible to set PK indexes inactive, duplicated PK values were
possible so it wouldn't be a totally invalid question to ask (even
though using COUNT as an existence test is a very poor practice...).

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.

./heLen