Bug #110703 SQL execute return error "BIGINT UNSIGNED value is out of range"

• Submitted: 15 Apr 2023 10:45
• Modified: 15 Apr 2023 13:08
• Reporter: Qingping Zhu
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 8.0.32
• OS: CentOS
• CPU Architecture: x86

[15 Apr 2023 10:45] Qingping Zhu

Description:
Return error when execute SQL command. 
MySQL should not return error for this scenario.

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (c0 BIGINT UNSIGNED, c1 MEDIUMINT UNSIGNED);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1(c0,c1) VALUES (162,24);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1 where c0 = (c1 - c0);
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`t1`.`c1` - `test`.`t1`.`c0`)'

How to repeat:
CREATE TABLE t1 (c0 BIGINT UNSIGNED, c1 MEDIUMINT UNSIGNED);
INSERT INTO t1(c0,c1) VALUES (162,24);
select * from t1 where c0 = (c1 - c0);

Suggested fix:
Use `Item_func_minus::decimal_op` instead of `Item_func_minus::int_op` to handle this scenario.

[15 Apr 2023 13:08] Roy Lyseng

This is how negative values from unsigned integer operations are supposed
to be handled, so it is not a bug.
You can overcome the problem by using explicit CAST operations in the SQL query.