Description:
MySQL returns inconsistent results for two logically equivalent forms of the same predicate when the only intended difference is index usage versus forced table scan. In the reproduced case, the indexed path returns `4` qualifying rows while the `IGNORE INDEX` path returns `0`.
How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
c1 BIT,
c2 BIT NOT NULL,
UNIQUE KEY uq_c2_c1 (c2, c1)
);
INSERT INTO t1 (c1, c2) VALUES
(NULL, b'0'),
(NULL, b'1'),
(b'0', b'0'),
(b'0', b'1');
WITH wf_outer AS (
SELECT
base.c1,
base.c2,
ROW_NUMBER() OVER w_main AS wf_rn,
LAG(base.c2, 1, NULL) OVER w_main AS prev_c2
FROM t1 AS base
WINDOW w_main AS (PARTITION BY base.c2 ORDER BY base.c1)
)
SELECT JSON_OBJECT(
'with_index_count',
(
SELECT COUNT(*)
FROM wf_outer AS o
WHERE o.wf_rn >= 1
AND INET_NTOA('2016-12-24 09:00:00') NOT IN (SELECT c2 FROM t1)
),
'full_scan_count',
(
SELECT COUNT(*)
FROM wf_outer AS o
WHERE o.wf_rn >= 1
AND INET_NTOA('2016-12-24 09:00:00') NOT IN (
SELECT c2 FROM t1 IGNORE INDEX (uq_c2_c1)
)
)
) AS observation;
Description: MySQL returns inconsistent results for two logically equivalent forms of the same predicate when the only intended difference is index usage versus forced table scan. In the reproduced case, the indexed path returns `4` qualifying rows while the `IGNORE INDEX` path returns `0`. How to repeat: DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( c1 BIT, c2 BIT NOT NULL, UNIQUE KEY uq_c2_c1 (c2, c1) ); INSERT INTO t1 (c1, c2) VALUES (NULL, b'0'), (NULL, b'1'), (b'0', b'0'), (b'0', b'1'); WITH wf_outer AS ( SELECT base.c1, base.c2, ROW_NUMBER() OVER w_main AS wf_rn, LAG(base.c2, 1, NULL) OVER w_main AS prev_c2 FROM t1 AS base WINDOW w_main AS (PARTITION BY base.c2 ORDER BY base.c1) ) SELECT JSON_OBJECT( 'with_index_count', ( SELECT COUNT(*) FROM wf_outer AS o WHERE o.wf_rn >= 1 AND INET_NTOA('2016-12-24 09:00:00') NOT IN (SELECT c2 FROM t1) ), 'full_scan_count', ( SELECT COUNT(*) FROM wf_outer AS o WHERE o.wf_rn >= 1 AND INET_NTOA('2016-12-24 09:00:00') NOT IN ( SELECT c2 FROM t1 IGNORE INDEX (uq_c2_c1) ) ) ) AS observation;