MySQL Bugs: #120711: Inconsistent result when IFNULL applied to INTEGER-type column

Bug #120711	Inconsistent result when IFNULL applied to INTEGER-type column
Submitted:	17 Jun 6:15	Modified:	17 Jun 8:13
Reporter:	Thomas Morgan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	9.7.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Consider a table `t` with one column `c` and one row with value 1.

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

This bug appears to occur for all integer-type columns (`INTEGER`, `INT`, `SMALLINT`, `TINYINT`, `MEDIUMINT`, `BIGINT`) when an index has been created on the column.

How to repeat:
MySQL 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 INT);
CREATE INDEX b ON t(c);
INSERT INTO t VALUES(1);
-- Query 1, returns 0 rows
SELECT c FROM t WHERE c = 0.9;
-- Query 2, returns 1 row, even though logically equivalent to query 1
SELECT c FROM t WHERE c = IFNULL(NULL, 0.9);

Thank you for the bug report.
Verified as a duplicate of internal bug#36448705.
A fix will be delivered in the next 9.7 release.

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