MySQL Bugs: #119097: STRAIGHT_JOIN returns different results compared to INNER JOIN with identical conditions

Bug #119097	STRAIGHT_JOIN returns different results compared to INNER JOIN with identical conditions
Submitted:	30 Sep 9:04	Modified:	11 Oct 3:24
Reporter:	Ye Shiyang	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	MySQL 8.4.6	OS:	Windows
Assigned to:		CPU Architecture:	Other (x64)
Tags:	INDEX, INNER_JOIN, JOIN_OPTIMIZATION, straight_join

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.

Thank you for the report and test case.