Bug #113302 Unexpected Result for an Index
Submitted: 1 Dec 2023 7:29 Modified: 1 Dec 2023 7:39
Reporter: JINSHENG BA Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:8.2.0, 8.0.35 OS:Any
Assigned to: CPU Architecture:Any

[1 Dec 2023 7:29] JINSHENG BA
Description:
CREATE TABLE t0(c0 INT, c1 INT);
INSERT INTO t0(c1, c0) VALUES(0, 1);

SELECT * FROM t0 WHERE t0.c1 IN (GREATEST(0.1, 0.2)); -- empty result
CREATE INDEX i0 ON t0(c1);
SELECT * FROM t0 WHERE t0.c1 IN (GREATEST(0.1, 0.2)); -- {1|0}

The same query returns different results with and without the index i0.

How to repeat:
docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.2.0

Then execute the above test case.
[1 Dec 2023 7:39] MySQL Verification Team
Hello Jinsheng Ba,

Thank you for the report and test case.
Verified as described.

regards,
Umesh