sql odd case of rounding compared to duckdb and postgresql

[Omega359]

Describe the bug

External error: query result mismatch:
[SQL] SELECT + 99 * NULLIF ( 86, - CASE WHEN NOT COUNT ( * ) BETWEEN 84 AND 54 THEN NULLIF ( - 59, COUNT ( * ) ) + CAST ( NULL AS REAL ) END ) / 48 + 27 * COALESCE ( + 76 * 67, 96 * 80 + 38, 13 * COUNT ( * ) )
[Diff] (-expected|+actual)
-   137661
+   137661.38
at test_files/sqlite/random/expr/slt_good_47.slt:10707

datafusion

> SELECT + 99 * NULLIF ( 86, - CASE WHEN NOT COUNT ( * ) BETWEEN 84 AND 54 THEN NULLIF ( - 59, COUNT ( * ) ) + CAST ( NULL AS REAL ) END ) / 48 + 27 * COALESCE ( + 76 * 67, 96 * 80 + 38, 13 * COUNT ( * ) );
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Int64(99) * nullif(Int64(86),(- CASE WHEN NOT count(*) BETWEEN Int64(84) AND Int64(54) THEN nullif(Int64(-59),count(*)) + NULL END)) / Int64(48) + Int64(27) * coalesce(Int64(76) * Int64(67),Int64(96) * Int64(80) + Int64(38),Int64(13) * count(*)) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 137661.38                                                                                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

duckdb and postgres:

D SELECT + 99 * NULLIF ( 86, - CASE WHEN NOT COUNT ( * ) BETWEEN 84 AND 54 THEN NULLIF ( - 59, COUNT ( * ) ) + CAST ( NULL AS REAL ) END ) / 48 + 27 * COALESCE ( + 76 * 67, 96 * 80 + 38, 13 * COUNT ( * ) );
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ (((+(99) * "nullif"(86, -(CASE  WHEN ((NOT (count_star() BETWEEN 84 AND 54))) THEN (("nullif"(-59, count_star()) +.  │
│                                                        double                                                        │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                                           137661.375 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

I cannot determine why df is rounding the result to 2 decimal places.

To Reproduce

sql above.

Expected behavior

results match other databases

Additional context

No response

[Omega359]

Another version of what I think may be the same thing:

External error: query result mismatch:
[SQL] SELECT DISTINCT - ( 43 ) * 42 + - + NULLIF ( + 90, - 49 - + CAST ( - - 99 AS REAL ) ) / - + 7, 35 + + 55 AS col1
[Diff] (-expected|+actual)
-   -1794
-   90
+   -1793.1428 90
at test_files/sqlite/random/expr/slt_good_42.slt:12794

Datafusion:

> SELECT DISTINCT - ( 43 ) * 42 + - + NULLIF ( + 90, - 49 - + CAST ( - - 99 AS REAL ) ) / - + 7, 35 + + 55 AS col1;
+----------------------------------------------------------------------------------------------+------+
| (- Int64(43)) * Int64(42) + (- nullif(Int64(90),Int64(-49) - (- Int64(-99)))) / (- Int64(7)) | col1 |
+----------------------------------------------------------------------------------------------+------+
| -1793.1428                                                                                   | 90   |
+----------------------------------------------------------------------------------------------+------+

duckdb and postgres:

D SELECT DISTINCT - ( 43 ) * 42 + - + NULLIF ( + 90, - 49 - + CAST ( - - 99 AS REAL ) ) / - + 7, 35 + + 55 AS col1;
┌────────────────────────────────────────────────────────────────────────────────┬───────┐
│ ((-43 * 42) + (-(+("nullif"(+(90), (-49 - +(CAST(99 AS FLOAT)))))) / -(+(7)))) │ col1  │
│                                     double                                     │ int32 │
├────────────────────────────────────────────────────────────────────────────────┼───────┤
│                                                             -1793.142857142857 │    90 │
└────────────────────────────────────────────────────────────────────────────────┴───────┘
``

[Omega359]

I suspect much of this is the same cause as #13780 - nullif typing being incorrect and real mapping to f32 where it is not possible to represent some integers exactly.

Postgresql also seems to have an interesting casting behavior that if you case an int to a real it may ignore it and implicitly cast it back to an int as long as there is no dependency requiring it to be a real (division for example)

[Omega359]

Resolved as fixed with change 'as REAL' to 'AS FLOAT8' in sqlite test files