~jk

In my rare spare time work on the http://jan.kneschke.de/2009/5/4/binlog-storage-engine-mysql-proxy-edition and for a few days I was wondering why my index-based JOINs didn't worked. With a index it works, with an index nothing is returned.

I narrowed it down to a simple test-case:

select * from test AS a JOIN test AS b USING (event_pos) WHERE a.event_pos IN (106);
...
1 row in set (0.03 sec)

EXPLAIN
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
|  1 | SIMPLE      | b     | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

vs.

select * from test AS a JOIN test AS b USING (event_pos) WHERE a.event_pos IN (106, 177);
0 row in set (0.03 sec)

EXPLAIN ...
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
|  1 | SIMPLE      | a     | range  | PRIMARY       | PRIMARY | 4       | NULL             |    2 | Using where | 
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.event_pos |    1 |             | 
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+

A self-JOIN on the PRIMARY KEY with two PK-values that exist.

The EXPLAINs look fine, eq_ref is what we want. But, we get no rows back. Looking at the debug output from MySQL and the wormhole storage engine it all looked fine. The right functions are called, the right return values are given.

At #mysql-dev Sergey Petrunia did take a look at my problem and we stepped through the code until I stumbled over:

static int
join_read_key(JOIN_TAB *tab)
{
    ...
    return table->status ? -1 : 0;
}

(gdb) p table->status
3 (aka STATUS_NO_RECORD)

Scanning the code in sql/sql_select.cc where that comes from:

case JT_EQ_REF:
  table->status=STATUS_NO_RECORD;
  if (tab->select) 
...

Who is supposed to change that value ? Me ? Checking the other storage engines it looks like there are two return codes: the obvious one and the hidden one. Depending on the execution plan (as seen above) one or the other is used. grmpf

Long story, simple fix:

=== modified file 'src/ha_wormhole.cc'
--- src/ha_wormhole.cc  2009-05-25 20:55:14 +0000
+++ src/ha_wormhole.cc  2009-05-28 13:28:06 +0000
@@ -1071,6 +1071,8 @@
        if (0 == wormhole_hook_call(L, 2)) {
                /* no thing pushed */
        } else if (lua_istable(L, -1)) {
+               table->status = 0; /** _WOOT_ ... we have to set it to 0 to make the eq_ref work */
+
                lua_getrow();
                lua_pop(L, 1); /* pop the table */
                DBUG_RETURN(0);

in the storage-engine's index_read() function and my JOINs on index work.