Bug #119117 INNER JOIN returns results when join condition evaluates to false

• Submitted: 9 Oct 4:15
• Reporter: Ye Shiyang
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.4.6
• OS: Windows
• CPU Architecture: Other (x64)
• Tags: INNER JOIN, JOIN_CONDITION

[9 Oct 4:15] Ye Shiyang

Description:
The INNER JOIN returns results even when the join condition evaluates to false. This violates the fundamental principle that INNER JOIN should only return rows where the join condition is true.

Specific behavior:
The condition t1.c1 = (t1.c1 > - 0.5) evaluates to 0 (false) when tested independently
However, the same condition used in an INNER JOIN ON clause returns rows instead of an empty result set

How to repeat:
-- Create test tables
CREATE TABLE t0(c0 DECIMAL);
CREATE TABLE t1(c0 VARCHAR(500), c1 DECIMAL);

-- Insert test data
INSERT IGNORE INTO t1(c0,c1) VALUES('x',0);
REPLACE INTO t0(c0) VALUES(1);

-- Test the condition independently (returns 0)
SELECT t1.c1 = (t1.c1 > - 0.5) from t1;

-- Use the same condition in INNER JOIN (should return empty but doesn't)
SELECT t0.c0, t1.c0, t1.c1 FROM t0 INNER JOIN t1 ON t1.c1 = (t1.c1 > - 0.5);

Suggested fix:
The issue may be related to type conversion or expression evaluation order in JOIN contexts