Issue Details (XML | Word | Printable)

Key: CORE-1227
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Andre Litfin
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

LIST() function seems not work if used twice or more in a query

Created: 24/Apr/07 11:51 AM   Updated: 10/Jul/07 10:09 AM
Component/s: None
Affects Version/s: 2.1 Alpha 1
Fix Version/s: 2.1 Beta 1

Time Tracking:
Not Specified

Environment: Windows XP Server and Client, proper fbclient.dll
Issue Links:
Duplicate
 
Relate
 


 Description  « Hide
If I try to use the LIST() function twice or more in a query (e.g. select list(kdap_vorname), list(kdap_nachname) from T_KUNDENANSPRECHPARTNER group by kdap_id;) the following error occurs:

Undefined name.
Dynamic SQL Error.
SQL error code = -204.
Implementation limit exceeded.
Block size exceeds implementation restriction.

Each LIST() funtion taken for itself works well, even with more data from the table (T_KUNDENANSPRECHPARTNER.*) where T_KUNDENANSPRECHPARTNER is described as


CREATE TABLE T_KUNDENANSPRECHPARTNER (
  KDAP_ID D_INTEGER_FORPK NOT NULL,
  KDAP_KDID "D_INTEGER_NOTNULLDEF-1" NOT NULL,
  KDAP_FIRMENNAME D_VARCHAR150_NOTNULL NOT NULL,
  KDAP_ABTEILUNG D_VARCHAR50,
  KDAP_ANREDE D_VARCHAR25_NOTNULL NOT NULL,
  KDAP_TITEL D_VARCHAR25,
  KDAP_VORNAME D_VARCHAR50,
  KDAP_NACHNAME D_VARCHAR100_NOTNULL NOT NULL,
  KDAP_STRASSE D_VARCHAR100,
  KDAP_INTERNALPOSTCODE D_VARCHAR100,
  KDAP_POSTFACHNR D_VARCHAR10,
  KDAP_POSTFACHPLZ D_VARCHAR7,
  KDAP_LAND D_VARCHAR3,
  KDAP_PLZ D_VARCHAR7_NOTNULL NOT NULL,
  KDAP_ORT D_VARCHAR100_NOTNULL NOT NULL,
  KDAP_TELEFON1 D_VARCHAR100,
  KDAP_TELEFON2 D_VARCHAR100,
  KDAP_TELEFAX D_VARCHAR100,
  KDAP_MOBILTELEFON D_VARCHAR100,
  KDAP_EMAIL D_VARCHAR100,
  KDAP_ERREICHBARKEIT D_VARCHAR100,
  KDAP_FUNKTION D_VARCHAR100,
  KDAP_AUFGABE D_VARCHAR100,
  KDAP_GEBAEUDE D_VARCHAR100,
  KDAP_GEBURTSDATUM D_DATE_DEF0,
  KDAP_PRIVSTRASSE D_VARCHAR150,
  KDAP_PRIVPLZ D_VARCHAR7,
  KDAP_PRIVORT D_VARCHAR150,
  KDAP_PRIVTELEFON D_VARCHAR100,
  KDAP_PRIVMOBTELEFON D_VARCHAR100,
  KDAP_PRIVEMAIL D_VARCHAR255,
  KDAP_INFO D_TEXT,
  KDAP_AKTIV D_SMALLINT_NOTNULLDEF1 NOT NULL);


ALTER TABLE T_KUNDENANSPRECHPARTNER ADD PRIMARY KEY (KDAP_ID);


CREATE INDEX IDX_T_KUNDENANSPRECHPARTNER ON T_KUNDENANSPRECHPARTNER(KDAP_KDID,KDAP_AKTIV,KDAP_NACHNAME);

CREATE INDEX IDX_T_KUNDENANSPRECHPARTNER1 ON T_KUNDENANSPRECHPARTNER(KDAP_NACHNAME,KDAP_AKTIV);

CREATE INDEX IDX_T_KUNDENANSPRECHPARTNER2 ON T_KUNDENANSPRECHPARTNER(KDAP_KDID,KDAP_NACHNAME);


SET TERM ^ ;

CREATE TRIGGER BI_T_KDAP_PK FOR T_KUNDENANSPRECHPARTNER
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
  IF (NEW.KDAP_ID IS NULL) THEN
      NEW.KDAP_ID = GEN_ID(T_KDAP_PK_GEN, 1);
END^

SET TERM ; ^

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Nickolay Samofatov added a comment - 24/Apr/07 12:00 PM
Dmitry, Adriano,

Wouldn't it be better for LIST function to return temporary blob sub_type text that user than could cast to CHAR/VARCHAR? LIST function design is somewhat ugly now, IMO.

Nikolay

Dmitry Yemanov added a comment - 24/Apr/07 12:02 PM
In Firebird, the row size is limited by 64K. The LIST function result is described as VARCHAR(32K). So this is a kind of "as designed". The only solution I can think of is to return BLOB TEXT from LIST, but it also has some drawbacks...

Andre Litfin added a comment - 24/Apr/07 12:17 PM
Wow, which fast feedback! Thanks a lot. Dmitry, I understand the limit. But I think, I'm not getting against it. I tried the following:

I created a new table named TESTTABLE with three columns: ID (int), FIELD1(char(5)) and FIELD2 (char(20))
I added two rows to the table: row1 with 1;'aaaaa';'bbbbbbb' and row2 with 1;'ccccc';'dddddddd'

Now I tried to query select list(field1), list(field2) from testtable group by id; and I got again the error

Undefined name.
Dynamic SQL Error.
SQL error code = -204.
Implementation limit exceeded.
Block size exceeds implementation restriction.

Querying select list(field1) from testtable group by id; or select list(field2) from testtable group by id; works well.

I think, that I don't get on the limit of VARCHAR(32K) or do I? What does the begin of the error message ('Undefined name') mean?

Dmitry Yemanov added a comment - 24/Apr/07 12:27 PM
The row buffer is static and it's size is fixed at the compile time. Hence any reference to LIST already means that 32K will used in the row buffer, regardless of the real aggregated string length.

Andre Litfin added a comment - 24/Apr/07 12:48 PM
Yeah, thanks, now I got it! But uff - that's hard. Is it a possibility to past an int as - lets say 'maxlength' parameter? Or could another function by introduced which returns a smaller value (e.g. 5K) so some list5k-functions could be used in the same query? I'm asking for this because I'm using some group_concat-functions in a MySQL-query and I'm trying to switch to firebird at the moment.

Dmitry Yemanov added a comment - 24/Apr/07 01:26 PM
An alternative option is already mentioned above: LIST could return a text blob. You may cast it to a string of any size, if you wish. This looks more flexible than a configurable aggregated string length limit in LIST itself. Let's see what other guys think about it.

Andre Litfin added a comment - 24/Apr/07 01:39 PM
Yes, but you mentioned some drawbacks, what are these?

At a workaround for the moment I'm using a procedure which returns a substring from 1 for 5000 of the list-function...

Dmitry Yemanov added a comment - 24/Apr/07 11:28 PM
Your workaround is perfectly valid and I think SUBSTRING(LIST) is more clear than an extra parameter in the LIST function.

As for drawbacks, they're related to blobs in general. You cannot group/order by a blob field directly (without casting it). Also, the client side needs an extra API round-trip to read the blob value. Other limitations are no longer an issue starting with v2.1, because this version implements text blobs and strings being fully compatible and interchangeable.

Adriano dos Santos Fernandes added a comment - 05/May/07 02:46 PM
Changed to return blob.

Philippe Makowski added a comment - 10/Jul/07 10:09 AM
Q/A tested ok