[firebird-support] Duplicates with LIST function in Fb 2.5.4?

2016-01-11 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
Hi, I'm puzzled by two queries returning duplicate rows:

1)
SELECT LIST('Hi') FROM RDB$DATABASE
UNION
SELECT LIST('Hi') FROM RDB$DATABASE

2)
WITH TMP(DuplicateRows) AS
(SELECT LIST('Hi') FROM RDB$DATABASE
 UNION
 SELECT LIST('Hi') FROM RDB$DATABASE)
SELECT DISTINCT DuplicateRows
FROM TMP

I was certain UNION (as opposed to UNION ALL) should remove duplicates and
the same with DISTINCT. To get the latter query to return one row, GROUP BY
can be added, I don't know how to make the first query return only one row.

Is this a Firebird error, and if so, is it a new one or one fixed in newer
versions?

Set

[firebird-support] Re: Duplicates with LIST function in Fb 2.5.4?

2016-01-11 Thread Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] 
11.01.2016 11:49, Svein Erling Tysvær wrote:

> Hi, I'm puzzled by two queries returning duplicate rows:
>
> 1)
> SELECT LIST('Hi') FROM RDB$DATABASE
> UNION
> SELECT LIST('Hi') FROM RDB$DATABASE
>
> 2)
> WITH TMP(DuplicateRows) AS
> (SELECT LIST('Hi') FROM RDB$DATABASE
>   UNION
>   SELECT LIST('Hi') FROM RDB$DATABASE)
> SELECT DISTINCT DuplicateRows
> FROM TMP

LIST returns a blob. DISTINCT doesn't work with blobs properly, it 
operates with blob ids (that are surely distinct) instead. See:

http://tracker.firebirdsql.org/browse/CORE-1345
http://tracker.firebirdsql.org/browse/CORE-1530


Dmitry

Re: [firebird-support] Re: Duplicates with LIST function in Fb 2.5.4?

2016-01-11 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
Thanks Dmitry, learning that the problem is with blobs and not particularly
LIST, DISTINCT or UNION helped me realise that the simple workaround is to
use cast(list() as varchar()) whenever I want to return
distinct values of blobs.

Set

2016-01-11 14:31 GMT+01:00 Dmitry Yemanov dim...@users.sourceforge.net
[firebird-support] :

> 11.01.2016 11:49, Svein Erling Tysvær wrote:
>
> > Hi, I'm puzzled by two queries returning duplicate rows:
> >
> > 1)
> > SELECT LIST('Hi') FROM RDB$DATABASE
> > UNION
> > SELECT LIST('Hi') FROM RDB$DATABASE
> >
> > 2)
> > WITH TMP(DuplicateRows) AS
> > (SELECT LIST('Hi') FROM RDB$DATABASE
> >   UNION
> >   SELECT LIST('Hi') FROM RDB$DATABASE)
> > SELECT DISTINCT DuplicateRows
> > FROM TMP
>
> LIST returns a blob. DISTINCT doesn't work with blobs properly, it
> operates with blob ids (that are surely distinct) instead. See:
>
> http://tracker.firebirdsql.org/browse/CORE-1345
> http://tracker.firebirdsql.org/browse/CORE-1530
>
>
> Dmitry
>
>
>
>
> 
> Posted by: Dmitry Yemanov 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>