Description:
When a DECIMAL column has a corresponding BIGINT generated column defined as IFNULL(decimal_col, 0) with an index, the MySQL optimizer incorrectly matches the WHERE clause IFNULL(decimal_col, 0) = 0 to the BIGINT generated column index. Because BIGINT truncates fractional DECIMAL values (e.g., CAST(0.12 AS BIGINT) = 0), rows where decimal_col = 0.12 are falsely included in the result set.
The correct evaluation in DECIMAL type would be:
IFNULL(0.12, 0) = 0.12 != 0 (no match)
But the BIGINT generated column index evaluates:
BIGINT(IFNULL(0.12, 0)) = BIGINT(0.12) = 0 = 0 (false match!)
Conditions that trigger the bug:
(a) A DECIMAL column is used in IFNULL(col, 0) = 0
(b) A BIGINT generated column is defined as IFNULL(col, 0)
(c) An index exists on the BIGINT generated column
(d) The DECIMAL column contains fractional values between 0 and 1
(e.g., 0.12, 0.55) that truncate to BIGINT 0
The bug is NOT triggered when:
- The generated column type is DECIMAL (same as base column type)
- There is no index on the BIGINT generated column
- The data has no fractional values between 0 and 1
How to repeat:
DROP DATABASE IF EXISTS db_test;
CREATE DATABASE db_test;
USE db_test;
CREATE TABLE t1 (
c1 DECIMAL(10,2) NOT NULL,
g1 BIGINT GENERATED ALWAYS AS (IFNULL(c1,0)) STORED,
KEY kg1 (g1)
) ENGINE=InnoDB;
INSERT INTO t1 (c1) VALUES (0.00),(0.12);
ANALYZE TABLE t1;
-- WRONG: returns 2 (optimizer uses BIGINT gen col index, BIGINT(0.12)=0)
SELECT 'NO_FORCE (wrong=2)' as test, COUNT(*) as cnt FROM t1 WHERE (IFNULL(c1, 0)) = 0;
-- CORRECT: returns 1 (table scan, DECIMAL(0.12) != 0)
SELECT 'IGNORE_GEN_IDX (correct=1)' as test, COUNT(*) as cnt FROM t1 IGNORE INDEX(kg1) WHERE (IFNULL(c1, 0)) = 0;
Using index:
+--------------------+-----+
| test | cnt |
+--------------------+-----+
| NO_FORCE (wrong=2) | 2 | <- WRONG! Extra 1 row
+--------------------+-----+
1 row in set (0.001 sec)
Using table scan:
+----------------------------+-----+
| test | cnt |
+----------------------------+-----+
| IGNORE_GEN_IDX (correct=1) | 1 | <- CORRECT!
+----------------------------+-----+
1 row in set (0.001 sec)
Suggested fix:
The optimizer should not rewrite IFNULL(decimal_col, 0) = 0 to use a BIGINT generated column index when the source expression involves DECIMAL type. The type coercion from DECIMAL to BIGINT is lossy (truncates fractional part) and changes the semantics of the comparison.
Possible approaches:
(1) When matching an expression to a generated column index, verify that
the generated column type is compatible with the expression type.
If the generated column type involves a lossy conversion (DECIMAL ->
BIGINT), do not use that index for the expression.
(2) Add a post-filter after the index lookup to re-evaluate the original
expression against the actual column values, not the truncated
generated column values.
Description: When a DECIMAL column has a corresponding BIGINT generated column defined as IFNULL(decimal_col, 0) with an index, the MySQL optimizer incorrectly matches the WHERE clause IFNULL(decimal_col, 0) = 0 to the BIGINT generated column index. Because BIGINT truncates fractional DECIMAL values (e.g., CAST(0.12 AS BIGINT) = 0), rows where decimal_col = 0.12 are falsely included in the result set. The correct evaluation in DECIMAL type would be: IFNULL(0.12, 0) = 0.12 != 0 (no match) But the BIGINT generated column index evaluates: BIGINT(IFNULL(0.12, 0)) = BIGINT(0.12) = 0 = 0 (false match!) Conditions that trigger the bug: (a) A DECIMAL column is used in IFNULL(col, 0) = 0 (b) A BIGINT generated column is defined as IFNULL(col, 0) (c) An index exists on the BIGINT generated column (d) The DECIMAL column contains fractional values between 0 and 1 (e.g., 0.12, 0.55) that truncate to BIGINT 0 The bug is NOT triggered when: - The generated column type is DECIMAL (same as base column type) - There is no index on the BIGINT generated column - The data has no fractional values between 0 and 1 How to repeat: DROP DATABASE IF EXISTS db_test; CREATE DATABASE db_test; USE db_test; CREATE TABLE t1 ( c1 DECIMAL(10,2) NOT NULL, g1 BIGINT GENERATED ALWAYS AS (IFNULL(c1,0)) STORED, KEY kg1 (g1) ) ENGINE=InnoDB; INSERT INTO t1 (c1) VALUES (0.00),(0.12); ANALYZE TABLE t1; -- WRONG: returns 2 (optimizer uses BIGINT gen col index, BIGINT(0.12)=0) SELECT 'NO_FORCE (wrong=2)' as test, COUNT(*) as cnt FROM t1 WHERE (IFNULL(c1, 0)) = 0; -- CORRECT: returns 1 (table scan, DECIMAL(0.12) != 0) SELECT 'IGNORE_GEN_IDX (correct=1)' as test, COUNT(*) as cnt FROM t1 IGNORE INDEX(kg1) WHERE (IFNULL(c1, 0)) = 0; Using index: +--------------------+-----+ | test | cnt | +--------------------+-----+ | NO_FORCE (wrong=2) | 2 | <- WRONG! Extra 1 row +--------------------+-----+ 1 row in set (0.001 sec) Using table scan: +----------------------------+-----+ | test | cnt | +----------------------------+-----+ | IGNORE_GEN_IDX (correct=1) | 1 | <- CORRECT! +----------------------------+-----+ 1 row in set (0.001 sec) Suggested fix: The optimizer should not rewrite IFNULL(decimal_col, 0) = 0 to use a BIGINT generated column index when the source expression involves DECIMAL type. The type coercion from DECIMAL to BIGINT is lossy (truncates fractional part) and changes the semantics of the comparison. Possible approaches: (1) When matching an expression to a generated column index, verify that the generated column type is compatible with the expression type. If the generated column type involves a lossy conversion (DECIMAL -> BIGINT), do not use that index for the expression. (2) Add a post-filter after the index lookup to re-evaluate the original expression against the actual column values, not the truncated generated column values.