Description:
When a query uses a covering index scan to read a DECIMAL(65,30) column, and that column participates in IFNULL or CASE expressions in the WHERE clause, the optimizer may incorrectly interpret some or all DECIMAL values as NULL when reconstructing them from the secondary index. This causes expression evaluation to fail, and rows that should pass the filter are discarded.
When the same data is read via the clustered index (table lookup), only small values like 0 and 1 are affected, while larger values remain correct.
However, in the covering index scan path, all DECIMAL values (including large ones) are mis‑interpreted as NULL, leading to completely wrong aggregate results (e.g., AVG returns NULL).
A JOIN‑based rewrite that forces clustered index reads shows much better behavior, indicating that the bug lies in the secondary‑index value reconstruction logic for DECIMAL(65,30).
Actual Results and Status
Query ref0 (AVG) Status
Single‑table (covering index) NULL Incorrect
JOIN rewrite (clustered lookup) 1.0000 Partially correct
EXPLAIN Analysis
Single‑table query (covering index):
-> Limit: 2 row(s)
-> Aggregate: avg(vp_s.vp_rowid)
-> Filter: (0 <> ifnull(<cache>(((0 <> NULL) xor (0 <> 0.5454...))),
(case vp_s.c1 when vp_s.vp_rowid then ... end)))
-> Covering index scan on vp_s using src_idx
JOIN rewrite query (clustered lookup):
-> Limit: 2 row(s)
-> Aggregate: avg(l.vp_rowid)
-> Nested loop inner join
-> Nested loop inner join
-> Covering index scan on l using PRIMARY
-> Filter: (0 <> ifnull(<cache>(...), (case r.c1 ...)))
-> Single-row index lookup on r using PRIMARY
-> Index lookup on rg using <auto_key0>
-> Materialize
-> Group aggregate: count(0)
-> Covering index scan on r using PRIMARY
How to repeat:
DROP DATABASE IF EXISTS repro_mysql714_db23;
CREATE DATABASE repro_mysql714_db23;
USE repro_mysql714_db23;
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 = 0;
CREATE TABLE vp_s (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 BIGINT NULL,
c1 DECIMAL(65, 30) NULL
) ENGINE = InnoDB;
INSERT INTO vp_s (c0, c1) VALUES
(-8388608, 333757159), (0, NULL), (0, NULL), (0, 0), (0, 1), (8388607, NULL);
ALTER TABLE vp_s ADD UNIQUE KEY src_idx (c1);
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, c1 DECIMAL(65,30) NULL, c0 BIGINT NULL) ENGINE = InnoDB;
INSERT INTO vp_l SELECT vp_rowid, c0 FROM vp_s;
INSERT INTO vp_r SELECT vp_rowid, c1, c0 FROM vp_s;
-- Single‑table query (covering index, incorrect): returns NULL (all rows filtered)
SELECT ALL AVG(vp_rowid) AS ref0 FROM vp_s
WHERE IFNULL((NULL) XOR (0.5454191638138058),
(CASE vp_s.c1 WHEN vp_s.vp_rowid THEN 2055221018 WHEN NULL THEN NULL
WHEN 0.6615707880681362 THEN 0.13839908025384118 ELSE vp_s.c1 END))
ORDER BY vp_s.vp_rowid LIMIT 2;
-- JOIN rewrite (clustered index read, partially correct): returns 1.0000
SELECT ALL AVG(vp_rowid) AS ref0 FROM (
SELECT l.vp_rowid AS vp_rowid, l.c0 AS c0, r.c1 AS c1
FROM vp_l l
JOIN (SELECT vp_rowid, COUNT(*) AS vp_count FROM vp_r GROUP BY vp_rowid) rg
ON l.vp_rowid = rg.vp_rowid
JOIN vp_r r ON r.vp_rowid = rg.vp_rowid
) vp_source
WHERE IFNULL((NULL) XOR (0.5454191638138058),
(CASE vp_source.c1 WHEN vp_source.vp_rowid THEN 2055221018 WHEN NULL THEN NULL
WHEN 0.6615707880681362 THEN 0.13839908025384118 ELSE vp_source.c1 END))
ORDER BY vp_source.vp_rowid LIMIT 2;
Description: When a query uses a covering index scan to read a DECIMAL(65,30) column, and that column participates in IFNULL or CASE expressions in the WHERE clause, the optimizer may incorrectly interpret some or all DECIMAL values as NULL when reconstructing them from the secondary index. This causes expression evaluation to fail, and rows that should pass the filter are discarded. When the same data is read via the clustered index (table lookup), only small values like 0 and 1 are affected, while larger values remain correct. However, in the covering index scan path, all DECIMAL values (including large ones) are mis‑interpreted as NULL, leading to completely wrong aggregate results (e.g., AVG returns NULL). A JOIN‑based rewrite that forces clustered index reads shows much better behavior, indicating that the bug lies in the secondary‑index value reconstruction logic for DECIMAL(65,30). Actual Results and Status Query ref0 (AVG) Status Single‑table (covering index) NULL Incorrect JOIN rewrite (clustered lookup) 1.0000 Partially correct EXPLAIN Analysis Single‑table query (covering index): -> Limit: 2 row(s) -> Aggregate: avg(vp_s.vp_rowid) -> Filter: (0 <> ifnull(<cache>(((0 <> NULL) xor (0 <> 0.5454...))), (case vp_s.c1 when vp_s.vp_rowid then ... end))) -> Covering index scan on vp_s using src_idx JOIN rewrite query (clustered lookup): -> Limit: 2 row(s) -> Aggregate: avg(l.vp_rowid) -> Nested loop inner join -> Nested loop inner join -> Covering index scan on l using PRIMARY -> Filter: (0 <> ifnull(<cache>(...), (case r.c1 ...))) -> Single-row index lookup on r using PRIMARY -> Index lookup on rg using <auto_key0> -> Materialize -> Group aggregate: count(0) -> Covering index scan on r using PRIMARY How to repeat: DROP DATABASE IF EXISTS repro_mysql714_db23; CREATE DATABASE repro_mysql714_db23; USE repro_mysql714_db23; 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 = 0; CREATE TABLE vp_s ( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 BIGINT NULL, c1 DECIMAL(65, 30) NULL ) ENGINE = InnoDB; INSERT INTO vp_s (c0, c1) VALUES (-8388608, 333757159), (0, NULL), (0, NULL), (0, 0), (0, 1), (8388607, NULL); ALTER TABLE vp_s ADD UNIQUE KEY src_idx (c1); 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, c1 DECIMAL(65,30) NULL, c0 BIGINT NULL) ENGINE = InnoDB; INSERT INTO vp_l SELECT vp_rowid, c0 FROM vp_s; INSERT INTO vp_r SELECT vp_rowid, c1, c0 FROM vp_s; -- Single‑table query (covering index, incorrect): returns NULL (all rows filtered) SELECT ALL AVG(vp_rowid) AS ref0 FROM vp_s WHERE IFNULL((NULL) XOR (0.5454191638138058), (CASE vp_s.c1 WHEN vp_s.vp_rowid THEN 2055221018 WHEN NULL THEN NULL WHEN 0.6615707880681362 THEN 0.13839908025384118 ELSE vp_s.c1 END)) ORDER BY vp_s.vp_rowid LIMIT 2; -- JOIN rewrite (clustered index read, partially correct): returns 1.0000 SELECT ALL AVG(vp_rowid) AS ref0 FROM ( SELECT l.vp_rowid AS vp_rowid, l.c0 AS c0, r.c1 AS c1 FROM vp_l l JOIN (SELECT vp_rowid, COUNT(*) AS vp_count FROM vp_r GROUP BY vp_rowid) rg ON l.vp_rowid = rg.vp_rowid JOIN vp_r r ON r.vp_rowid = rg.vp_rowid ) vp_source WHERE IFNULL((NULL) XOR (0.5454191638138058), (CASE vp_source.c1 WHEN vp_source.vp_rowid THEN 2055221018 WHEN NULL THEN NULL WHEN 0.6615707880681362 THEN 0.13839908025384118 ELSE vp_source.c1 END)) ORDER BY vp_source.vp_rowid LIMIT 2;