The 54.0.0 upgrade guide's CASE workaround is rewritten to a conjunction
- Dominant language
- Rust
- Stars
- 9.3k
- Forks
- 2.4k
- Avg merge
- 3d 7h
- Merged PRs (30d)
- 344
Description
### Describe the bug
Currently, `CASE` is rewritten into a conjunction, so a `THEN` branch that can fail runs on rows the `WHEN` excluded.
### To Reproduce
```sql
CREATE VIEW t AS SELECT * FROM (VALUES ('1'), ('abc'), ('2')) s(s);
CREATE VIEW u AS SELECT * FROM (VALUES ('1'), ('2')) s(s);
EXPLAIN SELECT s FROM t WHERE CASE WHEN s ~ '^[0-9]+$' THEN CAST(s AS INT) > 0 ELSE false END;
-- FilterExec predicate: CAST(column1 AS Int32) > 0 AND column1 ~ ^[0-9]+$
SELECT s FROM t WHERE s = 'zzz' AND CAST(s AS INT) > 0;
-- no rows, no error, so an all-false left operand does skip the cast
SELECT s FROM u WHERE CASE WHEN s ~ '^[0-9]+$' THEN CAST(s AS INT) > 0 ELSE false END;
-- 1 and 2, so it only bites once a row would fail the THEN branch
SELECT s FROM t WHERE CASE WHEN s ~ '^[0-9]+$' THEN CAST(s AS INT) > 0 ELSE false END;
-- Cast error: Cannot cast string 'abc' to value of Int32 type
```
### Expected behavior
`1` and `2`. `CAST(s AS INT)` runs only on rows where the `WHEN` held.
### Additional context
The 54.0.0 upgrade guide prescribes this exact `CASE`, "which has standardized short-circuit semantics".
With `ELSE false` the rewrite folds to `X AND A`.
datafusion-cli 55.0.0
Contributor guide
Assessment
This issue has not been assessed yet.