LPAD with length from DB returns negative sqllen

[Paulius Pazera]

Hello,

we are trying to migrate from firebird 2.5 to v5. On client side we are using Delphi with IBObjects. When using firebird v5 some 'select' statements, for example:

select lpad('6', RDB$RELATION_ID, '0') from rdb$database;

return 335544358 'message length error (encountered 2, expected 65538)' error

In isql that statement works fine. Comparing result in isql I see that in v2.5 result was 32k long, and in v5 it's 64k long

The reason is that sqllen in XSQLVAR is defined as 'short' on your end (i.e. signed 16 bit integer), and the value returned by isc_dsql_prepare is -3 (the value would be 0xfffd or 65533 if it was 'unsigned short'). On our end in IBObjects it's defined as smallint which matches 'short', thus is treated as negative value in various places, for example when calculating and allocating buffer for isc_dsql_fetch (which returns 'message length error' because buffer allocated was way too small)

we are using 'DataTypeCompatibility = 3.0' in firebird.conf for other purposes (mainly to avoid int128), but it doesn't help to shorten varchars

BTW, IBExpert fails with 'Out of memory' error, I guess due to the same reason

Question: was this change intentional to increase varchar size to 64k and we have to rework our end to 'unsigned short' equivalent, or was this accidental and I should register issue in tracker, or maybe you could suggest another solution?

Thanks,

Paulius (GLDS)

[Dimitry Sibiryakov]

Paulius Pazera wrote 30.04.2025 11:31:
> Question: was this change intentional to increase varchar size to 64k and we
> have to rework our end to 'unsigned short' equivalent, or was this accidental
> and I should register issue in tracker, or maybe you could suggest another solution?

Varchar always allowed to store up to 64k-2 bytes. Declaring its length as
signed 16 bits value was most likely an initial bug in IBO.

--
WBR, SD.

[Paulius Pazera]

you mean declaration bug in firebird? looks like IBO just copied from firebird:

sqlda_pub.h:

typedef struct
{
    ISC_SHORT    sqltype;            /* datatype of field */
    ISC_SHORT    sqlscale;            /* scale factor */
    ISC_SHORT    sqlsubtype;            /* datatype subtype - currently BLOBs only */
    ISC_SHORT    sqllen;                /* length of data area */
    ISC_SCHAR*    sqldata;            /* address of data */
    ISC_SHORT*    sqlind;                /* address of indicator variable */
    ISC_SHORT    sqlname_length;        /* length of sqlname field */
    ISC_SCHAR    sqlname[32];        /* name of field, name length + space for NULL */
    ISC_SHORT    relname_length;        /* length of relation name */
    ISC_SCHAR    relname[32];        /* field's relation name + space for NULL */
    ISC_SHORT    ownname_length;        /* length of owner name */
    ISC_SCHAR    ownname[32];        /* relation's owner name + space for NULL */
    ISC_SHORT    aliasname_length;    /* length of alias name */
    ISC_SCHAR    aliasname[32];        /* relation's alias name + space for NULL */
} XSQLVAR;

fb_types.h:

typedef short SSHORT;

typedef SSHORT ISC_SHORT;

[Dimitry Sibiryakov]

Paulius Pazera wrote 30.04.2025 11:44:
> you mean declaration bug in firebird?

I mean this declaration:

typedef struct paramvary {
ISC_USHORT vary_length;
ISC_UCHAR vary_string[1];
} PARAMVARY;

In OO API length of single field is unsigned 32 bits integer. ISC API was
declared deprecated then so the 32k limit was never risen there.

--
WBR, SD.

[Paulius Pazera]

back to original question: is 64k value assigned to signed short variable sqllen intentionally and we should rework our end to be unsigned int as well, or it happened accidentally and firebird should truncate varchars to 32k in ISC API so that length fits into unsigned short variable?

BTW, it doesn't look like 64k varchars were or are supported (executed via isql):

2.5.9:

create domain varchar64k as varchar(65533);
Statement failed, SQLSTATE = 22003
Dynamic SQL Error
-SQL error code = -842
-Short integer expected

5.0.2:

create domain varchar64k as varchar(65533);
Statement failed, SQLSTATE = 22003
Dynamic SQL Error
-SQL error code = -842
-Short integer expected
-At line 1, column 37

[Dmitry Yemanov]

30.04.2025 13:11, Paulius Pazera wrote:

> back to original question: is 64k value assigned to signed short
> variable sqllen intentionally and we should rework our end to be
> unsigned int as well, or it happened accidentally and firebird should
> truncate varchars to 32k in ISC API so that length fits into unsigned
> short variable?
>
> BTW, it doesn't look like 64k varchars were or are supported (executed
> via isql):

Declaratively - not, but in literals they're allowed since v3:

https://github.com/FirebirdSQL/firebird/issues/1944


Dmitry

[Mark Rotteveel]

That is not correct, VARCHAR has a maximum length of 32 KiB - 3.

SQL> select cast('a' as varchar(32766)) from rdb$database;
Statement failed, SQLSTATE = HY004
Dynamic SQL Error
-SQL error code = -204
-Data type unknown
-Implementation limit exceeded
-COLUMN

It succeeds with 32765.

Mark
--
Mark Rotteveel

[Paulius Pazera]

if I can not create varchar(65533) domain, field, variable, parameter or result, then why such long literals were needed, why LPAD returns varchar(65533) value, where we can put those 64k varchars values? obviously returning them via ISC API causes issues...

create table t(f varchar(65533));

Statement failed, SQLSTATE = 22003
Dynamic SQL Error
-SQL error code = -842
-Short integer expected

-At line 1, column 26

set term ^;
create procedure sp
returns (r varchar(1))
as
  declare v varchar(65533);
begin
  suspend;
end
^

Statement failed, SQLSTATE = 22003
Dynamic SQL Error
-SQL error code = -842
-Short integer expected

-At line 4, column 21
set term ;^

[Geoff Worboys]

On 30/04/2025 19:31, Paulius Pazera wrote:
> Hello,
>
> we are trying to migrate from firebird 2.5 to v5. On client side we are
> using Delphi with IBObjects. When using firebird v5 some 'select'
> statements, for example:
>
> select lpad('6', RDB$RELATION_ID, '0') from rdb$database;
>
> return 335544358 'message length error (encountered 2, expected 65538)'
> error
>
> In isql that statement works fine. Comparing result in isql I see that
> in v2.5 result was 32k long, and in v5 it's 64k long

> [...]

I am currently exploring the migration from v2.5 to v5 using an IBO
based application so was interested to see your post. I experimented with:

select
lpad('6', RDB$RELATION_ID, '0')
from rdb$database

And it seems the error only occurs when using a connection with
character set NONE (and presumably OCTETS). If I use ASCII the returned
varchar is 32765, if I use UTF8 the varchar is 8191. Both these match
with FB v2.5.

So if you can avoid using a byte-based character set on your connection
then maybe you can avoid the oddity. I guess the other possibility is
to do some explicit casts in the query itself.


--
Geoff Worboys
Telesis Computing

[Paulius Pazera]

I reworked our end to unsigned 16 bit sqllen, adjusted other related types to make size calculation and allocation correct, but the error is still there. SQLDA is copied from isc_dsql_prepare to isc_dsql_fetch, on our end I now see sqllen 65533, but I guess isc_dsql_fetch treats it as signed -3 thus fails. Added #8548 to firebird tracker

Thanks Jeff for the tip, I will check what character set we use and if we can switch to ASCII. Currently we are reworking such queries one by one when we bump into them, but global solution would be much safer