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

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.

Same result with mysql 5.0.4 under Debian Linux

better synopsis

Tested on 5.0.7.

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

mysql>

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>

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

Fixed in 4.1.14 and 5.0.11.

Documented in 4.1.14 and 5.0.11 changelogs.

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';

(0 - foo.qty) also results in: 18446744073709551615