Bug #120591 IFNULL(empty scalar subquery, column) breaks an obvious comparison and filters out a row
Submitted: 1 Jun 14:14 Modified: 1 Jun 19:53
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:14] y x 
Description:
`SELECT NULL WHERE FALSE` is always empty, so `IFNULL((SELECT NULL WHERE FALSE), c0)` should be exactly `c0`. Therefore the row `(c0, c1) = (0, 1)` must satisfy `c1 > IFNULL((SELECT NULL WHERE FALSE), c0)`. MySQL instead returns no rows.

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

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

SELECT t0.c1 AS ref0
FROM t0
WHERE t0.c1 > IFNULL((SELECT NULL WHERE FALSE), t0.c0); -- Expected correct result: 1  -- actual Wrong result: <empty>
[1 Jun 14:14] y x 
version 9.7.0
[1 Jun 19:53] 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.