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-issue-importer opened this issue Apr 24, 2007 · 18 comments

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 24, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 24, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 24, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 24, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 24, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 24, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 24, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 25, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 27, 2007

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 5, 2007

Commented by: @asfernandes

Changed to return blob.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 5, 2007

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 2.1 Beta 1 [ 10141 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 15, 2007

Modified by: @pcisar

Link: This issue is duplicated by CORE1260 [ CORE1260 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 20, 2007

Modified by: @pcisar

Link: This issue is related to QA150 [ QA150 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 10, 2007

Commented by: @pmakowski

Q/A tested ok

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 10, 2007

Modified by: @pmakowski

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 28, 2008

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2016

Modified by: @pavel-zotov

QA Status: No test

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2016

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
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants