Bug #110125 Unexpected query result fetched from an indexed column
Submitted: 19 Feb 2023 8:48 Modified: 20 Feb 2023 5:03
Reporter: John Jove Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.0.32, 5.7 OS:Ubuntu
Assigned to: CPU Architecture:Any

[19 Feb 2023 8:48] John Jove 
Description:
The SELECT statement returns an incorrect result on an indexed column.

How to repeat:
Run the following statements, in which the SELECT statement should return an empty result instead of a 0.

CREATE TABLE IF NOT EXISTS t0(c0 DOUBLE ZEROFILL) ;
CREATE INDEX i0 ON t0(c0 DESC);
INSERT DELAYED IGNORE INTO t0(c0) VALUES(0);

SELECT c0 FROM t0 WHERE (IFNULL(-1123870957, '')) IN (t0.c0); -- 0
[20 Feb 2023 5:03] MySQL Verification Team 
Hello John Jove,

Thank you for the report and feedback.

regards,
Umesh
[14 Oct 2024 15:24] OCA Admin 
Contribution submitted via Github - bug-110125 Unexpected query result fetched from an indexed column 
(*) Contribution by c q (Github hotdb-cq, mysql-server/pull/573#issuecomment-2410655526): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_2122268321.txt (text/plain), 1.89 KiB.