Description:
The same WHERE predicate returns a different number of rows depending on whether the optimizer uses the secondary index on column c2 or the query uses IGNORE INDEX (c2). This violates the expectation that index choice must not change logical results.
The outer predicate compares INET_NTOA('2016-12-24 09:00:00') to values from a NOT IN (SELECT c2 FROM t1 …) subquery, where c2 is BIT NOT NULL.
How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
c1 BIT,
c2 BIT NOT NULL,
UNIQUE (c2, c1)
);
INSERT INTO t1(c1,c2) VALUES (b'0',b'1'), (b'1',b'1');
INSERT INTO t1(c2) VALUES (b'0');
-- Uses index on c2 for the subquery (see EXPLAIN: index_subquery)
SELECT COUNT(*) AS n_indexed
FROM t1
WHERE INET_NTOA('2016-12-24 09:00:00') NOT IN (SELECT c2 FROM t1);
-- Same logical condition, but avoid using index c2 on both outer and inner references
SELECT COUNT(*) AS n_ignore_index
FROM t1 IGNORE INDEX (c2)
WHERE INET_NTOA('2016-12-24 09:00:00') NOT IN (SELECT c2 FROM t1 IGNORE INDEX (c2));
Actual result:
n_indexed = 3
n_ignore_index = 0
Description: The same WHERE predicate returns a different number of rows depending on whether the optimizer uses the secondary index on column c2 or the query uses IGNORE INDEX (c2). This violates the expectation that index choice must not change logical results. The outer predicate compares INET_NTOA('2016-12-24 09:00:00') to values from a NOT IN (SELECT c2 FROM t1 …) subquery, where c2 is BIT NOT NULL. How to repeat: DROP TABLE IF EXISTS t1; CREATE TABLE t1( c1 BIT, c2 BIT NOT NULL, UNIQUE (c2, c1) ); INSERT INTO t1(c1,c2) VALUES (b'0',b'1'), (b'1',b'1'); INSERT INTO t1(c2) VALUES (b'0'); -- Uses index on c2 for the subquery (see EXPLAIN: index_subquery) SELECT COUNT(*) AS n_indexed FROM t1 WHERE INET_NTOA('2016-12-24 09:00:00') NOT IN (SELECT c2 FROM t1); -- Same logical condition, but avoid using index c2 on both outer and inner references SELECT COUNT(*) AS n_ignore_index FROM t1 IGNORE INDEX (c2) WHERE INET_NTOA('2016-12-24 09:00:00') NOT IN (SELECT c2 FROM t1 IGNORE INDEX (c2)); Actual result: n_indexed = 3 n_ignore_index = 0