[Commits] Rev 3679: MDEV-4590: Wrong result on second PS execution with early constant subquery optimization (MDEV-83) in file:///home/tsk/mprog/src/10.0-md83/

timour at askmonty.org timour at askmonty.org
Fri Aug 9 15:53:58 EEST 2013


At file:///home/tsk/mprog/src/10.0-md83/

------------------------------------------------------------
revno: 3679
revision-id: timour at askmonty.org-20130528122232-cuzl0l22skk1yvor
parent: timour at askmonty.org-20130523201638-s0tkndjvwku1c0ws
fixes bug: https://mariadb.atlassian.net/browse/MDEV-4590
committer: timour at askmonty.org
branch nick: 10.0-md83
timestamp: Tue 2013-05-28 15:22:32 +0300
message:
  MDEV-4590: Wrong result on second PS execution with early constant subquery optimization (MDEV-83)
  
  The wrong result is observed in a test case from subselect_sj.test,
  with the following query:
  
  prepare s1 from
  ' select * from
  t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
  where t1.a < 5';
  execute s1;
  execute s1;
  
  Analysis:
  The first execution of the PS chooses an execution strategy for the
  IN subquery, and sets the chosen strategy once. The strategy choice
  is not reset between PS re-excutions.
  
  MDEV-83 changes subquery optimization so that it is possible to
  optimize subqueries earlier than before. IN subqueries without
  alternative strategies are optimized as early as make_join_statistics
  of the outer query. This idea is that for such queries there is no
  need to know how many times they will be executed.
  
  make_join_statistics is called much earlier than substitute_for_best_equal_field.
  In the test query, equality propagation substitutes t2.a with t1.a.
  Since the subquery plan uses materialization, and it was chosen before
  substitute_for_best_equal_field, the equality predicates used in the
  subquery incorrectly reference the field t2.a instead of t1.a. This
  results in a wrong result.
  
  Solution:
  Delay the creation of materialization data structures until the execution
  phase. There are two exceptions:
  - semi-join materialization needs these structures immediately,
  - EXPLAIN needs them to print the subquery plan.
  Both cases call explicitly Item_in_subselect::setup_mat_engine().
-------------- next part --------------
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2013-05-20 19:06:22 +0000
+++ b/sql/item_subselect.cc	2013-05-28 12:22:32 +0000
@@ -902,6 +902,10 @@ bool Item_in_subselect::exec()
       test_if_item_cache_changed(*left_expr_cache) < 0)
     DBUG_RETURN(FALSE);
 
+  if (first_execution && test_strategy(SUBS_MATERIALIZATION) &&
+      engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE)
+    setup_mat_engine();
+
   /*
     The exec() method below updates item::value, and item::null_value, thus if
     we don't call it, the next call to item::val_int() will return whatever
@@ -969,6 +973,16 @@ void Item_subselect::print(String *str,
 {
   if (engine)
   {
+    /*
+      For non-SJ materialization the engine is created during exec(), therefore
+      it is not available during EXPLAIN. Create the materialization engine in
+      order to print it.
+    */
+    if (is_in_predicate() &&
+        ((Item_in_subselect*)this)->test_strategy(SUBS_MATERIALIZATION) &&
+        engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE)
+      ((Item_in_subselect*)this)->setup_mat_engine();
+
     str->append('(');
     engine->print(str, query_type);
     str->append(')');

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2013-05-23 20:16:38 +0000
+++ b/sql/opt_subselect.cc	2013-05-28 12:22:32 +0000
@@ -5250,9 +5250,13 @@ bool setup_jtbm_semi_joins(JOIN *join, L
       {
         DBUG_ASSERT(subq_pred->test_set_strategy(SUBS_MATERIALIZATION));
         subq_pred->is_jtbm_const_tab= FALSE;
+
+        DBUG_ASSERT(subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE);
+        subq_pred->setup_mat_engine();
+        DBUG_ASSERT(subq_pred->engine->engine_type() == subselect_engine::HASH_SJ_ENGINE);
         subselect_hash_sj_engine *hash_sj_engine=
-          ((subselect_hash_sj_engine*)item->engine);
-        
+          ((subselect_hash_sj_engine*) subq_pred->engine);
+
         table->table= hash_sj_engine->tmp_table;
         table->table->pos_in_table_list= table;
 
@@ -5472,27 +5476,6 @@ bool JOIN::choose_subquery_plan(table_ma
     DBUG_PRINT("info",("outer_lookup_keys: %.2f", outer_lookup_keys));
   }
 
-  /*
-    If (1) materialization is a possible strategy based on semantic analysis
-    during the prepare phase, then if
-      (2) it is more expensive than the IN->EXISTS transformation, and
-      (3) it is not possible to create usable indexes for the materialization
-          strategy,
-      fall back to IN->EXISTS.
-    otherwise
-      use materialization.
-  */
-  if (in_subs->test_strategy(SUBS_MATERIALIZATION) &&
-      in_subs->setup_mat_engine())
-  {
-    /*
-      If materialization was the cheaper or the only user-selected strategy,
-      but it is not possible to execute it due to limitations in the
-      implementation, fall back to IN-TO-EXISTS.
-    */
-    in_subs->set_strategy(SUBS_IN_TO_EXISTS);
-  }
-
   if (in_subs->test_strategy(SUBS_MATERIALIZATION))
   {
     /* Restore the original query plan used for materialization. */



More information about the commits mailing list