#120647 transform keys the mapping table on raw float bits: -0.0 misses the entry for 0

Describe what's wrong

transform and the CASE expr WHEN form built on it never match a Float value of -0.0 against a mapping entry for 0, although -0.0 = 0 is true. SELECT CASE materialize(-0.0) WHEN 0 THEN 'match' ELSE 'no' END returns no. The same CASE with a non-constant WHEN operand (WHEN materialize(0)) returns match, because caseWithExpression then falls back to multiIf with real = semantics -- so one logical expression yields two different answers depending only on whether the WHEN operand is constant-folded.

Root cause: src/Functions/transform.cpp:817 -- the mapping-table key is the value's memory representation (memcpy(dst, ref.data(), ref.size()) of cache->from_column->getDataAt(i)), and the lookup side mirrors it with bit_cast<UInt64>. For Float32/Float64 that is bit equality, not = equality: it splits the two representations of zero, which = treats as equal. The comment on :798 ('for the purpose of bitwise equality we can treat them as UInt64') justifies the UInt64 storage of the key, but nothing normalizes -0.0 to +0.0 on either side. The admission check on :805 is NOT involved -- accurateEquals(0.0, 0.0) is true, so the entry for 0 is present; the control query transform(materialize(0.0), [0.0], ['match'], 'no') returns match, proving the table is populated and only the key matching is wrong.

Analysis details (evidence, affected locations, impact)

Why we believe this is a bug: caseWithExpression::executeImpl routes every all-constant CASE expr WHEN ... through transform (src/Functions/caseWithExpression.cpp:241 function_base->execute(transform_args, ...)) -> initializeTransformCache builds the lookup table, keying each admitted entry on the raw bytes of the cast value (src/Functions/transform.cpp:817-818) -> executeNum/executeNumToStringHelper/executeNumToNumHelper look the input up by bit_cast<UInt64>(pod[i]) (transform.cpp:328, :393, :462). +0.0 is 0x0000000000000000 and -0.0 is 0x8000000000000000, so the lookup misses. The Nullable(Float) variant takes the hash table and misses the same way: keys come from cache->from_column->updateHashWithValue (:844) and lookups from in->updateHashWithValue (:267), both of which hash the raw float bits.

Affected locations:

src/Functions/transform.cpp:817 — num mapping table: key is the raw bytes of the cast value
src/Functions/transform.cpp:328 — executeNum generic lookup by bit_cast(pod[i])
src/Functions/transform.cpp:393 — executeNumToStringHelper lookup by bit_cast(pod[i])
src/Functions/transform.cpp:462 — executeNumToNumHelper lookup by bit_cast(pod[i])
src/Functions/transform.cpp:844 — anything mapping table (Nullable(Float)): key is SipHash of the raw float bits
src/Functions/transform.cpp:267 — executeAnything lookup: SipHash of the raw float bits of the input
src/Functions/caseWithExpression.cpp:241 — CASE with all-constant WHEN/THEN is executed through transform; the non-constant form falls through to multiIf and disagrees

Impact: Silent wrong results for ordinary SQL over Float columns that contain a negative zero. -0.0 is produced by everyday expressions (round(-0.4), trunc(-0.2), -1 * 0.0, toFloat64('-0'), float underflow of a negative value) and by Parquet/Arrow/CSV ingestion, so CASE round(x) WHEN 0 THEN ... END and transform(x, [0], ...) silently take the ELSE/default branch for rows that WHERE x = 0 counts as matching. The in-tree test 04325_sparsity_float_negative_zero_excluded.sql states the opposite invariant for the same value class. The constant/non-constant split makes the failure non-obvious: rewriting WHEN 0 as WHEN materialize(0) fixes the answer.

Does it reproduce on most recent release?

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

How to reproduce

▶ Run on ClickHouse Fiddle

Reproducer

select transform(number, [1], [toFloat32(1)], toFloat32(1)) from numbers(3);
SELECT '---';
select transform(number, [3], [toFloat32(1)], toFloat32(1)) from numbers(6);
SELECT '--- negative zero compares equal to zero, so a mapping entry for 0 must match it';
SELECT materialize(-0.0) = 0;
SELECT transform(materialize(0.0), [0.0], ['match'], 'no');
SELECT transform(materialize(-0.0), [-0.0], ['match'], 'no');
SELECT transform(materialize(-0.0), [0.0], ['match'], 'no');
SELECT transform(CAST(materialize(-0.0) AS Nullable(Float64)), [0.0], ['match'], 'no');
SELECT CASE materialize(-0.0) WHEN 0 THEN 'match' ELSE 'no' END;
SELECT CASE materialize(-0.0) WHEN materialize(0) THEN 'match' ELSE 'no' END;

Expected behavior

Expected output of the reproducer above:

--- negative zero compares equal to zero, so a mapping entry for 0 must match it
1
match
match
match
match
match
match

Error message and/or stacktrace

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

--- negative zero compares equal to zero, so a mapping entry for 0 must match it
1
match
match
no
no
no
match

Suggested fix

Normalize negative zero when building and probing the mapping table: after the cast, replace -0.0 with +0.0 in cache->from_column (and apply the same normalization to the probe value in executeNum, executeNumToStringHelper, executeNumToNumHelper and executeAnything). Cheapest equivalent: add + 0.0 to the float value before taking the key on both sides. If bit matching is to be preserved for transform itself, instead add a Float/Nullable(Float) expression-type exclusion to can_use_transform in src/Functions/caseWithExpression.cpp:208-212, alongside the existing Nullable-WHEN and Dynamic/Variant exclusions, so CASE keeps = semantics.

Additional context

Open risks:

A mapping array containing both zeros, transform(x, [0.0, -0.0], ['pos','neg'], 'no'), currently resolves 0.0 to pos and -0.0 to neg; under = semantics both must take the first match (pos). Whichever fix is chosen must also collapse such duplicate entries via the existing insertIfNotPresent first-wins rule.

Found during automated review of PR #117520; the bug predates that PR (it reproduces on the master build just before it merged), so the introducing change is not identified yet. Severity P2 · Finding h_pr117520_101