Description:
When a query uses a WHERE condition containing CASE IFNULL(NULL, c0) WHEN 1 THEN FALSE ELSE -c0 END, and a descending unique index on a DECIMAL column exists, the covering index scan with Index Condition Pushdown (ICP) correctly filters rows, keeping only those with non‑NULL, non‑0, non‑1 values.
However, when the query is rewritten as a derived table with a JOIN or when forced to use a primary key scan, the same WHERE condition incorrectly retains rows that should have been filtered (e.g., c0=1), causing aggregate results (VAR_SAMP, STD) to deviate.
The root cause is that during ICP evaluation on the descending DECIMAL index, the negation (-c0) and comparison operations use an incorrect internal format for DECIMAL, causing the WHEN 1 branch to fail and falling into the ELSE branch.
Actual Results and Status
Query ref1 (VAR_SAMP) ref2 (STD) Filtering Effect Status
Single‑table NULL 0 Only one row (735016270) kept Correct
Derived table 0.5 0.5 Two rows (1 and 735016270) kept Incorrect
Explanation: The row with c0=1 should be filtered by WHEN 1 THEN FALSE. The condition is equivalent to c0 != 1 AND c0 != 0 AND c0 IS NOT NULL, so only 735016270 should remain.
EXPLAIN Analysis
Single‑table query (correct):
-> Limit: 3 row(s) (cost=1.24 rows=1)
-> Aggregate: var_samp(s.vp_rowid), std(s.vp_rowid) (cost=1.24 rows=1)
-> Filter: (0 <> (case ifnull(NULL,s.c0) when 1 then false else -(s.c0) end)) (cost=0.55 rows=3)
-> Covering index scan on s using s_idx (cost=0.55 rows=3)
Derived‑table query (incorrect):
-> Limit: 3 row(s) (cost=2.29 rows=1)
-> Aggregate: var_samp(l.vp_rowid), std(l.vp_rowid) (cost=2.29 rows=1)
-> Nested loop inner join (cost=1.6 rows=3)
-> Covering index scan on l using PRIMARY (cost=0.55 rows=3)
-> Filter: (0 <> (case ifnull(NULL,r.c0) when 1 then false else -(r.c0) end)) (cost=0.283 rows=1)
-> Single-row index lookup on r using PRIMARY (vp_rowid = l.vp_rowid)
How to repeat:
DROP DATABASE IF EXISTS vp714_d7_repro;
CREATE DATABASE vp714_d7_repro;
USE vp714_d7_repro;
CREATE TABLE s (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 DECIMAL NULL,
UNIQUE KEY s_idx(c0 DESC)
) ENGINE=InnoDB;
INSERT INTO s(c0) VALUES (NULL), (1), (735016270);
CREATE TABLE l (
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c0 DECIMAL NULL
) ENGINE=InnoDB;
CREATE TABLE r LIKE l;
INSERT INTO l SELECT * FROM s;
INSERT INTO r SELECT * FROM s;
-- Single‑table query (correct): keeps only c0=735016270, VAR_SAMP=NULL, STD=0
SELECT
'single' AS q,
818895259 AS ref0,
VAR_SAMP(vp_rowid) AS ref1,
STD(vp_rowid) AS ref2
FROM s
WHERE CASE IFNULL(NULL, c0)
WHEN 1 THEN FALSE
ELSE -c0
END
ORDER BY vp_rowid
LIMIT 3;
-- Derived‑table query (incorrect): incorrectly keeps c0=1, VAR_SAMP=0.5, STD=0.5
SELECT
'split' AS q,
818895259 AS ref0,
VAR_SAMP(vp_rowid) AS ref1,
STD(vp_rowid) AS ref2
FROM (
SELECT l.vp_rowid AS vp_rowid, r.c0 AS c0
FROM (SELECT * FROM l) l
JOIN (SELECT * FROM r) r
ON l.vp_rowid = r.vp_rowid
) s
WHERE CASE IFNULL(NULL, c0)
WHEN 1 THEN FALSE
ELSE -c0
END
ORDER BY vp_rowid
LIMIT 3;
Description: When a query uses a WHERE condition containing CASE IFNULL(NULL, c0) WHEN 1 THEN FALSE ELSE -c0 END, and a descending unique index on a DECIMAL column exists, the covering index scan with Index Condition Pushdown (ICP) correctly filters rows, keeping only those with non‑NULL, non‑0, non‑1 values. However, when the query is rewritten as a derived table with a JOIN or when forced to use a primary key scan, the same WHERE condition incorrectly retains rows that should have been filtered (e.g., c0=1), causing aggregate results (VAR_SAMP, STD) to deviate. The root cause is that during ICP evaluation on the descending DECIMAL index, the negation (-c0) and comparison operations use an incorrect internal format for DECIMAL, causing the WHEN 1 branch to fail and falling into the ELSE branch. Actual Results and Status Query ref1 (VAR_SAMP) ref2 (STD) Filtering Effect Status Single‑table NULL 0 Only one row (735016270) kept Correct Derived table 0.5 0.5 Two rows (1 and 735016270) kept Incorrect Explanation: The row with c0=1 should be filtered by WHEN 1 THEN FALSE. The condition is equivalent to c0 != 1 AND c0 != 0 AND c0 IS NOT NULL, so only 735016270 should remain. EXPLAIN Analysis Single‑table query (correct): -> Limit: 3 row(s) (cost=1.24 rows=1) -> Aggregate: var_samp(s.vp_rowid), std(s.vp_rowid) (cost=1.24 rows=1) -> Filter: (0 <> (case ifnull(NULL,s.c0) when 1 then false else -(s.c0) end)) (cost=0.55 rows=3) -> Covering index scan on s using s_idx (cost=0.55 rows=3) Derived‑table query (incorrect): -> Limit: 3 row(s) (cost=2.29 rows=1) -> Aggregate: var_samp(l.vp_rowid), std(l.vp_rowid) (cost=2.29 rows=1) -> Nested loop inner join (cost=1.6 rows=3) -> Covering index scan on l using PRIMARY (cost=0.55 rows=3) -> Filter: (0 <> (case ifnull(NULL,r.c0) when 1 then false else -(r.c0) end)) (cost=0.283 rows=1) -> Single-row index lookup on r using PRIMARY (vp_rowid = l.vp_rowid) How to repeat: DROP DATABASE IF EXISTS vp714_d7_repro; CREATE DATABASE vp714_d7_repro; USE vp714_d7_repro; CREATE TABLE s ( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 DECIMAL NULL, UNIQUE KEY s_idx(c0 DESC) ) ENGINE=InnoDB; INSERT INTO s(c0) VALUES (NULL), (1), (735016270); CREATE TABLE l ( vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL NULL ) ENGINE=InnoDB; CREATE TABLE r LIKE l; INSERT INTO l SELECT * FROM s; INSERT INTO r SELECT * FROM s; -- Single‑table query (correct): keeps only c0=735016270, VAR_SAMP=NULL, STD=0 SELECT 'single' AS q, 818895259 AS ref0, VAR_SAMP(vp_rowid) AS ref1, STD(vp_rowid) AS ref2 FROM s WHERE CASE IFNULL(NULL, c0) WHEN 1 THEN FALSE ELSE -c0 END ORDER BY vp_rowid LIMIT 3; -- Derived‑table query (incorrect): incorrectly keeps c0=1, VAR_SAMP=0.5, STD=0.5 SELECT 'split' AS q, 818895259 AS ref0, VAR_SAMP(vp_rowid) AS ref1, STD(vp_rowid) AS ref2 FROM ( SELECT l.vp_rowid AS vp_rowid, r.c0 AS c0 FROM (SELECT * FROM l) l JOIN (SELECT * FROM r) r ON l.vp_rowid = r.vp_rowid ) s WHERE CASE IFNULL(NULL, c0) WHEN 1 THEN FALSE ELSE -c0 END ORDER BY vp_rowid LIMIT 3;