sql result discrepency with sqlite and postgres

[Omega359]

Describe the bug

sqlite random/expr/slt_good_10.slt:

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'

> 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:

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

[Omega359]

I believe another sql that has the same cause is:

External error: query result mismatch:
[SQL] SELECT ALL + CASE WHEN AVG ( ALL + 88 ) >= 74 + + - 95 - CASE 23 WHEN 41 + - COUNT ( 16 ) THEN + 48 - 12 * + 70 - - ( - 95 ) * + 50 + 37 + + + COALESCE ( - 68, - - ( ( + 66 ) ) / + 28 ) END - + - 38 THEN + AVG ( 19 ) END - + 69
[Diff] (-expected|+actual)
-   NULL
+   -50
at test_files/sqlite/random/expr/slt_good_104.slt:38923

[Omega359]

This looks to have been fixed with #13885

DataFusion CLI v44.0.0
> SELECT ALL + + MIN ( - CAST ( + 6 AS INTEGER ) ) * CASE WHEN NULL BETWEEN - 88 AND ( - - 91 ) * - AVG ( 95 ) THEN COUNT ( - 34 ) END;
+--------------------------------------------------------------------------------------------------------------------------+
| min((- Int64(6))) * CASE WHEN NULL BETWEEN Int64(-88) AND (- Int64(-91)) * (- avg(Int64(95))) THEN count(Int64(-34)) END |
+--------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------+
1 row(s) fetched. 
Elapsed 0.014 seconds.
> SELECT ALL + CASE WHEN AVG ( ALL + 88 ) >= 74 + + - 95 - CASE 23 WHEN 41 + - COUNT ( 16 ) THEN + 48 - 12 * + 70 - - ( - 95 ) * + 50 + 37 + + + COALESCE ( - 68, - - ( ( + 66 ) ) / + 28 ) END - + - 38 THEN + AVG ( 19 ) END - + 69;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CASE WHEN avg(Int64(88)) >= Int64(74) + Int64(-95) - CASE Int64(23) WHEN Int64(41) + (- count(Int64(16))) THEN Int64(48) - Int64(12) * Int64(70) - (- Int64(-95)) * Int64(50) + Int64(37) + coalesce(Int64(-68),(- (- Int64(66))) / Int64(28)) END - Int64(-38) THEN avg(Int64(19)) END - Int64(69) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row(s) fetched.