You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "Omega359 (via GitHub)" <gi...@apache.org> on 2024/12/14 16:54:03 UTC

[I] sql result discrepency with sqlite, postgres and duckdb bug #3 [datafusion]

Omega359 opened a new issue, #13784:
URL: https://github.com/apache/datafusion/issues/13784

   ### Describe the bug
   
   This is an odd one that I'm unsure what to make of it
   
   ```sql
   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
   ```sql
   > 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_


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] sql result discrepency with sqlite, postgres and duckdb bug #3 [datafusion]

Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #13784:
URL: https://github.com/apache/datafusion/issues/13784#issuecomment-2585310572

   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)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] sql result discrepency with sqlite, postgres and duckdb bug #3 [datafusion]

Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 closed issue #13784: sql result discrepency with sqlite, postgres and duckdb bug #3
URL: https://github.com/apache/datafusion/issues/13784


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] sql result discrepency with sqlite, postgres and duckdb bug #3 [datafusion]

Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #13784:
URL: https://github.com/apache/datafusion/issues/13784#issuecomment-2585310683

   ```sql
   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 col2 * + + col0 * col2 * col0 * + - col2 / - + col2 AS col0 FROM tab0 AS cor0;
   ```
   
   Another example


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] sql result discrepency with sqlite, postgres and duckdb bug #3 [datafusion]

Posted by "jonahgao (via GitHub)" <gi...@apache.org>.
jonahgao commented on issue #13784:
URL: https://github.com/apache/datafusion/issues/13784#issuecomment-2560545696

   If [fail_on_overflow](https://github.com/apache/datafusion/blob/e718c1a5c5770c071c9c2e14a7681a7f1a2f3f23/datafusion/physical-expr/src/expressions/binary.rs#L88) is enabled, Datafusion will return an overflow error.
   ```
   > SELECT DISTINCT + col2 * + col2 * - col2 * col2 * + col2 / + col2 + col0 AS col2 FROM tab0;
   
   External error: External error: Arrow error: Arithmetic overflow: Overflow happened on: -96059601 * 99
   ```
   Maybe we need a new config option for `fail_on_overflow`, or even enable it by default.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] sql result discrepency with sqlite, postgres and duckdb bug #3 [datafusion]

Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #13784:
URL: https://github.com/apache/datafusion/issues/13784#issuecomment-2678954366

   Closed as fixed


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org