Description:
When a WHERE condition uses LEAST(0.8543270419078102, '2F') IN (bigint_column), and the optimizer converts IN to an equality condition to use an index lookup, it calls the LEAST expression's val_int() method to produce a key of the index column's type (BIGINT).
During this integer‑context evaluation:
The floating‑point 0.854... is rounded to 1;
The string '2F' is truncated to 2 (leading digits only);
LEAST(1, 2) returns 1.
Thus, the index lookup becomes c0 = 1, incorrectly matching the row with c0=1.
In a multi‑table join path, the LEAST expression is cached (<cache>) and compared as a DOUBLE, correctly returning no matches.
Actual Results and Status
Query Actual Result Status
Single‑table (index lookup) 1 row (NULL) Incorrect (should be empty)
Derived table (Hash Join) Empty set Correct
EXPLAIN Analysis
Single‑table query (incorrect):
-> Group (no aggregates)
-> Covering index lookup on vp_s using src_idx (c0 = least(0.8543270419078102,'2F'))
The optimizer converts IN to c0 = LEAST(...). The lookup key is the integer‑context result of LEAST (1).
Derived‑table query (correct):
-> Group (no aggregates)
-> Nested loop inner join
-> Inner hash join (concat('vp:',l.vp_rowid) = concat('vp:',vp_r.vp_rowid))
-> Covering index scan on vp_r using PRIMARY
-> Hash
-> Filter: (<cache>(least(0.854...,'2F')) = l.c0)
-> Covering index scan on l using l_idx
How to repeat:
DROP DATABASE IF EXISTS repro_mysql714_db4;
CREATE DATABASE repro_mysql714_db4;
USE repro_mysql714_db4;
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 = 1;
CREATE TABLE vp_s (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 BIGINT NULL
) ENGINE = InnoDB;
INSERT INTO vp_s (c0) VALUES (-903494292), (1);
CREATE INDEX src_idx ON vp_s(c0, vp_rowid);
CREATE TABLE vp_l (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 BIGINT NULL) ENGINE = InnoDB;
CREATE TABLE vp_r (vp_rowid BIGINT NOT NULL PRIMARY KEY) ENGINE = InnoDB;
INSERT INTO vp_l SELECT vp_rowid, c0 FROM vp_s;
INSERT INTO vp_r SELECT vp_rowid FROM vp_s;
CREATE INDEX l_idx ON vp_l(vp_rowid, c0);
-- Single‑table query (incorrect): returns 1 row
SELECT DISTINCT NULL AS ref0 FROM vp_s
WHERE (LEAST(0.8543270419078102, '2F')) IN (vp_s.c0)
GROUP BY NULL;
-- Derived‑table query (correct): returns empty set
SELECT DISTINCT NULL AS ref0 FROM (
SELECT l.vp_rowid, l.c0
FROM vp_l l
JOIN (SELECT vp_rowid, CONCAT('vp:', vp_rowid) AS payload FROM vp_r) sp
ON CONCAT('vp:', l.vp_rowid) = sp.payload
JOIN vp_r r ON r.vp_rowid = sp.vp_rowid
) vp_source
WHERE (LEAST(0.8543270419078102, '2F')) IN (vp_source.c0)
GROUP BY NULL;
Description: When a WHERE condition uses LEAST(0.8543270419078102, '2F') IN (bigint_column), and the optimizer converts IN to an equality condition to use an index lookup, it calls the LEAST expression's val_int() method to produce a key of the index column's type (BIGINT). During this integer‑context evaluation: The floating‑point 0.854... is rounded to 1; The string '2F' is truncated to 2 (leading digits only); LEAST(1, 2) returns 1. Thus, the index lookup becomes c0 = 1, incorrectly matching the row with c0=1. In a multi‑table join path, the LEAST expression is cached (<cache>) and compared as a DOUBLE, correctly returning no matches. Actual Results and Status Query Actual Result Status Single‑table (index lookup) 1 row (NULL) Incorrect (should be empty) Derived table (Hash Join) Empty set Correct EXPLAIN Analysis Single‑table query (incorrect): -> Group (no aggregates) -> Covering index lookup on vp_s using src_idx (c0 = least(0.8543270419078102,'2F')) The optimizer converts IN to c0 = LEAST(...). The lookup key is the integer‑context result of LEAST (1). Derived‑table query (correct): -> Group (no aggregates) -> Nested loop inner join -> Inner hash join (concat('vp:',l.vp_rowid) = concat('vp:',vp_r.vp_rowid)) -> Covering index scan on vp_r using PRIMARY -> Hash -> Filter: (<cache>(least(0.854...,'2F')) = l.c0) -> Covering index scan on l using l_idx How to repeat: DROP DATABASE IF EXISTS repro_mysql714_db4; CREATE DATABASE repro_mysql714_db4; USE repro_mysql714_db4; 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 = 1; CREATE TABLE vp_s ( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 BIGINT NULL ) ENGINE = InnoDB; INSERT INTO vp_s (c0) VALUES (-903494292), (1); CREATE INDEX src_idx ON vp_s(c0, vp_rowid); CREATE TABLE vp_l (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 BIGINT NULL) ENGINE = InnoDB; CREATE TABLE vp_r (vp_rowid BIGINT NOT NULL PRIMARY KEY) ENGINE = InnoDB; INSERT INTO vp_l SELECT vp_rowid, c0 FROM vp_s; INSERT INTO vp_r SELECT vp_rowid FROM vp_s; CREATE INDEX l_idx ON vp_l(vp_rowid, c0); -- Single‑table query (incorrect): returns 1 row SELECT DISTINCT NULL AS ref0 FROM vp_s WHERE (LEAST(0.8543270419078102, '2F')) IN (vp_s.c0) GROUP BY NULL; -- Derived‑table query (correct): returns empty set SELECT DISTINCT NULL AS ref0 FROM ( SELECT l.vp_rowid, l.c0 FROM vp_l l JOIN (SELECT vp_rowid, CONCAT('vp:', vp_rowid) AS payload FROM vp_r) sp ON CONCAT('vp:', l.vp_rowid) = sp.payload JOIN vp_r r ON r.vp_rowid = sp.vp_rowid ) vp_source WHERE (LEAST(0.8543270419078102, '2F')) IN (vp_source.c0) GROUP BY NULL;