SSC-EWI-TD0051

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

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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 [email protected]