Bug #20093 Arithmetic with signed and unsigned columns gives incorrect results
Submitted: 26 May 2006 18:05 Modified: 28 Oct 2019 23:53
Reporter: Mark Hughes Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.23-BK, 5.0.21,4.1BK,5.1BK OS:Any (Linux)
Assigned to: CPU Architecture:Any

[26 May 2006 18:05] Mark Hughes
Description:
Arithmetic with signed and unsigned columns gives incorrect results

How to repeat:
Running the following SQL:

drop table if exists mhtest;
create table mhtest (id int unsigned, score int);
insert into mhtest values (1,-1);
select id * score from mhtest;

Returns:

id * score
18446744073709551615
[26 May 2006 19:47] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.23-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
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 1 to server version: 5.0.23

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

mysql> create table mhtest (id int unsigned, score int);
insertQuery OK, 0 rows affected (0.02 sec)

 mysql> insert into mhtest values (1,-1);
Query OK, 1 row affected (0.00 sec)

smysql> select id * score from mhtest;
+----------------------+
| id * score           |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)
[27 May 2006 13:33] MySQL Verification Team
Also affected:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.19-debug |
+--------------+
1 row in set (0.00 sec)

mysql> select id * score from mhtest;
+----------------------+
| id * score           |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql> 
--------------------------------------------------------------
mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.12-beta-debug | 
+-------------------+
1 row in set (0.01 sec)

mysql> select id * score from mhtest;
+----------------------+
| id * score           |
+----------------------+
| 18446744073709551615 | 
+----------------------+
1 row in set (0.00 sec)
[8 Jun 2006 8:54] Georgi Kodinov
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:

According to the Reference Manual (section 12.4.1 Arithmetic Operators) :
"If one of the arguments is an unsigned integer, and the other argument is also an integer, the result is an unsigned integer".
[8 Jun 2006 9:22] Mark Hughes
That doesn't make sense to me. What I would expect, and what the manual says is:

1 (signed integer) * -1 (unsigned integer) = -1 (unsigned integer)

This also happens:

mysql> select 1 * -1;
+--------+
| 1 * -1 |
+--------+
|     -1 |
+--------+
[8 Jun 2006 10:19] Georgi Kodinov
in case of 'select 1 * -1' both arguments are of type signed INT. This makes for the result also being a signed value. Your case is actually : 
mysql> select CAST(1 as UNSIGNED) * -1;
--------------
select CAST(1 as UNSIGNED) * -1
--------------

+--------------------------+
| CAST(1 as UNSIGNED) * -1 |
+--------------------------+
|     18446744073709551615 |
+--------------------------+
[8 Jun 2006 11:08] Mark Hughes
OK, I see. Thanks for the clarification.
[9 Nov 2006 22:41] Brad Mace
Are you serious?  You're dismissing this without explanation?  In what universe is this the desirable behavior?  This is counter-intuitive to the point of being bizarre.  Even if all the values are unsigned, the result should be signed if that's what it takes to produce a reasonable result.  Getting a result of 14 quadrillion is useful to no one.  

If the value is being assigned to an unsigned field, then you can drop the sign, set it to zero, whatever.  But if it's being assigned to a field that can handle it, it should produce the only correct and reasonable result.
[11 Jan 2008 9:13] Daniel Fiske
I see people haven't put any comments on this for a while. I would also say there is little chance that this behavior is going to change. No matter how computationally correct it is, it lacks very little mathematical sense!

1 * -1, should = -1 and explicit casting SHOULD NOT BE REQUIRED.

....there must have been discussions on mysql's choice to do it this way? Have they justified this behaviour? Any links?

Thanks

Daniel.
[9 Jun 2009 9:04] Pelle Ravn
Just as the other people said, this doesn’t make any sense. I really think that the MySQL team should consider changing this. – This cross any logic sense of math, and the result (the huge unsigned number) makes even less sense!

