Description:
In MySQL 9.6.0, a WHERE clause using NULLIF(...) IS NULL may return rows even when NULLIF evaluates to a non-NULL value. The same expression in a SELECT evaluates correctly (0 for IS NULL). MariaDB 12.3.0 returns no rows, which is consistent with expected SQL semantics.
CREATE TABLE t1(c0 INT);
INSERT INTO t1 VALUES (1);
-- NULLIF returns -1 (non-NULL)
SELECT NULLIF(-1, c0) FROM t1;
-- Result: -1
SELECT NULLIF(-1, c0) IS NULL FROM t1;
-- Result: 0
-- Expected: no rows
SELECT * FROM t1 WHERE NULLIF(-1, c0) IS NULL;
-- MySQL 9.6.0: returns 1 row (wrong)
-- MariaDB 12.3.0: returns 0 rows (correct)
How to repeat:
CREATE TABLE t0(c0 INT, c1 VARCHAR(500), PRIMARY KEY(c0));
INSERT INTO t0 VALUES (1, NULL), (2, ''), (3, 'x');
SELECT c0, NULLIF(-1641423514, c1) AS p,
(NULLIF(-1641423514, c1) IS NULL) AS p_is_null
FROM t0;
-- All rows: p = -1641423514, p_is_null = 0
SELECT COUNT(*) FROM t0 WHERE NULLIF(-1641423514, c1) IS NULL;
-- MySQL 9.6.0: 3
-- MariaDB 12.3.0: 0
Description: In MySQL 9.6.0, a WHERE clause using NULLIF(...) IS NULL may return rows even when NULLIF evaluates to a non-NULL value. The same expression in a SELECT evaluates correctly (0 for IS NULL). MariaDB 12.3.0 returns no rows, which is consistent with expected SQL semantics. CREATE TABLE t1(c0 INT); INSERT INTO t1 VALUES (1); -- NULLIF returns -1 (non-NULL) SELECT NULLIF(-1, c0) FROM t1; -- Result: -1 SELECT NULLIF(-1, c0) IS NULL FROM t1; -- Result: 0 -- Expected: no rows SELECT * FROM t1 WHERE NULLIF(-1, c0) IS NULL; -- MySQL 9.6.0: returns 1 row (wrong) -- MariaDB 12.3.0: returns 0 rows (correct) How to repeat: CREATE TABLE t0(c0 INT, c1 VARCHAR(500), PRIMARY KEY(c0)); INSERT INTO t0 VALUES (1, NULL), (2, ''), (3, 'x'); SELECT c0, NULLIF(-1641423514, c1) AS p, (NULLIF(-1641423514, c1) IS NULL) AS p_is_null FROM t0; -- All rows: p = -1641423514, p_is_null = 0 SELECT COUNT(*) FROM t0 WHERE NULLIF(-1641423514, c1) IS NULL; -- MySQL 9.6.0: 3 -- MariaDB 12.3.0: 0