| Bug #114719 | Incorrect results caused by different access types for an index | ||
|---|---|---|---|
| Submitted: | 21 Apr 2024 14:31 | Modified: | 22 Apr 2024 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 | |
[22 Apr 2024 7:43]
MySQL Verification Team
Hello John, Thank you for the report and test case. Verified as described. regards, Umesh
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.