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. ________________________________ From: [email protected] <[email protected]> on behalf of Dalton Calford [email protected] [firebird-support] <[email protected]> Sent: December 11, 2017 2:37:39 PM To: [email protected] Subject: [firebird-support] Select Distinct returning multiple rows 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 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 recognises that the two rows are identical, while distinct does not. The above is an oversimplification of the query (which joins many tables etc) So, I tried to isolate the distinct clause from the underlying tables using a cte ie, with MYFOOBAR as ( select a.FOO, a.BAR, count(*) from FOOBAR a group by a.FOO, a.BAR) select distinct b.FOO, b.BAR from MYFOOBAR b but, that still returns two rows. The underlying tables do not have nulls. What I am trying to understand is what makes the distinct directive consider the rows differently from a group by directive. >From a logical standpoint, both should resolve things the same should they not >(one to eliminate duplicates from the result set, the other to count >duplicates) What am I missing here?
