From: Roy Lyseng
Date: August 20 2012 7:13am
Subject: bzr push into mysql-5.6 branch (roy.lyseng:4107 to 4108) Bug#14272788
List-Archive: http://lists.mysql.com/commits/144569
X-Bug: 14272788
Message-Id: <201208200713.q7K7DhGD011379@khepri07.no.oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 4108 Roy Lyseng	2012-08-20
      Bug#14272788: Query with MaterializeScan and materialized subquery
                    returns too many rows
      
      Patch #8 - Fix problem with how subqueries are transformed to semi-join.
      
      There was a problem in how JOIN::flatten_subqueries() determined whether
      to transform a subquery to semi-join or not. When checking number of
      tables in query block after a transformation, the tables already added
      were not considered.
      
      This is now changed so that the first loop decides whether a subquery
      qualifies for transformation or not, and the second and third loops
      reuse the decision from the first loop. In addition, if a join nest
      with fewer tables follows one with more tables, which was not transformed,
      the smaller nest is now considered. In addition, the condition
      "< MAX_TABLES" was checked, but it should be "<= MAX_TABLES".
      
      sql/item_subselect.h
        Added the sj_chosen member variable that tells whether a subquery is
        chosen for semi-join transformation.
      
      sql/sql_optimizer.cc
        Changes to JOIN::flatten_subqueries(), see above.

    modified:
      sql/item_subselect.h
      sql/sql_optimizer.cc
 4107 Roy Lyseng	2012-08-16
      Bug#14272788: Query with MaterializeScan and materialized subquery
                    returns too many rows
      
      Patch #7 - Extend JOIN::all_tables array - ad hoc fix while waiting for
                 the complete removal of this array (in 5.6).
      
      sql/sql_optimizer.cc
        Extend JOIN::all_tables array with two elements when allocating it.

    modified:
      sql/sql_optimizer.cc
=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2012-07-10 11:59:40 +0000
+++ b/sql/item_subselect.h	2012-08-20 07:13:10 +0000
@@ -280,10 +280,10 @@ public:
     EXEC_MATERIALIZATION
   };
   enum_exec_method exec_method;
-  /**
-    Priority of this predicate in the convert-to-semi-join-nest process.
-  */
+  /// Priority of this predicate in the convert-to-semi-join-nest process.
   int sj_convert_priority;
+  /// True if this predicate is chosen for semi-join transformation
+  bool sj_chosen;
   /**
     Used by subquery optimizations to keep track about where this subquery
     predicate is located, and whether it is a candidate for transformation.
@@ -298,8 +298,8 @@ public:
 
   Item_exists_subselect(st_select_lex *select_lex);
   Item_exists_subselect()
-    :Item_subselect(), value(FALSE), exec_method(EXEC_UNSPECIFIED),
-     sj_convert_priority(0), embedding_join_nest(NULL)
+    :Item_subselect(), value(false), exec_method(EXEC_UNSPECIFIED),
+     sj_convert_priority(0), sj_chosen(false), embedding_join_nest(NULL)
   {}
   virtual trans_res select_transformer(JOIN *join)
   {

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-08-16 08:10:28 +0000
+++ b/sql/sql_optimizer.cc	2012-08-20 07:13:10 +0000
@@ -6820,7 +6820,7 @@ bool JOIN::flatten_subqueries()
 
   /* First, convert child join's subqueries. We proceed bottom-up here */
   for (subq= sj_subselects.begin(), subq_end= sj_subselects.end(); 
-       subq != subq_end;
+       subq < subq_end;
        subq++)
   {
     /*
@@ -6858,27 +6858,32 @@ bool JOIN::flatten_subqueries()
            sj_subselects.size(), sj_subselects.element_size(),
            reinterpret_cast<qsort_cmp>(subq_sj_candidate_cmp));
 
-  // #tables-in-parent-query + #tables-in-subquery + sj nests < MAX_TABLES
+  // #tables-in-parent-query + #tables-in-subquery + sj nests <= MAX_TABLES
   /* Replace all subqueries to be flattened with Item_int(1) */
   arena= thd->activate_stmt_arena_if_needed(&backup);
-  for (subq= sj_subselects.begin(); 
-       subq != subq_end && 
-       tables + (*subq)->unit->first_select()->join->tables +
-       sj_subselects.size() < MAX_TABLES;
-       subq++)
+  uint table_count= tables;
+  for (subq= sj_subselects.begin(); subq < subq_end; subq++)
   {
+    // Add the tables in the subquery nest plus one in case of materialization:
+    const uint tables_added= (*subq)->unit->first_select()->join->tables + 1;
+    (*subq)->sj_chosen= table_count + tables_added <= MAX_TABLES;
+
+    if (!(*subq)->sj_chosen)
+      continue;
+
+    table_count+= tables_added;
+
     Item **tree= ((*subq)->embedding_join_nest == NULL) ?
                    &conds : ((*subq)->embedding_join_nest->join_cond_ref());
     if (replace_subcondition(this, tree, *subq, new Item_int(1), FALSE))
       DBUG_RETURN(TRUE); /* purecov: inspected */
   }
 
-  for (subq= sj_subselects.begin();
-       subq != subq_end &&
-       tables + (*subq)->unit->first_select()->join->tables +
-       sj_subselects.size() < MAX_TABLES;
-       subq++)
+  for (subq= sj_subselects.begin(); subq < subq_end; subq++)
   {
+    if (!(*subq)->sj_chosen)
+      continue;
+
     OPT_TRACE_TRANSFORM(trace, oto0, oto1,
                         (*subq)->unit->first_select()->select_number,
                         "IN (SELECT)", "semijoin");
@@ -6890,8 +6895,10 @@ bool JOIN::flatten_subqueries()
     3. Finalize the subqueries that we did not convert,
        ie. perform IN->EXISTS rewrite.
   */
-  for (; subq!= subq_end; subq++)
+  for (subq= sj_subselects.begin(); subq < subq_end; subq++)
   {
+    if ((*subq)->sj_chosen)
+      continue;
     {
       OPT_TRACE_TRANSFORM(trace, oto0, oto1,
                           (*subq)->unit->first_select()->select_number,

No bundle (reason: useless for push emails).