Bug #120710 Inconsistent result when IFNULL applied to DECIMAL-type column

• Submitted: 17 Jun 5:46
• Modified: 17 Jun 11:17
• Reporter: Thomas Morgan
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.7.0
• OS: Any
• CPU Architecture: Any

[17 Jun 5:46] Thomas Morgan

Description:
Consider a table `t` with one column `c` and two rows, with values NULL and 0. 

`SELECT c FROM t WHERE c IS NULL;` correctly returns one row (the NULL row). Changing `WHERE c IS NULL` to `WHERE (IFNULL(NULL, c)) IS NULL` should have no impact, as `(IFNULL(NULL, c))` is logically equivalent to `c`. However, the change incorrectly causes two rows to be returned from the query (NULL and 0).

This bug was not present in MySQL 8.4.

How to repeat:
MySQL Server version: 9.7.0.

Driver version: mysql-connector-j 9.7.0.

Execute the following statements:

DROP DATABASE IF EXISTS db;
CREATE DATABASE db;
USE db;
CREATE TABLE t(c DECIMAL UNIQUE);
INSERT INTO t VALUES(0);
INSERT INTO t VALUES(NULL);
-- Query 1 correctly returns 1 row (NULL)
SELECT c FROM t WHERE c IS NULL;
-- Query 2 returns 2 rows (0, NULL), even though logically equivalent to query 1
SELECT c FROM t WHERE (IFNULL(NULL, c)) IS NULL;

[17 Jun 11:17] Roy Lyseng

Thank you for the bug report.
Verified as a duplicate of bug#120429.

[17 Jun 14:24] Jean-François Gagné

Bug#120711 is also a duplicate submission of this bug.