MySQL Bugs: #120532: An unused derived subquery triggers a DISTINCT/IFNULL wrong-result in the original path

Bug #120532	An unused derived subquery triggers a DISTINCT/IFNULL wrong-result in the original path
Submitted:	23 May 12:34	Modified:	26 May 7:51
Reporter:	y x	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	9.7.0	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86

Description:
`subq0.ref0` is never referenced by the outer query, so this should be equivalent to first materializing that subquery into a temp table and then reading from it. For outer row `t1.c0 = 0`, the `LEFT JOIN` contributes a null-extended row, hence `IFNULL(t2.c0, t1.c0)` should be `0`. MySQL instead returns `NULL`.

How to repeat:
CREATE TABLE t1(c0 DECIMAL(10,0));
CREATE TABLE t2 LIKE t1;

INSERT INTO t2 VALUES (0), (0);
INSERT INTO t1 VALUES (1), (NULL), (0);

SELECT DISTINCT IFNULL(t2.c0, t1.c0) AS ref0, t1.c0 AS ref1
FROM (SELECT 1 AS ref0 FROM t1 LEFT JOIN t2 ON t1.c0 > t2.c0) AS subq0,
     t1 LEFT JOIN t2 ON t1.c0 > t2.c0
ORDER BY 2, 1; -- Expected correct result: NULL | NULL ; 0 | 0 ; 0 | 1  -- actual Wrong result: NULL | NULL ; NULL | 0 ; 0 | 1

Thank you for the bug report.
Verified as described.