Description:
In MySQL 9.6.0, when the optimizer chooses an Index Merge Intersection plan for a SELECT query with an OR predicate, the "Intersect rows sorted by row ID" operation incorrectly reduces the number of matching rows, causing valid result rows to be dropped.
Specifically, a query of the form:
SELECT COUNT(*) FROM t WHERE c2 = 0.7559466140838738 OR c7 = 287496496;
returns 2 rows when index_merge_intersection=on (the default), but correctly returns 3 rows when index_merge_intersection=off.
The missing row is: (c2=0.7559466140838738, c3=1776068260, c7=2084714166).
This row should match the "c2 = 0.7559466140838738" condition. There are two rows in the table with c2 = 0.7559466140838738: one with (c3=939902875, c7=281834758) and the missing one with (c3=1776068260, c7=2084714166). The index merge intersection algorithm appears to incorrectly deduplicate or merge the row ID sets from the two index scans, treating two distinct matching rows as one.
The optimizer produces a semantically wrong query result under default settings. Since index_merge_intersection is enabled by default, any workload that triggers index merge intersection on tables with multiple secondary indexes and OR predicates may silently return incorrect results.
How to repeat:
DROP DATABASE IF EXISTS db1_min;
CREATE DATABASE db1_min;
USE db1_min;
CREATE TABLE `t` (
`c2` double NOT NULL,
`c3` int NOT NULL,
`c7` int NOT NULL,
UNIQUE KEY `i30` (`c3` DESC,`c2`),
UNIQUE KEY `i64` (`c2` DESC,`c3`),
UNIQUE KEY `i95` (`c7`,`c3`) USING BTREE,
UNIQUE KEY `i61` (`c2`,`c7`) USING BTREE,
UNIQUE KEY `i107` (`c3` DESC,`c7` DESC),
UNIQUE KEY `i109` (`c3`,`c7`),
UNIQUE KEY `i20` (`c7` DESC,`c2`),
UNIQUE KEY `i114` (`c2`,`c7`,`c3`),
UNIQUE KEY `i36` (`c7`,`c3`,`c2`),
UNIQUE KEY `i67` (`c7`,`c2`),
UNIQUE KEY `i83` (`c2`,`c3`),
UNIQUE KEY `i68` (`c3` DESC,`c2` DESC),
KEY `imerge_b_3119` (`c2`),
KEY `imerge_a_3124` (`c7`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `t` VALUES
(0.7559466140838738,939902875,281834758),
(0.7559466140838738,1776068260,2084714166),
(0.31186114697490697,1699327703,1073842890),
(1044617263,2076213040,287496496);
ANALYZE TABLE t;
-- Buggy: returns 2 (WRONG -- should be 3)
SELECT COUNT(*) AS cnt FROM t WHERE c2 = 0.7559466140838738 OR c7 = 287496496;
-- Correct: returns 3
SET SESSION optimizer_switch = 'index_merge_intersection=off';
SELECT COUNT(*) AS cnt FROM t WHERE c2 = 0.7559466140838738 OR c7 = 287496496;
SET SESSION optimizer_switch = 'default';
-- Show the buggy execution plan
EXPLAIN SELECT * FROM t WHERE c2 = 0.7559466140838738 OR c7 = 287496496;
With default optimizer (index_merge_intersection=on):
+-----+
| cnt |
+-----+
| 2 | <- WRONG!
+-----+
1 row in set (0.00 sec)
With index_merge_intersection=off：
+-----+
| cnt |
+-----+
| 3 | <- CORRECT
+-----+
1 row in set (0.00 sec)
Expected result: Both queries should return cnt = 3 (two rows match c2 = 0.7559466140838738 and one row matches c7 = 287496496; no row matches both conditions).
Actual result: With index_merge_intersection=on (default), the first query returns cnt = 2. One of the two rows satisfying c2 = 0.7559466140838738 is incorrectly dropped by the index merge intersect operation.
Suggested fix:
Investigate the index merge intersection implementation in the storage engine layer, specifically the "Intersect rows sorted by row ID" merge/deduplication logic. When the optimizer performs an index merge for an OR predicate (i.e., it should be performing a union of row IDs from multiple index scans), the algorithm appears to incorrectly intersect or deduplicate the row ID sets. The merge sort of row IDs from the two index scans may be collapsing two distinct rows that happen to share a c2 value into a single result entry.
Key areas to investigate:
1. The row ID merge logic in index_merge_read() or equivalent function -- ensure that when merging sorted row ID streams for an OR (union) operation, all matching row IDs are preserved, including duplicates that come from different index scans.
2. Verify that the "sort by row ID and deduplicate" step correctly handles the case where two distinct rows (with different primary keys/row IDs) both satisfy one branch of the OR condition but are adjacent in the sorted output.
