CASE WHEN logical expression not short circuiting properly

[exaatto]

What happens?

Logical expression in CASE WHEN is not short circuiting properly. For example CASE WHEN a AND b, b is also evaluated when a is FALSE.

To Reproduce

drop table t;
create table t (n text);
insert into t values ('1'),('0'),('');
select n, case 
when n <> '' and cast(substr(n, 1, 1) as int) <= 0 then '0' 
when n <> '' and cast(substr(n, 1, 1) as int) > 0 then '1' 
else '2'end as x from t;

The above code gets an error:

Error: Conversion Error: Could not convert string '' to INT32

But if delete one WHEN statement, a correct result could be yield:

drop table t;
create table t (n text);
insert into t values ('1'),('0'),('');
select n, case 
when n <> '' and cast(substr(n, 1, 1) as int) <= 0 then '0' 
else '2'end as x from t;

Result:

┌───┬───┐
│ n │ x │
├───┼───┤
│ 1 │ 2 │
│ 0 │ 0 │
│   │ 2 │
└───┴───┘

Environment (please complete the following information):

• OS: Linux
• DuckDB Version: v0.3.2-dev175 847e60d
• DuckDB Client: cli

Before Submitting

• [YES ] Have you tried this on the latest master branch?

• Python: pip install duckdb --upgrade --pre
• R: install.packages("https://github.com/duckdb/duckdb/releases/download/master-builds/duckdb_r_src.tar.gz", repos = NULL)
• Other Platforms: You can find binaries here or compile from source.

• [ YES] Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

[exaatto]

I'm not sure short-circuiting is a standard behavior in ANSI SQL, but I've tried PostgreSQL/SQLite of which both produce proper and consistent short-circuiting result.

[Mytherin]

Thanks for the report!

Not sure about the SQL standard either, but short circuiting should happen. Will have a look.

[exaatto]

Thanks Mytherin! We can confirm this patch works in our situation.