Bug #119124 The result of the COALESCE function varies depending on its different parameters.

• Submitted: 10 Oct 3:28
• Reporter: Alice Alice
• Status: Open
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any

[10 Oct 3:28] Alice Alice

Description:
In theory, the results of the following three execution statements should be the same, but in practice, one of them produces an incorrect result.

mysql> SELECT c0  FROM t0 WHERE c0 = COALESCE(-1402090523);
Empty set (0.00 sec)

mysql> 
mysql> SELECT c0  FROM t0 WHERE c0 = COALESCE(-1402090523, '-271878899');
+------------------------+
| c0                     |
+------------------------+
| 0000000000000000000000 |
| 0000000000000000000000 |
+------------------------+
2 rows in set (0.00 sec)

mysql> 
mysql> SELECT c0 FROM t0 IGNORE INDEX(i1) WHERE c0 = COALESCE(-1402090523, '-271878899');
Empty set (0.00 sec)

How to repeat:
CREATE TABLE `t0` (
  `c0` double unsigned zerofill DEFAULT NULL,
  KEY `i1` (`c0` DESC)
);

INSERT INTO `t0` VALUES (0),(0);

SELECT c0  FROM t0 WHERE c0 = COALESCE(-1402090523);

SELECT c0  FROM t0 WHERE c0 = COALESCE(-1402090523, '-271878899');

SELECT c0 FROM t0 IGNORE INDEX(i1) WHERE c0 = COALESCE(-1402090523, '-271878899');