Bug #30959 ABS function error
Submitted: 11 Sep 2007 20:00 Modified: 15 Oct 2007 12:40
Reporter: David Brown Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.45/5.1 OS:Linux (Linux server1 2.6.9-11.ELsmp)
Assigned to:

[11 Sep 2007 20:00] David Brown
Description:
ABS() function reports an incorrect result (18446744073709551612) when applied to a negative sum involving unsigned numeric fields.

If the field is NOT defined as "unsigned" the problem does not occur.

Performing a direct ABS() function on literal numbers does not cause the problem.

As a workaround I've changed my numeric fields to not unsigned.

MySQL compiled from source with the following options

./configure --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --enable-assembler -disable-shared --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static 

How to repeat:
CREATE TABLE `test` 
  (`x` mediumint unsigned,
   `y` mediumint unsigned,
   `z` mediumint);

INSERT INTO test SET x = 5, y = 1, z = 8;

SELECT ABS(x - y) FROM test;

# Result 4 - Correct

SELECT ABS(y - x) FROM test;

# Result 18446744073709551612 - Incorrect

SELECT ABS(1 - x) FROM test;

# Result 18446744073709551612 - Incorrect

SELECT ABS(1 - z) FROM test;

# Result 7 - Correct
[11 Sep 2007 20:20] Miguel Solorzano
Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.50-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `test` 
    ->   (`x` mediumint unsigned,
    ->    `y` mediumint unsigned,
    ->    `z` mediumint);
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> INSERT INTO test SET x = 5, y = 1, z = 8;
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> SELECT ABS(x - y) FROM test;
+------------+
| ABS(x - y) |
+------------+
|          4 | 
+------------+
1 row in set (0.00 sec)

mysql> SELECT ABS(y - x) FROM test;
+----------------------+
| ABS(y - x)           |
+----------------------+
| 18446744073709551612 | 
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT ABS(1 - x) FROM test;
+----------------------+
| ABS(1 - x)           |
+----------------------+
| 18446744073709551612 | 
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT ABS(1 - z) FROM test;
+------------+
| ABS(1 - z) |
+------------+
|          7 | 
+------------+
1 row in set (0.00 sec)

mysql> 
--------------------------------------------------------
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.24-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `test` 
    ->   (`x` mediumint unsigned,
    ->    `y` mediumint unsigned,
    ->    `z` mediumint);
Query OK, 0 rows affected (0.06 sec)

mysql> 
mysql> INSERT INTO test SET x = 5, y = 1, z = 8;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT ABS(x - y) FROM test;
+------------+
| ABS(x - y) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql> 
mysql> # Result 4 - Correct
mysql> 
mysql> SELECT ABS(y - x) FROM test;
+------------+
| ABS(y - x) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql> 
mysql> # Result 18446744073709551612 - Incorrect
mysql> 
mysql> SELECT ABS(1 - x) FROM test;
+------------+
| ABS(1 - x) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql> 
mysql> # Result 18446744073709551612 - Incorrect
mysql> 
mysql> SELECT ABS(1 - z) FROM test;
+------------+
| ABS(1 - z) |
+------------+
|          7 |
+------------+
1 row in set (0.00 sec)
[12 Oct 2007 14:38] Konstantin Osipov
Bad data.
[15 Oct 2007 12:40] Sergei Golubchik
This is expected behaviour. See http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html starting from "MySQL supports arithmetic with both signed and unsigned 64-bit values."