Bug #103093 MINUS between const value 0 and field with MIN bigint value gets wrong result
Submitted: 24 Mar 12:14 Modified: 12 Apr 18:10
Reporter: Hubery Lu Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[24 Mar 12:14] Hubery Lu
Description:
It seems Item_func_minus::int_op() does not consider "0 - col" carefully if col has MIN bigint value. It's expected to report out-of-range exception since bigint cannot have MAX bigint value + 1.

How to repeat:
mysql> create table t1(a bigint) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values (-9223372036854775808);
Query OK, 1 row affected (0.01 sec)

mysql> select a, 0-a from t1;
+----------------------+----------------------+
| a                    | 0-a                  |
+----------------------+----------------------+
| -9223372036854775808 | -9223372036854775808 |
+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> delete from t1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (-9223372036854775807);
Query OK, 1 row affected (0.00 sec)

mysql> select a, 1-a from t1;
ERROR 1690 (22003): BIGINT value is out of range in '(1 - `hubery`.`t1`.`a`)'
[26 Mar 14:02] MySQL Verification Team
Hi Mr. Lu,

Thank you for your bug report.

However, it is not a bug.

Simply, the maximum value for the signed 64-bit integer is 9,223,372,036,854,775,807, hence 0 - 9,223,372,036,854,775,808 can not be expressed for that type of the integer.

Ideally, the error should be returned, but MySQL is not a very strict type-checking system. This will not change, since the introduction of the strict type-checking would break millions of existing applications.

Not a bug.
[26 Mar 14:51] Tor Didriksen
In other parts of the codebase, we try quite hard to handle LLONG_MIN, and over/underflow issues. I think this is a (legacy) bug, and that the fix is quite simple.
[12 Apr 18:10] Jon Stephens
Documented fix as follows in the MySQL 8.0.25 changelog:

    0 - (MAX(BIGINT) + 1) returned -(MAX(BIGINT) + 1). Now an out of
    range error is returned instead.

Closed.
[13 Apr 12:47] MySQL Verification Team
Thank you , Jon .....
[26 Apr 17:52] Jon Stephens
This fix now appears in MySQL 8.0.26. No other changes.
[27 Apr 12:26] MySQL Verification Team
Thank you, Jon.