Distributed table shard pruning failing in production despite local test success

A query against a ClickHouse Distributed table may return correct results on a single-node local configuration but scan every shard in a multi-shard production cluster. The optimize_skip_unused_shards setting is designed to prune shards by matching the WHERE clause against the sharding key, but this optimization relies on the sharding key being a deterministic function of the filter columns.

If the determinism requirement is not met, ClickHouse defaults to scanning all shards to ensure correctness, leading to significant performance regressions as the cluster scales. A local environment with a single shard often masks this behavior, as the execution plan for a pruned query and a full-cluster scan appears identical in that context.

There is uncertainty regarding the specific expression patterns ClickHouse uses to determine if a predicate maps to the sharding key. While force_optimize_skip_unused_shards can be used to trigger errors when pruning is impossible, its behavior with nondeterministic functions is not clearly defined across different versions.

What specific criteria does ClickHouse use to validate a filter as a deterministic function of the sharding key? How does allow_nondeterministic_optimize_skip_unused_shards interact with the pruning logic when complex expressions are involved? Is there a way to verify if a query is capable of pruning without deploying to a multi-shard test environment?