Baike.dev
Log in
Back to tool/Back to issues
#120646·ClickHouse

Date/DateTime `CASE` with a string literal takes ELSE: `accurateEquals` guard

Author: clickgapaiCreated Sep 17, 2026Updated Sep 17, 2026
Labelscomp-regular-function

Describe what's wrong

CASE d WHEN '2020-01-01' THEN 'match' ELSE 'nomatch' END returns nomatch for a row whose Date is 2020-01-01, while d = '2020-01-01' on the same row is 1. Same for Date32 and DateTime. The mapping entry is silently discarded, so with no ELSE the result is NULL and with a single WHEN the branch is dead. The identical predicate throws instead of mis-answering for other expression types: CASE ip WHEN '1.2.3.4' ..., CASE uuid WHEN '61f0...' ..., CASE dec WHEN '1.5' ... and CASE dt64 WHEN '2020-01-01 00:00:00.000' ... all fail with BAD_TYPE_OF_FIELD 'Cannot compare DB::IPv4 with std::basic_string'. This PR fixes the Enum leg of that same predicate and leaves these.

  • Root cause: src/Functions/transform.cpp:828 (and 805, 841) admits a mapping entry only if accurateEquals says the cast value equals the original literal. FieldVisitorAccurateEquals resolves an arithmetic-vs-String pair by readText-parsing the string (src/Common/FieldAccurateComparison.cpp:127-140), so it compares a Date's day count 18262 against '2020-01-01' parsed as 2020 -> false -> insertIfNotPresent is skipped and the entry never exists. The check is unnecessary for a String/FixedString source in the first place: castColumn already rejects an unparseable literal (CAST('nonsense' AS Date) throws), which is exactly the argument the PR's new comment (792-794) makes for Enum - but the new bypass is conditioned on isEnum(removeNullable(from_type)) (795), so only Enum gets it. For Decimal/DateTime64/UUID/IPv4 fields the visitor has no String branch at all and throws BAD_TYPE_OF_FIELD.
Analysis details (evidence, affected locations, impact)

Why we believe this is a bug: CASE expr WHEN ... -> caseWithExpression -> FunctionCaseWithExpression::executeImpl (src/Functions/caseWithExpression.cpp:229-241) builds transform(expr, [when...], [then...], else) passing the RAW expression plus an array of the WHEN-value supertype (String for a date literal) with no cast to the expression type -> FunctionTransform::buildImpl (src/Functions/transform.cpp:955) -> initializeTransformCache (711) casts the String array to from_type = Date (733-740) and then gates each entry at line 828 on accurateEquals((*cache->from_column)[i], (*from_column_uncast)[i]), i.e. Field(UInt64 18262) vs Field(String '2020-01-01').

Affected locations:

Impact: Silent wrong results from ordinary SQL: every CASE <date expression> WHEN '<date literal>' takes the ELSE branch (or yields NULL without ELSE), disagreeing with =, multiIf and the CASE documentation. IPv4/UUID/Decimal/DateTime64 expressions in the same shape fail the query with an internal 'Cannot compare' message. Pre-existing on master - NOT introduced by this PR - but it is the same predicate, the same three lines this PR edits, and the same user-facing shape as the issue the PR closes, so the fix is one condition away.

Does it reproduce on most recent release?

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

How to reproduce

▶ Run on ClickHouse Fiddle

Reproducer
-- `CASE` over a date expression with a string literal must take the matching branch,
-- like the equivalent `=` comparison does.

SELECT CASE materialize(toDate('2020-01-01')) WHEN '2020-01-01' THEN 'match' ELSE 'nomatch' END;
SELECT CASE materialize(toDate32('2020-01-01')) WHEN '2020-01-01' THEN 'match' ELSE 'nomatch' END;
SELECT CASE materialize(toDateTime('2020-01-01 00:00:00', 'UTC')) WHEN '2020-01-01 00:00:00' THEN 'match' ELSE 'nomatch' END;
SELECT transform(materialize(toDate('2020-01-01')), ['2020-01-01'], ['match'], 'nomatch');
SELECT multiIf(materialize(toDate('2020-01-01')) = '2020-01-01', 'match', 'nomatch');

Expected behavior

Expected output of the reproducer above:

match
match
match
match
match

Error message and/or stacktrace

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

nomatch
nomatch
nomatch
nomatch
match
Suggested fix

Generalize the property the PR already states instead of keying it on Enum: at src/Functions/transform.cpp:795 drop the isEnum(removeNullable(from_type)) conjunct and admit any entry whose source is String/FixedString, since castColumn rejects an unparseable literal for Date/DateTime/Decimal/UUID/IPv4/Enum alike - leaving accurateEquals to do what it is actually for, catching lossy numeric narrowing. Trade-off: a String literal that parses but is not exact ('1.0000000000000000001' -> Float32) would then be admitted as its rounded value rather than dropped; if that is unacceptable, the alternative is to compare in one type space (cast cache->from_column back to the array's nested type and compare same-type fields), at the cost of one extra cast at build time and of dropping non-canonical literals such as '2020-1-1'.

Additional context

Same pattern as #102511 (found by: lexical, vector, fix_path; vector: cosine distance 0.41 (agrees with the lexical leg)).

Open risks:

  • The BAD_TYPE_OF_FIELD variant (IPv4/UUID/Decimal/DateTime64) is the same line and the same fix, but its symptom is a failed query rather than a wrong answer; if only the wrong-results leg is fixed, those types stay broken.
  • transform with an explicitly written Array(String) mapping over a Date column has the same wrong result, so fixing this changes output for any user query that currently relies on the dropped entry (it can only turn a default/ELSE result into a mapped one).

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 P1 · Finding h_pr117520_001

Source: ClickHouse/ClickHouse

View original on GitHubView discussion on GitHub