Bug #9300 subtraction error

• Submitted: 19 Mar 2005 23:52
• Modified: 20 Mar 2005 11:36
• Reporter: Roy Miller
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.10a
• OS: Linux (suse 8)
• CPU Architecture: Any

[19 Mar 2005 23:52] Roy Miller

Description:

just upgraded mysql from v4.0.? to latest v4.1.10a

no structure chagnes between upgrades
no data changes
given tblTest.intValue = 1211273405
Field tblTest.intValue is an unsigned bigint

This command used to work. now it's broken

How to repeat:

Build the database and table 
____start____
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dbTest`;
USE `dbTest`;
CREATE TABLE `tblTest` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `intValue` bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `tblTest` (`id`,`intValue`) VALUES 
 (1,1211273405);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
____end____

then issue the following command:
____start____
SELECT intValue
, 1111272874 - intValue as intWrong
, 1111272874 + (- intValue) as intCorrect
FROM dbTest.tblTest
where id = 1
____end____

Suggested fix:
my found work around is:
sql command:
select 1111272874 + (- tblTest.intValue) as t from tblTest where id = 1
returns correct values
-100000531

[19 Mar 2005 23:54] Roy Miller

my original version was 4.0.13 (at the time it was the latest stable version)

[20 Mar 2005 11:36] MySQL Verification Team

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

One of the argument of substruction is UNSIGNED, so the result is also UNSIGNED.
You can run mysqld in NO_UNSIGNED_SUBTRACTION to get signed results of substruction.

mysql> SELECT intValue
    -> , 1111272874 - intValue as intWrong
    -> , 1111272874 + (- intValue) as intCorrect
    -> FROM tblTest
    -> where id = 1;
+------------+----------------------+------------+
| intValue   | intWrong             | intCorrect |
+------------+----------------------+------------+
| 1211273405 | 18446744073609551085 | -100000531 |
+------------+----------------------+------------+
1 row in set (0.01 sec)

mysql> set sql_mode=NO_UNSIGNED_SUBTRACTION;
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT intValue
    -> , 1111272874 - intValue as intWrong
    -> , 1111272874 + (- intValue) as intCorrect
    -> FROM tblTest
    -> where id = 1;
+------------+------------+------------+
| intValue   | intWrong   | intCorrect |
+------------+------------+------------+
| 1211273405 | -100000531 | -100000531 |
+------------+------------+------------+
1 row in set (0.01 sec)