When a table has varchar column and the varchar column size is defined bigger than 32K, MXOSRVR server may return truncted string to client application in certain circumstances.
Circumstance 1:
varchar column is defined as "Not NULL", length of the string stored in the varchar column < 32K, then MXOSRVR will truncate the last 2 characters of the string.
Eg:
CREATE TABLE T1 ( C1 varchar (200000) NOT NULL);
insert into T1 values ('aaaaaaaaaaaaaaaaaaaEE');
select * from T1; --> MXOSRVR will truncate the last 2 characters 'EE', returns 'aaaaaaaaaaaaaaaaaaa' to clients.
Circumstance 2:
varchar column is defined as "Not NULL", length of the string stored in the varchar column > 32K, then MXOSRVR can only return the first 3392 characters to client application.
Eg:
CREATE TABLE T1 ( C1 varchar (200000) NOT NULL);
insert into T1 with a sting longer than 32768 characters
select * from T1; -> MXOSRVR only returns the first 3392 characters.
Circumstance 3:
varchar column is defined as "NULLABLE", length of the string stored in the varchar column > 32K, then MXOSRVR will truncate the last 2 characters of the string.
Eg:
CREATE TABLE T1 ( C1 varchar (200000));
insert into T1 with a sting longer than 32768 characters
select * from T1; -> MXOSRVR will truncate the last 2 characters of the string.
Bug in code that was clearing the trailing part of the varchar column - was not distinguishing between 2 bytes length vs. 4 byte length. As per Anoop this code is a leftover and is not needed:
"
yes, this looks like a pbm in clearUnusedVarchar. It is not handling lengths correctly
...
This method is only called during bulk move of varchar when we clear unused
trailing bytes. We don’t really need to do that since we always add length in
front of returned data.
.....
No, all cases should be fine.
Callers should not rely on null padded trailing bytes and should only
pick up as many bytes as the prefix length indicates.
That clearing up of trailing bytes is probably a leftover from old days."
For the following test case "ZZ" was missing before the fix.
SQL>create table t38(c1 varchar(32768) not null);
--- SQL operation complete.
SQL>insert into t38 values('abcdefZZ');
--- 1 row(s) inserted.
SQL>select * from t38;
C1
-------
-------
abcdefZZ
===
Breakpoint 3, ExpTupleDesc:
len=32772, isNullable=0) at ../exp/
913 NABoolean isNull = FALSE;
(gdb) n
914 if (isNullable)
(gdb)
921 if (!isNull)
(gdb)
923 Int32 actualLen = getVarLength(
DED_FORMAT);
(gdb)
924 dataPtr += (sizeof(short) + actualLen);
(gdb) p actualLen
$9 = 8
(gdb) n
925 len -= (sizeof(short) + actualLen);
(gdb) p dataPtr
$10 = 0x288377a "ZZ"
(gdb) n
927 if (len)
(gdb) p len
$11 = 32762
(gdb) n
929 memset(dataPtr, 0, len);
(gdb)
==============