MySQL Bugs: #16285: NO_UNSIGNED_SUBTRACTION and subtraction of -1 from unsigned int problem

Bug #16285	NO_UNSIGNED_SUBTRACTION and subtraction of -1 from unsigned int problem
Submitted:	8 Jan 2006 17:25	Modified:	31 Aug 2006 23:22
Reporter:	Andy Rigby	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.19-BK, 5.0.17	OS:	Linux (Linux)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
When NO_UNSIGNED_SUBTRACTION is used, adding -1 to column that is unsigned int with value 0 gives different result from subtracting 1.

e.g.

0 - 1 = 0
whereas
0 + -1 = 65535

this is probably related to bug 14543, but seemed sufficiently different to report - without NO_UNSIGNED_SUBTRACTION both methods return identical result.

How to repeat:
[andy@trinity devel]$ mysql -S /tmp/mysql5.sock -uroot demo
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 1035 to server version: 5.0.17-standard-log

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

mysql> drop table if exists test;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test` (`myint` SMALLINT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values (0);
Query OK, 1 row affected (0.01 sec)

# First, without NO_UNSIGNED_SUBTRACTION
# this triggers bug 14543

mysql> set session sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> update test set myint = myint - 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from test;
+-------+
| myint |
+-------+
| 65535 |
+-------+
1 row in set (0.00 sec)

mysql> update test set myint = 0;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update test set myint = myint + - 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from test;       
+-------+
| myint |
+-------+
| 65535 |
+-------+
1 row in set (0.00 sec)

mysql> update test set myint = 0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# Now with NO_UNSIGNED_SUBTRACTION

mysql> set session sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> update test set myint = myint - 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 1

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

mysql> update test set myint = 0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update test set myint = myint + - 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from test;
+-------+
| myint |
+-------+
| 65535 |
+-------+
1 row in set (0.00 sec)

mysql>

Thank you for a bug report. Verified just as described on 5.0.19-BK (ChangeSet@1.2020, 2006-01-06 08:34:55-08:00) on Linux.

This issue should not be considered as a bug. We ALWAYS get SIGNED value as a result of subtraction in NO_UNSIGNED_SUBTRACTION mode

mysql> drop table if exists test;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test` (`myint` SMALLINT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 AS SELECT myint - 1 FROM test;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe t1;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| myint - 1 | bigint(17) unsigned |      |     | 0       |       |
+-----------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> set sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 AS SELECT myint - 1 FROM test;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe t2;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| myint - 1 | bigint(17) |      |     | 0       |       |
+-----------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t3 AS SELECT myint + 1 FROM test;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe t3;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| myint + 1 | bigint(17) unsigned |      |     | 0       |       |
+-----------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

So, the wrapped around/out of range values in the bug report are handled in full concert with that the section 11.2. "Numeric Types" of the Manual says at its very end.

Thanks to Peter Gulutzan for clarification.

The request for update of the section 5.2.5. "The Server SQL Mode" of the Manual has been sent.

See also BUG#15472.

Manual updated per Vladimir's email.