Bug #119121 STRAIGHT_JOIN returns fewer rows than expected when ON condition is true for multiple rows

• Submitted: 9 Oct 2025 11:06
• Modified: 16 Dec 2025 9:26
• Reporter: Ye Shiyang
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.4.6
• OS: Windows
• CPU Architecture: Other (x64)
• Tags: JOIN_CONDITION, straight_join

[9 Oct 2025 11:06] Ye Shiyang

Description:
In MySQL 8.4.6, a STRAIGHT_JOIN query returns fewer rows than expected when the ON condition evaluates to true for multiple rows. The query should return 2 rows but only returns 1 row.

How to repeat:
-- Create test tables
CREATE TABLE t0(c0 DECIMAL);
CREATE TABLE t1 LIKE t0;

-- Insert test data
INSERT INTO t0(c0) VALUES(0);
INSERT INTO t1(c0) VALUES(0),(1887483144);

-- Execute query (should return 2 rows but returns 1)
SELECT DISTINCT t1.c0, t0.c0 FROM t1 STRAIGHT_JOIN t0 ON t1.c0 > -0.3;

[15 Dec 2025 16:34] Øystein Grøvlen

Verified as described

[16 Dec 2025 9:26] Knut Anders Hatlen

This bug seems to have been fixed in MySQL 9.0.0 as part of the fix for bug#114196. Closing as duplicate.