Yesterday I tried to select using a CTE containing LIST(DISTINCT), and then
the main (outer) select tried to group by this LIST. The result ended up
with incorrect ordering as well as duplicates. Trying to make a
reproducible test case, I didn’t get the duplicates, but rather the list
contained duplicate values. I did a search to see if others have observed
the same thing, but didn’t find anything (though I’m far better with
Firebirds SELECT statement than internet searching, so this could well be
just me).
Here’s the query (“reproducible” test case):
with tmp(id, greeting) as
(select list(distinct 'I should go last'), list(distinct substring('Hi'
from 1 for 5))
from rdb$database
left join rdb$database r2 on (1=1)
union
select list(distinct 'I shouldn''t duplicate'), list(distinct
substring('Hello' from 1 for 5))
from rdb$database
left join rdb$database r2 on (1=1)
union
select list(distinct 'I shouldn''t duplicate'), list(distinct
substring('Hello' from 1 for 5))
from rdb$database
left join rdb$database r2 on (1=1))
select greeting, list(distinct id), count(distinct id)
from tmp
group by 1
order by Greeting
Expected result:
Hello I shouldn’t duplicate 1
Hi I should go last 1
Actual result (on Firebird 2.5.4, probably with ISO8859_1 as the character
set and may actual query may have had NO_NO as the collation):
Hi I should go last 1
Hello I shouldn’t duplicate,I shouldn’t duplicate 2
How the wrong result would have been if I had been able to completely make
a copy of my original error (random sorting and lots of duplicates):
Hi I should go last 1
Hello I shouldn’t duplicate 1
Hello I shouldn’t duplicate 1
I wrote the query to answer one particular question, so I could get a
similar result to what I wanted by first running only the content of the
CTE and then using PivotTable in Excel, but I was very surprised by bumping
into what I consider being two separate errors inside one query (duplicates
and ordering), or maybe even 2,5 errors since my original query returned
duplicate rows rather than duplicates within LIST(DISTINCT…).
Does anyone have similar experiences or an explanation?
Set
