| Bug #106483 | BIGINT overflow does not cause SELECT statments errors | ||
|---|---|---|---|
| Submitted: | 17 Feb 2022 6:43 | Modified: | 18 Feb 2022 6:50 |
| Reporter: | John Jove | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.28 | OS: | Any |
| Assigned to: | CPU Architecture: | x86 | |
[17 Feb 2022 13:47]
MySQL Verification Team
Hi Mr. Jove, Thank you for your bug report. Your report is not fully correct. First of all, you do not have BIGINT operations at all in your test case. What you have is an XOR operation between a CLOB type and a floating point type. If MySQL were a strongly typed system, it would have thrown an error. However, couple of decades ago it was decided that design of MySQL database will be such that it will always attempt to do what user-written query is asking it to do, including the mixing of totally different types in the same expression. Turning MySQL into a strongly typed system is not possible now, since it would break, literally, million of applications ...... Not a bug.
[18 Feb 2022 6:50]
John Jove
But I try it in the UPDATE statement like follows:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 TEXT);
INSERT INTO t0 VALUES('a');
UPDATE t0 SET c0 = 'b' WHERE -1E19 ^ t0.c0;
The UPDATE statement failed and throw an error.
mysql> UPDATE t0 SET c0 = 'b' WHERE -1E19 ^ t0.c0;
ERROR 1690 (22003): BIGINT value is out of range in '-(1E19)'
mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------+
| Error | 1690 | BIGINT value is out of range in '-(1E19)' |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)
I think the UPDATE statement should succeed as the SELECT statement does. But this inconsistent behavior between SELECT and UPDATE statement truly confused me.
[18 Feb 2022 14:10]
MySQL Verification Team
Hi Mr. Jove, Still not a bug. With SELECT, MySQL server tries to return the result. However, with UPDATE, erroneous SQL can lead to the UPDATE of all rows, hence the error. Not a bug.
Description: I tried the following statements, where an out of range BIGINT -1E19 appeared in the where clause of the SELECT statement. I think this out of range should cause SELECT errors, but it does not and even there is no warning generated. How to repeat: DROP TABLE IF EXISTS t0; CREATE TABLE t0(c0 TEXT); INSERT INTO t0 VALUES('a'); SELECT c0 FROM t0 WHERE -1E19 ^ t0.c0;