MySQL Bugs: #20093: Arithmetic with signed and unsigned columns gives incorrect results

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

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

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)

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)

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".

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 |
+--------+

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 |
+--------------------------+

OK, I see. Thanks for the clarification.

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.

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.

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.

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.

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.

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

See http://bugs.mysql.com/bug.php?id=58697.

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)

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.

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`)'

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.

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.