Bug #114719 Incorrect results caused by different access types for an index
Submitted: 21 Apr 14:31 Modified: 22 Apr 7:43
Reporter: John Jove Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.0.36 OS:Any
Assigned to: CPU Architecture:Any

[21 Apr 14:31] John Jove
Run the following statements, in which the query Q1 is expected to return {0}. However, an empty result is returned.
If we enforce a different join order (the query Q2), the return result becomes correct.
I introspect their query plans, in which the access type in Q1 is ref and the access type in Q2 is index.

How to repeat:
CREATE TABLE t1 (c1 longtext);
CREATE TABLE t2 (c1 bit, KEY i0 (c1));
INSERT INTO t1 (c1) VALUES ('0');
INSERT INTO t2 (c1) VALUES (b'0');
-- Q1
SELECT /*+ JOIN_ORDER(t1,t2) */ t1.c1 FROM t1 NATURAL JOIN t2 USE INDEX (i0); -- {}, expected to be {0}
-- Q2
SELECT /*+ JOIN_ORDER(t2,t1) */ t1.c1 FROM t1 NATURAL JOIN t2 USE INDEX (i0); -- {0}

Suggested fix:
In this case, we should not use the access type ref, since the data types of join columns are different.
[22 Apr 7:43] MySQL Verification Team
Hello John,

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