| Bug #114568 | Different join orders cause inconsistent results | ||
|---|---|---|---|
| Submitted: | 7 Apr 2024 13:43 | Modified: | 8 Apr 2024 6:37 |
| 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 | |
| Tags: | regression | ||
[8 Apr 2024 6:37]
MySQL Verification Team
Hello John, Thank you for the report and test case. Verified as described. regards, Umesh
Description: In the following case, I run the join query with different join orders, in which the same results are expected to be returned. However, query Q1 returns an empty result, while query Q2 returns a non-empty result. How to repeat: CREATE TABLE t1 (c1 BIT PRIMARY KEY); INSERT INTO t1 (c1) VALUES (b'0'); CREATE TABLE t2 (c1 BINARY); REPLACE INTO t2 (c1) VALUES ('0'); -- Q1 SELECT /*+ JOIN_ORDER(t2,t1) */ t1.c1 FROM t2 JOIN t1 USING (c1); -- Empty result -- Q2 SELECT /*+ JOIN_ORDER(t1,t2) */ t1.c1 FROM t2 JOIN t1 USING (c1); -- Non-empty result