| Bug #69138 | Wasted work in method Item_in_subselect::single_value_transformer | ||
|---|---|---|---|
| Submitted: | 3 May 2013 16:10 | Modified: | 1 Jul 2013 16:39 |
| Reporter: | Po-Chun Chang (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S5 (Performance) |
| Version: | 5.7, 5.6 | OS: | Any |
| Assigned to: | Roy Lyseng | CPU Architecture: | Any |
| Tags: | patch, performance | ||
[3 May 2013 16:10]
Po-Chun Chang
Suggested patch
Attachment: patch.diff (text/plain), 571 bytes.
[4 May 2013 3:40]
MySQL Verification Team
Thank you for the bug report.
[26 Jun 2013 2:58]
Po-Chun Chang
Suggested patch (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: patch.diff (text/plain), 571 bytes.
[1 Jul 2013 16:39]
Paul DuBois
Noted in 5.7.2 changelog. Several inefficiencies were corrected: A loop in Item_in_subselect::single_value_transformer() could execute too many times. The myisamchk(), my_test_if_sort_rep(), and recreate_table() functions in MyISAM code could execute too many times. Thanks to Po-Chun Chang for the patches to correct these issues.
Description: The problem appears in version 5.6 and in the latest revision 5216 in version 5.7. I attached a one-line patch that fixes it. In method "Item_in_subselect::single_value_transformer" in item_subselect.cc, the loop on "select_lex->master_unit()" (line 1491) should break immediately after "subquery_maybe_null" is set to "true". All the iterations after "subquery_maybe_null" is set to "true" do not perform any useful work, at best they just set "subquery_maybe_null" again to "true". Method "NTService::is_super_user" in nt_servc.cc has a similar loop, and this loop breaks immediately after "ret_value" is set to "TRUE", just like in the proposed patch. How to repeat: Once Item_in_subselect::single_value_transformer is executed. Suggested fix: === modified file 'sql/item_subselect.cc' --- sql/item_subselect.cc 2013-02-26 12:08:54 +0000 +++ sql/item_subselect.cc 2013-05-03 14:46:59 +0000 @@ -1491,8 +1491,10 @@ for (SELECT_LEX* lex= select_lex->master_unit()->first_select(); lex != NULL && lex->master_unit() == select_lex->master_unit(); lex= lex->next_select()) - if (lex->item_list.head()->maybe_null) + if (lex->item_list.head()->maybe_null) { subquery_maybe_null= true; + break; + } /* If this is an ALL/ANY single-value subquery predicate, try to rewrite