Bug #118832 using the return value replacing the IFNUILL function , the query returns different value;

• Submitted: 14 Aug 2:39
• Modified: 19 Aug 7:33
• Reporter: Alice Alice
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.41, 9.4.0
• OS: Any
• CPU Architecture: Any

[14 Aug 2:39] Alice Alice

Description:
When a constant value is replaced by the IFNULL(NULL, CONST_VALUE) function, the sql returns different value;

mysql> select IFNULL(NULL, 0.1261840155612991);
+----------------------------------+
| IFNULL(NULL, 0.1261840155612991) |
+----------------------------------+
|               0.1261840155612991 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ALL t0.c0 AS ref0, t0.c1 AS ref1, t0.c2 AS ref2, t0.c3 AS ref3, t0.c4 AS ref4, t0.c5 AS ref5, t0.c6 AS ref6 FROM t0 WHERE 0.1261840155612991 IN (t0.c3);
Empty set (0.00 sec)

mysql> SELECT ALL t0.c0 AS ref0, t0.c1 AS ref1, t0.c2 AS ref2, t0.c3 AS ref3, t0.c4 AS ref4, t0.c5 AS ref5, t0.c6 AS ref6 FROM t0 WHERE (IFNULL(NULL, 0.1261840155612991)) IN (t0.c3);
+----------+------+------+------+------+------+------+
| ref0     | ref1 | ref2 | ref3 | ref4 | ref5 | ref6 |
+----------+------+------+------+------+------+------+
|  Hn#N뺤  | NULL |  -44 |    0 |      | NULL |    0 |
+----------+------+------+------+------+------+------+
1 row in set (0.00 sec)

How to repeat:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `tlp2025052734` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `tlp2025052734`;

DROP TABLE IF EXISTS `t0`;

CREATE TABLE `t0` (
  `c0` varchar(500) /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf',
  `c1` tinyint /*!50606 STORAGE MEMORY */ DEFAULT NULL COMMENT 'asdf',
  `c2` bigint /*!50606 STORAGE DISK */ DEFAULT NULL,
  `c3` int DEFAULT NULL,
  `c4` varchar(500) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL COMMENT 'asdf',
  `c5` tinyint /*!50606 STORAGE MEMORY */ DEFAULT NULL COMMENT 'asdf',
  `c6` decimal(10,0) DEFAULT NULL COMMENT 'asdf',
  UNIQUE KEY `c1` (`c1`),
  UNIQUE KEY `c6` (`c6`),
  UNIQUE KEY `i0` (`c3` DESC,`c4`(2),`c5` DESC),
  KEY `i1` (`c2` DESC,`c5` DESC,`c0`(4),`c1`,`c6`,`c3` DESC) /*!80000 INVISIBLE */,
  KEY `i2` (`c4`(2),`c6`),
  KEY `i3` (`c4`(2) DESC) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t0` VALUES (' Hn#N뺤',NULL,-44,0,'',NULL,0);

SELECT ALL t0.c0 AS ref0, t0.c1 AS ref1, t0.c2 AS ref2, t0.c3 AS ref3, t0.c4 AS ref4, t0.c5 AS ref5, t0.c6 AS ref6 FROM t0 WHERE 0.1261840155612991 IN (t0.c3);

SELECT ALL t0.c0 AS ref0, t0.c1 AS ref1, t0.c2 AS ref2, t0.c3 AS ref3, t0.c4 AS ref4, t0.c5 AS ref5, t0.c6 AS ref6 FROM t0 WHERE (IFNULL(NULL, 0.1261840155612991)) IN (t0.c3);

[19 Aug 7:33] MySQL Verification Team

Verified as described

mysql> INSERT INTO `t0` VALUES (' Hn#N뺤',NULL,-44,0,'',NULL,0);
Query OK, 1 row affected (0.001 sec)

mysql> 
mysql> SELECT ALL t0.c0 AS ref0, t0.c1 AS ref1, t0.c2 AS ref2, t0.c3 AS ref3, t0.c4 AS ref4, t0.c5 AS ref5, t0.c6 AS ref6 FROM t0 WHERE 0.1261840155612991 IN (t0.c3);
Empty set (0.000 sec)

mysql> 
mysql> SELECT ALL t0.c0 AS ref0, t0.c1 AS ref1, t0.c2 AS ref2, t0.c3 AS ref3, t0.c4 AS ref4, t0.c5 AS ref5, t0.c6 AS ref6 FROM t0 WHERE (IFNULL(NULL, 0.1261840155612991)) IN (t0.c3);
+----------+------+------+------+------+------+------+
| ref0     | ref1 | ref2 | ref3 | ref4 | ref5 | ref6 |
+----------+------+------+------+------+------+------+
|  Hn#N뺤  | NULL |  -44 |    0 |      | NULL |    0 |
+----------+------+------+------+------+------+------+
1 row in set (0.001 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 9.4.0     |
+-----------+
1 row in set (0.000 sec)

mysql>