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
Triage: Needs Triage: D3 (Medium)

[3 May 2013 16:10] Po-Chun Chang
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
[3 May 2013 16:10] Po-Chun Chang
Suggested patch

Attachment: patch.diff (text/plain), 571 bytes.

[4 May 2013 3:40] Miguel Solorzano
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.