Firebird remove duplicate ROWS on SELECT LIST subquery

Question

I'm using Firebird database v2.5 and I'm not able to remove duplicate rows from my query:

SELECT  DISTINCT u.id_user, 
        (SELECT LIST(g.id_user) 
         from gen g 
         where g.id_user=u.id_user 
         GROUP BY id_user) as list_g_user
FROM users u 
where  u.id_user = 1 
  INNER JOIN ...

and this is my result:

 id_user  |  list_g_user 
===================================
1         |  437,499,718,739,835,865
1         |  437,499,718,739,835,865
1         |  437,499,718,739,835,865
1         |  437,499,718,739,835,865

The second column result list_g_user is a BLOB type.

Same result and multiple rows. Can someone explain why?

This is the function reference: Firebird Documentation: LIST()

Answer 1

As already pointed out by JNevill, distinct with blobs does not behave as expected in Firebird: it compares blob ids (the 'pointer' to the blob), not the content of the blob, and list() produces a blob.

To workaround this, there are several possible solutions:

1. Cast the result of list() to a varchar so it can be compared correctly, eg:

   SELECT cast(LIST(g.id_user) as varchar(100)) ...
   

This however does mean that the character length of the list should not exceed varchar length (100 for this example).

2. Your use of inner join in the query without using anything from the joined table, seems to suggest you are using the join as an existence check only. Replace that join with an exists check:

   SELECT u.id_user, 
           (SELECT LIST(g.id_user) 
            from gen g 
            where g.id_user=u.id_user 
            GROUP BY id_user) as list_g_user
   FROM users u 
   where  u.id_user = 1 
   and exists (select * from <currently joined table> x where x.id_user = u.id_user)
   

3. As suggested by JNevill in the comments, you could try using group by id_user on the top-level query, but this might not always work as it relies on the blob ids being adjacent. And it is not a viable solution in situations where you can't use group by:

   SELECT  DISTINCT u.id_user, 
           (SELECT LIST(g.id_user) 
            from gen g 
            where g.id_user=u.id_user 
            GROUP BY id_user) as list_g_user
   FROM users u 
   where  u.id_user = 1 
     INNER JOIN ...
   group by u.id_user
   

Answer 2

SELECT  DISTINCT u.id_user,
        CAST(SUBSTRING((SELECT LIST(g.id_user)
         from gen g
         where g.id_user=u.id_user
         GROUP BY id_user) FROM 1 FOR 1000) AS VARCHAR(1000)) as list_g_user
FROM users u
where  u.id_user = 1
  INNER JOIN ...