Skip to content

Evaluates CASE branches even if their WHEN clause is false #8909

New issue

Have a question about this project? # for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “#”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? # to your account

Closed
tv42 opened this issue Jan 19, 2024 · 2 comments · Fixed by #8957
Closed

Evaluates CASE branches even if their WHEN clause is false #8909

tv42 opened this issue Jan 19, 2024 · 2 comments · Fixed by #8957
Labels
bug Something isn't working

Comments

@tv42
Copy link
Contributor

tv42 commented Jan 19, 2024

Describe the bug

Datafusion evaluates CASE ... WHEN ... THEN expressions even for non-true branches, and bubbles up runtime errors from them.

This is wrong because the CASE could be explicitly protecting against the divide-by-zero case, and differs from other sql engines.

To Reproduce

SELECT CASE 1 WHEN 2 THEN 42/0 END;
Optimizer rule 'simplify_expressions' failed
caused by
Arrow error: Divide by zero error

Expected behavior

I expected the CASE to evaluate to NULL, as none of its WHEN branches were true and it did not have an ELSE.

Compare to SQLite:

sqlite> .nullvalue NULL
sqlite> SELECT CASE 1 WHEN 2 THEN 42/0 END;
NULL

Compare to Postgres:

postgres=# \pset null 'NULL'
Null display is "NULL".
postgres=# SELECT CASE 1 WHEN 2 THEN 42/0 END;
 case
------
 NULL
(1 row)

Additional context

No response

@alamb
Copy link
Contributor

alamb commented Jan 19, 2024

Possible related #8833 / #8814

cc @haohuaijin and @liukun4515

@haohuaijin
Copy link
Contributor

haohuaijin commented Jan 20, 2024

It looks like related to the simplify_expressions rule because the simplify_expressions rule does the ConstEvaluator, result in 42/0 evaluated, get Divide by zero error.

This is a similar problem to what #8814 describes. maybe we should not do ConstEvaluator for short-circuited operators.

I also do some test in datafusion-cli v34

DataFusion CLI v34.0.0
❯ SELECT CASE 1 WHEN 2 THEN 42/0 END;
Optimizer rule 'simplify_expressions' failed
caused by
Arrow error: Divide by zero error
❯ select 4/0;
Optimizer rule 'simplify_expressions' failed
caused by
Arrow error: Divide by zero error
❯ select 1 > 4 and 5/0 > 2;
Optimizer rule 'simplify_expressions' failed
caused by
Arrow error: Divide by zero error

# for free to join this conversation on GitHub. Already have an account? # to comment
Labels
bug Something isn't working
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants