Only deterministic expression in PK:

Example of a deterministic (but non-injective) primary key:

ENGINE = MergeTree()
ORDER BY left(pod, length(pod) - length(substringIndex(pod, '-', -1)) - 1);

Example predicates that can use the index:

SELECT * FROM table WHERE pod = 'alice';
SELECT * FROM table WHERE pod IN ('alice', 'bob');
SELECT * FROM table WHERE has(['alice', 'bob'], pod);

Deterministic + Injective expression in PK:

Example of an injective primary key:

ENGINE = MergeTree()
ORDER BY reverse(tuple(reverse(p), hex(p)))

Example predicates that can effectively use the index:

SELECT * FROM table WHERE p != 'abc';
SELECT * FROM table WHERE p NOT IN ('abc', '12345');
SELECT * FROM table WHERE NOT has(['abc', '12345'], p);

See tests/queries/0_stateless/03778_deterministic_injective_functions_key_condition.sql for concrete examples.

Changelog category (leave one):

• Performance Improvement

Changelog entry (a user-readable short description of the changes that goes into CHANGELOG.md):

Allow any deterministic expression in Primary Key to be used for data skipping (e.g. ORDER BY cityHash64(user_id)/ ORDER BY length(user_id)). For deterministic expressions, ClickHouse can apply the expression to query constants and use the result in the primary key index for predicates like =, IN, and has. If the expression is also injective (e.g. ORDER BY hex(p) or ORDER BY reverse(tuple(reverse(p), hex(p)))), we can effectively use the index for the negated forms: !=, NOT IN, and NOT has. Closes #10685. Closes #82161.

Documentation entry for user-facing changes

• Documentation is written (mandatory for new features)