Repository navigation
sql result discrepency with sqlite, postgres and duckdb bug #3 #13784
Description
Describe the bug
This is an odd one that I'm unsure what to make of it
CREATE TABLE tab0(col0 INTEGER, col1 INTEGER, col2 INTEGER);
INSERT INTO tab0 VALUES(97,1,99);
INSERT INTO tab0 VALUES(15,81,47);
INSERT INTO tab0 VALUES(87,21,10);
SELECT DISTINCT + col2 * + col2 * - col2 * col2 * + col2 / + col2 + col0 AS col2 FROM tab0;results in:
External error: query result mismatch:
[SQL] SELECT DISTINCT + col2 * + col2 * - col2 * col2 * + col2 / + col2 + col0 AS col2 FROM tab0
[Diff] (-expected|+actual)
-4879666
- -96059504
+ -9292487
-9913
at test_files/sqlite/random/aggregates/slt_good_102.slt:15306
Datafusion
> SELECT DISTINCT + col2 * + col2 * - col2 * col2 * + col2 / + col2 + col0 AS col2 FROM tab0;
+----------+
| col2 |
+----------+
| -9292487 |
| -9913 |
| -4879666 |
+----------+sqlite returns a -96059504 result, both duckdb and postgres fail with integer overflow errors.
I think if a db support integer promotion the result from sqlite is actually correct. I have no idea how DF is coming up with it's result unless it's wrapping the value or truncating it somehow.
To Reproduce
sql above
Expected behavior
No response
Additional context
No response
Activity
jonahgao commentedon Dec 24, 2024
If fail_on_overflow is enabled, Datafusion will return an overflow error.
Maybe we need a new config option for
fail_on_overflow, or even enable it by default.Omega359 commentedon Jan 11, 2025
After re-generating the .slt files to change REAL -> FLOAT8 this and another similar error is the only blocker I think to having the full test suite complete without failures. That doesn't mean that there isn't errors - it just would mean that all queries that DF handles without an error return the same results as sqlite or if not that the same results as postgres (ish, float rounded to 4 decimal places)
Omega359 commentedon Jan 11, 2025
Another example
Omega359 commentedon Feb 24, 2025
Closed as fixed