0

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?

CN42
  • 3
  • 1
  • 1
    `tinyint unsigned` means it can't contain negative (below 0) values. If your subtraction results in a negative value, it causes that error. – Ken White Nov 12 '20 at 04:18
  • *`where rentPayDay - 11 <= 5`* Use such conditions never. It forbids index usage and causes table fullscan. When possible convert the condition expression to the form `{column} {operator} {constant expression}` The expression will be evaluated during execution plan building - one time per the whole query. I.e. the expression must look like `where rentPayDay <= 5 + 11` Additionally - the problem with "out of range" will not occur. – Akina Nov 12 '20 at 04:36
  • @Akina answer show me the way to solve the problem. May thanks to you and your reminds. – CN42 Nov 12 '20 at 06:31
  • Reply @KenWhite, I didn't find any reference, but I guess the ```{rentPayDay - 11}``` express should follow the same restriction as rentPayDay's definition. the better and safe way should be compare rentPayDay with some other calculated value. – CN42 Nov 12 '20 at 06:34

2 Answers2

1

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;

As demonstrated here.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • 1
    unfortunately, this didn't solve my problem. Many thanks to you. – CN42 Nov 12 '20 at 06:35
  • Cannot solve. The substraction, not the function, is performed firstly. Due to operand type it is performed as unsigned calculation - and negative result causes error. – Akina Nov 12 '20 at 06:37
  • I've tried a new approach. Surprisingly annoying to fix. – tadman Nov 12 '20 at 06:46
  • @Akina show the right direction to this. my problem solved as this : select roomNum, owner, rentPayDay from t_room where (rentPayDay >= EXTRACT(DAY FROM NOW())) AND (rentPayDay <= EXTRACT(DAY FROM NOW()) + 5); – CN42 Nov 12 '20 at 06:52
  • That's another solution to avoid the subtraction. – tadman Nov 12 '20 at 06:54
0

with the reference of @Akina and @Ken White, here is my guess to this error:

the express {columnName OPERATOR -value-} still be restricted as a whole with the same definition of {columnName}.

I don't know why and what happened in MySQL'e response to my query. The better way to solve this:

please refer to @Akina comment under my original question post.

Many thanks to all who care and help.

CN42
  • 3
  • 1