From: Date: April 25 2008 1:59am
Subject: bk commit into 6.0 tree (sergefp:1.2626) BUG#36133
List-Archive: http://lists.mysql.com/commits/45978
X-Bug: 36133
Message-Id: <20080424235948.DAE4222B10E@pslp.localdomain>

Below is the list of changes that have just been committed into a local
6.0 repository of sergefp.  When sergefp does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-04-25 03:59:38+04:00, sergefp@stripped +5 -0
  BUG#36133 "Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &"
  - The problem was that the range optimizer evaluated constant expressions, 
    and among them it would try to evaluate IN-subquery predicates slated for
    handling with materialization strategy. However, these predicates require
    that parent_join->setup_subquery_materialization() is invoked before one
    attempts to evaluate them.
  
  - Fixed by making the range optimizer not to evaluate expressions that have
    item->is_expensive() == TRUE (these are materialization subqueries and 
    stored function calls). This should also resolve the problem that EXPLAIN 
    may be too long. 
    This change cuts off some opportunities for range optimizer, but this is 
    the price we're willing to pay for separation of query optimization and
    execution. 

  mysql-test/r/subselect_mat.result@stripped, 2008-04-25 03:59:32+04:00, sergefp@stripped +11 -0
    BUG#36133 "Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &"
    - Testcase

  mysql-test/t/subselect_mat.test@stripped, 2008-04-25 03:59:32+04:00, sergefp@stripped +12 -0
    BUG#36133 "Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &"
    - Testcase

  sql/item.h@stripped, 2008-04-25 03:59:32+04:00, sergefp@stripped +5 -0
    BUG#36133 "Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &"
    - Added comment for Item::is_expensive()

  sql/opt_range.cc@stripped, 2008-04-25 03:59:32+04:00, sergefp@stripped +6 -2
    BUG#36133 "Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &"
    - Don't evaluate a const item if it has item->expensive().

  sql/sql_select.cc@stripped, 2008-04-25 03:59:32+04:00, sergefp@stripped +7 -7
    BUG#36133 "Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &"
    - Better comment

diff -Nrup a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
--- a/mysql-test/r/subselect_mat.result	2008-04-02 13:33:18 +04:00
+++ b/mysql-test/r/subselect_mat.result	2008-04-25 03:59:32 +04:00
@@ -1134,3 +1134,14 @@ a
 2
 3
 drop table t1, t2, t3;
+create table t2 (a int, b int, key(a), key(b));
+insert into t2 values (3,3),(3,3),(3,3);
+select 1 from t2 where  
+t2.a > 1 
+or 
+t2.a = 3 and not t2.a not in (select t2.b from t2);
+1
+1
+1
+1
+drop table t2;
diff -Nrup a/mysql-test/t/subselect_mat.test b/mysql-test/t/subselect_mat.test
--- a/mysql-test/t/subselect_mat.test	2007-11-07 13:43:37 +03:00
+++ b/mysql-test/t/subselect_mat.test	2008-04-25 03:59:32 +04:00
@@ -794,3 +794,15 @@ select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 
 drop table t1, t2, t3;
+
+#
+# BUG#36133 "Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &&"
+#
+create table t2 (a int, b int, key(a), key(b));
+insert into t2 values (3,3),(3,3),(3,3);
+select 1 from t2 where  
+    t2.a > 1 
+  or 
+    t2.a = 3 and not t2.a not in (select t2.b from t2);
+drop table t2;
+
diff -Nrup a/sql/item.h b/sql/item.h
--- a/sql/item.h	2008-03-15 01:19:52 +03:00
+++ b/sql/item.h	2008-04-25 03:59:32 +04:00
@@ -1025,6 +1025,11 @@ public:
       cost Item::execution_cost(),
     where 'cost' is either 'double' or some structure of various cost
     parameters.
+
+    NOTE
+      This function is now used to prevent evaluation of materialized IN
+      subquery predicates before it is allowed. grep for 
+      DontEvaluateMaterializedSubqueryTooEarly to see the uses.
   */
   virtual bool is_expensive()
   {
diff -Nrup a/sql/opt_range.cc b/sql/opt_range.cc
--- a/sql/opt_range.cc	2008-04-14 15:30:04 +04:00
+++ b/sql/opt_range.cc	2008-04-25 03:59:32 +04:00
@@ -5327,8 +5327,12 @@ static SEL_TREE *get_mm_tree(RANGE_OPT_P
     }
     DBUG_RETURN(tree);
   }
-  /* Here when simple cond */
-  if (cond->const_item())
+  /* 
+    Here when simple cond 
+    There are limits on what kinds of const items we can evaluate, grep for
+    DontEvaluateMaterializedSubqueryTooEarly.
+  */
+  if (cond->const_item() & !cond->is_expensive())
   {
     /*
       During the cond->val_int() evaluation we can come across a subselect 
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2008-04-24 02:01:05 +04:00
+++ b/sql/sql_select.cc	2008-04-25 03:59:32 +04:00
@@ -10968,14 +10968,14 @@ remove_eq_conds(THD *thd, COND *cond, It
   }
   else if (cond->const_item() && !cond->is_expensive())
   /*
-    TODO:
+    DontEvaluateMaterializedSubqueryTooEarly:
+    TODO: 
     Excluding all expensive functions is too restritive we should exclude only
-    materialized IN because it is created later than this phase, and cannot be
-    evaluated at this point.
-    The condition should be something as (need to fix member access):
-      !(cond->type() == Item::FUNC_ITEM &&
-        ((Item_func*)cond)->func_name() == "<in_optimizer>" &&
-        ((Item_in_optimizer*)cond)->is_expensive()))
+    materialized IN subquery predicates because they can't yet be evaluated
+    here (they need additional initialization that is done later on).
+
+    The proper way to exclude the subqueries would be to walk the cond tree and
+    check for materialized subqueries there.
   */
   {
     *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;