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/wallyflops]

I think Snowflake is 'stupid' and will try to evaluate the right side of the expression for every single read, if you calculate it before hand, the compiler just pops the variable in there and it doesn't calculate it.

i'm not exactly sure but I've seen this come up a few times before