MySQL Bugs: #118224: When a constant value is replaced by the LEST function, the sql returns different value;

Bug #118224	When a constant value is replaced by the LEST function, the sql returns different value;
Submitted:	19 May 2025 2:46	Modified:	7 Feb 19:21
Reporter:	SamonBing SamonBing	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.41	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a constant value is replaced by the LEST function, the return value of the LEST function is the same as that of the constant, but the query result is different.

How to repeat:
mysql> SELECT ALL hbtest.c0 AS ref0 FROM hbtest WHERE 0.7401335565101936 IN (hbtest.c0);
Empty set (0.00 sec)

mysql> select LEAST(0.8044314491415255, 0.7401335565101936);
+-----------------------------------------------+
| LEAST(0.8044314491415255, 0.7401335565101936) |
+-----------------------------------------------+
|                            0.7401335565101936 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ALL hbtest.c0 AS ref0 FROM hbtest WHERE LEAST(0.8044314491415255, 0.7401335565101936) IN (hbtest.c0);
+------+
| ref0 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Supplement the table structure and data:

CREATE TABLE `hbtest` (
  `c0` bigint /*!50606 STORAGE DISK */ DEFAULT NULL COMMENT 'asdf',
  KEY `i0` (`c0`)
);

insert into hbtest values(1);

Supplement the table structure and data:

CREATE TABLE `hbtest` (
  `c0` bigint /*!50606 STORAGE DISK */ DEFAULT NULL COMMENT 'asdf',
  KEY `i0` (`c0`)
);

insert into hbtest values(1);

another scenerio similar with this problem;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `tidb202505298`;

USE `tidb202505298`;

DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` (
  `c0` varchar(392) NOT NULL,
  PRIMARY KEY (`c0`),
  KEY `i0` (`c0`(9))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t0` VALUES ('1234567890'),('wegf3grq3g');

/*!50001 DROP VIEW IF EXISTS `v0`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_0900_ai_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v0` (`c0`) AS select ((NULL >> NULL) like (6.99366851E8 is null)) AS `((((NULL)>>(NULL)))LIKE(((6.99366851E8) IS NULL)))` from `t0` where (0 <> cast(`t0`.`c0` as char charset binary)) */;

mysql> select (NOT (IS_IPV4_MAPPED(((((CAST(t0.c0 AS TIME))AND(-1754917274)))^(EXPORT_SET('0', t0.c0, t0.c0)))))) from t0;
+-----------------------------------------------------------------------------------------------------+
| (NOT (IS_IPV4_MAPPED(((((CAST(t0.c0 AS TIME))AND(-1754917274)))^(EXPORT_SET('0', t0.c0, t0.c0)))))) |
+-----------------------------------------------------------------------------------------------------+
|                                                                                                   1 |
|                                                                                                   1 |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM  v0 LEFT  OUTER JOIN t0 ON v0.c0 WHERE 1;
+------+------+
| c0   | c0   |
+------+------+
| NULL | NULL |
+------+------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT * FROM  v0 LEFT  OUTER JOIN t0 ON v0.c0 WHERE (NOT (IS_IPV4_MAPPED(((((CAST(t0.c0 AS TIME))AND(-1754917274)))^(EXPORT_SET('0', t0.c0, t0.c0))))));
Empty set (0.00 sec)

after replace the constant value "1" with "(NOT (IS_IPV4_MAPPED(((((CAST(t0.c0 AS TIME))AND(-1754917274)))^(EXPORT_SET('0', t0.c0, t0.c0))))))", the return results changed.

Thank you for the bug report.
Verified as described.