BLOBs, DISTINCT and GROUP BY

Dalton Calford wrote:

I have a weird problem.

An over-simplified description of the problem is as follows:

select a.FOO, a.BAR from FOOBAR a

returns two rows:

a.FOO   a.BAR
A       124
A       124

And:

select distinct a.FOO, a.BAR from FOOBAR a

returns the same two rows:

a.FOO   a.BAR
A       124
A       124

BUT:

select a.FOO, a.BAR, count(*) from FOOBAR a group by a.FOO, a.BAR

returns one row:

a.FOO   a.BAR   count
A       124     2

So, the GROUP BY recognizes that the two rows are identical, while DISTINCT does not.

Eventually I found the issue - DISTINCT and GROUP BY compare blobs differently (don't know if this is by design or a bug). DISTINCT considers all blobs to be different (I am assuming it compare blobid's) while GROUP BY appears to compare contents.

Casting the blob to a VARCHAR eliminated the issues.