Bug #11402 Select abs(-1) * -1 result: 18446744073709551615
Submitted: 16 Jun 2005 22:24 Modified: 7 Aug 2005 0:19
Reporter: Martin Koeberle Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.7 OS:Microsoft Windows (Windows 2003)
Assigned to: Jim Winstead CPU Architecture:Any

[16 Jun 2005 22:24] Martin Koeberle
Description:
multiplication of  the function result abs() with a negative number results nonsens
Example: 
Select abs(-2) * 3     Result 6  -->OK
Select abs(-2) * -3    Result 18446744073709551610

Tried with different query browsers. Same result in all browsers

How to repeat:
Simple add query to any query browser
Select abs(-2) * -1  or 
Select abs(1) * -1

Suggested fix:
No way found.
[16 Jun 2005 22:28] Martin Koeberle
Same result with mysql 5.0.4 under Debian Linux
[16 Jun 2005 22:39] Martin Koeberle
better synopsis
[17 Jun 2005 6:55] Vasily Kishkin
Tested on 5.0.7.

mysql> Select abs(-2) * -3;
+----------------------+
| abs(-2) * -3         |
+----------------------+
| 18446744073709551610 |
+----------------------+
1 row in set (0.00 sec)

mysql>
[17 Jun 2005 8:27] Shane Bester
Test case:

select version();
select 1 * -1;
select abs(1) * 1;
select abs(-1) * 1;
select abs(1) * -1;

Tested with Linux:
mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.7-beta-standard |
+---------------------+
1 row in set (0.00 sec)

mysql> select 1 * -1;
+--------+
| 1 * -1 |
+--------+
|     -1 |
+--------+
1 row in set (0.00 sec)

mysql> select abs(1) * 1;
+------------+
| abs(1) * 1 |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> select abs(-1) * 1;
+-------------+
| abs(-1) * 1 |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select abs(1) * -1;
+----------------------+
| abs(1) * -1          |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

Tested with Windows:
mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.0.7-beta-nt |
+---------------+
1 row in set (0.19 sec)

mysql> select 1 * -1;
+--------+
| 1 * -1 |
+--------+
|     -1 |
+--------+
1 row in set (0.03 sec)

mysql> select abs(1) * 1;
+------------+
| abs(1) * 1 |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> select abs(-1) * 1;
+-------------+
| abs(-1) * 1 |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select abs(1) * -1;
+----------------------+
| abs(1) * -1          |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql>
[23 Jun 2005 2:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26348
[2 Aug 2005 2:23] Jim Winstead
Fixed in 4.1.14 and 5.0.11.
[7 Aug 2005 0:19] Mike Hillyer
Documented in 4.1.14 and 5.0.11 changelogs.
[27 Sep 2007 0:03] Arthur Ketcham
I am running 5.0.24a on Linux (not current version, I know). But this bug is still present in some circumstances.

The SELECT abs(n) * -1 issue does not affect this version, however, the following will always occur :

I have a table 'foo', and for local_id: 528, the value of qty is 1.
When I do the following query, the result is: 18446744073709551615

SELECT (foo.qty * -1) AS bar
FROM foo
WHERE local_id = '528';
[27 Sep 2007 0:06] Arthur Ketcham
(0 - foo.qty) also results in: 18446744073709551615