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 );