Bug #119121 STRAIGHT_JOIN returns fewer rows than expected when ON condition is true for multiple rows
Submitted: 9 Oct 11:06
Reporter: Ye Shiyang Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.4.6 OS:Windows
Assigned to: CPU Architecture:Other (x64)
Tags: JOIN_CONDITION, straight_join

[9 Oct 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;