| Bug #114204 | Unexpected Results by COALESCE | ||
|---|---|---|---|
| Submitted: | 4 Mar 2024 8:43 | Modified: | 4 Mar 2024 8:56 |
| Reporter: | JINSHENG BA | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 8.2.0, 8.3.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[4 Mar 2024 8:56]
MySQL Verification Team
Hello Jinsheng Ba, Thank you for the report and test case. Verified as described. regards, Umesh
Description: CREATE TABLE t0(c0 INT ZEROFILL NULL); CREATE INDEX i0 USING BTREE ON t0; CREATE INDEX i1 USING BTREE ON t0(c0 DESC); INSERT IGNORE INTO t0(c0) VALUES(-1); SELECT t0.c0 AS ref0 FROM t0; -- {0000000000} SELECT ALL t0.c0 AS ref0 FROM t0 WHERE COALESCE(0.1, 0.3) != t0.c0; -- {0000000000} SELECT t0.c0 AS ref0 FROM t0 WHERE NOT (COALESCE(0.1, 0.3) != t0.c0); -- {0000000000} The last two queries have mutually exclusive predicates for WHERE, but return the same results. How to repeat: docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.2.0 Then execute the above test case.