Bug #120171 Wrong result: LEFT JOIN empty derived table + WHERE equality uses literal from SELECT list

• Submitted: 28 Mar 8:58
• Modified: 29 Mar 20:05
• Reporter: mu mu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.45
• OS: Ubuntu (22.04)
• CPU Architecture: Any

[28 Mar 8:58] mu mu

Description:
Hi, MySQL developers.

Please see the test case below.

When the derived table (SELECT ... FROM D) returns no rows, LEFT JOIN must leave the inner side as NULL. The predicate WHERE B.C = I.G is then 2 = NULL, which is UNKNOWN in SQL and must not pass the WHERE filter. The result should be an empty set

How to repeat:
CREATE TABLE B (C INT);
INSERT INTO B VALUES (2);
CREATE TABLE D (E INT);
-- D has no rows; derived table produces 0 rows.

-- Wrong: returns 1 row (should be empty)
SELECT 1 AS r
FROM B
LEFT JOIN (SELECT 2 AS G FROM D) I ON TRUE
WHERE B.C = I.G;

-- Expected behavior (same data, 0 rows): 
SELECT 1 AS r
FROM B
LEFT JOIN (SELECT 2 AS G FROM D) I ON TRUE
WHERE (B.C = I.G) IS TRUE;

[29 Mar 20:05] Roy Lyseng

Thank you for the bug report.
Verified as described.