Bug #101269 using different index may produce different results
Submitted: 22 Oct 2020 3:26 Modified: 11 Apr 9:06
Reporter: xiaoyang chen Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.7, 8.0, 8.0.22, 5.7.32 OS:Any
Assigned to: CPU Architecture:Any

[22 Oct 2020 3:26] xiaoyang chen 
Description:
The same SQL, forcing the use of different indexes, may produce different results.

```
CREATE TABLE `t0` (
  `c0` mediumint(8) unsigned zerofill DEFAULT NULL,
  `c1` decimal(10,0) unsigned zerofill DEFAULT NULL,
  UNIQUE KEY `c0` (`c0`),
  UNIQUE KEY `c1` (`c1`),
  KEY `i0` (`c0`)
) ENGINE=InnoDB; 

INSERT IGNORE INTO t0(c1) VALUES(968599958);
INSERT IGNORE INTO t0(c1, c0) VALUES("", -1.1961035E8);
INSERT IGNORE INTO t0(c1) VALUES(NULL);
INSERT INTO t0(c0) VALUES(NULL);
INSERT IGNORE INTO t0(c1) VALUES(NULL), (NULL);

```

SELECT t0.c0 AS ref0 FROM t0 FORCE INDEX(c0) WHERE (NOT ((COALESCE(NULL, "FWlPp", 'TO')) != (t0.c1)));
+----------+
| ref0     |
+----------+
| 00000000 |
+----------+

SELECT t0.c0 AS ref0 FROM t0 FORCE INDEX(c1) WHERE (NOT ((COALESCE(NULL, "FWlPp", 'TO')) != (t0.c1)));
Empty set, 1 warning (0.00 sec)

How to repeat:
Follow the above description.
[22 Oct 2020 3:40] xiaoyang chen 
no more
[22 Oct 2020 4:36] MySQL Verification Team 
Hello xiaoyang chen,

Thank you for the report and test case.

regards,
Umesh
[17 Nov 2021 0:41] hao chen 
This bug has repaired in Bug#32345941 - SELECT RESULT IS MISMATCH WHEN USE COALESCE AND NOT.  See: https://github.com/mysql/mysql-server/commit/c204c7ff9d40b37cd4e5e66246953d82ff4e31e1
[11 Apr 9:06] Erlend Dahl 
The above observation is correct. Duplicate of 

Bug#102151 select result is mismatch when use COALESCE and not.

which was fixed in 8.0.27.