Description:
This is a follow‑up on a previously closed issue (https://bugs.mysql.com/bug.php?id=120801&thankws=4). You mentioned that the specific case had been fixed in MySQL 9.7, but the same root cause is still reproducible in the following two scenarios:
IF(NULL, …, double) IN (bigint_column)
GREATEST(string, double) IN (bigint_column) after splitting
When the optimizer performs an index lookup on a BIGINT column with an index, it implicitly converts the DOUBLE constant on the right side to BIGINT (truncating toward zero), thereby incorrectly matching rows where the column value is 0.
In paths without an index (e.g., a full table scan), the comparison uses exact numeric semantics and does not truncate, so the result is correct.
Actual Results and Status
Case Query Actual Result Status
1 Single table (src with index) 2 rows Incorrect
1 Split (joined no index) 0 rows Correct
2 Single table (src with constant folding) 0 rows Correct
2 Split (scalar subquery rewrite) 1 row Incorrect
EXPLAIN Analysis
Case 1 (single table with index):
-> Aggregate: count(0)
-> Covering index lookup on src using i_src (c0 = if(NULL,0.353,0.433))
The lookup key is IF(NULL, 0.353, 0.433) which evaluates to 0.433... (DOUBLE). The optimizer casts it to BIGINT, truncating to 0, matching the two rows with c0=0.
Case 1 (split, no index):
-> Aggregate: count(0)
-> Filter: (<cache>(if(NULL,0.353,0.433)) = src.c0)
-> Table scan on src
Comparison uses numeric semantics; 0.433 ≠ 0, no matches.
Case 2 (single table, constant folding):
-> Zero input rows (Impossible WHERE noticed after reading const tables)
The optimizer evaluates GREATEST and realizes 0.191... does not equal the unique value 0, returns empty.
Case 2 (split, index lookup):
-> Aggregate: count(0)
-> Index lookup on src using <auto_key0> (c1 = greatest('-1112434914',0.191...))
The lookup key GREATEST(...) yields 0.191..., truncated to 0, matching the row with c1=0.
How to repeat:
Case 1: IF(NULL, …, double) IN (bigint_column)
DROP DATABASE IF EXISTS repro;
CREATE DATABASE repro;
USE repro;
CREATE TABLE src (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 BIGINT NULL
) ENGINE=InnoDB;
INSERT INTO src (c0) VALUES (0), (0), (1);
CREATE INDEX i_src ON src(c0, vp_rowid);
CREATE TABLE l (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 BIGINT NULL) ENGINE=InnoDB;
CREATE TABLE r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 BIGINT NULL) ENGINE=InnoDB;
INSERT INTO l (vp_rowid, c0) SELECT vp_rowid, c0 FROM src;
INSERT INTO r (vp_rowid, c0) SELECT vp_rowid, c0 FROM src;
DROP TEMPORARY TABLE IF EXISTS joined;
CREATE TEMPORARY TABLE joined AS
SELECT l.vp_rowid, l.c0 FROM l JOIN r ON l.vp_rowid = r.vp_rowid;
-- Single table query (has index, incorrect)
SELECT 'single' AS query_type, COUNT(*) AS row_count
FROM src
WHERE (IF(NULL, 0.3530399531809283, 0.43307147808600643)) IN (c0);
-- Split query (temp table no index, correct)
SELECT 'split' AS query_type, COUNT(*) AS row_count
FROM joined AS src
WHERE (IF(NULL, 0.3530399531809283, 0.43307147808600643)) IN (c0);
Case 2: GREATEST(string, double) IN (bigint_column) after splitting
DROP DATABASE IF EXISTS repro;
CREATE DATABASE repro;
USE repro;
CREATE TABLE src (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c1 BIGINT NULL
) ENGINE=InnoDB;
INSERT INTO src (c1) VALUES (0);
ALTER TABLE src ADD UNIQUE KEY i2 (c1);
CREATE TABLE r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c1 BIGINT NULL) ENGINE=InnoDB;
INSERT INTO r SELECT vp_rowid, c1 FROM src;
-- Single table query (optimizer sees Impossible WHERE, correct: 0 rows)
SELECT 'single' AS query_type, COUNT(*) AS row_count
FROM src
WHERE (GREATEST("-1112434914", 0.1914106003702002)) IN (c1);
-- Split query (scalar subquery hides the column, index lookup truncates, incorrect: 1 row)
SELECT 'split' AS query_type, COUNT(*) AS row_count
FROM (
SELECT vp_rowid,
(SELECT r.c1 FROM r WHERE r.vp_rowid = src.vp_rowid) AS c1
FROM src
WHERE vp_rowid IN (SELECT r.vp_rowid FROM r)
) AS src
WHERE (GREATEST("-1112434914", 0.1914106003702002)) IN (c1);
