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@askmonty.org-20120530161018-kgsiqesmnohtnbc6 parent: holyfoot@askmonty.org-20120529045925-h35akgdit83ql5yq fixes bug(s): https://launchpad.net/bugs/1006231 committer: timour@askmonty.org 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@mariadb.org 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