Description:
MySQL evaluates an `IN (SELECT ...)` predicate differently depending on whether indexed access is used or a full scan is forced with `IGNORE INDEX`. In the mutated repro, the predicate is evaluated once per row and the mismatch is amplified into a count difference:
`with_index_count = 0`
`full_scan_count = 4`
The bug is observable on a window-function query that uses a named window and `LEAD(..., NULL)` together with the shown seed data.
How to repeat:
DROP TABLE IF EXISTS t18;
CREATE TABLE t18 (
c1 BIT,
c2 BIT NOT NULL,
UNIQUE KEY uq_c2_c1 (c2, c1)
);
INSERT INTO t18 (c1, c2) VALUES
(NULL, b'0'),
(NULL, b'1'),
(b'0', b'0'),
(b'0', b'1');
WITH wf_probe AS (
SELECT
grp_id,
ord_id,
ROW_NUMBER() OVER w_main AS wf_rn,
LEAD(ord_id, 1, NULL) OVER w_main AS next_ord
FROM (
SELECT 7 AS grp_id, 1 AS ord_id
UNION ALL
SELECT 7, 2
UNION ALL
SELECT 9, 1
) AS seed
WINDOW w_main AS (PARTITION BY grp_id ORDER BY ord_id)
)
SELECT JSON_OBJECT(
'with_index_count',
(
SELECT COUNT(*)
FROM t18 AS outer_t
WHERE (
SELECT CASE
WHEN EXISTS (SELECT 1 FROM wf_probe WHERE grp_id = 7 AND wf_rn = 1)
THEN INET_NTOA('2016-12-24 09:00:00') IN (SELECT c2 FROM t18)
ELSE NULL
END
)
),
'full_scan_count',
(
SELECT COUNT(*)
FROM t18 AS outer_t
WHERE (
SELECT CASE
WHEN EXISTS (SELECT 1 FROM wf_probe WHERE grp_id = 7 AND wf_rn = 1)
THEN INET_NTOA('2016-12-24 09:00:00') IN (
SELECT c2 FROM t18 IGNORE INDEX (uq_c2_c1)
)
ELSE NULL
END
)
)
) AS observation;
Description: MySQL evaluates an `IN (SELECT ...)` predicate differently depending on whether indexed access is used or a full scan is forced with `IGNORE INDEX`. In the mutated repro, the predicate is evaluated once per row and the mismatch is amplified into a count difference: `with_index_count = 0` `full_scan_count = 4` The bug is observable on a window-function query that uses a named window and `LEAD(..., NULL)` together with the shown seed data. How to repeat: DROP TABLE IF EXISTS t18; CREATE TABLE t18 ( c1 BIT, c2 BIT NOT NULL, UNIQUE KEY uq_c2_c1 (c2, c1) ); INSERT INTO t18 (c1, c2) VALUES (NULL, b'0'), (NULL, b'1'), (b'0', b'0'), (b'0', b'1'); WITH wf_probe AS ( SELECT grp_id, ord_id, ROW_NUMBER() OVER w_main AS wf_rn, LEAD(ord_id, 1, NULL) OVER w_main AS next_ord FROM ( SELECT 7 AS grp_id, 1 AS ord_id UNION ALL SELECT 7, 2 UNION ALL SELECT 9, 1 ) AS seed WINDOW w_main AS (PARTITION BY grp_id ORDER BY ord_id) ) SELECT JSON_OBJECT( 'with_index_count', ( SELECT COUNT(*) FROM t18 AS outer_t WHERE ( SELECT CASE WHEN EXISTS (SELECT 1 FROM wf_probe WHERE grp_id = 7 AND wf_rn = 1) THEN INET_NTOA('2016-12-24 09:00:00') IN (SELECT c2 FROM t18) ELSE NULL END ) ), 'full_scan_count', ( SELECT COUNT(*) FROM t18 AS outer_t WHERE ( SELECT CASE WHEN EXISTS (SELECT 1 FROM wf_probe WHERE grp_id = 7 AND wf_rn = 1) THEN INET_NTOA('2016-12-24 09:00:00') IN ( SELECT c2 FROM t18 IGNORE INDEX (uq_c2_c1) ) ELSE NULL END ) ) ) AS observation;