New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
LIST() function seems not work if used twice or more in a query [CORE1227] #1651
Comments
Commented by: @samofatov 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 |
Commented by: @dyemanov 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... |
Commented by: Andre Litfin (andrelitfin) 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)) Now I tried to query select list(field1), list(field2) from testtable group by id; and I got again the error Undefined name. 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? |
Commented by: @dyemanov 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. |
Commented by: Andre Litfin (andrelitfin) 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. |
Commented by: @dyemanov 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. |
Commented by: Andre Litfin (andrelitfin) 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... |
Commented by: @dyemanov 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. |
Modified by: @asfernandesassignee: Adriano dos Santos Fernandes [ asfernandes ] |
Commented by: @asfernandes Changed to return blob. |
Modified by: @asfernandesstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 2.1 Beta 1 [ 10141 ] |
Commented by: @pmakowski Q/A tested ok |
Modified by: @pmakowskistatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pcisarWorkflow: jira [ 11930 ] => Firebird [ 15553 ] |
Modified by: @pavel-zotovQA Status: No test |
Modified by: @pavel-zotovQA Status: No test => Done successfully |
Submitted by: Andre Litfin (andrelitfin)
Is duplicated by CORE1260
Is related to QA150
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 ; ^
Commits: cfe0ad9
The text was updated successfully, but these errors were encountered: