Description:
When a WHERE condition uses IN (IFNULL(0.7957106135922298, 'nK')) and this condition is pushed down as an index lookup key, the optimizer must convert the constant value to the index column type (BIGINT).
Because the return type of IFNULL is inferred as VARCHAR (due to the string second argument), the actual returned value is the string '0.7957106135922298'. During conversion to BIGINT, the internal implementation incorrectly produces the integer 1 instead of the correct 0, causing the index lookup to match the row with c0=1, returning an unexpected result.
In the single‑table query, the optimizer performs constant evaluation and directly marks the condition as Impossible WHERE, returning an empty set, thus avoiding the bug.
This is a wrong‑result bug caused by improper type conversion of a mixed‑type IFNULL expression during index lookup key generation.
Actual Results and Status
Query Actual Result Status
Single‑table (constant evaluation) Empty set Correct
Derived table (index lookup) 1 Incorrect
EXPLAIN Analysis
Single‑table query (correct):
-> Zero rows (Impossible WHERE noticed after reading const tables)
Derived‑table query (incorrect):
-> Index lookup on vp_s using <auto_key0> (c0 = ifnull(0.7957106135922298,'nK'))
-> Materialize
-> Nested loop inner join
-> Covering index scan on l using PRIMARY
-> Single-row covering index lookup on r using PRIMARY
-> Select #4 (subquery in projection; dependent)
-> Single-row index lookup on r using PRIMARY
How to repeat:
DROP DATABASE IF EXISTS repro_mysql714_db54;
CREATE DATABASE repro_mysql714_db54;
USE repro_mysql714_db54;
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
SET SESSION big_tables = 0;
CREATE TABLE vp_s (vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 BIGINT NULL) ENGINE = InnoDB;
INSERT INTO vp_s (c0) VALUES (NULL), (1);
ALTER TABLE vp_s ADD UNIQUE KEY i0 (c0);
CREATE TABLE vp_l (vp_rowid BIGINT NOT NULL PRIMARY KEY) ENGINE = InnoDB;
CREATE TABLE vp_r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 BIGINT NULL) ENGINE = InnoDB;
INSERT INTO vp_l SELECT vp_rowid FROM vp_s;
INSERT INTO vp_r SELECT vp_rowid, c0 FROM vp_s;
-- Single‑table query (correct): returns empty set
SELECT EXISTS (SELECT 1) AS ref0 FROM vp_s
WHERE (vp_s.c0) IN (IFNULL(0.7957106135922298, 'nK'));
-- Derived‑table query (incorrect): returns 1
SELECT EXISTS (SELECT 1) AS ref0 FROM (
SELECT l.vp_rowid,
(SELECT r.c0 FROM vp_r r WHERE r.vp_rowid = l.vp_rowid) AS c0
FROM vp_l l
WHERE EXISTS (SELECT 1 FROM vp_r r WHERE r.vp_rowid = l.vp_rowid)
) vp_s
WHERE (vp_s.c0) IN (IFNULL(0.7957106135922298, 'nK'));
Description: When a WHERE condition uses IN (IFNULL(0.7957106135922298, 'nK')) and this condition is pushed down as an index lookup key, the optimizer must convert the constant value to the index column type (BIGINT). Because the return type of IFNULL is inferred as VARCHAR (due to the string second argument), the actual returned value is the string '0.7957106135922298'. During conversion to BIGINT, the internal implementation incorrectly produces the integer 1 instead of the correct 0, causing the index lookup to match the row with c0=1, returning an unexpected result. In the single‑table query, the optimizer performs constant evaluation and directly marks the condition as Impossible WHERE, returning an empty set, thus avoiding the bug. This is a wrong‑result bug caused by improper type conversion of a mixed‑type IFNULL expression during index lookup key generation. Actual Results and Status Query Actual Result Status Single‑table (constant evaluation) Empty set Correct Derived table (index lookup) 1 Incorrect EXPLAIN Analysis Single‑table query (correct): -> Zero rows (Impossible WHERE noticed after reading const tables) Derived‑table query (incorrect): -> Index lookup on vp_s using <auto_key0> (c0 = ifnull(0.7957106135922298,'nK')) -> Materialize -> Nested loop inner join -> Covering index scan on l using PRIMARY -> Single-row covering index lookup on r using PRIMARY -> Select #4 (subquery in projection; dependent) -> Single-row index lookup on r using PRIMARY How to repeat: DROP DATABASE IF EXISTS repro_mysql714_db54; CREATE DATABASE repro_mysql714_db54; USE repro_mysql714_db54; SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; SET SESSION big_tables = 0; CREATE TABLE vp_s (vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 BIGINT NULL) ENGINE = InnoDB; INSERT INTO vp_s (c0) VALUES (NULL), (1); ALTER TABLE vp_s ADD UNIQUE KEY i0 (c0); CREATE TABLE vp_l (vp_rowid BIGINT NOT NULL PRIMARY KEY) ENGINE = InnoDB; CREATE TABLE vp_r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 BIGINT NULL) ENGINE = InnoDB; INSERT INTO vp_l SELECT vp_rowid FROM vp_s; INSERT INTO vp_r SELECT vp_rowid, c0 FROM vp_s; -- Single‑table query (correct): returns empty set SELECT EXISTS (SELECT 1) AS ref0 FROM vp_s WHERE (vp_s.c0) IN (IFNULL(0.7957106135922298, 'nK')); -- Derived‑table query (incorrect): returns 1 SELECT EXISTS (SELECT 1) AS ref0 FROM ( SELECT l.vp_rowid, (SELECT r.c0 FROM vp_r r WHERE r.vp_rowid = l.vp_rowid) AS c0 FROM vp_l l WHERE EXISTS (SELECT 1 FROM vp_r r WHERE r.vp_rowid = l.vp_rowid) ) vp_s WHERE (vp_s.c0) IN (IFNULL(0.7957106135922298, 'nK'));