Description:
mysql> CREATE TABLE t (id INT PRIMARY KEY, i INT, KEY k (i));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,0),(7,-2);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT id FROM t FORCE INDEX (k) WHERE i = CAST(1.5 AS DOUBLE); -- [2] ← returns the i=2 row!
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)
mysql> SELECT id FROM t IGNORE INDEX (k) WHERE i = CAST(1.5 AS DOUBLE); -- [] ✓ correct (1.5 ≠ any integer)
Empty set (0.00 sec)
Full shape matrix (index access returns the rounded row in all cases): CAST(1.5 AS DOUBLE/FLOAT/DECIMAL) (1.5→2, 0.5→0, −1.5→−2, 1.4→1, 1.6→2), CAST('1.5' AS DOUBLE), '1.5'+0, COALESCE(CAST(1.5 AS DOUBLE),0), ABS(-CAST(1.5 AS DOUBLE)), CAST(1.5 AS DOUBLE)+0. Constant-folding path is correct: 1.5, 1.5e0, 1.5+0, IFNULL(1.5,0), CAST('2.5' AS DECIMAL).
Dividing line: runtime-evaluated REAL-type expressions (not constant-folded) are rounded when stored as int keys, and the predicate is absorbed by ref access.
Impact boundary: SELECT-with-index only — DELETE/UPDATE with the same predicate do not mis-operate (full scan, no ref absorption); JOIN ON is immune; BIGINT / INT UNSIGNED columns fail the same way; DECIMAL/DOUBLE/string/TIME/DATETIME columns are correct (integer column × REAL expression only).
Root cause attribution: f60d845928f (Bug#36448705, "Wrong results for ref access with decimal-to-int truncation") added save_in_field_check_truncation() round-trip checking for DECIMAL_RESULT — the literal path is fixed (i = 1.5 correct) — but CAST expressions (Item_typecast_ path) bypass the check*: ref access absorbs the predicate and the index returns rounded (not truncated) wrong rows.
How to repeat:
CREATE TABLE t (id INT PRIMARY KEY, i INT, KEY k (i));
INSERT INTO t VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,0),(7,-2);
SELECT id FROM t FORCE INDEX (k) WHERE i = CAST(1.5 AS DOUBLE); -- [2] ← returns the i=2 row!
SELECT id FROM t IGNORE INDEX (k) WHERE i = CAST(1.5 AS DOUBLE); -- [] ✓ correct (1.5 ≠ any integer)
-- EXPLAIN: "Covering index range scan on t using k over (i = 2)" ← key rounded to 2
Description: mysql> CREATE TABLE t (id INT PRIMARY KEY, i INT, KEY k (i)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,0),(7,-2); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT id FROM t FORCE INDEX (k) WHERE i = CAST(1.5 AS DOUBLE); -- [2] ← returns the i=2 row! +----+ | id | +----+ | 2 | +----+ 1 row in set (0.00 sec) mysql> SELECT id FROM t IGNORE INDEX (k) WHERE i = CAST(1.5 AS DOUBLE); -- [] ✓ correct (1.5 ≠ any integer) Empty set (0.00 sec) Full shape matrix (index access returns the rounded row in all cases): CAST(1.5 AS DOUBLE/FLOAT/DECIMAL) (1.5→2, 0.5→0, −1.5→−2, 1.4→1, 1.6→2), CAST('1.5' AS DOUBLE), '1.5'+0, COALESCE(CAST(1.5 AS DOUBLE),0), ABS(-CAST(1.5 AS DOUBLE)), CAST(1.5 AS DOUBLE)+0. Constant-folding path is correct: 1.5, 1.5e0, 1.5+0, IFNULL(1.5,0), CAST('2.5' AS DECIMAL). Dividing line: runtime-evaluated REAL-type expressions (not constant-folded) are rounded when stored as int keys, and the predicate is absorbed by ref access. Impact boundary: SELECT-with-index only — DELETE/UPDATE with the same predicate do not mis-operate (full scan, no ref absorption); JOIN ON is immune; BIGINT / INT UNSIGNED columns fail the same way; DECIMAL/DOUBLE/string/TIME/DATETIME columns are correct (integer column × REAL expression only). Root cause attribution: f60d845928f (Bug#36448705, "Wrong results for ref access with decimal-to-int truncation") added save_in_field_check_truncation() round-trip checking for DECIMAL_RESULT — the literal path is fixed (i = 1.5 correct) — but CAST expressions (Item_typecast_ path) bypass the check*: ref access absorbs the predicate and the index returns rounded (not truncated) wrong rows. How to repeat: CREATE TABLE t (id INT PRIMARY KEY, i INT, KEY k (i)); INSERT INTO t VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,0),(7,-2); SELECT id FROM t FORCE INDEX (k) WHERE i = CAST(1.5 AS DOUBLE); -- [2] ← returns the i=2 row! SELECT id FROM t IGNORE INDEX (k) WHERE i = CAST(1.5 AS DOUBLE); -- [] ✓ correct (1.5 ≠ any integer) -- EXPLAIN: "Covering index range scan on t using k over (i = 2)" ← key rounded to 2