MySQL Bugs: #115408: Inconsistent Query Results in MySQL

Bug #115408	Inconsistent Query Results in MySQL
Submitted:	23 Jun 2024 3:51	Modified:	26 Jun 2024 10:27
Reporter:	Wenqian Deng	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
See the reproduction part.

How to repeat:
Set up the table:

CREATE TABLE table_0 (c_1 SMALLINT, c_2 INT, c_0 BIGINT);
INSERT INTO table_0 VALUES (28999, -1286849144, -37317338326856);

SELECT *, CAST(table_0.c_1 * table_0.c_2 AS SIGNED) FROM table_0;
+-------+-------------+-----------------+-------------------------------------------+
| c_1   | c_2         | c_0             | CAST(table_0.c_1 * table_0.c_2 AS SIGNED) |
+-------+-------------+-----------------+-------------------------------------------+
| 28999 | -1286849144 | -37317338326856 |                           -37317338326856 |
+-------+-------------+-----------------+-------------------------------------------+

c_0 and CAST(table_0.c_1 * table_0.c_2 AS SIGNED) have the same value in table_0.

execute this SELECT:

SELECT table_0.c_2 
FROM table_0 
WHERE ((table_0.c_2 <> (SELECT (-2071467172 & 140704899 & -189541664))) 
AND (table_0.c_0 > -7187267701664251602)) 
OR (table_0.c_0 < (-3751869525204584190 * -1267392877799265691 * 4803159170931056042));

Result: Error: BIGINT value is out of range in '(-(3751869525204584190) * -(1267392877799265691))'

Modify the SQL query by replacing table_0.c_0 with CAST(table_0.c_1 * table_0.c_2 AS SIGNED):

SELECT table_0.c_2 
FROM table_0 
WHERE ((table_0.c_2 <> (SELECT (-2071467172 & 140704899 & -189541664))) 
AND (CAST(table_0.c_1 * table_0.c_2 AS SIGNED) > -7187267701664251602)) 
OR (CAST(table_0.c_1 * table_0.c_2 AS SIGNED) < (-3751869525204584190 * -1267392877799265691 * 4803159170931056042));

Result:

+-------------+
| c_2         |
+-------------+
| -1286849144 |
+-------------+

Expected:

Both queries should either return the same result or produce an error, as they are logically equivalent.

Hi Mr. Deng,

Thank you for your bug report.

However, this is not a bug.

When you write SIGNED it implies INT.

That is a difference in the output of the queries.

Not a bug.

HI Mr. Deng,

One more comment.

You wrote :

-------------------------
: Error: BIGINT value is out of range in '(-(3751869525204584190) * -(1267392877799265691))'

Modify the SQL query by replacing table_0.c_0 with CAST(table_0.c_1 * table_0.c_2 AS SIGNED):
-------------------------

We do not see how are :

 '(-(3751869525204584190) * -(1267392877799265691))

identical with 

 CAST(table_0.c_1 * table_0.c_2 AS SIGNED):

Hi, the value of table_0.c_0 is identical with CAST(table_0.c_1 * table_0.c_2 AS SIGNED).

(-(3751869525204584190) * -(1267392877799265691)) in the error message is a random constant in SELECT.

According to the document, 

SIGNED [INTEGER]
Produces a signed BIGINT value.

Hi,

According to the document, SIGNED is just the attribute to the defined integer type.

Hence, our previous question still remains .......

Hi,

To expand on our question .....

SMALLINT * INT makes INT , not BIGINT.

Thank you for the correction. Perhaps the statement in the documentation is somewhat confusing for users. 

Additionally, if I change all columns in the table to BIGINT type (as I think this might ensure type consistency), the issue persists. 

see: https://dbfiddle.uk/zybl9_Bj

Hi Mr. Deng,

The problem definitely has to persist.

Namely, -(3751869525204584190) * -(1267392877799265691) is far beyond the range of the 64-bit signed integer.

Not a bug.