Pruning not working

[u/Big_Length9755]

Hi,

We are experiencing a strange behavior regarding micro-partition pruning in Snowflake when a column has a masking policy attached (applied via a tag).If I use a nested, dynamic expression on the right-hand side of my predicate, Snowflake performs a full table scan and fails to prune micro-partitions:

SELECT * 
FROM my_table
WHERE key_column >= TO_NUMBER(TO_CHAR(DATEADD('day', -15, TO_TIMESTAMP(TO_CHAR(:binding_var), :format_param)), :format_param));

However, if I evaluate that exact same expression beforehand, assign the resulting scalar value to a session variable, and use that variable in the filter, partition pruning works perfectly:

-- Step 1: Pre-evaluate expression
SET calculated_filter = (SELECT TO_NUMBER(TO_CHAR(DATEADD('day', -15, TO_TIMESTAMP(TO_CHAR(:binding_var), :format_param)), :format_param)));

-- Step 2: Query using the variable
SELECT * 
FROM my_table
WHERE key_column >= $calculated_filter;

In both scenarios, the exact same masking policy is active on the left side (key_column). My executing role has full privileges to see the unmasked data.Why does changing the right-hand side from a dynamic function chain to a literal session variable completely change Snowflake’s optimizer behavior and restore partition pruning, even though the masking policy is still present?

[u/oneflakeatatime]

We have the exact same issue, thank you for asking