Bug #101269 using different index may produce different results

• Submitted: 22 Oct 2020 3:26
• Modified: 22 Oct 2020 4:36
• Reporter: xiaoyang chen
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 5.7, 8.0, 8.0.22, 5.7.32
• OS: Any
• 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