maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

Hi Timour,

As discussed on the phone call:
- Let's add a separate MDEV item to add "the assert". 
- Ok to push tis patch.


On Wed, May 30, 2012 at 07:12:33PM +0300, Timour Katchaounov wrote:
> Sergey,
>
> Could you please review yet another bug fix for expensive subqueries
> executed during optimiziation.
>
> Timour
>
> ------------------------------------------------------------
> revno: 3535
> revision-id: timour@xxxxxxxxxxxx-20120530161018-kgsiqesmnohtnbc6
> parent: holyfoot@xxxxxxxxxxxx-20120529045925-h35akgdit83ql5yq
> fixes bug(s): https://launchpad.net/bugs/1006231
> committer: timour@xxxxxxxxxxxx
> branch nick: 5.3
> timestamp: Wed 2012-05-30 19:10:18 +0300
> message:
>   Fix for bug lp:1006231
>
>   Analysis:
>
>   When a subquery that needs a temp table is executed during
>   the prepare or optimize phase of the outer query, at the end
>   of the subquery execution all the JOIN_TABs of the subquery
>   are replaced by a new JOIN_TAB that selects from the temp table.
>   However that temp table has no corresponding TABLE_LIST.
>   Once EXPLAIN execution reaches its last phase, it tries to print
>   the names of the subquery tables through its TABLE_LISTs, but in
>   the case of this bug there is no such TABLE_LIST (it is NULL),
>   hence a crash.
>
>   Solution:
>   The fix is to block subquery evaluation inside
>   Item_func_like::fix_fields and Item_func_like::select_optimize()
>   using the Item::is_expensive() test.

> === modified file 'mysql-test/r/subselect_innodb.result'
> --- a/mysql-test/r/subselect_innodb.result	2012-05-20 12:57:29 +0000
> +++ b/mysql-test/r/subselect_innodb.result	2012-05-30 16:10:18 +0000
> @@ -365,4 +365,19 @@ GROUP BY 1
>  MAX( f1 )
>  NULL
>  drop table t1, t2, t3;
> +#
> +# LP BUG#1006231 crash in select_describe
> +#
> +create table t1(a1 int) ENGINE=InnoDB;
> +insert into t1 values (1);
> +explain
> +select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1));
> +id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
> +1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    1       
> +2       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    1       
> +3       SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    1       Using temporary; Using filesort
> +select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1));
> +1
> +1
> +drop table t1;
>  set optimizer_switch=@subselect_innodb_tmp;
> 
> === modified file 'mysql-test/t/subselect_innodb.test'
> --- a/mysql-test/t/subselect_innodb.test	2012-05-20 12:57:29 +0000
> +++ b/mysql-test/t/subselect_innodb.test	2012-05-30 16:10:18 +0000
> @@ -356,4 +356,16 @@ WHERE f2 >= (
>  
>  drop table t1, t2, t3;
>  
> +--echo #
> +--echo # LP BUG#1006231 crash in select_describe
> +--echo #
> +
> +create table t1(a1 int) ENGINE=InnoDB;
> +insert into t1 values (1);
> +explain
> +select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1));
> +select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1));
> +drop table t1;
> +
> +
>  set optimizer_switch=@subselect_innodb_tmp;
> 
> === modified file 'sql/item_cmpfunc.cc'
> --- a/sql/item_cmpfunc.cc	2012-05-20 12:57:29 +0000
> +++ b/sql/item_cmpfunc.cc	2012-05-30 16:10:18 +0000
> @@ -4656,7 +4656,7 @@ longlong Item_func_like::val_int()
>  
>  Item_func::optimize_type Item_func_like::select_optimize() const
>  {
> -  if (args[1]->const_item())
> +  if (args[1]->const_item() && !args[1]->is_expensive())
>    {
>      String* res2= args[1]->val_str((String *)&cmp.value2);
>      const char *ptr2;
> @@ -4743,7 +4743,8 @@ bool Item_func_like::fix_fields(THD *thd
>        We could also do boyer-more for non-const items, but as we would have to
>        recompute the tables for each row it's not worth it.
>      */
> -    if (args[1]->const_item() && !use_strnxfrm(collation.collation))
> +    if (args[1]->const_item() && !use_strnxfrm(collation.collation) &&
> +        !args[1]->is_expensive())
>      {
>        String* res2 = args[1]->val_str(&cmp.value2);
>        if (!res2)
> 

> _______________________________________________
> commits mailing list
> commits@xxxxxxxxxxx
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits


-- 
BR
 Sergei
-- 
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog