Here is the data in MySQL(8.0.x) running with MacOS:
ysql> desc t_room;
+--------------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------------+------+-----+---------+-------+
| roomNum | varchar(60) | NO | PRI | NULL | |
| roomType | varchar(20) | NO | | NULL | |
| roomDesc | varchar(400) | YES | | NULL | |
| rentPayDay | tinyint unsigned | YES | | NULL | |
| rentFee | smallint unsigned | NO | | NULL | |
| asstMine | varchar(400) | YES | | NULL | |
| asstOwner | varchar(400) | YES | | NULL | |
| owner | varchar(30) | YES | | NULL | |
| signDate | date | YES | | NULL | |
| contract | mediumblob | YES | | NULL | |
| roomFacility | varchar(120) | YES | | NULL | |
+--------------+-------------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
mysql> select rentPayDay from t_room;
+------------+
| rentPayDay |
+------------+
| 15 |
| 20 |
| 25 |
| 10 |
+------------+
4 rows in set (0.00 sec)
when I do the query with where clause as below, everything is fine:
mysql> select roomNum, owner, rentPayDay from t_room where rentPayDay - 10 <= 5;
+---------------+--------+------------+
| roomNum | owner | rentPayDay |
+---------------+--------+------------+
| xxxC-906 | John | 15 |
| xxxxx-908 | Doe | 10 |
+---------------+--------+------------+
2 rows in set (0.00 sec)
BUT, when I minus a value that maybe bigger than some of the rentPayDay values, like this:
mysql> select roomNum, owner, rentPayDay from t_room where rentPayDay - 11 <= 5;
things went wrong like this:
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`dev_learn`.`t_room`.`rentPayDay` - 11)'
Any one have any idea about this? why the TINYINT data can't minus a bigger numeric value in the where clause?
An UNSIGNED TINYINT has a range 0-255, and negative values can't be expressed in it.
If you're getting casting errors, consider:
SELECT * FROM rooms WHERE CAST(rentPayDay AS DECIMAL) - 11 <= 5;