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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.36
• OS: Any
• CPU Architecture: Any

[21 Apr 14:31] John Jove

Description:
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.

regards,
Umesh