v.20.11Improvement

Add allow_nondeterministic_optimize_skip_unused_shards for Sharding Keys

Add allow_nondeterministic_optimize_skip_unused_shards (to allow non deterministic like rand() or dictGet() in sharding key). #16105 (Azat Khuzhin).
Introduces the allow_nondeterministic_optimize_skip_unused_shards setting to allow non-deterministic functions such as rand() or dictGet() in the sharding key optimizations.

Why it matters

This feature enables users to use non-deterministic functions in sharding keys when optimizing queries to skip unused shards, which was previously restricted. It solves the problem of limited function support in sharding key filters and allows more flexible and dynamic query optimization in distributed environments.

How to use it

Enable the feature by setting allow_nondeterministic_optimize_skip_unused_shards=1 in the session, user profile, or query settings. This allows non-deterministic functions like rand() or dictGet() to be used within sharding key expressions for shard filtering.