| Bug #119144 | INNER JOIN and STRAIGHT_JOIN return different results with identical ABS function conditions | ||
|---|---|---|---|
| Submitted: | 13 Oct 2025 12:49 | Modified: | 6 Jan 12:48 |
| Reporter: | Ye Shiyang | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.4.6 | OS: | Windows |
| Assigned to: | Øystein Grøvlen | CPU Architecture: | Any (x64) |
| Tags: | ABS_FUNCTION, INNER_JOIN, straight_join | ||
[6 Jan 12:48]
Øystein Grøvlen
Thank you, for the test case. Verified as described.
Description: INNER JOIN and STRAIGHT_JOIN queries with identical join conditions involving ABS function return different results. This violates the fundamental principle that STRAIGHT_JOIN should be semantically equivalent to INNER_JOIN. If we delete the indexs,the bug can't be reproduce. How to repeat: -- Create test tables CREATE TABLE IF NOT EXISTS t0(c0 TINYTEXT); CREATE TABLE t1(c0 INT, c1 FLOAT); -- Insert test data INSERT DELAYED IGNORE INTO t1(c0) VALUES(0.6507387521314772); -- Create indexes CREATE UNIQUE INDEX i0 USING BTREE ON t1(c0); CREATE INDEX i1 USING HASH ON t1(c1); REPLACE LOW_PRIORITY INTO t0(c0) VALUES(0.651051750871951); REPLACE DELAYED INTO t0(c0) VALUES("9"); -- First query (returns empty) SELECT DISTINCT t0.c0, t1.c0, t1.c1 FROM t0 INNER JOIN t1 ON (ABS(0.5457520772349101)) = (t1.c0); -- Second query (returns 2 rows, should match first query) SELECT DISTINCT t0.c0, t1.c0, t1.c1 FROM t0 STRAIGHT_JOIN t1 ON (ABS(0.5457520772349101)) = (t1.c0);