Description:
An INNER JOIN between two InnoDB tables returns only one of three matching rows when dynamic range access selects a covering index merge intersection. Disabling index_merge_intersection returns all three rows.
EXPLAIN reports "Range checked for each record" for the inner table. Runtime optimizer traces show index_roworder_intersect with covering=true for all three outer rows. The join columns use different collations, preventing ref access in this test case.
Related reports: Bug #50402 describes a similar bitmap/template initialization issue during repeated subquery execution. This case occurs during dynamic range scans, where a newly allocated, empty column bitmap is passed to ha_index_init(). The failing SELECT contains no subquery.
How to repeat:
Run MySQL_Index_Merge_Repro.sql in an empty test schema. The script contains the table definitions, data generation, EXPLAIN, and comparison queries. It creates 3 rows in t_c and 3,003 rows in t_step.
The failing query is:
SELECT COUNT(*)
FROM t_c c STRAIGHT_JOIN t_step step
ON c.cfm_idx = step.cfm_idx AND c.irt_nbr = step.irt_nbr
WHERE c.irt_nbr IN ('IRN001','IRN002','IRN003');
Expected: 3
Actual: 1
The same query returns 3 with either of these changes:
- Add /*+ SET_VAR(optimizer_switch='index_merge_intersection=off') */ after SELECT.
- Add IGNORE INDEX (idx_1, idx_2) after t_step step.
The detail query in the script also returns only the IRN001 row instead of all three matching rows.
Suggested fix:
In InitIndexRangeScan() (sql/range_optimizer/index_range_scan.cc), the new iterator's empty column_bitmap is installed before ha_index_init(). InnoDB builds its field retrieval template before the required fields are marked. On later dynamic range scans, the reused handler has sql_stat_start=false, so index_read() does not rebuild the template and required fields are not copied correctly.
Populate column_bitmap with the current index fields before ha_index_init(), and explicitly add all primary-key fields when required by the current handler for position(). Restore the saved read/write bitmap pointers on both success and error paths.
With this change, the original query returns all three rows. Seventeen related MTR tests passed on the community build, including the new regression test.
