Nullable dict key + transform_null_in: declined rewrite loses PK pruning

Describe what's wrong

With transform_null_in = 1 and a Nullable key expression, optimize_inverse_dictionary_lookup stops rewriting dictGet(dict, attr, key) = const, so the predicate stays a per-row dictGet that no index can prune. SELECT count() FROM t WHERE dictGet('d', 'a', nid) = 'x' on ENGINE = MergeTree ORDER BY nid reads Granules: 125/125 instead of Granules: 1/125 - a full scan where one granule sufficed. Row results are unchanged (3 either way), so no correctness test sees it. Reachable from an ordinary local SELECT; no distributed table, no parallel replicas.

• Root cause: The two InverseDictionaryLookupPass call sites treat std::nullopt as "do not rewrite" when the only thing the helper could not supply was a shard-stable NAME. Pre-PR both sites emitted in unconditionally, which was semantically correct locally (in propagates a NULL left argument exactly as dictGet(...) = const does) and prunable; only the initiator/shard name divergence was broken. Declining trades a correct-and-prunable local plan for a full scan. The sibling OR/AND chain sites do not pay this, because their fallback equals/notEquals chain is still a prunable atom.

Analysis details (evidence, affected locations, impact)

Why we believe this is a bug: InverseDictionaryLookupPass.cpp:485 (constant-fold arm) and :531 (IN-subquery arm) ask getInFunctionNameForPassCreatedNode ([src/Analyzer/Utils.cpp:222](https://github.com/ClickHouse/ClickHouse/blob/2d19d1b6e073/src/Analyzer/Utils.cpp#L222)) for the name to use. At Utils.cpp:235 the helper returns std::nullopt because canContainNull(Nullable(UInt64)) is true, and both call sites answer nullopt with a bare return; (:488, :534) that abandons the rewrite. The surviving predicate is equals(dictGet(...), 'x'); dictGet is not in KeyCondition::atom_map, so the whole atom is unknown and no range is derived.

Affected locations:

• src/Analyzer/Passes/InverseDictionaryLookupPass.cpp:488 — constant-fold arm: return; on nullopt abandons key_expr IN <const array of keys>
• src/Analyzer/Passes/InverseDictionaryLookupPass.cpp:534 — IN-subquery arm: same return; abandons key_expr IN (SELECT key FROM dictionary(...))
• src/Analyzer/Utils.cpp:235 — canContainNull decline that produces the nullopt both sites act on

Impact: Any transform_null_in = 1 workload filtering on dictGet(dict, attr, nullable_key) <op> const loses primary-key, bloom_filter and every other skip-index pruning for that predicate and falls back to a full scan plus a per-row dictionary lookup. Measured on 1000 rows at index_granularity = 8: Granules: 1/125 -> Granules: 125/125 (125x granules) for both the constant-fold arm (= 'x') and the IN-subquery arm (LIKE 'x%'); bloom_filter on the same column goes from pruning to not pruning. Results stay correct, so the only symptom is latency and read amplification. optimize_inverse_dictionary_lookup is ON by default, so no opt-in beyond transform_null_in = 1 is required.

Does it reproduce on most recent release?

Yes — confirmed on current master (commit 2d19d1b6e073).

How to reproduce

▶ Run on ClickHouse Fiddle

Reproducer

-- The inverse-dictionary-lookup optimization turns `dictGet(dict, attr, key) = const` into
-- `key IN (matching keys)`, which the primary key can prune. A `Nullable` key expression must
-- keep that pruning whatever `transform_null_in` is set to.

DROP TABLE IF EXISTS t_05224;
DROP DICTIONARY IF EXISTS d_05224;

CREATE TABLE t_05224 (nid Nullable(UInt64)) ENGINE = MergeTree ORDER BY nid
SETTINGS index_granularity = 8, allow_nullable_key = 1;
INSERT INTO t_05224 SELECT if(number % 10 = 0, NULL, number) FROM numbers(1000);

CREATE DICTIONARY d_05224 (k UInt64, a String) PRIMARY KEY k
SOURCE(CLICKHOUSE(QUERY 'SELECT arrayJoin([1, 2, 3]) AS k, \'x\' AS a'))
LAYOUT(flat()) LIFETIME(0);

SELECT 'prunes, transform_null_in = 0', count() > 0 FROM (EXPLAIN indexes = 1 SELECT count() FROM t_05224 WHERE dictGet('d_05224', 'a', nid) = 'x' SETTINGS transform_null_in = 0) WHERE explain LIKE '%Granules: %/%' AND toUInt64OrZero(extract(explain, 'Granules: (\d+)/')) < toUInt64OrZero(extract(explain, 'Granules: \d+/(\d+)'));
SELECT 'prunes, transform_null_in = 1', count() > 0 FROM (EXPLAIN indexes = 1 SELECT count() FROM t_05224 WHERE dictGet('d_05224', 'a', nid) = 'x' SETTINGS transform_null_in = 1) WHERE explain LIKE '%Granules: %/%' AND toUInt64OrZero(extract(explain, 'Granules: (\d+)/')) < toUInt64OrZero(extract(explain, 'Granules: \d+/(\d+)'));

SELECT 'rows, transform_null_in = 0', count() FROM t_05224 WHERE dictGet('d_05224', 'a', nid) = 'x' SETTINGS transform_null_in = 0;
SELECT 'rows, transform_null_in = 1', count() FROM t_05224 WHERE dictGet('d_05224', 'a', nid) = 'x' SETTINGS transform_null_in = 1;

DROP DICTIONARY d_05224;
DROP TABLE t_05224;

Expected behavior

Expected output of the reproducer above:

prunes, transform_null_in = 0	1
prunes, transform_null_in = 1	1
rows, transform_null_in = 0	3
rows, transform_null_in = 1	3

Error message and/or stacktrace

Actual output of the reproducer above on master (2d19d1b6e073):

prunes, transform_null_in = 0	1
prunes, transform_null_in = 1	0
rows, transform_null_in = 0	3
rows, transform_null_in = 1	3

Suggested fix

Keep the rewrite at both dictionary sites. Two options, trading differently: (a) normalize the name where the tree leaves the initiator - buildQueryTreeForShard.cpp / StorageDistributed.cpp already rewrite in -> globalIn there, so applying getNullInFunctionName in the same place lets every pass keep emitting plain in and removes the need for a decline arm at all five sites; the cost is a second place that must know the rename. (b) Keep the helper but have the dictionary sites emit nullIn and restore NULL propagation around it instead of abandoning the rewrite; cheaper to write, but the wrapper has to stay transparent to KeyCondition or the pruning is lost anyway. If neither is taken, correct the PR description's cost paragraph - it currently states primary-key pruning is unchanged and that nothing working is lost at the dictionary sites.

Additional context

Same pattern as #103085 (found by: vector, fix_path; vector: cosine distance 0.32), #117532 (found by: vector; vector: cosine distance 0.32).

Open risks:

• Only a Nullable(UInt64) key was exercised. LowCardinality(Nullable(T)), Variant and Dynamic key expressions take the same canContainNull / hasDynamicStructure decline at Utils.cpp:235 and should lose pruning identically, but were not run.
• Composite-key dictionaries reach the same two call sites through a Tuple key expression; canContainNull is false for Tuple(Nullable(T), T), so those should be unaffected - not verified.

Found during automated review of PR #117897. Severity P2 · Finding h_pr117897_001

cc @groeneai @alexey-milovidov (from #117897)