Bug #11402 Select abs(-1) * -1 result: 18446744073709551615

• Submitted: 16 Jun 2005 22:24
• Modified: 7 Aug 2005 0:19
• Reporter: Martin Koeberle
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.7
• OS: 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] MySQL Verification Team

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