List:Commits« Previous MessageNext Message »
From:Evgeny Potemkin Date:January 14 2011 12:53pm
Subject:Re: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#59326
View as plain text  
Hi Ole John,

On 01/13/11 21:14, Ole John Aske wrote:
> On 13.01.2011 18:34, Evgeny Potemkin wrote:
>> Hi Ole John,
>>
>> The patch and the idea is good, thanks for looking into this! But, anyway, 
>> I'm unsure about this fix.
>> 1) Your reasoning is quite obvious and straight but the actual cost for some 
>> queries has grown (see changes in test results). This could be argued that 
>> our cost model is far from good, but then we have to measure the improvement 
>> (or regression) somehow. AFAIU this optimization should lead to less rows to 
>> be read, but there is no handler stats in your tests. Please add them. BTW it 
>> would be good to also show handler stats for queries with grown cost.
> Agree, I can try to add some handler stats to 'greedy' tests to prove that the 
> optimizer will choose the QEP with fewest handler calls with this patch.
> Re 'cost for some queries has grown': Remember these costs are only relative 
> numbers. If the cost for the chosen QEP grows, The cost for the discarded QEP 
> has grown even more.
I agree that the cost is an abstract value, but we have to take it into account 
since basically it's our cost model. Changing the way it is calculated is 
actually changing the cost model, and we have to be sure that the change brings 
an improvement and doesn't introduce regressions. Thus it's a small but yet 
serious change.
Re cost growth: I need to check few things. Will send my findings later.
BTW, the fix definitely will go to trunk only. It's too risky for the rest.
>
>> 2) There already were few cases when an obvious 100% improvement caused 
>> regressions in some cases. It would be good if you contact our QA and ask 
>> them to test this patch for perf. improvements/regressions on big tables.
> This optimizer patch will only affect 'star-join' QEPs.
The thing is that your fix doesn't have any conditions about star joins. Thus, 
even if the most effect would be seen on star joins, the change would affect all 
joins.
> I don't know the perf tests used by QA, but my general experience is that such 
> tests normally are
> very explicit about join ordering. Thus they normally leave little to the 
> optimizer in terms of reordering the joins.
QA uses random query generator which could generate any meaningless query;) 
Including star and non-star joins. I don't see any problem here.
>
>> 3) Roy has noticed that join buffering could significantly affect the time 
>> spent. It would be good if QA will test this patch with join cache turned off 
>> and on (only test with default value would be enough).
>
> I don't really see the connection with this bug here: Sure, there are 
> anomalities in the result Roy observes wrt. increased runtime when using join 
> cache.
> However these results are *without* my patch. Furthermore, when my patch is 
> applied  the fastest of the QEPs are chosen even for Roy's test.
>
> My opinion is that the performance regression with join cache should be 
> reported as another bug.
I agree that the join cache is a completely separate problem and the goal isn't 
to benchmark join cache. The idea was to see base difference(when join cache 
turned off) against unmodified server and the difference on a 'typical' server 
where join cache level is set to default.
>
> Regards Ole John
>
>
>>
>> Regards, Evgen.
>>
>> On 01/06/11 15:21, Ole John Aske wrote:
>>> #At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1/ based on 
>>> revid:nirbhay.choubey@stripped
>>>
>>>   3534 Ole John Aske    2011-01-06
>>>        Fix for bug#59326: Greedy optimizer produce stupid query execution 
>>> plans.
>>>
>>>        The 'greedy' query plan optimizer fails to consider the size of
>>>        intermediate query result when calculating the accumulated
>>>        'cost' of a query. This may result in slowly executing queries
>>>        when there are much faster QEP's available.
>>>
>>>        When there are multiple JOIN orders available, it would normally be
>>>        optimal to join the tables producing the smallest intermediate
> results
>>>        first. This means that any EQ_REF should be joined before REF's,
> which
>>>        in turn should be joined prior to full table scans (ALL).
>>>
>>>        This fix add the 'CPU-cost' of processing 'current_record_count' 
>>> records at
>>>        each level to 'current_read_time' *before* it is used as 'accumulated
> 
>>> cost'
>>>        argument to recursive best_extension_by_limited_search() calls. This 
>>> ensures
>>>        that the cost of a huge join-fanout early in the QEP is correctly 
>>> reflected
>>>        in the cost of the final QEP.
>>>
>>>        Several new MTR tests has been added to greedy_optimizer.test to 
>>> ensure that
>>>        QEP plans joins the tables in the order EQ_REF, REF, ALL whenever 
>>> possible.
>>>
>>>        Also updated result files for several test where the QEP or
> 'query_cost'
>>>        changes due to this fix. The changed query plans has been verified to
> 
>>> be an
>>>        improvement over the original QEPs.
>>>       @ mysql-test/r/greedy_optimizer.result
>>>          Several QEP changed (improved) due to changes in greedy optimizer.
>>>          Generally EQ_REF and REF's are now executed prior to table
> scans(ALL)
>>>          whenever possible.
>>>
>>>          Added result for new testcases
>>>       @ mysql-test/r/select.result
>>>          Changed order of result rows due to changed QEP
>>>       @ mysql-test/r/subselect.result
>>>          Changed (improved) QEP.
>>>
>>>      modified:
>>>        mysql-test/r/greedy_optimizer.result
>>>        mysql-test/r/join.result
>>>        mysql-test/r/select.result
>>>        mysql-test/r/status.result
>>>        mysql-test/r/subselect.result
>>>        mysql-test/t/greedy_optimizer.test
>>>        sql/sql_select.cc
>>> === modified file 'mysql-test/r/greedy_optimizer.result'
>>> --- a/mysql-test/r/greedy_optimizer.result    2009-01-16 10:45:17 +0000
>>> +++ b/mysql-test/r/greedy_optimizer.result    2011-01-06 12:21:00 +0000
>>> @@ -200,28 +200,28 @@ select @@optimizer_search_depth;
>>>   0
>>>   explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = 
>>> t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and 
>>> t5.c52 = t6.c61 and t6.c62 = t7.c71;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> -1    SIMPLE    t1    ALL    NULL    NULL    NULL    NULL    3
>>> -1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>> +1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t2.c22    1
>>>   1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t4.c42    1
>>>   1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t6.c62    
>>> 1    Using index
>>> +1    SIMPLE    t1    ALL    NULL    NULL    NULL    NULL    3    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    821.837037
>>> +Last_query_cost    1371.437037
>>>   explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = 
>>> t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and 
>>> t5.c52 = t6.c61 and t6.c62 = t7.c71;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> -1    SIMPLE    t1    ALL    NULL    NULL    NULL    NULL    3
>>> -1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>> +1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t2.c22    1
>>>   1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t4.c42    1
>>>   1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t6.c62    
>>> 1    Using index
>>> +1    SIMPLE    t1    ALL    NULL    NULL    NULL    NULL    3    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    821.837037
>>> +Last_query_cost    1371.437037
>>>   explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6
>>> @@ -229,11 +229,11 @@ id    select_type    table    type    possible_keys
>>>   1    SIMPLE    t1    eq_ref    PRIMARY    PRIMARY    4    test.t2.c21    
>>> 1    Using where
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using index
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using index
>>> -1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using index
>>> +1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    289.418727
>>> +Last_query_cost    362.618727
>>>   explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6
>>> @@ -241,11 +241,11 @@ id    select_type    table    type    possible_keys
>>>   1    SIMPLE    t1    eq_ref    PRIMARY    PRIMARY    4    test.t2.c21    
>>> 1    Using where
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using index
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using index
>>> -1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using index
>>> +1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    289.418727
>>> +Last_query_cost    362.618727
>>>   explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = 
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and 
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6
>>> @@ -253,11 +253,11 @@ id    select_type    table    type    possible_keys
>>>   1    SIMPLE    t1    eq_ref    PRIMARY    PRIMARY    4    test.t2.c21    
>>> 1    Using where
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using where
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using where
>>> -1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using where
>>> +1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    289.418727
>>> +Last_query_cost    362.618727
>>>   explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = 
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and 
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6
>>> @@ -265,11 +265,11 @@ id    select_type    table    type    possible_keys
>>>   1    SIMPLE    t1    eq_ref    PRIMARY    PRIMARY    4    test.t2.c21    
>>> 1    Using where
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using where
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using where
>>> -1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using where
>>> +1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    289.418727
>>> +Last_query_cost    362.618727
>>>   set optimizer_search_depth=1;
>>>   select @@optimizer_search_depth;
>>>   @@optimizer_search_depth
>>> @@ -352,28 +352,28 @@ select @@optimizer_search_depth;
>>>   62
>>>   explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = 
>>> t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and 
>>> t5.c52 = t6.c61 and t6.c62 = t7.c71;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> -1    SIMPLE    t1    ALL    NULL    NULL    NULL    NULL    3
>>> -1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>> +1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t2.c22    1
>>>   1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t4.c42    1
>>>   1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t6.c62    
>>> 1    Using index
>>> +1    SIMPLE    t1    ALL    NULL    NULL    NULL    NULL    3    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    821.837037
>>> +Last_query_cost    1371.437037
>>>   explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = 
>>> t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and 
>>> t5.c52 = t6.c61 and t6.c62 = t7.c71;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> -1    SIMPLE    t1    ALL    NULL    NULL    NULL    NULL    3
>>> -1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>> +1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t2.c22    1
>>>   1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t4.c42    1
>>>   1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t6.c62    
>>> 1    Using index
>>> +1    SIMPLE    t1    ALL    NULL    NULL    NULL    NULL    3    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    821.837037
>>> +Last_query_cost    1371.437037
>>>   explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6
>>> @@ -381,11 +381,11 @@ id    select_type    table    type    possible_keys
>>>   1    SIMPLE    t1    eq_ref    PRIMARY    PRIMARY    4    test.t2.c21    
>>> 1    Using where
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using index
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using index
>>> -1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using index
>>> +1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    289.418727
>>> +Last_query_cost    362.618727
>>>   explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6
>>> @@ -393,11 +393,11 @@ id    select_type    table    type    possible_keys
>>>   1    SIMPLE    t1    eq_ref    PRIMARY    PRIMARY    4    test.t2.c21    
>>> 1    Using where
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using index
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using index
>>> -1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using index
>>> +1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    289.418727
>>> +Last_query_cost    362.618727
>>>   explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = 
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and 
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6
>>> @@ -405,11 +405,11 @@ id    select_type    table    type    possible_keys
>>>   1    SIMPLE    t1    eq_ref    PRIMARY    PRIMARY    4    test.t2.c21    
>>> 1    Using where
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using where
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using where
>>> -1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using where
>>> +1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    289.418727
>>> +Last_query_cost    362.618727
>>>   explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = 
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and 
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6
>>> @@ -417,11 +417,11 @@ id    select_type    table    type    possible_keys
>>>   1    SIMPLE    t1    eq_ref    PRIMARY    PRIMARY    4    test.t2.c21    
>>> 1    Using where
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using where
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using where
>>> -1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using where
>>> +1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    289.418727
>>> +Last_query_cost    362.618727
>>>   set optimizer_prune_level=1;
>>>   select @@optimizer_prune_level;
>>>   @@optimizer_prune_level
>>> @@ -441,7 +441,7 @@ id    select_type    table    type    possible_keys
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t6.c62    
>>> 1    Using index
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    821.837037
>>> +Last_query_cost    1693.637037
>>>   explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = 
>>> t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and 
>>> t5.c52 = t6.c61 and t6.c62 = t7.c71;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t1    ALL    NULL    NULL    NULL    NULL    3
>>> @@ -453,55 +453,55 @@ id    select_type    table    type    possible_keys
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t6.c62    
>>> 1    Using index
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    821.837037
>>> +Last_query_cost    1693.637037
>>>   explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t1    ALL    PRIMARY    NULL    NULL    NULL    3
>>> -1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using index
>>> -1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using index
>>> -1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using index
>>> +1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>> +1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>> +1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    794.837037
>>> +Last_query_cost    844.037037
>>>   explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t1    ALL    PRIMARY    NULL    NULL    NULL    3
>>> -1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using index
>>> -1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using index
>>> -1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using index
>>> +1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>> +1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>> +1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    794.837037
>>> +Last_query_cost    844.037037
>>>   explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = 
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and 
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t1    ALL    PRIMARY    NULL    NULL    NULL    3
>>> +1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    1
>>> +1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using where
>>> +1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using where
>>>   1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>> -1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using where
>>>   1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>> -1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using where
>>>   1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>> -1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using where
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    794.837037
>>> +Last_query_cost    844.037037
>>>   explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = 
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and 
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t1    ALL    PRIMARY    NULL    NULL    NULL    3
>>> +1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    1
>>> +1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using where
>>> +1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using where
>>>   1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>> -1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using where
>>>   1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>> -1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using where
>>>   1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>> -1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using where
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    794.837037
>>> +Last_query_cost    844.037037
>>>   set optimizer_search_depth=1;
>>>   select @@optimizer_search_depth;
>>>   @@optimizer_search_depth
>>> @@ -593,7 +593,7 @@ id    select_type    table    type    possible_keys
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t6.c62    
>>> 1    Using index
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    821.837037
>>> +Last_query_cost    1693.637037
>>>   explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = 
>>> t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and 
>>> t5.c52 = t6.c61 and t6.c62 = t7.c71;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t1    ALL    NULL    NULL    NULL    NULL    3
>>> @@ -605,55 +605,55 @@ id    select_type    table    type    possible_keys
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t6.c62    
>>> 1    Using index
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    821.837037
>>> +Last_query_cost    1693.637037
>>>   explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t1    ALL    PRIMARY    NULL    NULL    NULL    3
>>> -1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using index
>>> -1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using index
>>> -1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using index
>>> +1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>> +1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>> +1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    794.837037
>>> +Last_query_cost    844.037037
>>>   explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t1    ALL    PRIMARY    NULL    NULL    NULL    3
>>> -1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using index
>>> -1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using index
>>> -1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using index
>>> +1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>> +1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>> +1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    794.837037
>>> +Last_query_cost    844.037037
>>>   explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = 
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and 
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t1    ALL    PRIMARY    NULL    NULL    NULL    3
>>> +1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    1
>>> +1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using where
>>> +1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using where
>>>   1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>> -1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using where
>>>   1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>> -1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using where
>>>   1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>> -1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using where
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    794.837037
>>> +Last_query_cost    844.037037
>>>   explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = 
>>> t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and 
>>> t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 
>>> and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = 
>>> t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and 
>>> t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 
>>> and t5.c53 = t7.c75 and t6.c62 = t7.c76;
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>>   1    SIMPLE    t1    ALL    PRIMARY    NULL    NULL    NULL    3
>>> +1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    1
>>> +1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using where
>>> +1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using where
>>>   1    SIMPLE    t2    ALL    NULL    NULL    NULL    NULL    6    Using 
>>> where; Using join buffer
>>> -1    SIMPLE    t3    eq_ref    PRIMARY    PRIMARY    4    test.t1.c12    
>>> 1    Using where
>>>   1    SIMPLE    t4    ALL    NULL    NULL    NULL    NULL    12    Using 
>>> where; Using join buffer
>>> -1    SIMPLE    t5    eq_ref    PRIMARY    PRIMARY    4    test.t1.c14    
>>> 1    Using where
>>>   1    SIMPLE    t6    ALL    NULL    NULL    NULL    NULL    18    Using 
>>> where; Using join buffer
>>> -1    SIMPLE    t7    eq_ref    PRIMARY    PRIMARY    4    test.t1.c16    
>>> 1    Using where
>>>   show status like 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    794.837037
>>> +Last_query_cost    844.037037
>>>   drop table t1,t2,t3,t4,t5,t6,t7;
>>>   CREATE TABLE t1 (a int, b int, d int, i int);
>>>   INSERT INTO t1 VALUES (1,1,1,1);
>>> @@ -729,6 +729,261 @@ t4 LEFT JOIN (t5 JOIN t5_1 ON t5.l = t5_
>>>   ;
>>>   1
>>>   1
>>> -SET optimizer_search_depth = DEFAULT;
>>>   DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1;
>>> -End of 5.0 tests
>>> +#
>>> +# Bug #59326: Greedy optimizer produce stupid query execution plans.
>>> +#
>>> +CREATE TABLE t10(
>>> +K INT NOT NULL AUTO_INCREMENT,
>>> +I INT,
>>> +PRIMARY KEY(K)
>>> +);
>>> +INSERT INTO t10(I) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
>>> +CREATE TABLE t100 LIKE t10;
>>> +INSERT INTO t100(I)
>>> +SELECT X.I FROM t10 AS X,t10 AS Y;
>>> +CREATE TABLE t10000 LIKE t10;
>>> +INSERT INTO t10000(I)
>>> +SELECT X.I FROM t100 AS X, t100 AS Y;
>>> +EXPLAIN SELECT * FROM t10,t100,t10000;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    t100    ALL    NULL    NULL    NULL    NULL    100    Using 
>>> join buffer
>>> +1    SIMPLE    t10000    ALL    NULL    NULL    NULL    NULL    10000    
>>> Using join buffer
>>> +EXPLAIN SELECT * FROM t10,t10000,t100;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    t100    ALL    NULL    NULL    NULL    NULL    100    Using 
>>> join buffer
>>> +1    SIMPLE    t10000    ALL    NULL    NULL    NULL    NULL    10000    
>>> Using join buffer
>>> +EXPLAIN SELECT * FROM t100,t10,t10000;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    t100    ALL    NULL    NULL    NULL    NULL    100    Using 
>>> join buffer
>>> +1    SIMPLE    t10000    ALL    NULL    NULL    NULL    NULL    10000    
>>> Using join buffer
>>> +EXPLAIN SELECT * FROM t100,t10000,t10;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    t100    ALL    NULL    NULL    NULL    NULL    100    Using 
>>> join buffer
>>> +1    SIMPLE    t10000    ALL    NULL    NULL    NULL    NULL    10000    
>>> Using join buffer
>>> +EXPLAIN SELECT * FROM t10000,t10,t100;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    t100    ALL    NULL    NULL    NULL    NULL    100    Using 
>>> join buffer
>>> +1    SIMPLE    t10000    ALL    NULL    NULL    NULL    NULL    10000    
>>> Using join buffer
>>> +EXPLAIN SELECT * FROM t10000,t100,t10;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    t100    ALL    NULL    NULL    NULL    NULL    100    Using 
>>> join buffer
>>> +1    SIMPLE    t10000    ALL    NULL    NULL    NULL    NULL    10000    
>>> Using join buffer
>>> +EXPLAIN SELECT * FROM t10,t100,t10000
>>> +WHERE t100.K=t10.I
>>> +AND t10000.K=t10.K;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    PRIMARY    NULL    NULL    NULL    10
>>> +1    SIMPLE    t100    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1
>>> +1    SIMPLE    t10000    eq_ref    PRIMARY    PRIMARY    4    test.t10.K   
> 1
>>> +EXPLAIN SELECT * FROM t10,t10000,t100
>>> +WHERE t100.K=t10.I
>>> +AND t10000.K=t10.K;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    PRIMARY    NULL    NULL    NULL    10
>>> +1    SIMPLE    t100    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1
>>> +1    SIMPLE    t10000    eq_ref    PRIMARY    PRIMARY    4    test.t10.K   
> 1
>>> +EXPLAIN SELECT * FROM t100,t10,t10000
>>> +WHERE t100.K=t10.I
>>> +AND t10000.K=t10.K;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    PRIMARY    NULL    NULL    NULL    10
>>> +1    SIMPLE    t100    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1
>>> +1    SIMPLE    t10000    eq_ref    PRIMARY    PRIMARY    4    test.t10.K   
> 1
>>> +EXPLAIN SELECT * FROM t100,t10000,t10
>>> +WHERE t100.K=t10.I
>>> +AND t10000.K=t10.K;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    PRIMARY    NULL    NULL    NULL    10
>>> +1    SIMPLE    t100    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1
>>> +1    SIMPLE    t10000    eq_ref    PRIMARY    PRIMARY    4    test.t10.K   
> 1
>>> +EXPLAIN SELECT * FROM t10000,t10,t100
>>> +WHERE t100.K=t10.I
>>> +AND t10000.K=t10.K;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    PRIMARY    NULL    NULL    NULL    10
>>> +1    SIMPLE    t100    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1
>>> +1    SIMPLE    t10000    eq_ref    PRIMARY    PRIMARY    4    test.t10.K   
> 1
>>> +EXPLAIN SELECT * FROM t10000,t100,t10
>>> +WHERE t100.K=t10.I
>>> +AND t10000.K=t10.K;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    PRIMARY    NULL    NULL    NULL    10
>>> +1    SIMPLE    t100    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1
>>> +1    SIMPLE    t10000    eq_ref    PRIMARY    PRIMARY    4    test.t10.K   
> 1
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.K=t10.I
>>> +AND t10000.I=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    t100    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    
>>> 1    Using index
>>> +1    SIMPLE    t10000    ALL    NULL    NULL    NULL    NULL    10000    
>>> Using where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.K=t10.I
>>> +AND t10000.I=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    t100    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    
>>> 1    Using index
>>> +1    SIMPLE    t10000    ALL    NULL    NULL    NULL    NULL    10000    
>>> Using where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    t10000    eq_ref    PRIMARY    PRIMARY    4    test.t10.I   
> 
>>> 1    Using index
>>> +1    SIMPLE    t100    ALL    NULL    NULL    NULL    NULL    100    Using 
>>> where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    t10000    eq_ref    PRIMARY    PRIMARY    4    test.t10.I   
> 
>>> 1    Using index
>>> +1    SIMPLE    t100    ALL    NULL    NULL    NULL    NULL    100    Using 
>>> where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t100.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    t10000    eq_ref    PRIMARY    PRIMARY    4    test.t10.I   
> 
>>> 1    Using index
>>> +1    SIMPLE    t100    ALL    NULL    NULL    NULL    NULL    100    Using 
>>> where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t100.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    t10000    eq_ref    PRIMARY    PRIMARY    4    test.t10.I   
> 
>>> 1    Using index
>>> +1    SIMPLE    t100    ALL    NULL    NULL    NULL    NULL    100    Using 
>>> where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    X    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1   
> 
>>> Using index
>>> +1    SIMPLE    Y    index    NULL    PRIMARY    4    NULL    10000    Using
> 
>>> index; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE Y.K=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    Y    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1   
> 
>>> Using index
>>> +1    SIMPLE    X    index    NULL    PRIMARY    4    NULL    10000    Using
> 
>>> index; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> +AND Y.I=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    X    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1   
> 
>>> Using index
>>> +1    SIMPLE    Y    ALL    NULL    NULL    NULL    NULL    10000    Using 
>>> where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=t10.I
>>> +AND Y.K=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    Y    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1   
> 
>>> Using index
>>> +1    SIMPLE    X    ALL    NULL    NULL    NULL    NULL    10000    Using 
>>> where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> +AND Y.I=X.K;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    X    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1   
> 
>>> Using index
>>> +1    SIMPLE    Y    ALL    NULL    NULL    NULL    NULL    10000    Using 
>>> where; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=Y.K
>>> +AND Y.K=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    ALL    NULL    NULL    NULL    NULL    10
>>> +1    SIMPLE    Y    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1   
> 
>>> Using index
>>> +1    SIMPLE    X    ALL    NULL    NULL    NULL    NULL    10000    Using 
>>> where; Using join buffer
>>> +CREATE INDEX IX ON t10(I);
>>> +CREATE INDEX IX ON t100(I);
>>> +CREATE INDEX IX ON t10000(I);
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.K=t10.I
>>> +AND t10000.I=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    index    IX    IX    5    NULL    10    Using index
>>> +1    SIMPLE    t100    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    
>>> 1    Using index
>>> +1    SIMPLE    t10000    ref    IX    IX    5    test.t100.K    1000    
>>> Using where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.K=t10.I
>>> +AND t10000.I=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    index    IX    IX    5    NULL    10    Using index
>>> +1    SIMPLE    t100    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    
>>> 1    Using index
>>> +1    SIMPLE    t10000    ref    IX    IX    5    test.t100.K    1000    
>>> Using where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    index    IX    IX    5    NULL    10    Using index
>>> +1    SIMPLE    t10000    eq_ref    PRIMARY    PRIMARY    4    test.t10.I   
> 
>>> 1    Using index
>>> +1    SIMPLE    t100    ref    IX    IX    5    test.t10.I    10    Using 
>>> where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    index    IX    IX    5    NULL    10    Using index
>>> +1    SIMPLE    t10000    eq_ref    PRIMARY    PRIMARY    4    test.t10.I   
> 
>>> 1    Using index
>>> +1    SIMPLE    t100    ref    IX    IX    5    test.t10.I    10    Using 
>>> where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t100.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    index    IX    IX    5    NULL    10    Using index
>>> +1    SIMPLE    t10000    eq_ref    PRIMARY    PRIMARY    4    test.t10.I   
> 
>>> 1    Using index
>>> +1    SIMPLE    t100    ref    IX    IX    5    test.t10.I    10    Using 
>>> where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> +AND t10000.K=t100.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    index    IX    IX    5    NULL    10    Using index
>>> +1    SIMPLE    t10000    eq_ref    PRIMARY    PRIMARY    4    test.t10.I   
> 
>>> 1    Using index
>>> +1    SIMPLE    t100    ref    IX    IX    5    test.t10.I    10    Using 
>>> where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    index    IX    IX    5    NULL    10    Using index
>>> +1    SIMPLE    X    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1   
> 
>>> Using index
>>> +1    SIMPLE    Y    index    NULL    PRIMARY    4    NULL    10000    Using
> 
>>> index; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE Y.K=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    index    IX    IX    5    NULL    10    Using index
>>> +1    SIMPLE    Y    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1   
> 
>>> Using index
>>> +1    SIMPLE    X    index    NULL    PRIMARY    4    NULL    10000    Using
> 
>>> index; Using join buffer
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> +AND Y.I=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    index    IX    IX    5    NULL    10    Using index
>>> +1    SIMPLE    X    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1   
> 
>>> Using index
>>> +1    SIMPLE    Y    ref    IX    IX    5    test.X.K    1000    Using 
>>> where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=t10.I
>>> +AND Y.K=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    index    IX    IX    5    NULL    10    Using index
>>> +1    SIMPLE    Y    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1   
> 
>>> Using index
>>> +1    SIMPLE    X    ref    IX    IX    5    test.t10.I    1000    Using 
>>> where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> +AND Y.I=X.K;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    index    IX    IX    5    NULL    10    Using index
>>> +1    SIMPLE    X    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1   
> 
>>> Using index
>>> +1    SIMPLE    Y    ref    IX    IX    5    test.X.K    1000    Using 
>>> where; Using index
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=Y.K
>>> +AND Y.K=t10.I;
>>> +id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    Extra
>>> +1    SIMPLE    t10    index    IX    IX    5    NULL    10    Using index
>>> +1    SIMPLE    Y    eq_ref    PRIMARY    PRIMARY    4    test.t10.I    1   
> 
>>> Using index
>>> +1    SIMPLE    X    ref    IX    IX    5    test.Y.K    1000    Using 
>>> where; Using index
>>> +DROP TABLE t10,t100,t10000;
>>> +SET OPTIMIZER_SEARCH_DEPTH = DEFAULT;
>>> +END OF 5.0 TESTS
>>>
>>> === modified file 'mysql-test/r/join.result'
>>> --- a/mysql-test/r/join.result    2010-11-22 21:34:22 +0000
>>> +++ b/mysql-test/r/join.result    2011-01-06 12:21:00 +0000
>>> @@ -894,7 +894,7 @@ id    select_type    table    type    possible_keys
>>>   1    SIMPLE    B    eq_ref    PRIMARY    PRIMARY    4    test.A.b    1
>>>   show status like '%cost%';
>>>   Variable_name    Value
>>> -Last_query_cost    24.016090
>>> +Last_query_cost    28.016090
>>>   select '^^: The above should be ~= 20 + cost(select * from t1). Value less
> 
>>> than 20 is an error' Z;
>>>   Z
>>>   ^^: The above should be ~= 20 + cost(select * from t1). Value less than 20
> 
>>> is an error
>>>
>>> === modified file 'mysql-test/r/select.result'
>>> --- a/mysql-test/r/select.result    2010-06-24 08:00:48 +0000
>>> +++ b/mysql-test/r/select.result    2011-01-06 12:21:00 +0000
>>> @@ -2204,10 +2204,10 @@ a    a
>>>   select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on 
>>> t1.a>1;
>>>   a    a
>>>   2    1
>>> -3    1
>>>   2    2
>>> -3    2
>>>   2    3
>>> +3    1
>>> +3    2
>>>   3    3
>>>   select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using 
>>> ( a );
>>>   a
>>>
>>> === modified file 'mysql-test/r/status.result'
>>> --- a/mysql-test/r/status.result    2009-06-10 08:58:36 +0000
>>> +++ b/mysql-test/r/status.result    2011-01-06 12:21:00 +0000
>>> @@ -149,7 +149,7 @@ a    a
>>>   1    1
>>>   SHOW SESSION STATUS LIKE 'Last_query_cost';
>>>   Variable_name    Value
>>> -Last_query_cost    4.805836
>>> +Last_query_cost    5.205836
>>>   DROP TABLE t1;
>>>   show status like 'com_show_status';
>>>   Variable_name    Value
>>>
>>> === modified file 'mysql-test/r/subselect.result'
>>> --- a/mysql-test/r/subselect.result    2010-11-08 10:55:43 +0000
>>> +++ b/mysql-test/r/subselect.result    2011-01-06 12:21:00 +0000
>>> @@ -1353,10 +1353,10 @@ a
>>>   explain extended select * from t2 where t2.a in (select t1.a from t1,t3 
>>> where t1.b=t3.a);
>>>   id    select_type    table    type    possible_keys    key    key_len    
>>> ref    rows    filtered    Extra
>>>   1    PRIMARY    t2    index    NULL    a    5    NULL    4    100.00    
>>> Using where; Using index
>>> -2    DEPENDENT SUBQUERY    t1    ref    a    a    5    func    1001    
>>> 100.00    Using where; Using index
>>> -2    DEPENDENT SUBQUERY    t3    index    a    a    5    NULL    3    
>>> 100.00    Using where; Using index; Using join buffer
>>> +2    DEPENDENT SUBQUERY    t3    index    a    a    5    NULL    3    
>>> 100.00    Using index
>>> +2    DEPENDENT SUBQUERY    t1    ref    a    a    10    func,test.t3.a    
>>> 1167    100.01    Using where; Using index
>>>   Warnings:
>>> -Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` 
>>> where<in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 from
> `test`.`t1` join 
>>> `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and 
>>> (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
>>> +Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t2` 
>>> where<in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 from
> `test`.`t1` join 
>>> `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`a`) and 
>>> (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
>>>   insert into t1 values (3,31);
>>>   select * from t2 where t2.a in (select a from t1 where t1.b<>  30);
>>>   a
>>>
>>> === modified file 'mysql-test/t/greedy_optimizer.test'
>>> --- a/mysql-test/t/greedy_optimizer.test    2009-01-16 10:45:17 +0000
>>> +++ b/mysql-test/t/greedy_optimizer.test    2011-01-06 12:21:00 +0000
>>> @@ -380,7 +380,145 @@ LEFT JOIN (
>>>   ) ON t4.d = t1.d
>>>   ;
>>>
>>> -SET optimizer_search_depth = DEFAULT;
>>>   DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1;
>>>
>>> ---echo End of 5.0 tests
>>> +
>>> +--echo #
>>> +--echo # Bug #59326: Greedy optimizer produce stupid query execution plans.
>>> +--echo #
>>> +
>>> +CREATE TABLE t10(
>>> +  K INT NOT NULL AUTO_INCREMENT,
>>> +  I INT,
>>> +  PRIMARY KEY(K)
>>> +);
>>> +INSERT INTO t10(I) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
>>> +
>>> +CREATE TABLE t100 LIKE t10;
>>> +INSERT INTO t100(I)
>>> +SELECT X.I FROM t10 AS X,t10 AS Y;
>>> +
>>> +CREATE TABLE t10000 LIKE t10;
>>> +INSERT INTO t10000(I)
>>> +SELECT X.I FROM t100 AS X, t100 AS Y;
>>> +
>>> +## All crossproducts should be executed in order t10,t100,t10000
>>> +EXPLAIN SELECT * FROM t10,t100,t10000;
>>> +EXPLAIN SELECT * FROM t10,t10000,t100;
>>> +EXPLAIN SELECT * FROM t100,t10,t10000;
>>> +EXPLAIN SELECT * FROM t100,t10000,t10;
>>> +EXPLAIN SELECT * FROM t10000,t10,t100;
>>> +EXPLAIN SELECT * FROM t10000,t100,t10;
>>> +
>>> +
>>> +## Ordering between T100,T10000 EQ-joined T10 should
>>> +## be with smallest table joined first
>>> +EXPLAIN SELECT * FROM t10,t100,t10000
>>> +WHERE t100.K=t10.I
>>> +  AND t10000.K=t10.K;
>>> +EXPLAIN SELECT * FROM t10,t10000,t100
>>> +WHERE t100.K=t10.I
>>> +  AND t10000.K=t10.K;
>>> +EXPLAIN SELECT * FROM t100,t10,t10000
>>> +WHERE t100.K=t10.I
>>> +  AND t10000.K=t10.K;
>>> +EXPLAIN SELECT * FROM t100,t10000,t10
>>> +WHERE t100.K=t10.I
>>> +  AND t10000.K=t10.K;
>>> +EXPLAIN SELECT * FROM t10000,t10,t100
>>> +WHERE t100.K=t10.I
>>> +  AND t10000.K=t10.K;
>>> +EXPLAIN SELECT * FROM t10000,t100,t10
>>> +WHERE t100.K=t10.I
>>> +  AND t10000.K=t10.K;
>>> +
>>> +## EQ_REF Should be executed before table scan(ALL)
>>> +## - Independent of #records in table being EQ_REF-joined
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.K=t10.I
>>> +  AND t10000.I=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.K=t10.I
>>> +  AND t10000.I=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> +  AND t10000.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> +  AND t10000.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> +  AND t10000.K=t100.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> +  AND t10000.K=t100.I;
>>> +
>>> +## EQ_REF&  Cross join two instances of t10000 with t10:
>>> +## Always EQ_REF join first before producing cross product
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE Y.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> +  AND Y.I=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=t10.I
>>> +  AND Y.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> +  AND Y.I=X.K;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=Y.K
>>> +  AND Y.K=t10.I;
>>> +
>>> +## Create indexes to test REF access
>>> +CREATE INDEX IX ON t10(I);
>>> +CREATE INDEX IX ON t100(I);
>>> +CREATE INDEX IX ON t10000(I);
>>> +
>>> +## EQ_REF Should be executed before 'REF'
>>> +## - Independent of #records in table being EQ_REF-joined
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.K=t10.I
>>> +  AND t10000.I=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.K=t10.I
>>> +  AND t10000.I=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> +  AND t10000.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> +  AND t10000.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t100,t10000
>>> +WHERE t100.I=t10.I
>>> +  AND t10000.K=t100.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000,t100
>>> +WHERE t100.I=t10.I
>>> +  AND t10000.K=t100.I;
>>> +
>>> +## EQ_REF&  Cross join two instances of t10000 with t10:
>>> +## Always EQ_REF join first before producing cross product
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE Y.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> +  AND Y.I=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=t10.I
>>> +  AND Y.K=t10.I;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.K=t10.I
>>> +  AND Y.I=X.K;
>>> +EXPLAIN SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
>>> +WHERE X.I=Y.K
>>> +  AND Y.K=t10.I;
>>> +
>>> +DROP TABLE t10,t100,t10000;
>>> +
>>> +SET OPTIMIZER_SEARCH_DEPTH = DEFAULT;
>>> +
>>> +--ECHO END OF 5.0 TESTS
>>>
>>> === modified file 'sql/sql_select.cc'
>>> --- a/sql/sql_select.cc    2010-12-28 23:47:05 +0000
>>> +++ b/sql/sql_select.cc    2011-01-06 12:21:00 +0000
>>> @@ -5377,6 +5377,8 @@ best_extension_by_limited_search(JOIN
>>>           }
>>>         }
>>>
>>> +      current_read_time+= current_record_count / (double) TIME_FOR_COMPARE;
>>> +
>>>         if ( (search_depth>  1)&&  (remaining_tables& 
> ~real_table_bit) )
>>>         { /* Recursively expand the current partial plan */
>>>           swap_variables(JOIN_TAB*, join->best_ref[idx], *pos);
>>> @@ -5395,7 +5397,6 @@ best_extension_by_limited_search(JOIN
>>>             'join' is either the best partial QEP with 'search_depth' 
>>> relations,
>>>             or the best complete QEP so far, whichever is smaller.
>>>           */
>>> -        current_read_time+= current_record_count / (double)
> TIME_FOR_COMPARE;
>>>           if (join->sort_by_table&&
>>>               join->sort_by_table !=
>>>               join->positions[join->const_tables].table->table)
>>>
>>>
>>>
>>>
>>
>
>

Thread
bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#59326Ole John Aske6 Jan
  • Re: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#59326Evgeny Potemkin13 Jan
    • Re: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#59326Ole John Aske13 Jan
    • Re: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#59326Ole John Aske13 Jan
      • Re: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#59326Evgeny Potemkin14 Jan