Description:
For a table with an index on a BIGINT column id, the following query:
SELECT COUNT(*) AS cnt
FROM s
WHERE NOT (IFNULL(0.5, 0) NOT IN (id));
returns cnt = 1, while evaluating the same predicate row by row:
SELECT id, NOT (IFNULL(0.5, 0) NOT IN (id)) AS pred
FROM s;
returns pred = 0 (i.e., the predicate is false for the only row, which should therefore not be counted).
When the table has no index, or when IFNULL() is removed (using 0.5 directly), COUNT(*) correctly returns 0.
The optimizer incorrectly transforms the condition into an index lookup id = IFNULL(0.5,0) and uses the estimated row count (rows=1) for the aggregate, ignoring the actual filtering effect.
Explain output (wrong query):
-> Aggregate: count(0) (cost=0.45 rows=1)
-> Covering index lookup on s using id (id=ifnull(0.5,0)) (cost=0.35 rows=1)
Note the optimizer simplified the condition to id = ifnull(0.5,0) as a covering index lookup.
Explain output (correct query – no index):
-> Aggregate: count(0) (cost=0.45 rows=1)
-> Filter: (<cache>(ifnull(0.5,0)) = s_plain.id) (cost=0.35 rows=1)
-> Table scan on s_plain (cost=0.35 rows=1)
Here the optimizer correctly retains a filter with ifnull(0.5,0) = id.A full index scan is performed; the predicate is evaluated per row and the result is correct.
Root Cause Analysis
The optimizer incorrectly rewrites the predicate NOT (expr NOT IN (indexed_col)) into an index equality lookup indexed_col = expr when all of the following conditions are met simultaneously:
The column has an index;
The outer expression is of the form NOT (... NOT IN (...));
The left-hand side of NOT IN is IFNULL(non-integer numeric constant, ...).
How to repeat:
DROP DATABASE IF EXISTS test_db2;
CREATE DATABASE test_db2;
USE test_db2;
CREATE TABLE s (
id BIGINT NOT NULL,
KEY(id)
) ENGINE=InnoDB;
INSERT INTO s VALUES (1);
-- Wrong result: returns 1
SELECT COUNT(*) AS cnt
FROM s
WHERE NOT (IFNULL(0.5, 0) NOT IN (id));
-- Row-by-row predicate: shows pred=0 for id=1
SELECT id,
NOT (IFNULL(0.5, 0) NOT IN (id)) AS pred
FROM s;
-- Control 1: without index, correctly returns 0
CREATE TABLE s_plain (
id BIGINT NOT NULL
) ENGINE=InnoDB;
INSERT INTO s_plain VALUES (1);
SELECT COUNT(*) AS cnt
FROM s_plain
WHERE NOT (IFNULL(0.5, 0) NOT IN (id));
-- Control 2: with index but without IFNULL, correctly returns 0
SELECT COUNT(*) AS cnt
FROM s
WHERE NOT (0.5 NOT IN (id));
Description: For a table with an index on a BIGINT column id, the following query: SELECT COUNT(*) AS cnt FROM s WHERE NOT (IFNULL(0.5, 0) NOT IN (id)); returns cnt = 1, while evaluating the same predicate row by row: SELECT id, NOT (IFNULL(0.5, 0) NOT IN (id)) AS pred FROM s; returns pred = 0 (i.e., the predicate is false for the only row, which should therefore not be counted). When the table has no index, or when IFNULL() is removed (using 0.5 directly), COUNT(*) correctly returns 0. The optimizer incorrectly transforms the condition into an index lookup id = IFNULL(0.5,0) and uses the estimated row count (rows=1) for the aggregate, ignoring the actual filtering effect. Explain output (wrong query): -> Aggregate: count(0) (cost=0.45 rows=1) -> Covering index lookup on s using id (id=ifnull(0.5,0)) (cost=0.35 rows=1) Note the optimizer simplified the condition to id = ifnull(0.5,0) as a covering index lookup. Explain output (correct query – no index): -> Aggregate: count(0) (cost=0.45 rows=1) -> Filter: (<cache>(ifnull(0.5,0)) = s_plain.id) (cost=0.35 rows=1) -> Table scan on s_plain (cost=0.35 rows=1) Here the optimizer correctly retains a filter with ifnull(0.5,0) = id.A full index scan is performed; the predicate is evaluated per row and the result is correct. Root Cause Analysis The optimizer incorrectly rewrites the predicate NOT (expr NOT IN (indexed_col)) into an index equality lookup indexed_col = expr when all of the following conditions are met simultaneously: The column has an index; The outer expression is of the form NOT (... NOT IN (...)); The left-hand side of NOT IN is IFNULL(non-integer numeric constant, ...). How to repeat: DROP DATABASE IF EXISTS test_db2; CREATE DATABASE test_db2; USE test_db2; CREATE TABLE s ( id BIGINT NOT NULL, KEY(id) ) ENGINE=InnoDB; INSERT INTO s VALUES (1); -- Wrong result: returns 1 SELECT COUNT(*) AS cnt FROM s WHERE NOT (IFNULL(0.5, 0) NOT IN (id)); -- Row-by-row predicate: shows pred=0 for id=1 SELECT id, NOT (IFNULL(0.5, 0) NOT IN (id)) AS pred FROM s; -- Control 1: without index, correctly returns 0 CREATE TABLE s_plain ( id BIGINT NOT NULL ) ENGINE=InnoDB; INSERT INTO s_plain VALUES (1); SELECT COUNT(*) AS cnt FROM s_plain WHERE NOT (IFNULL(0.5, 0) NOT IN (id)); -- Control 2: with index but without IFNULL, correctly returns 0 SELECT COUNT(*) AS cnt FROM s WHERE NOT (0.5 NOT IN (id));