Subject | Re: distinct on list() function |
---|---|
Author | |
Post date | 2019-11-12T15:17:44Z |
This is not a bug, but a documented feature. list() returns BLOB. And blobs are distict because BLOB_ID-s are compared.
SELECT DISTINCT, ORDER BY and GROUP BY work on the BLOB ID, not the contents.
You ust use CAST in this case.
with cte as
(select 1 cnt from rdb$database
union all
select 2 from rdb$database
)
select cast(list(cnt) as varchar(10)) from cte
union
select cast(list(cnt) as varchar(10)) from cte
(select 1 cnt from rdb$database
union all
select 2 from rdb$database
)
select cast(list(cnt) as varchar(10)) from cte
union
select cast(list(cnt) as varchar(10)) from cte