Details
Major Description
Problem Description
I have identified a critical data inconsistency issue where the query results become incorrect after inserting invalid string values into a FLOAT ZEROFILL column with an index. The issue is specifically observable when the optimizer performs an Index-Only Scan.
The following MRE demonstrates the transition from a correct state (S1) to a corrupted state (S2).
Step-by-Step Reproduction
|
|
-- [Setup]
|
DROP TABLE IF EXISTS mysql_bug_mre; |
CREATE TABLE mysql_bug_mre ( |
c0 FLOAT ZEROFILL NULL |
) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; |
CREATE INDEX i_c0 ON mysql_bug_mre (c0); |
|
|
-- [Phase S1: Initial state with mixed data]
|
INSERT IGNORE INTO mysql_bug_mre (c0) VALUES (0.0), ("x"), (NULL); |
|
|
-- S1 Query: Result is correct
|
SELECT MAX(c0) AS s1_max FROM mysql_bug_mre WHERE c0 = 0.0; |
-- Expected & Observed: 0.0
|
|
|
-- [Phase S2: Insert more invalid strings to trigger the bug]
|
START TRANSACTION; |
INSERT IGNORE INTO mysql_bug_mre (c0) VALUES |
(0.0), (0.0), (0.0), (""), ("x"), ("-0x"), (NULL), (NULL); |
COMMIT; |
ANALYZE TABLE mysql_bug_mre; |
|
|
-- S2 Query 1: Aggregate inconsistency
|
SELECT MAX(c0) AS s2_max FROM mysql_bug_mre WHERE c0 = 0.0; |
-- Observed: -0.0 (Incorrect, should be 0.0)
|
|
|
-- S2 Query 2: Result set inconsistency
|
-- The WHERE clause filters for 0.0, but the result contains NULL
|
SELECT c0 FROM mysql_bug_mre WHERE c0 = 0.0 ORDER BY c0; |
/* Observed output:
|
+------+
|
| c0 |
|
+------+
|
| ... |
|
| 0.0 |
|
| NULL | <-- This should not be here
|
+------+
|
*/
|
|
|
-- S2 Query 3: Index-Only Scan vs Table Scan
|
SELECT MAX(c0) FROM mysql_bug_mre FORCE INDEX (i_c0) WHERE c0 = 0.0; |
-- Returns: -0.0 (Wrong)
|
SELECT MAX(c0) FROM mysql_bug_mre IGNORE INDEX (i_c0) WHERE c0 = 0.0; |
-- Returns: 0.0 (Correct) |
Key Findings
In S1, the system behaves correctly.
In S2, after inserting strings like "-0x" (which triggers a warning and converts to 0), the index i_c0 seems to store or interpret the value as -0.0 or fails to distinguish it from NULL during an index-only scan.
The EXPLAIN plan shows Using where; Using index, confirming that the error occurs when the engine relies solely on the index tree.
Expected Behavior
MAX(c0) should consistently return 0.0 regardless of index usage.
SELECT ... WHERE c0 = 0.0 should never include NULL values in the result set.