Subject Re: Select Distinct returning multiple rows
Author Dalton Calford
Post date 2017-12-11T20:04:51Z

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: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com> on behalf of Dalton Calford dcalford@... [firebird-support] <firebird-support@yahoogroups.com>
Sent: December 11, 2017 2:37:39 PM
To: firebird-support@yahoogroups.com
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?