Use of NO UNSIGNED SUBTRACT SQL Mode in Handling Overflow

In case of enabled SQL strict mode, subtraction between integers value in which one is of UNSIGNED type will produce an unsigned result by default. But MySQL produces an error if the result is a negative one. It can be observed with the following example −

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

mysql> Select CAST(0 AS UNSIGNED) -1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

The error after the query above shows that it is an overflow after numeric arithmetic expression.

Now, it can be handled with the help of enabling NO_UNSIGNED_SUBTRACTION SQL mode. After enabling this mode the result would be -1 instead of an error.

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

mysql> Select CAST(0 AS UNSIGNED) -1;
+------------------------+
| CAST(0 AS UNSIGNED) -1 |
+------------------------+
|                     -1 |
+------------------------+
1 row in set (0.00 sec)