MySQL Bugs: #103093: MINUS between const value 0 and field with MIN bigint value gets wrong result

Bug #103093	MINUS between const value 0 and field with MIN bigint value gets wrong result
Submitted:	24 Mar 2021 12:14	Modified:	12 Apr 2021 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

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`)'

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.

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.

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.

Thank you , Jon .....

This fix now appears in MySQL 8.0.26. No other changes.

Thank you, Jon.