Form Listbox Firebird SQL error Base 7.3.5

[frofa]

Having just upgraded my local LibreOffice instal from version 7.3.3 to v. 7.3.5, I find that one of my Base FORMS with a LIST BOX throws an error when the form is opened. Basically, the listbox allows a client name to be selected using a drop-down and the SQL code for the listbox concatenates the client’s last and first names and the client ID (integer) is written to a filter table (to show that client’s records in the form.

The listbox code is:
SELECT COALESCE ( “lastname”, ‘’ ) || ', ’ || COALESCE ( “firstname”, ‘’ ), “id_cl” FROM “clients” ORDER BY “lastname” ASC

The above code worked fine with Base in LO 7.3.3 but throws the error below with LO 7.3.5…

firebird_sdbc error:
*arithmetic exception, numeric overflow, or string truncation
*string right truncation
*expected length 34, actual 69
caused by
‘isc_dsql_fetch’

The last name and first name columns of the clients table are both VCHAR(16). NOTE: If I change them both to VCHAR(50) the error changes also to…
*expected length 102, actual 205

• So the ‘actual’ value above seems to be the expected length+1
• And the ‘expected length’ is the sum of the two NAME field-lengths+2

I have tried this with setting different settings for the allowed 2 name-fields, and the above formula holds good.

Does anyone know what is going on here. It seems to be a bug?

[Wanderer]

@frofa

worked fine with Base in LO 7.3.3 but throws the error below with LO 5.3.5

So you actually DOWNgraded to an older version??

@frofa

So the ‘actual’ value above seems to be twice the expected length+1

I remember there were some issues with firebase concerning the length of utf8-strings, as this depends obviously on the number of char-codes higher 255 but you’d have to check the bug reports.
.
Is this an embedded firebase-table or are you connecting to an separatly installed firebase.

[frofa]

Several corrections to my original post. Yes, I did UPGRADE my LibreOffice instal from version 7.3.3 to 7.3.5 I am using an EMBEDDED Firebird database.

I am also wondering what this means…issues with FIREBIRD concerning the length of utf8-strings, as this depends obviously on the number of char-codes higher 255 but you’d have to check the bug reports.

Something weird seems to be going on with the way FIREBIRD is evaluating the allowed resulting lengths of the calculated length of the combined lastname and firstname parts of the full name in the LISTBOX (drop-down).

[Wanderer]

@frofa

I am also wondering what this means…issues with FIREBIRD concerning the length of utf8-strings

I remembered maybe this:

String truncation exception on UPPER/LOWER functions, UTF8 database and some multibyte characters [CORE5255]

[Villeroy]

When storing persons data, you should store both first names and family names. Then you would not need to coalesce because both columns would not accept emptyness.
At least, the familiy name should be mandatory. A person entry without any name makes no sense and therefore should not be stored at all.

SImple rule: Any operation with a Null value returns Null.

A || ‘, ’ || B returns Null if either A or B is null (or both).
COALESCE(A ||’, '|| B, A) returns A if B is missing and the empty string if both are missing. If A is mandatory, you always get some name and you get the comma only if both A and B are given.

[RobertG]

@frofa

SELECT COALESCE ( “lastname”, ‘’ ) || ', ’ || COALESCE ( “firstname”, ‘’ ), “id_cl” FROM “clients” ORDER BY “lastname” ASC

Have tried this with LO 7.3.5.2 and couldn’t get any error. But note: I have to change many single- and doublequotes to execute. Did you change anything with collation of the database? With which version of LO did you create the database? Could you upload an example database here?

Hint for the code:
SELECT COALESCE ( "lastname" || ', ', '' ) || COALESCE ( "firstname", '' ), "id_cl" FROM "clients" ORDER BY "lastname" ASC
will show the comma only if “lastname” exists. When creating such a code I have set “lastname” as a field, which shouldn be empty (NOT NULL). So I get this code instead:
SELECT "lastname" || COALESCE ( ', '||"firstname", '' ), "id_cl" FROM "clients" ORDER BY "lastname" ASC

Tested the same with a internal Firebird database created 2019-11-01. Couldn’t reproduce the bug with this version also.

@Wanderer : Bug of Firebird is about UPPER, LOWER and CONTAINING. Has nothing to do with the code for the listbox @frofa posted.

Could be it has something to do with string length, which has something to do with the collation. With UTF8 you will nee 4 byte for a string, but length calculation in Firebird depends on characters with 1 byte. But this has been solved with bug 105142 in LO 5.3.1. Could have nothing to do with LO 7.3.3 and couldn’t have been changed to LO 7.3.5

[frofa]

Hello RobertG:

Unfortunately, the alternative code you gave above throws the same error message as my original code.

To answer you questions, I am running the code just as a normal SQL query (separate from the code used to populated the form listbox). To answer you question, I think I created the database in quite an early version of LibreOffice - whatever was current in early 2020. I don’t recall changing anything with the collation of the database.

I will do some more testing and report back - maybe post a sample database in the next days. Thanks for your suggestions anyway,

[Wanderer]

@RobertG

Could have nothing to do with LO 7.3.3 and couldn’t have been changed to LO 7.3.5

As the TO gave different Versions I was not sure, if he was migrating from 5.something - and don’t have the Versions as present as you. Question is, if the Firebird-team found all quirks on the topic of multi-byte charsets…

[frofa]

I have created a new embedded FB database from scratch. I set-up a single table by executing the following SQL code:

CREATE TABLE “clients”
(“id_cl” INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
“lastname” VARCHAR(16),
“firstname” VARCHAR(16),
“company” VARCHAR(50),
“job” VARCHAR(32),
“street” VARCHAR(40),
“suburb” VARCHAR(20),
“state” VARCHAR(5),
“postcode” VARCHAR(7),
“landlineh” VARCHAR(12),
“landlinew” VARCHAR(12),
“mobile” VARCHAR(12),
“email” VARCHAR(40),
“note” VARCHAR(64),
“date_add” DATE,
“date_upd” DATE,
“referral” VARCHAR(32),
PRIMARY KEY (“id_cl”));

I then saved the new Firebird base-file, and entered some made-up data just for client lastname and firstname columns. No other columns had any data added.

I then created and saved my original problem query…

SELECT COALESCE ( “lastname”, ‘’ ) || ', ’ || COALESCE ( “firstname”, ‘’ ), “id_cl” FROM “clients” ORDER BY “lastname” ASC

…then ran the query and got the following message (similar to the message I got when I reported it originally in this post-discussion).

firebird_sdbc error:
*arithmetic exception, numeric overflow, or string truncation
*string right truncation
*expected length 34, actual 78
caused by
‘isc_dsql_fetch’

So, it seems the problem with my original query is not tied to my particular original database file (or setup). The error message also obeys the same mathematical relationship between ‘expected length’ and ‘actual length’ as appearing in the error message earlier given.

The database I described above is uploaded here-below.

Can anyone tell me what is going on? It looks like a bug (as I suggested before)

billings_TEST2.odb (3.5 KB)

[RobertG]

Downloaded the file, executed the query (with LO 7.4.0.2, also LO 7.3.5.2). Couldn’t see any buggy behavior here.
My system: OpenSUSE 15.3 64bit rpm Linux.
Please try it with Help → Restart in Safe Mode.

[frofa]

I restarted in in SAFE MODE, but exactly the same buggy behaviour persists. I am using Mac OS Catalina v. 10.15.7. Should I revert to the earlier version of LibreOffice I was using (7.3.3) to see what happens there?

[RobertG]

Try it. If it works there: Report a bug. Might be a special Mac-bug.

[frofa]

@RobertG: I tried reverting to LibreOffice version 7.3.3 as you suggested, and now there is no concatenation error - all works perfectly, so it would seem to be a bug that has been introduced since v.7.3.3. I have reported a bug HERE