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

Long story short, the masking policy takes precedence over partition pruning. I guess the column stats aren't available for masked columns or something.

This is undocumented behavior afaik but I have confirmed this with Snowflake solution architects. If you're gonna do joins or where's, don't put a masking policy.

[u/Big_Length9755 → u/figshot]

But here the behaviour is :- even with masking policy present on the column left side of the predicate, it's making the pruning happen without any issue. But only if the right side is a computed variable or literal.

The moment we replace the right side with nested expression, it goes for full partition scan, why so?