apache / apache/datafusion

The 54.0.0 upgrade guide's CASE workaround is rewritten to a conjunction

Open
#25,136 1 comment 0 reactions 1 assignee Claimed by @emecii View on GitHub
bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.