Description:
When subtracting two UNSIGNED INTEGERs that evaluate to a negative value, mysqld gives this error message:
mysql> SELECT booked - cancelled > 0 FROM tbl1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`av`.`tbl1`.`booked` - `av`.`tbl1`.`cancelled`)'
A clearer message would be:
ERROR 1690 (22003): numeric overflow (BIGINT UNSIGNED) while subtracting two unsigned values: (`av`.`tbl1`.`booked` - `av`.`tbl1`.`cancelled`)
How to repeat:
mysql> create table tbl1 ( id int unsigned not null primary key , booked tinyint unsigned, cancelled tinyint unsigned ) engine = innodb;
Query OK, 0 rows affected (0.05 sec)
mysql> show create table tbl1\G
*************************** 1. row ***************************
Table: tbl1
Create Table: CREATE TABLE `tbl1` (
`id` int(10) unsigned NOT NULL,
`booked` tinyint(3) unsigned DEFAULT NULL,
`cancelled` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SELECT booked - cancelled > 0 FROM tbl1;
Empty set (0.00 sec)
mysql> insert into tbl1 VALUES ( 1, 10, 5 ), ( 2, 20, 10 );
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT booked - cancelled > 0 FROM tbl1;
+------------------------+
| bookedd - cancelled > 0 |
+------------------------+
| 1 |
| 1 |
+------------------------+
2 rows in set (0.00 sec)
mysql> insert into tbl1 VALUES ( 3, 5, 10 );
Query OK, 1 row affected (0.00 sec)
mysql> SELECT booked - cancelled > 0 FROM tbl1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`av`.`tbl1`.`booked` - `av`.`tbl1`.`cancelled`)'
Suggested fix:
Replace the error message with:
ERROR 1690 (22003): numeric overflow (BIGINT UNSIGNED) while subtracting two unsigned values: (`av`.`tbl1`.`booked` - `av`.`tbl1`.`cancelled`)
© 2025, Oracle Corporation and/or its affiliates
Description: When subtracting two UNSIGNED INTEGERs that evaluate to a negative value, mysqld gives this error message: mysql> SELECT booked - cancelled > 0 FROM tbl1; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`av`.`tbl1`.`booked` - `av`.`tbl1`.`cancelled`)' A clearer message would be: ERROR 1690 (22003): numeric overflow (BIGINT UNSIGNED) while subtracting two unsigned values: (`av`.`tbl1`.`booked` - `av`.`tbl1`.`cancelled`) How to repeat: mysql> create table tbl1 ( id int unsigned not null primary key , booked tinyint unsigned, cancelled tinyint unsigned ) engine = innodb; Query OK, 0 rows affected (0.05 sec) mysql> show create table tbl1\G *************************** 1. row *************************** Table: tbl1 Create Table: CREATE TABLE `tbl1` ( `id` int(10) unsigned NOT NULL, `booked` tinyint(3) unsigned DEFAULT NULL, `cancelled` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> SELECT booked - cancelled > 0 FROM tbl1; Empty set (0.00 sec) mysql> insert into tbl1 VALUES ( 1, 10, 5 ), ( 2, 20, 10 ); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT booked - cancelled > 0 FROM tbl1; +------------------------+ | bookedd - cancelled > 0 | +------------------------+ | 1 | | 1 | +------------------------+ 2 rows in set (0.00 sec) mysql> insert into tbl1 VALUES ( 3, 5, 10 ); Query OK, 1 row affected (0.00 sec) mysql> SELECT booked - cancelled > 0 FROM tbl1; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`av`.`tbl1`.`booked` - `av`.`tbl1`.`cancelled`)' Suggested fix: Replace the error message with: ERROR 1690 (22003): numeric overflow (BIGINT UNSIGNED) while subtracting two unsigned values: (`av`.`tbl1`.`booked` - `av`.`tbl1`.`cancelled`)