ClickHouse / ClickHouse/ClickHouse
`transform` and `CASE ... WHEN` never match a `Nullable(Enum)` value: every row silently gets the default/ELSE result
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
**TL;DR:** When the first argument of `transform` is a `Nullable(Enum...)`, the internal match table is built empty, so every row — including rows whose value is listed in the match array — silently falls through to the default. Since `CASE x WHEN ... THEN ... ELSE ... END` compiles to `transform`, an ordinary `CASE` over a `Nullable(Enum)` column returns the `ELSE` branch for every row. Non-nullable `Enum`, `Nullable(String)` and `Nullable` numeric inputs all match correctly, so the wrong result is easy to miss.
### Company or project name
N/A
### Describe what's wrong
1. `CASE x WHEN 'a' THEN 'q' ELSE 'z' END` over a `Nullable(Enum8('a' = 1, 'b' = 2))` column returns `z` for the row whose value is `a`. The same `CASE` over the `Nullable(String)` cast of the column returns `q`, and over a non-nullable `Enum8` column it returns `q`, so only the `Nullable(Enum)` combination is broken.
2. The direct form fails identically, including for a bare constant: `transform(CAST('a', 'Nullable(Enum8(\'a\'=1,\'b\'=2))'), ['a'], ['q'], 'z')` returns `z`.
3. Mechanism: `initializeTransformCache` in `src/Functions/transform.cpp` classifies the input with `WhichDataType which(from_type)` on the raw argument type, so for `Nullable(Enum8)` the `which.isEnum()` exemption in the numeric branch (`which.isEnum() || accurateEquals(...)` — "the correctness of strings are already checked by casting them to the Enum type") never applies. The keys land in the generic branches, whose guard `accurateEquals((*cache->from_column)[i], (*from_column_uncast)[i])` compares the cast key (the enum code, an `Int64` `1`) with the uncast literal (the `String` `'a'`). They are never equal, every key is discarded, the lookup table stays empty, and each row takes the default (4-argument form) or the passthrough (3-argument form).
### Does it reproduce on the most recent release?
Yes — reproduced identically on 26.9.1.1 and on 26.4.1.1; the code is unchanged on current master. Not a regression.
### How to reproduce
```sql
CREATE TABLE t_ne (x Nullable(Enum8('a' = 1, 'b' = 2))) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO t_ne VALUES ('a'), (NULL), ('b');
SELECT x, CASE x WHEN 'a' THEN 'q' ELSE 'z' END FROM t_ne;
-- a z <- wrong, should be q
-- \N z
-- b z
SELECT transform(x, ['a'], ['q'], 'z') FROM t_ne; -- z, z, z <- wrong
SELECT transform(CAST(x, 'Nullable(String)'), ['a'], ['q'], 'z') FROM t_ne; -- q, z, z (correct, control)
SELECT transform(CAST('a', 'Nullable(Enum8(\'a\'=1,\'b\'=2))'), ['a'], ['q'], 'z'); -- z <- wrong
SELECT transform(CAST('a', 'Enum8(\'a\'=1,\'b\'=2)'), ['a'], ['q'], 'z'); -- q (correct, control)
```
### Expected behavior
The `a` row returns `q` — the same result these queries produce for a non-nullable `Enum8` column and for the `Nullable(String)` cast of the same data. (A NULL value taking the default branch is correct and not part of this report.)
Contributor guide
Assessment
This issue has not been assessed yet.