Bug #112242 Unexpected Results by use_invisible_indexes

• Submitted: 4 Sep 2023 4:40
• Modified: 4 Sep 2023 8:32
• Reporter: JINSHENG BA
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.1.0, 8.0.34
• OS: Any
• CPU Architecture: Any

[4 Sep 2023 4:40] JINSHENG BA

Description:
CREATE TABLE t0(c0 INT);
INSERT INTO t0(c0) VALUES(1);
CREATE INDEX i0 USING HASH ON t0(c0) INVISIBLE;

SET SESSION optimizer_switch = 'use_invisible_indexes=off';
SELECT t0.c0 FROM t0 WHERE COALESCE(0.6) IN (t0.c0); -- {}
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
SELECT t0.c0 FROM t0 WHERE COALESCE(0.6) IN (t0.c0); -- {1}

Changing the configuration 'use_invisible_indexes' affects the query result.

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

Then execute above test case.

[4 Sep 2023 8:32] MySQL Verification Team

Hello Jinsheng Ba,

Thank you for the report and test case.

regards,
Umesh