Description:
When a table is read through a multi-valued index with ref access (for example 'x' MEMBER OF (col ->
'$')) and that table is on the inner side of a nested-loop join, each matching row is returned only
for the first outer row that reaches it. Later outer rows don't see it. The join silently loses rows,
with no error or warning.
The cause is in RefIterator<Reverse>::Init() (sql/iterators/ref_row_iterators.cc). The same code is
in RefOrNullIterator::Init():
m_first_record_since_init = true;
m_is_mvi_unique_filter_enabled = false;
if (table()->file->inited) return false;
...
if (table()->key_info[m_ref->key].flags & HA_MULTI_VALUED_KEY) {
table()->file->ha_extra(HA_EXTRA_ENABLE_UNIQUE_RECORD_FILTER);
table()->prepare_for_position();
m_is_mvi_unique_filter_enabled = true;
}
When the iterator is re-initialised for the next outer row, the index is still open, so Init()
returns early. That has two effects:
- The handler's unique record filter (handler::m_unique) is not reset. It still holds every row ID
returned for earlier outer rows, so filter_dup_records() discards those rows now.
- m_is_mvi_unique_filter_enabled becomes false. Read() then stops at the first HA_ERR_KEY_NOT_FOUND
from a filtered duplicate instead of skipping it, which cuts the scan short.
Range access (JSON_OVERLAPS, JSON_CONTAINS) is not affected, because IndexRangeScanIterator::Init()
calls ha_index_or_rnd_end() before each re-scan, and ha_index_end() resets the filter.
With production data the optimizer chooses this plan on its own, so reports silently return
incomplete results.
How to repeat:
CREATE TABLE o (id INT PRIMARY KEY);
CREATE TABLE t (id INT PRIMARY KEY, j JSON, KEY mvi ((CAST(j -> '$' AS CHAR(16) ARRAY))));
INSERT INTO o VALUES (1), (2), (3);
INSERT INTO t VALUES (1, '["a"]'), (2, '["a"]');
-- Correct: 3 x 2 = 6
SELECT COUNT(*) FROM o STRAIGHT_JOIN t IGNORE INDEX (mvi) WHERE 'a' MEMBER OF (t.j -> '$');
-- Wrong: returns 2
SELECT COUNT(*) FROM o STRAIGHT_JOIN t FORCE INDEX (mvi) WHERE 'a' MEMBER OF (t.j -> '$');
-- Plan: "Index lookup on t using mvi" with loops=3, but only 2 rows in total
EXPLAIN ANALYZE
SELECT COUNT(*) FROM o STRAIGHT_JOIN t FORCE INDEX (mvi) WHERE 'a' MEMBER OF (t.j -> '$');
-- Range access is not affected: returns 6
SELECT COUNT(*) FROM o STRAIGHT_JOIN t FORCE INDEX (mvi) WHERE JSON_OVERLAPS(t.j -> '$', '["a"]');
FORCE INDEX and STRAIGHT_JOIN only make the plan deterministic on tiny data. With larger tables the
optimizer chooses this plan without hints.
Suggested fix:
In RefIterator<Reverse>::Init() and RefOrNullIterator::Init(), reset the unique record filter on
every Init(), including when the index is already open. For example, move the MVI block ahead of the
early return:
m_first_record_since_init = true;
m_is_mvi_unique_filter_enabled = false;
if (!table()->file->inited &&
init_index(table(), table()->file, m_ref->key, m_use_order)) {
return true;
}
if (table()->key_info[m_ref->key].flags & HA_MULTI_VALUED_KEY) {
table()->file->ha_extra(HA_EXTRA_ENABLE_UNIQUE_RECORD_FILTER); // resets m_unique
table()->prepare_for_position();
m_is_mvi_unique_filter_enabled = true;
}
return set_record_buffer(table(), m_expected_rows);
ha_extra(HA_EXTRA_ENABLE_UNIQUE_RECORD_FILTER) already calls m_unique->reset(true) when the filter
exists. The set_record_buffer() call on re-init needs checking, since the original code skipped it on
that path.
Workarounds: IGNORE INDEX (<mvi>), or replace MEMBER OF with JSON_OVERLAPS(col -> '$',
JSON_ARRAY(value)) so the lookup uses range access.
Description: When a table is read through a multi-valued index with ref access (for example 'x' MEMBER OF (col -> '$')) and that table is on the inner side of a nested-loop join, each matching row is returned only for the first outer row that reaches it. Later outer rows don't see it. The join silently loses rows, with no error or warning. The cause is in RefIterator<Reverse>::Init() (sql/iterators/ref_row_iterators.cc). The same code is in RefOrNullIterator::Init(): m_first_record_since_init = true; m_is_mvi_unique_filter_enabled = false; if (table()->file->inited) return false; ... if (table()->key_info[m_ref->key].flags & HA_MULTI_VALUED_KEY) { table()->file->ha_extra(HA_EXTRA_ENABLE_UNIQUE_RECORD_FILTER); table()->prepare_for_position(); m_is_mvi_unique_filter_enabled = true; } When the iterator is re-initialised for the next outer row, the index is still open, so Init() returns early. That has two effects: - The handler's unique record filter (handler::m_unique) is not reset. It still holds every row ID returned for earlier outer rows, so filter_dup_records() discards those rows now. - m_is_mvi_unique_filter_enabled becomes false. Read() then stops at the first HA_ERR_KEY_NOT_FOUND from a filtered duplicate instead of skipping it, which cuts the scan short. Range access (JSON_OVERLAPS, JSON_CONTAINS) is not affected, because IndexRangeScanIterator::Init() calls ha_index_or_rnd_end() before each re-scan, and ha_index_end() resets the filter. With production data the optimizer chooses this plan on its own, so reports silently return incomplete results. How to repeat: CREATE TABLE o (id INT PRIMARY KEY); CREATE TABLE t (id INT PRIMARY KEY, j JSON, KEY mvi ((CAST(j -> '$' AS CHAR(16) ARRAY)))); INSERT INTO o VALUES (1), (2), (3); INSERT INTO t VALUES (1, '["a"]'), (2, '["a"]'); -- Correct: 3 x 2 = 6 SELECT COUNT(*) FROM o STRAIGHT_JOIN t IGNORE INDEX (mvi) WHERE 'a' MEMBER OF (t.j -> '$'); -- Wrong: returns 2 SELECT COUNT(*) FROM o STRAIGHT_JOIN t FORCE INDEX (mvi) WHERE 'a' MEMBER OF (t.j -> '$'); -- Plan: "Index lookup on t using mvi" with loops=3, but only 2 rows in total EXPLAIN ANALYZE SELECT COUNT(*) FROM o STRAIGHT_JOIN t FORCE INDEX (mvi) WHERE 'a' MEMBER OF (t.j -> '$'); -- Range access is not affected: returns 6 SELECT COUNT(*) FROM o STRAIGHT_JOIN t FORCE INDEX (mvi) WHERE JSON_OVERLAPS(t.j -> '$', '["a"]'); FORCE INDEX and STRAIGHT_JOIN only make the plan deterministic on tiny data. With larger tables the optimizer chooses this plan without hints. Suggested fix: In RefIterator<Reverse>::Init() and RefOrNullIterator::Init(), reset the unique record filter on every Init(), including when the index is already open. For example, move the MVI block ahead of the early return: m_first_record_since_init = true; m_is_mvi_unique_filter_enabled = false; if (!table()->file->inited && init_index(table(), table()->file, m_ref->key, m_use_order)) { return true; } if (table()->key_info[m_ref->key].flags & HA_MULTI_VALUED_KEY) { table()->file->ha_extra(HA_EXTRA_ENABLE_UNIQUE_RECORD_FILTER); // resets m_unique table()->prepare_for_position(); m_is_mvi_unique_filter_enabled = true; } return set_record_buffer(table(), m_expected_rows); ha_extra(HA_EXTRA_ENABLE_UNIQUE_RECORD_FILTER) already calls m_unique->reset(true) when the filter exists. The set_record_buffer() call on re-init needs checking, since the original code skipped it on that path. Workarounds: IGNORE INDEX (<mvi>), or replace MEMBER OF with JSON_OVERLAPS(col -> '$', JSON_ARRAY(value)) so the lookup uses range access.