MSCEWI3091

LENGTHB transformed to OCTET_LENGTH results may vary due to memory management of DBMS

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

This issue happens when there is an invocation to LENGTHB function that returns the size of a column or literal in bytes. This function is transformed into OCTET_LENGTH Snowflake's function.

When the parameter to the function is a column, the result will be the size of the value that the column has, this size may vary from Oracle to Snowflake, the type of the column plays an important role in the result returned by the function.

Example Code

Input Code:

Queries

CREATE TABLE char_table
(
	char_column1 CHAR(15)
);

INSERT INTO char_table VALUES ('Hello world');

SELECT char_column1, LENGTHB(char_column1), LENGTH('Hello world') FROM char_table;

Result

|CHAR_COLUMN1   |LENGTHB(CHAR_COLUMN1)|LENGTH('HELLOWORLD')|
|---------------|---------------------|--------------------|
|Hello world    |15                   |11                  |

Output Code:

Queries

CREATE OR REPLACE TABLE PUBLIC.char_table (
char_column1 CHAR(15));

INSERT INTO PUBLIC.char_table VALUES ('Hello world');

SELECT char_column1,
OCTET_LENGTH(char_column1) /*** MSC-WARNING - MSCEWI3091 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/, LENGTH('Hello world') FROM PUBLIC.char_table;

Result

|CHAR_COLUMN1|OCTET_LENGTH(CHAR_COLUMN1)|LENGTH('HELLO WORLD')|
|------------|--------------------------|---------------------|
|Hello world |11                        |11                   |

Recommendations

• Manually check the data types used.

• Check the encoding of the columns used because OCTET_LENGTH can return bigger sizes when the string contains Unicode code points.

• If you need more support, you can email us at [email protected]