MySQL Bugs: #18712: Truncation problem

Bug #18712	Truncation problem
Submitted:	1 Apr 2006 22:29	Modified:	8 May 2006 18:54
Reporter:	Guillaume Girou	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.21-BK, 5.0.19	OS:	Linux (Linux / Windows)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
With i, a tinyint unsigned column, and i=0
when we do
i=i-1
the result is 255 whereas it must be 0

How to repeat:
mysql> CREATE TABLE t (i TINYINT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT t SET i = 0;
Query OK, 1 row affected (0.02 sec)

mysql> UPDATE t SET i = -1;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Enregistrements correspondants: 1  Modifiés: 0  Warnings: 1

mysql> SELECT i FROM t;
+---+
| i |
+---+
| 0 |
+---+
1 row in set (0.02 sec)

mysql> UPDATE t SET i = i - 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Enregistrements correspondants: 1  Modifiés: 1  Warnings: 1

mysql> SELECT i FROM t; # i == 255
+-----+
| i   |
+-----+
| 255 |
+-----+
1 row in set (0.00 sec)

Same with MySQL 5.1.7!

Also those:

UPDATE t SET i = i - 11;
UPDATE t SET i = i - 1111;

result in '255'.

And I can add:

If i is an 'int' it returns 4294967295 -- an int is an int(10) by default
If i is a 'bigint' it returns 18446744073709551615 -- a bigint is an int(20) by default

Thank you for a problem report. Verified just as described with 5.0.21-BK on Linux:

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

mysql> CREATE TABLE t (i TINYINT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT t SET i = 0;
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE t SET i = -1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'i' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+---+
| i |
+---+
| 0 |
+---+
1 row in set (0.00 sec)

mysql> UPDATE t SET i = i - 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'i' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+-----+
| i   |
+-----+
| 255 |
+-----+
1 row in set (0.00 sec)

mysql> show variables like 'sql%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
| sql_notes     | ON    |
| sql_warnings  | ON    |
+---------------+-------+
3 rows in set (0.00 sec)

This inconsistent behaviour is a bug, because, according to the manual (http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html):

"In non-strict mode, when an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the column data type range. If you store 256 into a TINYINT or TINYINT UNSIGNED column, MySQL stores 127 or 255, respectively."

SET i = -1 works as described above, while SET i = i - 1 - does not!

The problem here is that in this query:

UPDATE t SET i = i - 1

the value i - 1 is evaluated first. Since field 'i' is unsigned, the value goes around already
here. So it does not become -1, it becomes the other end of the unsigned value, e.g:
select i-1 from t would result to 18446744073709551615. (or similar, upper end depending on the machine architecture) . 

For someone, this could be the desired behavior, so it cannot be fixed. Instead if one
wants to have signed results instead of unsigned when one of the components has unsigned
flag set, you can do this:

UPDATE t SET i = CAST(i - 1 AS SIGNED)

and it will result to 0.

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/commits/5947

The behavior demonstrated in the report should be described more
clearly in the manual. I'm changing this to a documentation bug and
assigning it to myself.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

The change in unsigned integer subtraction now is described in
the "SQL changes" section of this page:

http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html

See also Bug#15472.