Hi Jørgen,
Thanks for looking at the patch. See replies inline:
On 17/12/2010 12:15, Jorgen Loland wrote:
> Hi Olav,
>
> Thank you for the patch. See comments inline
>
> On 12/09/2010 03:06 PM, Olav Sandstaa wrote:
>> #At file:///export/home/tmp/mysql2/opt-bug58463-fix1/ based on
>> revid:tor.didriksen@stripped
>>
>> 3312 Olav Sandstaa 2010-12-09
>> Patch for Bug#58463 Error Can't find record on SELECT with
>> JOIN and ORDER BY
>>
>> This problem was caused by the server pushing down an index
>> condition on
>> the primary key as part of the optimization phase. Later,
>> during the
>> execution phase the server (due to a bug) changed to use a
>> secondary index
>> instead of the primary index for accessing the table. This
>> made MRR fail
>> in phase 3 due to not finding the expected records when
>> retrieving them
>> using the primary key due to the pushed index condition
>> filtering them away.
>>
>> This problem with the server accidentally switching to using a
>> secondary index will
>> be fixed by the fix for Bug#58456.
>>
>> This patch contains a test case based on the bug report and an
>> added assert to
>> the DS-MRR implementation that will trigger if a similar
>> situation should occur
>> again (DS-MRR used on a secondary index while an index
>> condition has been pushed
>> on the primary index for the same handler object).
>>
> (...)
>> === modified file 'sql/handler.cc'
>> --- a/sql/handler.cc 2010-11-18 16:34:56 +0000
>> +++ b/sql/handler.cc 2010-12-09 14:06:48 +0000
>> @@ -4683,6 +4683,16 @@
>> n_ranges, mode, buf);
>> DBUG_RETURN(retval);
>> }
>> +
>> + /*
>> + This assert will hit if we have pushed an index condition to the
>> + primary key index and then "changes our mind" and uses a different
>> + index for retrieving data with MRR.
>> + */
>
> typo: "change our mind" and use
I will correct this in an updated version of the patch.
>> + DBUG_ASSERT(!h->pushed_idx_cond ||
>> + h->pushed_idx_cond_keyno == h->active_index ||
>> + h->pushed_idx_cond_keyno != table->s->primary_key);
>> +
>
> Is this a problem when we switch from PK to secondary index only?
> Could it also be the case that we switch from one secondary index to
> another secondary index?
It is a problem for MRR only when switching from PK to a secondary
index. The reason for this is that MRR will use the PK in its last phase
for retrieving the complete records.
If we switch from one secondary index to another secondary index it
would not be a problem for MRR. If an index condition was pushed on the
first of these two secondary indexes it would not be used when MRR
retrieved the records since this first secondary index would not be used
by MRR (and thus, the pushed index condition would not be used).
Olav
