Bug #115129 Results in “BIGINT Value Out of Range” Error When Executing SELECT

• Submitted: 26 May 8:18
• Modified: 27 May 10:23
• Reporter: Wenqian Deng
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.4
• OS: Any
• CPU Architecture: Any

[26 May 8:18] Wenqian Deng

Description:
MySQL returns an error message: BIGINT value is out of range in '-9223372036854775808 * 1454377110' when performing a SELECT.

However, I didn't directly have -9223372036854775808 * 1454377110 in my SELECT.

How to repeat:
Create the table:
CREATE TABLE t0 (c_0 INT, c_1 INT, c_2 INT);

Execute the following query:
SELECT t0.c_1 
FROM t0 
WHERE (t0.c_1 = t0.c_0 * t0.c_1 AND t0.c_0 = -9223372036854775808) 
AND t0.c_1 = 1454377110;

Expected Behavior
The query should return an empty set

Actual Behavior
The query results in an error message: BIGINT value is out of range in '-9223372036854775808 * 1454377110'

[27 May 9:55] MySQL Verification Team

Hi MR. Deng,

Thank you for your bug report.

However, this is not a bug.

MySQL optimiser works in a way to make the query as fast as possible. Since you have AND with a value that leads to the multiplication, the error message that you get is quite correct.

Not a bug.

[27 May 10:23] Wenqian Deng

Thanks for the kind response. But I think that if the SQL query itself does not have a syntax error, it should not throw an error, which is confusing. Furthermore, Postgres and SQLite do not throw such errors when used.

[27 May 10:33] MySQL Verification Team

Hi Mr. Deng,

There are many database makes and each one has it's own optimiser .........

Not a bug.