When running Javascript UDF with VARIANT data type passed into it as parameter, it fails with error:
TIME inside VARIANT value – not supported
This issue can be reproduced using below steps:
-- Have a JS UDF that accepts VARIANT data type CREATE OR REPLACE FUNCTION UDF_VARIANT_TIMESTAMP_TEST(V VARIANT) RETURNS VARIANT LANGUAGE JAVASCRIPT AS $$ RETURN V; $$; -- A table with VARIANT column CREATE OR REPLACE TABLE TEST (A VARIANT); -- Insert TIME value into VARIANT column INSERT INTO TEST SELECT TO_VARIANT(OBJECT_CONSTRUCT('TIME', '00:00:00'::TIME)); -- Pass the VARIANT to the JS function will fail with error SELECT UDF_VARIANT_TIMESTAMP_TEST(A) FROM TEST;
And the last SELECT statement will fail with above mentioned error.
This is because Javascript does not support the TIME data type, as it only has DATE data type, so Snowflake is NOT able to perform the conversion. To avoid unexpected errors, the type checking is enforced when VARIANT data type is being converted to Javascript’s Object.
The solution is to NOT use TIME data type in the VARIANT, use STRING instead. So below will work:
CREATE OR REPLACE TABLE TEST (A VARIANT); INSERT INTO TEST SELECT TO_VARIANT(OBJECT_CONSTRUCT('TIME', '00:00:00')); SELECT UDF_VARIANT_TIMESTAMP_TEST(A) FROM TEST;
Hope above can help!