Description:
In MySQL 8.4.6, when using STRAIGHT_JOIN and INNER_JOIN with identical query conditions, different results are returned. This violates the SQL standard requirement for consistent JOIN semantics.
Specific behavior:
INNER JOIN query returns result row (1,1)
STRAIGHT_JOIN query returns empty result set
Both queries use identical ON conditions and table structures
How to repeat:
-- Create test tables
CREATE TABLE t0(c0 DECIMAL);
CREATE TABLE t1(c0 SMALLINT);
-- Insert test data
INSERT INTO t0(c0) VALUES(1);
INSERT INTO t1(c0) VALUES(0.9);
-- Create unique index
CREATE UNIQUE INDEX i0 USING BTREE ON t1(c0);
-- Execute INNER JOIN query (returns result)
SELECT * FROM t0 INNER JOIN t1 ON t1.c0 = COS(1);
-- Execute STRAIGHT_JOIN query (returns empty)
SELECT * FROM t0 STRAIGHT_JOIN t1 ON t1.c0 = COS(1);
Suggested fix:
The issue appears to be caused by the presence of the unique index on t1(c0). When the index is removed, both queries return consistent results.
