I'm debugging a join in our custom storage engine and I'm seeing
behavior I don't understand how to debug.
We have two tables, each one has three columns (foo, bar, baz). In
both tables foo is unindexed, bar is a primary key, and baz is a
secondary index. We're debugging a very simple join query:
from t1, t2
where t1.foo = t2.foo
This query works perfectly fine. Similarly, if I change the condition
to any of the following, the query works fine as well:
t1.baz = t2.foo
t1.baz = t2.baz
However, the moment we introduce bar into the where condition (the
primary key), the query returns zero rows. I can select for these rows
separately, but they're not discovered in a join. The only difference
I see in EXPLAIN is that the primary key causes an eq_ref join (vs. a
ref join). I've looked through the logs, and it looks like end_send
(followed by send_data) never gets called when I introduce bar into
the where condition. However, the storage engine index code finds the
appropriate row just fine. I'm not sure what causes MySQL to decide to
ignore the match.
I'm not sure how to debug this. Does anyone have any pointers that might help?
- Slava Akhmechet