Omega359 opened a new issue, #13779:
URL: https://github.com/apache/datafusion/issues/13779
### Describe the bug
sqlite random/expr/slt_good_10.slt:
```sql
SELECT ALL + + MIN ( - CAST ( + 6 AS INTEGER ) ) * CASE WHEN NULL BETWEEN -
88 AND ( - - 91 ) * - AVG ( 95 ) THEN COUNT ( - 34 ) END
```
Both PG and sqlite have a result of 'null'. DF result is '-6'
The difference looks to be the handling of a case clause that doesn't match
and doesn't have an else block. Most db's including PG have the result of a
case block with no matches and no else to be 'null'. It seems DF is '1'
```sql
> select CASE WHEN NULL BETWEEN - 88 AND ( - - 91 ) * - AVG ( 95 ) THEN
COUNT ( - 34 ) END;
+------------------------------------------------------------------------------------------------------+
| CASE WHEN NULL BETWEEN Int64(-88) AND (- Int64(-91)) * (- avg(Int64(95)))
THEN count(Int64(-34)) END |
+------------------------------------------------------------------------------------------------------+
| 1
|
+------------------------------------------------------------------------------------------------------+
```
The following is duckdb's behavior with the same sql:
```sql
D select CASE WHEN NULL BETWEEN - 88 AND ( - - 91 ) * - AVG ( 95 ) THEN
COUNT ( - 34 ) END;
┌───────────────────────────────────────────────────────────────────────────────────────┐
│ CASE WHEN ((NULL BETWEEN -88 AND (91 * -(avg(95))))) THEN (count(-34))
ELSE NULL END │
│ int64
│
├───────────────────────────────────────────────────────────────────────────────────────┤
│
│
└───────────────────────────────────────────────────────────────────────────────────────┘
D select typeof(CASE WHEN NULL BETWEEN - 88 AND ( - - 91 ) * - AVG ( 95 )
THEN COUNT ( - 34 ) END);
┌───────────────────────────────────────────────────────────────────────────────────────────────┐
│ typeof(CASE WHEN ((NULL BETWEEN -88 AND (91 * -(avg(95))))) THEN
(count(-34)) ELSE NULL END) │
│ varchar
│
├───────────────────────────────────────────────────────────────────────────────────────────────┤
│ BIGINT
│
└───────────────────────────────────────────────────────────────────────────────────────────────┘
```
### To Reproduce
sql above.
### Expected behavior
I would expect the same result as PG and other db's.
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For additional commands, e-mail: github-h...@datafusion.apache.org
