Subject Re: [ib-support] Firbird 1 Release Version
Author Claudio Valderrama C.
""Pirtea Calin"" <pcalin@...> wrote in message
> The problem is that when concatenating it checks only to see if the summ
> those two types sizes are beyond 32764

The limit is 32765.

> It has only to do with varchar concatenation.

No. The limit has to do with anything that's concatenated. Varchars carry a
length indicator that uses two bytes. Chars don't have it and hence are
padded to their full limit. This is why in DSQL you can define up to
char(32767) but up to varchar(32765).

> I tried this
> select cast('test' as varchar(20000))||cast('concatenated' as
> varchar(12764)) from rdb$database
> --Implementation limit exceeded
> --block size exceeds implementation restriction
> and it doesn't work, while this works ok
> select cast('test' as varchar(20000))||cast('concatenated' as
> varchar(12763)) from rdb$database
> (I think it should allow 32765 instead of 32763)

The estimation of the length in the bowels of the engine was there before
me. I only put a check. It could be a bit better (so I made it 1% smarter)
but now that the limit has been bumped to 65535, it doesn't matter too much:
you can't reach the maximum. See my answer to Raf:

I've bumped the limit to 64K-1 to see what happens. This is the largest
concatenation you can produce:
select cast(1 as varchar(32752))||cast(0 as varchar(32765)) from

Thanks to God the compiler catches this limit exactly, because I can
demonstrate that with only one unit more, the DSQL counter for the record's
length overflows and wraps to a meaningless value.

65517 is the length of the resulting varchar. The "2" is for the length
counter in any varchar. The 16 is decomposed as it follows:
- two bytes for the null indicator in the result
- two bytes for the EOF indicator (no more records)
- eight bytes for the dbkey (aka raw record position)
- four bytes for the record version.

But since in the world of this engine anything is a surprise, the DSQL layer
considers concatenation to produce a varchar whereas the core engine thinks
it's char. Hence, we have the "2" for the length counter as a phantom.

Now, why I thought initially that I should restrict the result of the
concatenation to 32767 only: anybody that has used the API knows the
XSQLDA's XSQLVAR struct. Take a look at its definition, please! Individual
field length's are contained in a SIGNED SHORT named sqllen. When I asked
isql for the result, this is what I got. I've copied all the crud verbatim:

SQL> set sqlda_display;
SQL> select cast('test' as varchar(32752))||cast('concatenated' as
varchar(32765)) from rdb$database;

INPUT SQLDA version: 1 sqldaid: ²²²²²²²²²²²²²²
sqldabc: -33686019 sqln: 10 sqld: 0

OUTPUT SQLDA version: 1 sqldaid: ²²²²²²²²²²²²²²¶ sqldabc: -33686019 sqln: 20
sqld: 1
01: sqltype: 448 VARYING sqlscale: 0 sqlsubtype: 0 sqllen: -19
: name: (0) alias: (0)
: table: (0) owner: (0)

Take a look at the sqllen= -19. This is the signed representation of 65517,
the result of the concatenation for the user (remember the user doesn't see
the two byte length indicator). That's enough room for infamy. You should
remember to treat it as unsigned.

So I will repeat: 64K-1 will be enabled for concatenation, BUT USE IT AT
YOUR OWN RISK. Don't come crying later that something broke in your

Something stinks with the concatenation rules here. We get effectively a
char, not a varchar, despite what DSQL says. How I know? Because it's padded
with blanks.

Claudio Valderrama C. - -
Independent developer
Owner of the Interbase® WebRing