Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 无符号类型转换问题
[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]
mysql> create table a1(id int not null auto_increment primary key, name varchar(20), total bigint unsigned);
Query OK, 0 rows affected (0.12 sec)
mysql> insert into a1 values(null, 'a', 15);
Query OK, 1 row affected (0.06 sec)
mysql> select * from a1;
+----+------+-------+
| id | name | total |
+----+------+-------+
| 1 | a | 15 |
+----+------+-------+
1 row in set (0.00 sec)
mysql> select 14-total from a1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(14 - `hhm`.`a1`.`total`)
How is type conversion handled at this low level?
BIGINT UNSIGNED only supports positive numbers. How can your result of -1 for 14-15 be stored under the BIGINT UNSIGNED type?
There is another issue with this type conversion that needs to be consistent with MySQL.
It looks like someone has also raised this issue with MySQL.
The conclusion is that this is not a bug.
If you don’t want to see this error, just cast it.
https://bugs.mysql.com/bug.php?id=83902
[4 Jan 2020 10:17] Roy Lyseng
Posted by developer:
This is not a bug.
The CRC32 function produces an UNSIGNED value, and the UNSIGNED property is propagated to the addition operator.
Workaround is to convert the CRC32 result to a signed integer:
select ((floor(-(401))) * CAST(CRC32(0) AS SIGNED));
You can also forcibly change the data type of the select result: SELECT 14 - CAST(total AS SIGNED) FROM a1;
How is the result type of select defined, according to the definition of the table structure fields?
I found this in the official MySQL documentation:
Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. If the result would otherwise have been negative, an error results:
mysql> alter table a1 add sum bigint;
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from a1;
+----+------+-------+------+
| id | name | total | sum |
+----+------+-------+------+
| 1 | a | 15 | NULL |
+----+------+-------+------+
1 row in set (0.01 sec)
mysql> update a1 set sum=15;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select 14-sum from a1;
+--------+
| 14-sum |
+--------+
| -1 |
+--------+
1 row in set (0.01 sec)
The result set of a SELECT statement itself does not store the field types in the database; it is just a temporary result set. However, each expression or calculation result will have an implicit data type, which is determined by the SQL engine based on the input data types and operations.
|
username: TiDB_C罗
|
username: 
|
username: