Bug #119339 The results are different after extracting the value of the COALESCE function

• Submitted: 8 Nov 17:27
• Reporter: Go Yakult
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.41，9.5.0
• OS: Any
• CPU Architecture: Any

[8 Nov 17:27] Go Yakult

Description:
COALESCE(0.1, 1) = 0.1，But the following two queries are not equal.

mysql> SELECT t0.c0 FROM t0 WHERE (! ((COALESCE(0.1, 1)) != (t0.c0))) ;
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.000 sec)

mysql> 
mysql> SELECT t0.c0 FROM t0 WHERE (! ((0.1) != (t0.c0)));
Empty set, 1 warning (0.000 sec)

How to repeat:
DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` (
  `c0` smallint DEFAULT NULL,
  KEY `i1` (`c0`) USING BTREE,
  KEY `i0` (`c0`)
);

insert into t0(c0) values(1),(0);

SELECT t0.c0 FROM t0 WHERE (! ((COALESCE(0.1, 1)) != (t0.c0))) ;
SELECT t0.c0 FROM t0 WHERE (! ((0.1) != (t0.c0)));