MySQL Bugs: #39874: Strange value when encounting a negative value for datatype INTEGER UNSIGNED

Bug #39874	Strange value when encounting a negative value for datatype INTEGER UNSIGNED
Submitted:	6 Oct 2008 9:29	Modified:	9 Oct 2008 15:05
Reporter:	Cheng Johnny	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0 & 5.1	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When we encountered a negative value from a calculation for a column of datatype INTEGER SIGNED, we got the max range value of the following datatype : BIGINT SIGNED. Same case for SMALLINT, TINYINT, etc ...

When we stored the result of the calculation into a variable, we got the right value. By casting into datatype SIGNED, we got the right value too.

How to repeat:
CREATE TABLE test (
  id INT PRIMARY KEY,
  label VARCHAR(255),
  order_num INT UNSIGNED,
  order_num2 INT SIGNED
);

INSERT INTO test (id, label, order_num, order_num2) VALUES
(1, 'test1', 1, 1),
(2, 'test2', 2, 2),
(3, 'test3', 3, 3),
(4, 'test4', 4, 4);

-- order_num have strange values
SELECT order_num - 20, order_num2 - 20; 

-- it works fine
SELECT  (@tmp := order_num) - 20, order_num2 - 20 FROM test; 

-- the order_num become 0 instead of 2 (row with id = 1)
UPDATE test SET order_num = 1+ABS(order_num-2), order_num2 = 1+ABS(order_num2-2) WHERE id IN(1,2);

-- it works fine
UPDATE test SET order_num = 1+ABS((@tmp := order_num-2)), order_num2 = 1+ABS((@tmp := order_num2-2)) WHERE id IN(1,2);

Verified with 5.0.66a and 5.1.28-rc:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 -T test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> CREATE TABLE test (
    ->   id INT PRIMARY KEY,
    ->   label VARCHAR(255),
    ->   order_num INT UNSIGNED,
    ->   order_num2 INT SIGNED
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO test (id, label, order_num, order_num2) VALUES
    -> (1, 'test1', 1, 1),
    -> (2, 'test2', 2, 2),
    -> (3, 'test3', 3, 3),
    -> (4, 'test4', 4, 4);
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select id, order_num, 1 + ABS(order_num-2) from test;
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `test`
Org_table:  `test`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY NO_DEFAULT_VALUE NUM PART_KEY

Field   2:  `order_num`
Catalog:    `def`
Database:   `test`
Table:      `test`
Org_table:  `test`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      UNSIGNED NUM

Field   3:  `1 + ABS(order_num-2)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     13
Max_length: 1
Decimals:   0
Flags:      UNSIGNED BINARY NUM

+----+-----------+----------------------+
| id | order_num | 1 + ABS(order_num-2) |
+----+-----------+----------------------+
|  1 |         1 |                    0 | -- <-- this is a wrong result
|  2 |         2 |                    1 |
|  3 |         3 |                    2 |
|  4 |         4 |                    3 |
+----+-----------+----------------------+
4 rows in set (0.00 sec)

ABS(x) >= 0 for any x, by definition, so 1 + ABS(x) is definitely >=1 for any x.

I don't see anything wrong with it. You get a wraparound for big numbers. Where x + y can be 0 even if both x and y are > 0.

If you want to get negative results when doing calculations with unsigned numbers set NO_UNSIGNED_SUBTRACTION in your sql_mode.

Not a bug