It should not be how the computer thinks the answer should be; it’s how the user thinks, because it’s him or she who are working with it. What if all calculators in the whole world acted like this because a computer thinks this is the right answer. It’s a calculator, then it should work like a calculator.
[9 Jun 2009 11:08] Giuseppe Maxia
Although it is a documented and intentional behavior, it is unintuitive and in many cases not desirable.
A workaround is available, but again, if users don't expect this result (and there should not be reasonable expectation) they are unlikely to code defensively by taking this issue into account.
 
  select cast(id * score as signed) from mhtest

I am reopening this bug with a re-triaging request.
[9 Jun 2009 15:42] Dean Ellis
I think intuition has little to do with it.

The product of two positive (or unsigned) integers should, intuitively, be positive as well.

mysql> select 922337203685477580 * 12 x;
+----------------------+
| x                    |
+----------------------+
| -7378697629483820656 | 
+----------------------+

mysql> select cast(922337203685477580 as unsigned) * 12 x;
+----------------------+
| x                    |
+----------------------+
| 11068046444225730960 | 
+----------------------+

The documented behavior is rather more "intuitive" in this case.

As with FLOAT, you really must understand your datatypes.
[24 Mar 2010 14:40] Eric Herman
I agree we really must understand our datatypes, but this behavior still seems surprising.... Perhaps if the behavior will not change, then it could at least issue a warning? 

mysql> create table t1 (foo int unsigned NOT NULL, bar int NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 VALUES (1,1),(1,2),(2,1);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT foo, bar, foo-bar, bar-foo FROM t1;
+-----+-----+----------------------+----------------------+
| foo | bar | foo-bar              | bar-foo              |
+-----+-----+----------------------+----------------------+
|   1 |   1 |                    0 |                    0 | 
|   1 |   2 | 18446744073709551615 |                    1 | 
|   2 |   1 |                    1 | 18446744073709551615 | 
+-----+-----+----------------------+----------------------+
3 rows in set (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

5.1.42-enterprise-gpl-advanced-log
[3 Dec 2010 18:31] MySQL Verification Team
See http://bugs.mysql.com/bug.php?id=58697.
[22 Mar 2012 14:30] MaxiM Basunov
Confirmed in 5.1.61 (Ubuntu 11.10)
Confirmed in 5.1.52 (CentOS 6)

mysql> create table test (n1 int unsigned, n2  int unsigned, f1 float);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test values (100, 50, 10),(50, 70, 10);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select n1, n2, f1, (n1-n2)*f1, n1*f1-n2*f1 from test;
+------+------+------+----------------------+-------------+
| n1   | n2   | f1   | (n1-n2)*f1           | n1*f1-n2*f1 |
+------+------+------+----------------------+-------------+
|  100 |   50 |   10 |                  500 |         500 |
|   50 |   70 |   10 | 1.84467440737096e+20 |        -200 |
+------+------+------+----------------------+-------------+
2 rows in set (0.00 sec)

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.1.61-0ubuntu0.11.10.1 |
+-------------------------+
1 row in set (0.00 sec)
[4 Apr 2012 20:31] SERC Team
Bug fix for 20093. Project/Directory name: sql, File name:item_func.h, Function name:check_integer_overflow, Line no.: 257

Attachment: item_func.h (text/plain), 53.34 KiB.

[3 Oct 2014 11:50] Hartmut Holzgraefe
Seems to be fixed in 5.6.20:

mysql> select id * score from mhtest;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`mhtest`.`id` * `test`.`mhtest`.`score`)'
[28 Oct 2019 23:53] Roy Lyseng
Posted by developer:
 
Range checks have been implemented in MySQL 5.6.
However, result type of operations on operands of mixed signedness has not been changed.
[5 May 6:09] Yushan ZHANG
I think at least the query computes the correct result, say:

mysql> select cast(-1 as unsigned);
                                                                                
+----------------------+
| cast(-1 as unsigned) |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

If check for the 2's complement of -1, it is `11111111 11111111 ... (64 bits of 1s in total)`. The case directly takes the representation and computes the correct answer. 

But the point here is, what should be the correct type of the result value `1`? It seems it uses unsigned by default now.