MySQL Bugs: #120590: SELECT DISTINCT drops IFNULL(stored NULL, derived non-NULL) and loses a non-NULL projection row

Bug #120590	SELECT DISTINCT drops IFNULL(stored NULL, derived non-NULL) and loses a non-NULL projection row
Submitted:	1 Jun 14:13	Modified:	1 Jun 19:52
Reporter:	y x	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	9.7.0	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86

Description:
`subq0` contributes both `ref0 = 0` and `ref0 = NULL`. For outer row `(t0.c1, t0.c0) = (NULL, NULL)`, `IFNULL(t0.c1, subq0.ref0)` should therefore produce both `0` and `NULL`. MySQL instead keeps only the `NULL` row and loses the `0` row.

How to repeat:
CREATE TABLE t0(c0 DECIMAL(10,0), c1 DECIMAL(10,0));
CREATE TABLE t2(c0 VARCHAR(1));

INSERT INTO t0(c1, c0) VALUES (1, 0), (NULL, NULL);
INSERT INTO t2 VALUES (''), ('');

SELECT DISTINCT t0.c1 AS ref0, t0.c0 AS ref1, IFNULL(t0.c1, subq0.ref0) AS ref2
FROM (SELECT t0.c0 AS ref0 FROM t0 INNER JOIN t2 ON t2.c0 <= 'b') AS subq0,
     t0
ORDER BY 1, 2, 3; -- Expected correct result: NULL | NULL | NULL ; NULL | NULL | 0 ; 1 | 0 | 1  -- actual Wrong result: NULL | NULL | NULL ; 1 | 0 | 1

Thank you for the bug report.

Verified as a duplicate of bug#120429.

A fix for this problem will be delivered in release 9.7.1.