ClickHouse / ClickHouse/ClickHouse
Chain-to-IN rewrite changes NaN comparison semantics at default settings: a notEquals conjunction silently drops NaN rows and an equals disjunction silently adds them
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
**Describe what's wrong**
The analyzer rewrite that folds equality/inequality chains into `IN`/`NOT IN` (`LogicalExpressionOptimizerPass`, gated by `optimize_min_equality_disjunction_chain_length` / `optimize_min_inequality_conjunction_chain_length`, both default 3) silently changes query results when one of the constants is NaN. Comparison functions use IEEE semantics (`nan = nan` is 0, `nan != nan` is 1), while `IN` uses set-membership semantics where NaN matches NaN (`nan IN (nan)` is 1). The rewrite converts one into the other, so at pure default settings a conjunction of `notEquals` silently loses NaN rows and a disjunction of `equals` silently adds them, in both cases disagreeing with the query as written.
Both directions are wrong at pure defaults:
```sql
CREATE TABLE t (f Float64) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO t VALUES (nan), (1), (2), (5);
-- Conjunction of notEquals: the row with f = nan satisfies every conjunct
-- (nan != nan, nan != 1., nan != 2. are all 1), so the correct answer is 2 (rows nan and 5).
SELECT count() FROM t WHERE (f != nan) AND (f != 1.) AND (f != 2.);
-- returns 1 at defaults (rewritten to notIn(f, (nan, 1, 2)); the set matches the NaN row and drops it)
SELECT count() FROM t WHERE (f != nan) AND (f != 1.) AND (f != 2.)
SETTINGS optimize_min_inequality_conjunction_chain_length = 100000;
-- returns 2 (chain preserved: correct)
-- Disjunction of equals: no disjunct is true for the NaN row (nan = nan is 0),
-- so the correct answer is 2 (rows 1 and 2).
SELECT count() FROM t WHERE (f = nan) OR (f = 1.) OR (f = 2.);
-- returns 3 at defaults (rewritten to in(f, (nan, 1, 2)); the set matches the NaN row and adds it)
SELECT count() FROM t WHERE (f = nan) OR (f = 1.) OR (f = 2.)
SETTINGS optimize_min_equality_disjunction_chain_length = 100000;
-- returns 2 (chain preserved: correct)
```
The operator-level divergence that the rewrite smuggles across:
```sql
SELECT nan = nan; -- 0
SELECT nan IN (nan); -- 1
```
`EXPLAIN QUERY TREE` on the conjunction at defaults shows the conversion:
```
FUNCTION id: 4, function_name: notIn, function_type: ordinary, result_type: UInt8
CONSTANT id: 7, constant_value: Tuple_(Float64_nan, Float64_1, Float64_2), constant_value_type: Tuple(Float64, Float64, Float64)
```
The chain-collection lambdas in `LogicalExpressionOptimizerPass.cpp` (`add_not_equals_function_if_not_present` in the AND arm and `add_equals_function_if_not_present` in the OR arm) guard only NULL literals (`!literal->getValue().isNull()`) before admitting a constant into the future `IN` tuple; there is no NaN guard, although the rewrite `x = a OR x = b => x IN (a, b)` does not hold when a constant is NaN. A NaN constant reaching either arm should keep that conjunct/disjunct out of the conversion (or suppress the conversion), the same way NULL literals are already kept out.
**How to reproduce**
* Which ClickHouse server version to use: reproduced on current master (26.9.1.417), 26.8, 25.8 and 24.8 (https://fiddle.clickhouse.com/ for the released versions) — long-standing, not a regression.
* Queries above; pure default settings. `clickhouse local` reproduces it too.
Related but distinct: #116852 (same rewrite breaks String/FixedString padding equivalence), #112245 and #114014 (same rewrite re-interprets DateTime literals under custom-key parallel replicas), #116927 (explicit positive `IN (nan)` loses NaN rows to part pruning — that issue is about index pruning of a user-written `IN`; this one is about the rewrite silently changing the semantics of a query that never used `IN`).
**Expected behavior**
The chain queries return the same results whether or not the chain-to-`IN` rewrite fires: `WHERE (f != nan) AND (f != 1.) AND (f != 2.)` returns the rows where every conjunct evaluates true under IEEE comparison semantics (rows `nan` and `5`, count 2), and `WHERE (f = nan) OR (f = 1.) OR (f = 2.)` returns the rows where some disjunct is true (rows `1` and `2`, count 2).
Contributor guide
Assessment
This issue has not been assessed yet.