Bug #115409 Inconsistent Query Results in SELECT

• Submitted: 23 Jun 4:09
• Modified: 25 Jun 9:48
• Reporter: Wenqian Deng
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[23 Jun 4:09] Wenqian Deng

Description:
See the reproduction part.

How to repeat:
Set up the table:

CREATE TABLE table_0 (c_1 DATE, c_0 BIGINT);
INSERT INTO table_0 VALUES ('3937-05-10', '1438089');

mysql> SELECT *, CAST(TO_DAYS(table_0.c_1) AS SIGNED) FROM table_0;
+------------+---------+--------------------------------------+
| c_1        | c_0     | CAST(TO_DAYS(table_0.c_1) AS SIGNED) |
+------------+---------+--------------------------------------+
| 3937-05-10 | 1438089 |                              1438089 |
+------------+---------+--------------------------------------+

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

execute this SELECT:

SELECT table_0.c_0 
FROM table_0 
WHERE ((table_0.c_0 > (-6498242090020715023 & -5304191213068114627)) 
AND (NOT table_0.c_0 IS NULL)) 
AND (table_0.c_0 BETWEEN (CASE WHEN (table_0.c_0 >= 5012737949059595650) 
THEN -8193026920534675560 ELSE -8193026920534675560 END) 
AND (SELECT (-4006693057388804004 * -7183001761995876130 * -6986254851626180057 * 5449534193544976482)));

Result:
Error: BIGINT value is out of range in '(-(4006693057388804004) * -(7183001761995876130))'

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

SELECT CAST(TO_DAYS(table_0.c_1) AS SIGNED) 
FROM table_0 
WHERE ((CAST(TO_DAYS(table_0.c_1) AS SIGNED) > (-6498242090020715023 & -5304191213068114627)) 
AND (NOT CAST(TO_DAYS(table_0.c_1) AS SIGNED) IS NULL)) 
AND (CAST(TO_DAYS(table_0.c_1) AS SIGNED) BETWEEN (CASE WHEN (CAST(TO_DAYS(table_0.c_1) AS SIGNED) >= 5012737949059595650) 
THEN -8193026920534675560 ELSE -8193026920534675560 END) 
AND (SELECT (-4006693057388804004 * -7183001761995876130 * -6986254851626180057 * 5449534193544976482)));

Result:
Empty set

Expected:

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

[25 Jun 9:48] MySQL Verification Team

Hi Mr. Deng,

Thank you for your bug report.

In this bug report, SIGNED implies INT, so this is not a bug either.

Not a bug.

[25 Jun 10:48] MySQL Verification Team

Hi Mr. Deng,

We do not see how is:

'(-(4006693057388804004) * -(7183001761995876130))'

identical with CAST(TO_DAYS(table_0.c_1) AS SIGNED).