Hello,
I'm conducting research on utilizing CLS in a project. We are implementing a lookup table to determine what tags a user can see. The CLS function looks like this:
CREATE OR REPLACE FUNCTION {catalog}.{schema}.mask_column(value VARIANT, tag STRING)
RETURNS VARIANT
COMMENT 'ABAC utility: Completely mask string values with asterisks'
RETURN
CASE
WHEN EXISTS (
SELECT 1
FROM {catalog}.{schema}.lookup_access
WHERE (lookup_access.tag = mask_column.tag
AND principal = current_user())
AND can_view = true
) THEN value
WHEN schema_of_variant(value) = 'DATE' THEN '1970-01-01'::VARIANT
WHEN schema_of_variant(value) = 'TIMESTAMP' THEN '1970-01-01'::VARIANT
WHEN schema_of_variant(value) = 'INT' THEN 0::VARIANT
WHEN schema_of_variant(value) = 'BIGINT' THEN 0::VARIANT
WHEN schema_of_variant(value) = 'DOUBLE' THEN 0.00::VARIANT
WHEN schema_of_variant(value) = 'STRING' THEN '***'::VARIANT
ELSE NULL::VARIANT
END;
Used alone, it works perfectly fine. It also works as intended when implemented with a policy. But, when using a policy with this function, and selecting a DATE type column from a table, the following error appears:
[INVALID_VARIANT_CAST] The variant value `0` cannot be cast into `"DATE"`. Please use `try_variant_get` instead. SQLSTATE: 22023
If I remove the date fields from the select query, the function works as intended. Additionally, if I remove the lookup table query from the UDF, the policy also works for date-type columns.
Why is this happening? Are there any workarounds, or is this a known bug?
Thank you!
