sql odd case of rounding compared to duckdb and postgresql

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

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 │
└────────────────────────────────────────────────────────────────────────────────┴───────┘
``

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)

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

Navigation Menu

Repository navigation

sql odd case of rounding compared to duckdb and postgresql #13781

Describe the bug

To Reproduce

Expected behavior

Additional context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

sql odd case of rounding compared to duckdb and postgresql #13781

Description

Describe the bug

To Reproduce

Expected behavior

Additional context

Activity

Omega359 commented on Dec 14, 2024

Omega359 commented on Jan 6, 2025

Omega359 commented on Jan 12, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions