SSC-EWI-TD0051

Teradata BYTES function results differs from Snowflake LENGTH function for byte columns

Severity

Low

Description

Since Teradata byte datatype has a fixed length, BYTES function will always count the trailing zeros inserted to fit smaller byte type values into the column, returning the size of the column instead of the size of the value inserted originally. However, Snowflake binary type has variable size, meaning that the LENGTH function will always return the size of the inserted values. Take the following code as an example:

Teradata:

IN -> Teradata_01.sql

create table exampleTable(
	bytecol byte(10)
);

insert into exampleTable values ('2B'XB);

select bytes(bytecol) from exampleTable; 
-- Will return 10, the size of bytecol

Equivalent code in Snowflake:

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE exampleTable (
	bytecol BINARY
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

INSERT INTO exampleTable
VALUES (TO_BINARY('2B'));

SELECT
	LENGTH(bytecol) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0051 - TERADATA BYTES FUNCTION RESULTS DIFFER FROM SNOWFLAKE LENGTH FUNCTION FOR BYTE TYPE COLUMNS ***/!!! from
	exampleTable;
	-- Will return 10, the size of bytecol

Example code:

Input code:

IN -> Teradata_02.sql

create table sampleTable(
    byteColumn byte(10),
    varbyteColumn varbyte(15)
);

select bytes(byteColumn), bytes(varbyteColumn) from sampleTable;

Output code:

OUT -> Teradata_02.sql

CREATE OR REPLACE TABLE sampleTable (
    byteColumn BINARY,
    varbyteColumn BINARY(15)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

SELECT
    LENGTH(byteColumn) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0051 - TERADATA BYTES FUNCTION RESULTS DIFFER FROM SNOWFLAKE LENGTH FUNCTION FOR BYTE TYPE COLUMNS ***/!!!,
    LENGTH(varbyteColumn) from
    sampleTable;

Recommendations

• Analyze the use given to the BYTES function results, the Snowflake LENGTH function behavior was the one desired from the start and no changes are required.

• If you need more support, you can email us at [e-mail]