Bug #117451 Comparing a BIGINT column with the result of an IF function using "=" yields unexpected results.

• Submitted: 13 Feb 2025 1:33
• Modified: 13 Feb 2025 4:15
• Reporter: wang jack
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.4.1 9.1.0, 8.0.41
• OS: Windows (windows 11)
• CPU Architecture: x86 (x86_64)

[13 Feb 2025 1:33] wang jack

Description:
From my understanding, the first query below should return empty, but it is actually return 1 row.

mysql> SELECT t0.c0 FROM t0 WHERE (t0.c0) = (IF(1122822918, 0.34987436284057793, NULL));
+----------------------+
| c0                   |
+----------------------+
| 00000000000000000000 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(((t0.c0) = (IF(1122822918, 0.34987436284057793, NULL))) IS TRUE) FROM t0;
+----------------------------------------------------------------------+
| SUM(((t0.c0) = (IF(1122822918, 0.34987436284057793, NULL))) IS TRUE) |
+----------------------------------------------------------------------+
|                                                                    0 |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
The ZEROFILL attribute and index of a column can affect the results.

DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;

CREATE TABLE t0(c0 BIGINT ZEROFILL NOT NULL) ;
CREATE TABLE IF NOT EXISTS t1 LIKE t0;
CREATE INDEX i0 ON t0(c0 ASC) VISIBLE;
INSERT IGNORE INTO t0(c0) VALUES(-202718286);

SELECT t0.c0 FROM t0 WHERE (t0.c0) = (IF(1122822918, 0.34987436284057793, NULL));
SELECT SUM(((t0.c0) = (IF(1122822918, 0.34987436284057793, NULL))) IS TRUE) FROM t0;

[13 Feb 2025 4:15] MySQL Verification Team

Hello wang jack,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

[30 Dec 2025 7:41] Xinhao Zhao

Duplicate of Bug#108765