Subject Re: distinct on list() function
Author
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