Description:
This issue can be described using two temporal states of the same table.
In the baseline state S1, after creating the table and inserting a small amount of data, the query
------------------------------------
mysql> SELECT MAX(c0) AS s1_max
-> FROM mysql_bug_mre
-> WHERE c0 = 0.0;
+--------+
| s1_max |
+--------+
| 0 |
+--------+
1 row in set (0.001 sec)
------------------------------------
Then I transform S1 into S2 using only some INSERT statements, followed by ANALYZE TABLE, and execute the same query again. In S2, the query returns NULL.
------------------------------------
mysql> SELECT MAX(c0) AS s2_max
-> FROM mysql_bug_mre
-> WHERE c0 = 0.0;
+--------+
| s2_max |
+--------+
| NULL |
+--------+
1 row in set (0.001 sec)
------------------------------------
Because S2 is obtained from S1 by append-only inserts, the filtered result set for WHERE c0 = 0.0 in S2 should be a superset of the corresponding result set in S1. Therefore, the aggregate result should satisfy the monotonicity invariant MAX(S1) <= MAX(S2). Instead, the observed results are:
------------------------------------
MAX(S1) = 0.0
MAX(S2) = NULL
------------------------------------
This violates the expected S1-to-S2 monotonicity relationship.
How to repeat:
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);
-- S1: baseline state
INSERT IGNORE INTO mysql_bug_mre (c0) VALUES (0.0), ("x"), (NULL);
-- Query result on S1: returns 0.0
SELECT MAX(c0) AS s1_max
FROM mysql_bug_mre
WHERE c0 = 0.0;
-- S2: append-only expansion of S1
START TRANSACTION;
INSERT IGNORE INTO mysql_bug_mre (c0) VALUES
(0.0), (0.0), (0.0), (""), ("x"), ("-0x"), (NULL), (NULL);
COMMIT;
-- Refresh statistics after the state transition
ANALYZE TABLE mysql_bug_mre;
-- Query result on S2: returns NULL (wrong)
SELECT MAX(c0) AS s2_max
FROM mysql_bug_mre
WHERE c0 = 0.0;
-- Optional diagnostics
SELECT COUNT(*) AS zero_count
FROM mysql_bug_mre
WHERE c0 = 0.0;
SELECT MIN(c0) AS s2_min
FROM mysql_bug_mre
WHERE c0 = 0.0;
SELECT c0
FROM mysql_bug_mre
WHERE c0 = 0.0
ORDER BY c0;
SELECT MAX(c0)
FROM mysql_bug_mre FORCE INDEX (i_c0)
WHERE c0 = 0.0;
SELECT MAX(c0)
FROM mysql_bug_mre IGNORE INDEX (i_c0)
WHERE c0 = 0.0;
EXPLAIN SELECT c0
FROM mysql_bug_mre
WHERE c0 = 0.0;
Description: This issue can be described using two temporal states of the same table. In the baseline state S1, after creating the table and inserting a small amount of data, the query ------------------------------------ mysql> SELECT MAX(c0) AS s1_max -> FROM mysql_bug_mre -> WHERE c0 = 0.0; +--------+ | s1_max | +--------+ | 0 | +--------+ 1 row in set (0.001 sec) ------------------------------------ Then I transform S1 into S2 using only some INSERT statements, followed by ANALYZE TABLE, and execute the same query again. In S2, the query returns NULL. ------------------------------------ mysql> SELECT MAX(c0) AS s2_max -> FROM mysql_bug_mre -> WHERE c0 = 0.0; +--------+ | s2_max | +--------+ | NULL | +--------+ 1 row in set (0.001 sec) ------------------------------------ Because S2 is obtained from S1 by append-only inserts, the filtered result set for WHERE c0 = 0.0 in S2 should be a superset of the corresponding result set in S1. Therefore, the aggregate result should satisfy the monotonicity invariant MAX(S1) <= MAX(S2). Instead, the observed results are: ------------------------------------ MAX(S1) = 0.0 MAX(S2) = NULL ------------------------------------ This violates the expected S1-to-S2 monotonicity relationship. How to repeat: 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); -- S1: baseline state INSERT IGNORE INTO mysql_bug_mre (c0) VALUES (0.0), ("x"), (NULL); -- Query result on S1: returns 0.0 SELECT MAX(c0) AS s1_max FROM mysql_bug_mre WHERE c0 = 0.0; -- S2: append-only expansion of S1 START TRANSACTION; INSERT IGNORE INTO mysql_bug_mre (c0) VALUES (0.0), (0.0), (0.0), (""), ("x"), ("-0x"), (NULL), (NULL); COMMIT; -- Refresh statistics after the state transition ANALYZE TABLE mysql_bug_mre; -- Query result on S2: returns NULL (wrong) SELECT MAX(c0) AS s2_max FROM mysql_bug_mre WHERE c0 = 0.0; -- Optional diagnostics SELECT COUNT(*) AS zero_count FROM mysql_bug_mre WHERE c0 = 0.0; SELECT MIN(c0) AS s2_min FROM mysql_bug_mre WHERE c0 = 0.0; SELECT c0 FROM mysql_bug_mre WHERE c0 = 0.0 ORDER BY c0; SELECT MAX(c0) FROM mysql_bug_mre FORCE INDEX (i_c0) WHERE c0 = 0.0; SELECT MAX(c0) FROM mysql_bug_mre IGNORE INDEX (i_c0) WHERE c0 = 0.0; EXPLAIN SELECT c0 FROM mysql_bug_mre WHERE c0 = 0.0;