> Sent: Saturday, 26 March 2011 11:41 AM
> Hey all,
>
> it's been a while since my last post here which means I'm a happy Firebird
> user for many years now - thank you to the community, especially the
> developers and doc writers =) Now I just noticed bug when using the LIST()
> function. My SQL statement is:
>
> SELECT UG.UG_ID_GROUP
> , UG.UG_NAME
> , UG.UG_DESCR
> , (
> SELECT CAST(LIST(U.U_NAME, ', ') AS VARCHAR(1000)) FROM U_USER U
> WHERE U.U_ID_USER IN ( SELECT UAGRP.UAGRP_ID_USER FROM
> U_U_AND_GRP UAGRP WHERE UAGRP.UAGRP_ID_GROUP =
> UG.UG_ID_GROUP --ORDER BY U.U_NAME /*uncomment this*/
> )
> ) AS USER_LIST
> FROM U_GROUP UG
> FOR UPDATE
>
> This selects all user groups from U_GROUP and sub-selects a list of the group
> members' user names from U_USER. Membership is defined via m-to-n
> table U_U_AND_GRP.
>
> Without the ORDER BY this returns "WM, EM, OZ" and "FI" in column
> USER_LIST (two records in result set). When I uncomment the ORDER BY
> clause then my grid shows nothing in this column. I thought it might always
> return NULL (on Windows XP) but doing this SELECT on another machine
> (Windows Server 2008 R2) I saw a strange value of "ÿÿ"
> returned instead of "WM, EM, OZ".
>
> For now I can live fine without the LIST contents being ordered, I just wanted
> to let you know. I searched the tracker but couldn't find this particular bug
> there.
>
> I get this behavior on
>
> - Windows XP SP3 32Bit with
> - Firebird-2.1.3.18185_0_Win32
> - Firebird-2.1.4.18393_0_Win32
> - Firebird-2.5.0.26074_1_Win32
> (all Super-Server)
>
> - Windows Server 2008 R2 64Bit with
> - Firebird-2.1.3.18185_0_Win32
> (Super-Server).
>
> I'm using Delphi 2007 and IBObjects 4.8.7 to access the database but
> confirmed this with ISQL on Windows XP with Firebird 2.5.0 and Firebird 2.1.3.
> See below for my table definitions. Let me know if you need more
> information.
>
> Regards,
> Markus
>
> CREATE TABLE U_GROUP (
> UG_ID_GROUP INTEGER NOT NULL
> , UG_NAME VARCHAR( 40 ) NOT NULL
> , UG_DESCR VARCHAR( 300 )
> , CONSTRAINT PK_U_GROUP
> PRIMARY KEY ( UG_ID_GROUP )
> );
>
> CREATE TABLE U_USER (
> U_ID_USER INTEGER NOT NULL
> , U_NAME VARCHAR( 40 ) NOT NULL
> , CONSTRAINT PK_U_USER
> PRIMARY KEY ( U_ID_USER )
> , CONSTRAINT UQ_U_NAME
> UNIQUE ( U_NAME )
> );
>
> CREATE TABLE U_U_AND_GRP (
> UAGRP_ID_U_AND_GRP INTEGER NOT NULL
> , UAGRP_ID_USER INTEGER NOT NULL
> , UAGRP_ID_GROUP INTEGER NOT NULL
> , CONSTRAINT PK_U_U_AND_GRP
> PRIMARY KEY ( UAGRP_ID_U_AND_GRP )
> , CONSTRAINT UQ_UAGRP_USER_GROUP
> UNIQUE ( UAGRP_ID_USER, UAGRP_ID_GROUP ) );
>
> ALTER TABLE U_U_AND_GRP
> ADD CONSTRAINT FK_UAGRP_ID_GROUP
> FOREIGN KEY ( UAGRP_ID_GROUP )
> REFERENCES U_GROUP ( UG_ID_GROUP )
> , ADD CONSTRAINT FK_UAGRP_ID_USER
> FOREIGN KEY ( UAGRP_ID_USER )
> REFERENCES U_USER ( U_ID_USER );
>

Do you think that NULLS are concatenating to NULLS?

i.e. if you do the sub-select on its own without the list function, do you have a NULL anywhere?

I'm not sure if there is something like this influencing your results. I tried a simple test with a NULL record included, but the concatenation still worked.

Maybe there is some encoded data in the string that is screwing up the display? Can you see the results using IBSQL/IB Expert or any other tool? You may have to cast to a varchar to see any results.

Paul.