Bug #113302 Unexpected Result for an Index

• Submitted: 1 Dec 2023 7:29
• Modified: 1 Dec 2023 7:39
• Reporter: JINSHENG BA
• Status: Verified
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 8.2.0, 8.0.35
• OS: Any
• 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

[14 Oct 15:24] OCA Admin

Contribution submitted via Github - bug-113302/110125/113317  
(*) Contribution by c q (Github hotdb-cq, mysql-server/pull/572#issuecomment-2408501423): 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_2120670342.txt (text/plain), 1.49 KiB.