MySQL Bugs: #119127: INNER JOIN and INTERSECT of LEFT/RIGHT JOINs return different results when unique index is present

Bug #119127	INNER JOIN and INTERSECT of LEFT/RIGHT JOINs return different results when unique index is present
Submitted:	10 Oct 11:58
Reporter:	Ye Shiyang	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.4.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	INNER_JOIN, UNIQUE_INDEX

Description:
In MySQL 8.4.6, when a unique index is present, an INNER JOIN query and an INTERSECT of LEFT JOIN and RIGHT JOIN queries with identical conditions return different results. This violates the mathematical equivalence that should exist between these query patterns.

How to repeat:
-- Create test tables
CREATE TABLE t0(c0 TINYINT, c1 INT);
CREATE TABLE t1 LIKE t0;

-- Create unique index (required for bug reproduction)
CREATE UNIQUE INDEX i0 ON t0(c0, c1);

INSERT LOW_PRIORITY IGNORE INTO t1(c0) VALUES(1821057091);
INSERT DELAYED INTO t0(c0, c1) VALUES(0.8, 304092102);

-- First query (returns 1 row)
SELECT DISTINCT * FROM t1 INNER JOIN t0 ON (t0.c0) = (SIN(0.8));

-- Second query (returns empty, should match first query)
(SELECT DISTINCT * FROM t1 LEFT JOIN t0 ON (t0.c0) = (SIN(0.8))) 
INTERSECT 
(SELECT DISTINCT * FROM t1 RIGHT JOIN t0 ON (t0.c0) = (SIN(0.8)));

Suggested fix:
THE first query should return empty