| Bug #27735 | subtracting the result of two MAX aggregates returns incorrect results | ||
|---|---|---|---|
| Submitted: | 10 Apr 2007 16:42 | Modified: | 10 Apr 2007 18:26 |
| Reporter: | Tim Brazil | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.0.37 | OS: | Linux (x86_64) |
| Assigned to: | CPU Architecture: | Any | |
[10 Apr 2007 17:33]
Giuseppe Maxia
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php See the "no_unsigned_subtraction" mode in the manual http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
[10 Apr 2007 18:26]
Tim Brazil
Sorry about that. It's another case of "read the fine manual" :(
Description: I reproduced this problem using both the pre-built rpm packages from up2date and also mysql binaries I personally built. I have only used 64 bit Linux Workstation 4 boxes with the 2.6 kernel so I'm not sure whether it exists on other architectures. Reproduciable test script included below. How to repeat: /* test script */ CREATE DATABASE IF NOT EXISTS xxxdb; USE xxxdb; DROP TABLE IF EXISTS xxxtable; CREATE TABLE xxxtable ( col1 int(10) unsigned NULL, col2 int(10) unsigned NULL, col3 int(10) unsigned NULL, col4 int(10) unsigned NULL); INSERT INTO xxxtable VALUES (0, 0, 0, 4275467); /* individual max results are correct */ SELECT max(col1-col2), max(col3+col4) FROM xxxtable; /* adding the results of two max's are correct */ SELECT max(col1-col2)+max(col3+col4) FROM xxxtable; /* subtracting the results of two max's are incorrect */ SELECT max(col1-col2)-max(col3+col4) FROM xxxtable; /* results are: max(col1-col2) max(col3+col4) 0 4275467 max(col1-col2)+max(col3+col4) 4275467 max(col1-col2)-max(col3+col4) 18446744073705276149 <---- this is incorrect, should be -4275467 */