Description:
An indexed BIGINT column is compared with a DOUBLE column. A row whose value
is not exactly representable as a double (2^53 + 1) is returned by NEITHER
"v IN (SELECT d ...)" NOR "v NOT IN (SELECT d ...)", although the table has no
NULLs, so every row must be in exactly one of the two results. Evaluated as an
expression in the select list, the same predicate is TRUE for that row.
The two access paths use different comparison rules:
- an index lookup on kv converts the DOUBLE to the BIGINT key, so only
v = 9007199254740992 matches;
- every other path compares both sides as DOUBLE, where 9007199254740993
rounds to 9007199254740992, so both rows match.
IN and EXISTS go through the index lookup; NOT IN (antijoin through
<in_optimizer>/<exists> ... IS FALSE) uses the double comparison. So the row
drops out of both. The same split makes a plain inner join return a different
row set depending on whether the index is used.
DECIMAL(30,0) against DOUBLE behaves the same way and is worse: with the
index, the matching rows disappear from IN, from NOT IN, and from the join.
Whatever comparison rule is intended (exact, or as double), it should be
applied the same way on every access path. PostgreSQL, for example, compares
bigint = float8 as float8 on every path, so its IN and NOT IN still partition
the table.
How to repeat:
CREATE DATABASE t091;
USE t091;
CREATE TABLE big(id INT PRIMARY KEY, v BIGINT, KEY kv(v)) ENGINE=InnoDB;
CREATE TABLE dbl(id INT PRIMARY KEY, d DOUBLE) ENGINE=InnoDB;
INSERT INTO big VALUES (1, 9007199254740992), -- 2^53
(2, 9007199254740993), -- 2^53 + 1
(3, 1);
INSERT INTO dbl VALUES (1, 9007199254740992);
ANALYZE TABLE big, dbl;
SELECT id FROM big WHERE v IN (SELECT d FROM dbl) ORDER BY id;
-- 1
SELECT id FROM big WHERE v NOT IN (SELECT d FROM dbl) ORDER BY id;
-- 3
-- row 2 is in neither result
SELECT id, v, (v IN (SELECT d FROM dbl)) AS in_q,
(v NOT IN (SELECT d FROM dbl)) AS notin_q
FROM big ORDER BY id;
-- 1 9007199254740992 1 0
-- 2 9007199254740993 1 0 <- the server itself says row 2 is IN
-- 3 1 0 1
-- the index changes the join result
SELECT big.id FROM big JOIN dbl ON big.v = dbl.d ORDER BY big.id;
-- 1
SELECT big.id FROM big IGNORE INDEX(kv) JOIN dbl ON big.v = dbl.d ORDER BY big.id;
-- 1, 2
SELECT id FROM big IGNORE INDEX(kv) WHERE v IN (SELECT d FROM dbl) ORDER BY id;
-- 1, 2
EXPLAIN FORMAT=TREE for the join on trunk:
-> Nested loop inner join
-> Filter: (dbl.d is not null)
-> Table scan on dbl
-> Filter: (big.v = dbl.d)
-> Covering index lookup on big using kv (v = dbl.d)
The residual "Filter: (big.v = dbl.d)" would accept row 2, which shows that
the index lookup itself has narrowed the key.
-- DECIMAL(30,0): with the index the rows vanish from everything
CREATE TABLE dec30(id INT PRIMARY KEY, v DECIMAL(30,0), KEY kv(v)) ENGINE=InnoDB;
CREATE TABLE dbl2(id INT PRIMARY KEY, d DOUBLE) ENGINE=InnoDB;
INSERT INTO dec30 VALUES (1, 123456789012345678901234567890),
(2, 123456789012345678901234567891),
(3, 1);
INSERT INTO dbl2 VALUES (1, 123456789012345678901234567890);
SELECT id FROM dec30 WHERE v IN (SELECT d FROM dbl2); -- (empty)
SELECT id FROM dec30 WHERE v NOT IN (SELECT d FROM dbl2); -- 3
SELECT id, (v IN (SELECT d FROM dbl2)) FROM dec30 ORDER BY id;
-- 1 1 / 2 1 / 3 0 <- rows 1 and 2 are IN per the select list
SELECT dec30.id FROM dec30 JOIN dbl2 ON dec30.v = dbl2.d; -- (empty)
SELECT dec30.id FROM dec30 IGNORE INDEX(kv) JOIN dbl2 ON dec30.v = dbl2.d; -- 1, 2
SHOW WARNINGS is empty after every query above.
Measured on trunk 26.10.0:
form rows returned
v IN (SELECT d FROM dbl) 1
EXISTS (SELECT 1 FROM dbl WHERE d = v) 1
v = (SELECT d FROM dbl) 1
big JOIN dbl ON v = d 1
big IGNORE INDEX(kv) JOIN dbl ON v = d 1, 2
v IN (...) with IGNORE INDEX(kv) 1, 2
v NOT IN (SELECT d FROM dbl) 3
per-row (v IN (...)) in the select list true for 1 and 2
Suggested fix:
When a ref/eq_ref lookup is built for "exact_numeric_key = double_value", either
do not use the index for that comparison, or treat the lookup as a range
filter whose result is re-checked with the same comparison the non-indexed
path uses (as the residual Filter already would). The rows returned should not
depend on whether the index is chosen.
Description: An indexed BIGINT column is compared with a DOUBLE column. A row whose value is not exactly representable as a double (2^53 + 1) is returned by NEITHER "v IN (SELECT d ...)" NOR "v NOT IN (SELECT d ...)", although the table has no NULLs, so every row must be in exactly one of the two results. Evaluated as an expression in the select list, the same predicate is TRUE for that row. The two access paths use different comparison rules: - an index lookup on kv converts the DOUBLE to the BIGINT key, so only v = 9007199254740992 matches; - every other path compares both sides as DOUBLE, where 9007199254740993 rounds to 9007199254740992, so both rows match. IN and EXISTS go through the index lookup; NOT IN (antijoin through <in_optimizer>/<exists> ... IS FALSE) uses the double comparison. So the row drops out of both. The same split makes a plain inner join return a different row set depending on whether the index is used. DECIMAL(30,0) against DOUBLE behaves the same way and is worse: with the index, the matching rows disappear from IN, from NOT IN, and from the join. Whatever comparison rule is intended (exact, or as double), it should be applied the same way on every access path. PostgreSQL, for example, compares bigint = float8 as float8 on every path, so its IN and NOT IN still partition the table. How to repeat: CREATE DATABASE t091; USE t091; CREATE TABLE big(id INT PRIMARY KEY, v BIGINT, KEY kv(v)) ENGINE=InnoDB; CREATE TABLE dbl(id INT PRIMARY KEY, d DOUBLE) ENGINE=InnoDB; INSERT INTO big VALUES (1, 9007199254740992), -- 2^53 (2, 9007199254740993), -- 2^53 + 1 (3, 1); INSERT INTO dbl VALUES (1, 9007199254740992); ANALYZE TABLE big, dbl; SELECT id FROM big WHERE v IN (SELECT d FROM dbl) ORDER BY id; -- 1 SELECT id FROM big WHERE v NOT IN (SELECT d FROM dbl) ORDER BY id; -- 3 -- row 2 is in neither result SELECT id, v, (v IN (SELECT d FROM dbl)) AS in_q, (v NOT IN (SELECT d FROM dbl)) AS notin_q FROM big ORDER BY id; -- 1 9007199254740992 1 0 -- 2 9007199254740993 1 0 <- the server itself says row 2 is IN -- 3 1 0 1 -- the index changes the join result SELECT big.id FROM big JOIN dbl ON big.v = dbl.d ORDER BY big.id; -- 1 SELECT big.id FROM big IGNORE INDEX(kv) JOIN dbl ON big.v = dbl.d ORDER BY big.id; -- 1, 2 SELECT id FROM big IGNORE INDEX(kv) WHERE v IN (SELECT d FROM dbl) ORDER BY id; -- 1, 2 EXPLAIN FORMAT=TREE for the join on trunk: -> Nested loop inner join -> Filter: (dbl.d is not null) -> Table scan on dbl -> Filter: (big.v = dbl.d) -> Covering index lookup on big using kv (v = dbl.d) The residual "Filter: (big.v = dbl.d)" would accept row 2, which shows that the index lookup itself has narrowed the key. -- DECIMAL(30,0): with the index the rows vanish from everything CREATE TABLE dec30(id INT PRIMARY KEY, v DECIMAL(30,0), KEY kv(v)) ENGINE=InnoDB; CREATE TABLE dbl2(id INT PRIMARY KEY, d DOUBLE) ENGINE=InnoDB; INSERT INTO dec30 VALUES (1, 123456789012345678901234567890), (2, 123456789012345678901234567891), (3, 1); INSERT INTO dbl2 VALUES (1, 123456789012345678901234567890); SELECT id FROM dec30 WHERE v IN (SELECT d FROM dbl2); -- (empty) SELECT id FROM dec30 WHERE v NOT IN (SELECT d FROM dbl2); -- 3 SELECT id, (v IN (SELECT d FROM dbl2)) FROM dec30 ORDER BY id; -- 1 1 / 2 1 / 3 0 <- rows 1 and 2 are IN per the select list SELECT dec30.id FROM dec30 JOIN dbl2 ON dec30.v = dbl2.d; -- (empty) SELECT dec30.id FROM dec30 IGNORE INDEX(kv) JOIN dbl2 ON dec30.v = dbl2.d; -- 1, 2 SHOW WARNINGS is empty after every query above. Measured on trunk 26.10.0: form rows returned v IN (SELECT d FROM dbl) 1 EXISTS (SELECT 1 FROM dbl WHERE d = v) 1 v = (SELECT d FROM dbl) 1 big JOIN dbl ON v = d 1 big IGNORE INDEX(kv) JOIN dbl ON v = d 1, 2 v IN (...) with IGNORE INDEX(kv) 1, 2 v NOT IN (SELECT d FROM dbl) 3 per-row (v IN (...)) in the select list true for 1 and 2 Suggested fix: When a ref/eq_ref lookup is built for "exact_numeric_key = double_value", either do not use the index for that comparison, or treat the lookup as a range filter whose result is re-checked with the same comparison the non-indexed path uses (as the residual Filter already would). The rows returned should not depend on whether the index is chosen.