List:Internals« Previous MessageNext Message »
From:Zhixuan Zhu Date:June 16 2009 3:33pm
Subject:RE: Question about optimizer removing const conditions
View as plain text  
I found for this case "2<4 and id>1" (always-true-const-cond + 1
regular-cond): conds = thd->lex->select_lex.where->next (conds !=
thd->lex->select_lex.where); 

For all the other cases: conds = thd->lex->select_lex.where.

I still can't explain this inconsistence. But we can get the filter
finally.

Thanks very much for your help.
Grace

-----Original Message-----
From: Igor.Babaev@stripped [mailto:Igor.Babaev@stripped] 
Sent: Monday, June 15, 2009 11:30 PM
To: Zhixuan Zhu
Cc: internals@stripped
Subject: Re: Question about optimizer removing const conditions



Zhixuan Zhu wrote:
> -- Don't know why I keep getting the old emails into my mail box. But
> here is my last message one more time. --
> 
> OK, now I do see the type of conds after remove_eq_conds is Item_func
> and I found my filter id>1 in there. Thank you! But where is this
conds
> hidden in thd->lex->select_lex.where? I can't relate these two things
> together. And actually at the engine interface we have only access to
> thd->lex->select_lex.where.
> 
> Thanks for your help. I'm getting closer but need probably one more
> hint:)

mysql_execute_command() [sql_parse.cc]
calls
   mysql_select()
   and passes select_lex->where as the value for the 'conds' parameter
   mysql_select()
   calls
      JOIN::prepare()
      that sets JOIN::conds to the parameter 'conds'=select_lex->where.

Regards,
Igor.

> 
> Regards,
> Grace
> 
> -----Original Message-----
> From: Igor.Babaev@stripped [mailto:Igor.Babaev@stripped] 
> Sent: Monday, June 15, 2009 2:01 PM
> To: Zhixuan Zhu
> Cc: internals@stripped
> Subject: Re: Question about optimizer removing const conditions
> 
> 
> 
> Zhixuan Zhu wrote:
>> Sorry, but I'm still confused. The type of
> (thd->lex->select_lex.where)
>> is ITEM_COND.
> 
> The type of thd->lex->select_lex.where is Item:
> 
> class st_select_lex: public st_select_lex_node
> {
> public:
>    Name_resolution_context context;
>    char *db;
>    Item *where, *having;                         /* WHERE & HAVING 
> clauses */
> ...
> 
> (see sql_lex.h)
> 
> Regards,
> Igor.
> 
>> (gdb) p (thd->lex->select_lex.where)->type()
>> $32 = Item::COND_ITEM
>>
>> I agree it should be optimized to id>1 because the other cond is an
>> always-true condition. But where/how can I get the condition id>1 as
> the
>> result of the optimization.
> 
> 
> 
> Before the statement
> 
> conds= remove_eq_conds(thd, conds, cond_value);
> 
> conds refers to '2<4 and id>1'
> 
> After it:
> 
> conds refers to 'id>1'
> 
> Yet it can be easily checked that conds == thd->lex->select_lex.where
> 
> Regards,
> Igor.
> 
> 
> 
> 
>> Thanks,
>> Grace
>>
>>
>> -----Original Message-----
>> From: Igor.Babaev@stripped [mailto:Igor.Babaev@stripped] 
>> Sent: Monday, June 15, 2009 12:45 PM
>> To: Zhixuan Zhu
>> Cc: internals@stripped
>> Subject: Re: Question about optimizer removing const conditions
>>
>> Hi!
>>
>> Zhixuan Zhu wrote:
>>> Test case:
>>> Create table test (id int);
>>> Insert into test values (1);
>>> Insert into test values (2);
>>>
>>> Select * from test where 2<4 and id > 1;
>>>
>>> Run mysqld in gdb and break after JOIN::optimize (like in
> JOIN::exec):
>>> (gdb) p ((Item_cond*)(thd->lex->select_lex.where))->func_name()
>>> $28 = 0x846df2e "and"
>>> (gdb) p
>>>
((Item_cond*)(thd->lex->select_lex.where))->argument_list()->elements
>>> $29 = 0
>>>
>>> The where condition is all removed with just an empty "and" left.
>> Your mistake is in that you use a wrong casting:
>>
>> After
>> conds= remove_eq_conds(thd, conds, cond_value) ;
>>
>> conds [=thd->lex->select_lex.where] is simplified into 'id > 1'
>> This condition is represented by an item of the Item_func type.
>> This type differs from the Item_cond type that is base class only
>> for Item_cond_and, Item_cond_or and Item_cond_xor.
>>
>> Regards,
>> Igor.
>>
>>> Thanks,
>>> Zhixuan
>>>
>>> -----Original Message-----
>>> From: Igor.Babaev@stripped [mailto:Igor.Babaev@stripped] 
>>> Sent: Friday, June 12, 2009 6:50 PM
>>> To: Zhixuan Zhu
>>> Subject: Re: Question about optimizer removing const conditions
>>>
>>>
>>>
>>> Zhixuan Zhu wrote:
>>>> No condition is actually returned from remove_eq_cond() for that
>>> statement and that's the weird part. Yes I guess we can do some
trick
>> in
>>> sql_select.cc. But we try not to hack the kernel but stay at the
>>> interface API level. We are in rnd_init() call for the DML statement
>> and
>>> try to get the execution plan from the thd structure. For DML we can
>>> only get [thd->lex->select_lex.where] which carries the filters of
> the
>>> where clause. It's been always successful until this one.
>>>> We did the similar handling for SELECT but get the conditions from
>>> [thd->lex->select_lex.join]. For DML "join" is always null though.
>>>> But why after all the optimization the valid condition gets
dropped?
>>> I can't tell you this without any test case.
>>>
>>> Regards,
>>> Igor.
> 
Thread
Question about optimizer removing const conditionsZhixuan Zhu12 Jun
  • Re: Question about optimizer removing const conditionsIgor Babaev13 Jun
    • RE: Question about optimizer removing const conditionsZhixuan Zhu13 Jun
RE: Question about optimizer removing const conditionsZhixuan Zhu15 Jun
  • Re: Question about optimizer removing const conditionsIgor Babaev15 Jun
    • RE: Question about optimizer removing const conditionsZhixuan Zhu16 Jun
      • Re: Question about optimizer removing const conditionsIgor Babaev16 Jun
        • RE: Question about optimizer removing const conditionsZhixuan Zhu16 Jun
Re: Question about optimizer removing const conditionsIgor Babaev16 Jun