Repository navigation
sql result discrepency with sqlite, postgres and duckdb #13780
Description
Describe the bug
External error: query result mismatch:
[SQL] SELECT - NULLIF ( + 15, - 27 + + CAST ( NULL AS REAL ) + + + MIN ( 35 ) * - COUNT ( * ) * - 14 + 64 ) * 87 * + 75 * 34 * + 76 + - - 31 AS col0, - 10 * - + 33 * ( + - NULLIF ( 21, + 70 * + 71 ) ) * - 15
at test_files/sqlite/random/expr/slt_good_11.slt:9790
SELECT - NULLIF ( + 15, - 27 + + CAST ( NULL AS REAL ) + + + MIN ( 35 ) * - COUNT ( * ) * - 14 + 64 ) * 87 * + 75 * 34 * + 76 + - - 31 AS col0, - 10 * - + 33 * ( + - NULLIF ( 21, + 70 * + 71 ) ) * - 15;df result:
> SELECT - NULLIF ( + 15, - 27 + + CAST ( NULL AS REAL ) + + + MIN ( 35 ) * - COUNT ( * ) * - 14 + 64 ) * 87 * + 75 * 34 * + 76 + - - 31 AS col0, - 10 * - + 33 * ( + - NULLIF ( 21, + 70 * + 71 ) ) * - 15
;
+--------------+---------------------------------------------------------------------------------------+
| col0 | Int64(-10) * (- Int64(33)) * (- nullif(Int64(21),Int64(70) * Int64(71))) * Int64(-15) |
+--------------+---------------------------------------------------------------------------------------+
| -252908960.0 | 103950 |
+--------------+---------------------------------------------------------------------------------------+postgres, duckdb and sqlite result:
D SELECT - NULLIF ( + 15, - 27 + + CAST ( NULL AS REAL ) + + + MIN ( 35 ) * - COUNT ( * ) * - 14 + 64 ) * 87 * + 75 * 34 * + 76 + - - 31 AS col0, - 10 * - + 33 * ( + - NULLIF ( 21, + 70 * + 71 ) ) * - 15;
┌────────────┬──────────────────────────────────────────────────────────────────┐
│ col0 │ (((-10 * -(+(33))) * +(-("nullif"(21, (+(70) * +(71)))))) * -15) │
│ int32 │ int32 │
├────────────┼──────────────────────────────────────────────────────────────────┤
│ -252908969 │ 103950 │
└────────────┴──────────────────────────────────────────────────────────────────┘Duckdb and sqlite have col0 as an integer, pg and df have it as a float.
To Reproduce
sql above.
Expected behavior
No response
Additional context
No response
Activity
Omega359 commentedon Jan 5, 2025
The core issue is something related to the cast to the
REALtype. Changing the sql to cast to DOUBLE results in the correct results:Omega359 commentedon Jan 6, 2025
I've spent a few hours trying to determine where DataFusion was going wrong with the math and finally came up with a condensed test case. The math is fine ... as far as Rust is concerned. You can see a boiled down test case I wrote up on the rust playground.
The core issue is that the result cannot be represented as a f32 which is what the
REALtype maps to. So technically the result that DataFusion is returning is correct.Sqlite maps real to 8 bytes.
Postgresql maps real to 4 bytes ... but for the query in query it uses an int type (proper handling of nullif types as opposed to DataFusion's incorrect type selection). Even if you force part of the query to be a real type it still uses int:
SELECT - 15.0000::float4 * 16860600 + 31 AS col0;=> -252908969SELECT - 15.0001::float4 * 16860600 + 31 AS col0;=> -252910657.34972382Duckdb maps real to 4 bytes. If you force the type to be float4 it returns the same result as DataFusion:
So I think for these queries the resolution here should be that DataFusion should fix the typing of nullif .. and then update the results in the slt test if the type maps to f32.
Omega359 commentedon Jan 6, 2025
Addendum: Since the sqlite tests come from sqlite (duh) where REAL is mapped to 8 bytes (Double/f64) I would like to propose that I update the sqlite .slt files and change:
'AS REAL' -> 'AS DOUBLE'
This would better match the actual types being tested and would fix many of the failing results. Along with correcting the nullif type behavior would fix almost all the remaining tests that have result mismatches with sqlite/postgresql.
Thoughts? @alamb, @aweltsch, @2010YOUY01, @jayzhan-synnada
alamb commentedon Jan 7, 2025
First of all, very nice 🕵️ work !
As I understand, this means using f64 for floating point operations in the test. This sounds like a very good and pragmatic thing to do in my mind
The other potential thing to do might simply be to map
AS REALto useDataType::Float64but that is a larger and much more potentially disruptive changeThanks agian @Omega359
Omega359 commentedon Jan 11, 2025
I went with using FLOAT8 vs DOUBLE as double isn't a valid type name in Postgresql and it interfered with the result comparisons.
Omega359 commentedon Jan 12, 2025
Resolved as fixed with change 'as REAL' to 'AS FLOAT8' in sqlite test files