Skip to content
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

Closed
firebird-automations opened this issue Apr 24, 2007 · 18 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

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

@firebird-automations
Copy link
Collaborator Author

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...

@firebird-automations
Copy link
Collaborator Author

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

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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...

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Changed to return blob.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.1 Beta 1 [ 10141 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is duplicated by CORE1260 [ CORE1260 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA150 [ QA150 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

Q/A tested ok

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 11930 ] => Firebird [ 15553 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test => Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants