Bug #120588 IFNULL(NULL, decimal_column) is not equivalent to the column inside a joined WHERE clause
Submitted: 1 Jun 14:08 Modified: 1 Jun 19:51
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

[1 Jun 14:08] y x 
Description:
`IFNULL(NULL, t0.c1)` should be exactly `t0.c1`. With rows `t0.c1 = NULL` and `t0.c1 = -1` plus a joined row `t2.c0 = 0`, the predicate `t2.c0 > IFNULL(NULL, t0.c1)` should therefore be equivalent to `t2.c0 > t0.c1`, and must keep `-1`. MySQL instead returns an empty result set.

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

INSERT INTO t0 VALUES (NULL), (-1);
INSERT INTO t2 VALUES (0);

SELECT t0.c1 AS ref0
FROM t0
JOIN t2 ON TRUE
WHERE t2.c0 > IFNULL(NULL, t0.c1); -- Expected correct result: -1  -- actual Wrong result: <empty>
[1 Jun 19:51] Roy Lyseng 
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.