>>>>> "Slava" == Slava Akhmechet <coffeemug@stripped> writes:
Slava> I'm debugging a join in our custom storage engine and I'm seeing
Slava> behavior I don't understand how to debug.
Slava> We have two tables, each one has three columns (foo, bar, baz). In
Slava> both tables foo is unindexed, bar is a primary key, and baz is a
Slava> secondary index. We're debugging a very simple join query:
Slava> select *
Slava> from t1, t2
Slava> where t1.foo = t2.foo
Slava> This query works perfectly fine. Similarly, if I change the condition
Slava> to any of the following, the query works fine as well:
Slava> t1.baz = t2.foo
Slava> t1.baz = t2.baz
Slava> However, the moment we introduce bar into the where condition (the
Slava> primary key), the query returns zero rows. I can select for these rows
Slava> separately, but they're not discovered in a join. The only difference
Slava> I see in EXPLAIN is that the primary key causes an eq_ref join (vs. a
Slava> ref join). I've looked through the logs, and it looks like end_send
Slava> (followed by send_data) never gets called when I introduce bar into
Slava> the where condition. However, the storage engine index code finds the
Slava> appropriate row just fine. I'm not sure what causes MySQL to decide to
Slava> ignore the match.
Slava> I'm not sure how to debug this. Does anyone have any pointers that might help?
Configure MySQL with --full-debug and start mysqld with:
mysqld --debug &
Then run both queries in a client
Check the difference in traces for the two queries in the trace file
/tmp/mysqld.trace. This should enable you to quickly find the problem.
For information of MariaDB, the community developed server based on
source code from MySQL, check out www.askmonty.org