Bug #114539 Unexpected Results by the hint NO_INDEX
Submitted: 3 Apr 2024 7:36 Modified: 3 Apr 2024 8:13
Reporter: John Jove Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.0.36 OS:Any
Assigned to: CPU Architecture:Any

[3 Apr 2024 7:36] John Jove 
Description:
When applying the query hint NO_INDEX to run the same query by another query plan, the query result is changed unexpectedly, since we expect that the query optimizer should always output the same result for a given query.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 BIT);
CREATE INDEX i8 ON t1(c1 ASC);
INSERT INTO t1 VALUES (0);

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (c1 TEXT);
INSERT INTO t2 VALUES ('0');

SELECT /*+ NO_INDEX(t1)*/ t1.c1, t2.c1 FROM t1 NATURAL JOIN t2; -- Non-empty
SELECT t1.c1, t2.c1 FROM t1 NATURAL JOIN t2; -- Empty

Suggested fix:
In this case, index should not be preferred to access the table t1.
[3 Apr 2024 8:13] MySQL Verification Team 
Hello John,

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

regards,
Umesh