Description:
When a query uses WHERE 1 NOT IN (IFNULL(NULL, column), ...) to filter a DECIMAL column, a direct covering index scan on the base table correctly evaluates the expression and returns the expected row count (the two rows with c0=0).
However, when the query is rewritten as a multi‑table derived table with STRAIGHT_JOIN, the same IFNULL(NULL, column) expression incorrectly returns NULL during the covering index scan. This causes the NOT IN condition to evaluate to NULL (unknown), filtering out all rows and producing an aggregate result of 0.
Actual Results and Status
Query Actual Result Status
Single‑table (descending covering scan) 2 Correct
STRAIGHT_JOIN multi‑table (ascending covering scan) 0 Incorrect
EXPLAIN Analysis
Single‑table query (correct):
-> Aggregate: count(0)
-> Filter: (1 not in (ifnull(NULL,c0),<cache>(cast('abc' as signed))))
-> Covering index scan on vp_s using i0 (c0 DESC)
STRAIGHT_JOIN query (incorrect):
-> Aggregate: count(0)
-> Nested loop inner join
-> Filter: (1 not in (ifnull(NULL,l.c0),<cache>(cast('abc' as signed))))
-> Covering index scan on l using l_idx (c0)
-> Single-row covering index lookup on r using PRIMARY
How to repeat:
DROP DATABASE IF EXISTS repro_mysql714_db8;
CREATE DATABASE repro_mysql714_db8;
USE repro_mysql714_db8;
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 = 1;
CREATE TABLE vp_s (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 DECIMAL(65, 30) NULL
) ENGINE = InnoDB;
INSERT INTO vp_s (c0) VALUES (NULL), (0), (0), (1);
ALTER TABLE vp_s ADD KEY i0 (c0 DESC);
CREATE TABLE vp_l (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(65,30) NULL) ENGINE = InnoDB;
CREATE TABLE vp_r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(65,30) NULL) ENGINE = InnoDB;
INSERT INTO vp_l SELECT vp_rowid, c0 FROM vp_s;
INSERT INTO vp_r SELECT vp_rowid, c0 FROM vp_s;
CREATE INDEX l_idx ON vp_l(c0);
-- Single‑table query (correct): returns 2
SELECT COUNT(*) FROM vp_s
WHERE 1 NOT IN (IFNULL(NULL, vp_s.c0), CAST('abc' AS SIGNED));
-- STRAIGHT_JOIN multi‑table query (incorrect): returns 0
SELECT COUNT(*) FROM (
SELECT l.vp_rowid, l.c0
FROM vp_l l STRAIGHT_JOIN vp_r r ON l.vp_rowid = r.vp_rowid
) t
WHERE 1 NOT IN (IFNULL(NULL, t.c0), CAST('abc' AS SIGNED));
Description: When a query uses WHERE 1 NOT IN (IFNULL(NULL, column), ...) to filter a DECIMAL column, a direct covering index scan on the base table correctly evaluates the expression and returns the expected row count (the two rows with c0=0). However, when the query is rewritten as a multi‑table derived table with STRAIGHT_JOIN, the same IFNULL(NULL, column) expression incorrectly returns NULL during the covering index scan. This causes the NOT IN condition to evaluate to NULL (unknown), filtering out all rows and producing an aggregate result of 0. Actual Results and Status Query Actual Result Status Single‑table (descending covering scan) 2 Correct STRAIGHT_JOIN multi‑table (ascending covering scan) 0 Incorrect EXPLAIN Analysis Single‑table query (correct): -> Aggregate: count(0) -> Filter: (1 not in (ifnull(NULL,c0),<cache>(cast('abc' as signed)))) -> Covering index scan on vp_s using i0 (c0 DESC) STRAIGHT_JOIN query (incorrect): -> Aggregate: count(0) -> Nested loop inner join -> Filter: (1 not in (ifnull(NULL,l.c0),<cache>(cast('abc' as signed)))) -> Covering index scan on l using l_idx (c0) -> Single-row covering index lookup on r using PRIMARY How to repeat: DROP DATABASE IF EXISTS repro_mysql714_db8; CREATE DATABASE repro_mysql714_db8; USE repro_mysql714_db8; 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 = 1; CREATE TABLE vp_s ( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 DECIMAL(65, 30) NULL ) ENGINE = InnoDB; INSERT INTO vp_s (c0) VALUES (NULL), (0), (0), (1); ALTER TABLE vp_s ADD KEY i0 (c0 DESC); CREATE TABLE vp_l (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(65,30) NULL) ENGINE = InnoDB; CREATE TABLE vp_r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(65,30) NULL) ENGINE = InnoDB; INSERT INTO vp_l SELECT vp_rowid, c0 FROM vp_s; INSERT INTO vp_r SELECT vp_rowid, c0 FROM vp_s; CREATE INDEX l_idx ON vp_l(c0); -- Single‑table query (correct): returns 2 SELECT COUNT(*) FROM vp_s WHERE 1 NOT IN (IFNULL(NULL, vp_s.c0), CAST('abc' AS SIGNED)); -- STRAIGHT_JOIN multi‑table query (incorrect): returns 0 SELECT COUNT(*) FROM ( SELECT l.vp_rowid, l.c0 FROM vp_l l STRAIGHT_JOIN vp_r r ON l.vp_rowid = r.vp_rowid ) t WHERE 1 NOT IN (IFNULL(NULL, t.c0), CAST('abc' AS SIGNED));