List:Commits« Previous MessageNext Message »
From:vasil.dimov Date:September 7 2011 3:26pm
Subject:bzr push into mysql-trunk branch (vasil.dimov:3444 to 3445)
View as plain text  
 3445 Vasil Dimov	2011-09-07 [merge]
      Merge mysql-5.5 -> mysql-trunk

    modified:
      storage/innobase/row/row0mysql.c
 3444 Olav Sandstaa	2011-09-07
      Fix for Bug#11766559 "59696: Optimizer fails to move WHERE condition
      on JOIN column when joining with a view".
      
      When optimizing a JOIN of two tables the optimizer tries to move WHERE
      conditions on the join column(s) to the first table in the JOIN in
      order to reduce the number of records it has to read. If the second
      operand of the JOIN is a view that is identical to the underlying
      table the optimizer should be able to perform the same optimization.
      In this case it fails to move the WHERE condition on the join column
      to the first table.
      
      The cause for this is that when the second operand is a view then
      the conditions contain Item_ref objects that reference the real item
      field objects. The existing code fails to evaluate these since it
      only evaluates the Item_ref object and fails to look at the real
      objects. As a result it fails to detect that the equals predicate on
      the second join operand can be substituted by a corresponding equals
      predicate on the first operand.
      
      There are two places where we fail to handle Item_ref correctly and that
      causes the optimizer to fail to do this optimization:
      
      1. In optimize_cond() we call build_equal_items() which again calls
      build_equal_items_for_cond() where we in Item_func::compile() fail to
      evaluate the Item::equal_fields_propagator() for the condition on the
      view operand. As a result the field used in the condition for the view
      is not considered as an equal field to the join column of the first
      table when this later is evaluated in make_join_statistics().
      
      2. Later in JOIN::optimize() we call substitute_for_best_equal_field()
      which uses Item_func::transform() to select the best condition of the
      tables. Item_func::transform() fails to evaluate the different
      conditions that are equal due to these being stored in a Item_ref
      object.
      
      The fix for these problems is to implement compile() and transform()
      functions for the Item_ref class that compile/transform both the
      Item_ref object itself and the item object referenced by the 'ref'
      pointer. So when Item_func::compile() and Item_func::transform() 
      evaluate Item_ref object this fix ensures that both the Item_ref 
      object and the object it references are evaluated.
     @ mysql-test/r/join.result
        Test case for Bug#11766559 "59696: Optimizer fails to move WHERE
        condition on JOIN column when joining with a view".
     @ mysql-test/r/subquery_sj_none.result
        Change in explain/query plan due to the fix for Bug#11766559 "59696: 
        Optimizerfails to move WHERE condition on JOIN column when joining
        with a view"
        
        The change in query plan for the following query:
        
          select * from t2 where (b,a) in (select a,b from t1 where a=3);
        
        where the table t2 is defined as:
        
          create table t2 as select * from t1;
        
        is caused by that when the optimizer optimizes the subquery it will
        try to optimize the conditions (in optimize_cond()). One of the things
        it does it to call build_equal_items_for_cond() for the following
        condition:
        
          (<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`)
        
        When this is "compiled" in build_equal_items_for_cond() both the
        <cache>(`test`.`t2`.`b`) and `test`.`t1`.`a` are stored in Item_ref
        objects. So with the existing code the real items does not get
        "compiled" and are not considered for alternatives. So the result of
        the "compilation" is:
        
          (<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`)
        
        With the patch applied both of the real items get evaluated and the
        code is able to find that `test`.`t1`.`a` can be replaced by the
        constant "3". So the result from the "compilation" of this condition is
        
          <cache>(`test`.`t2`.`b`) = 3
        
        Which causes the execution plan for the subquery to change as it has.
     @ mysql-test/r/subquery_sj_none_bka.result
        Change in explain/query plan due to the fix for Bug#11766559 "59696: 
        Optimizerfails to move WHERE condition on JOIN column when joining
        with a view".
        
        For detailed explanation see change log entry for subquery_sj_none.result.
     @ mysql-test/r/subquery_sj_none_bka_nobnl.result
        Change in explain/query plan due to the fix for Bug#11766559 "59696: 
        Optimizerfails to move WHERE condition on JOIN column when joining
        with a view".
        
        For detailed explanation see change log entry for subquery_sj_none.result.
     @ mysql-test/r/subquery_sj_none_bkaunique.result
        Change in explain/query plan due to the fix for Bug#11766559 "59696: 
        Optimizerfails to move WHERE condition on JOIN column when joining
        with a view".
        
        For detailed explanation see change log entry for subquery_sj_none.result.
     @ mysql-test/suite/opt_trace/r/bugs_no_prot_none.result
        Change in optimizer trace due to the fix for Bug#11766559 "59696:
        Optimizer fails to move WHERE condition on JOIN column when joining
        with a view"
        
        The change in query plan for the following query:
        
          SELECT *
          FROM where_subselect_20070
          WHERE (field1, ( SELECT COUNT( col_int_key ) FROM t1 )) IN (
            SELECT table2 .col_int_key AS field1,
                ( SELECT COUNT( col_int_key )
                  FROM t1
                )
            FROM t1 AS table1
              JOIN t1 AS table2
              ON table2 .col_int_key = table1 .col_int_key
          );
        
        is caused by that when the optimizer optimizes the subquery it will
        try to optimize the conditions (in optimize_cond()). One of the things
        it does it to call build_equal_items_for_cond() for the following
        condition:
        
          <cache>(`test`.`where_subselect_20070`.`field1`) = `test`.`table2`.`col_int_key`
         
        When this is "compiled" in build_equal_items_for_cond() both the
        <cache>(`test`.`where_subselect_20070`.`field1`) and
        `test`.`table2`.`col_int_key` are stored in Item_ref objects. With
        the existing code the real items do not get "compiled" and this item
        is not included as an "equality item".
        
        With the fix this equality is included and this leads to a new
        possible candidate for ref access as seen as the first difference in
        the optimizer trace output:
        
        
                                "ref_optimizer_key_uses": [
                                  {
                                    "database": "test",
                                    "table": "table1",
                                    "field": "col_int_key",
                                    "equals": "<cache>(`test`.`where_subselect_20070`.`field1`)",
                                    "null_rejecting": false
                                  },
        
        The following changes to the optimizer trace is caused by the
        optimizer deciding to actually use this ref access for accessing
        table1 (due to lower cost).
     @ mysql-test/suite/opt_trace/r/bugs_ps_prot_none.result
        Change in optimizer trace due to the fix for Bug#11766559 "59696:
        Optimizer fails to move WHERE condition on JOIN column when joining
        with a view".
        
        For detailed explanation see change log entry for bugs_no_prot_none.result.
     @ mysql-test/t/join.test
        Test case for Bug#11766559 "59696 Optimizer fails to move WHERE
        condition on JOIN column when joining with a view".
     @ sql/item.cc
        Implement transform() and compile() member functions for the Item_ref
        class. These functionss will then do transform/compile for both the
        Item_ref object and the object it has a reference to.
     @ sql/item.h
        Implement transform() and compile() member functions for the
        Item_ref class.

    modified:
      mysql-test/r/join.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nobnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
      mysql-test/suite/opt_trace/r/bugs_no_prot_none.result
      mysql-test/suite/opt_trace/r/bugs_ps_prot_none.result
      mysql-test/t/join.test
      sql/item.cc
      sql/item.h
=== modified file 'storage/innobase/row/row0mysql.c'
--- a/storage/innobase/row/row0mysql.c	revid:olav.sandstaa@stripped
+++ b/storage/innobase/row/row0mysql.c	revid:vasil.dimov@stripped
@@ -3306,6 +3306,19 @@ check_next_foreign:
 			   "index_id CHAR;\n"
 			   "foreign_id CHAR;\n"
 			   "found INT;\n"
+
+			   "DECLARE CURSOR cur_fk IS\n"
+			   "SELECT ID FROM SYS_FOREIGN\n"
+			   "WHERE FOR_NAME = :table_name\n"
+			   "AND TO_BINARY(FOR_NAME)\n"
+			   "  = TO_BINARY(:table_name)\n"
+			   "LOCK IN SHARE MODE;\n"
+
+			   "DECLARE CURSOR cur_idx IS\n"
+			   "SELECT ID FROM SYS_INDEXES\n"
+			   "WHERE TABLE_ID = table_id\n"
+			   "LOCK IN SHARE MODE;\n"
+
 			   "BEGIN\n"
 			   "SELECT ID INTO table_id\n"
 			   "FROM SYS_TABLES\n"
@@ -3328,13 +3341,9 @@ check_next_foreign:
 			   "IF (:table_name = 'SYS_FOREIGN_COLS') THEN\n"
 			   "       found := 0;\n"
 			   "END IF;\n"
+			   "OPEN cur_fk;\n"
 			   "WHILE found = 1 LOOP\n"
-			   "       SELECT ID INTO foreign_id\n"
-			   "       FROM SYS_FOREIGN\n"
-			   "       WHERE FOR_NAME = :table_name\n"
-			   "               AND TO_BINARY(FOR_NAME)\n"
-			   "                 = TO_BINARY(:table_name)\n"
-			   "               LOCK IN SHARE MODE;\n"
+			   "       FETCH cur_fk INTO foreign_id;\n"
 			   "       IF (SQL % NOTFOUND) THEN\n"
 			   "               found := 0;\n"
 			   "       ELSE\n"
@@ -3344,12 +3353,11 @@ check_next_foreign:
 			   "               WHERE ID = foreign_id;\n"
 			   "       END IF;\n"
 			   "END LOOP;\n"
+			   "CLOSE cur_fk;\n"
 			   "found := 1;\n"
+			   "OPEN cur_idx;\n"
 			   "WHILE found = 1 LOOP\n"
-			   "       SELECT ID INTO index_id\n"
-			   "       FROM SYS_INDEXES\n"
-			   "       WHERE TABLE_ID = table_id\n"
-			   "       LOCK IN SHARE MODE;\n"
+			   "       FETCH cur_idx INTO index_id;\n"
 			   "       IF (SQL % NOTFOUND) THEN\n"
 			   "               found := 0;\n"
 			   "       ELSE\n"
@@ -3360,6 +3368,7 @@ check_next_foreign:
 			   "               AND TABLE_ID = table_id;\n"
 			   "       END IF;\n"
 			   "END LOOP;\n"
+			   "CLOSE cur_idx;\n"
 			   "DELETE FROM SYS_COLUMNS\n"
 			   "WHERE TABLE_ID = table_id;\n"
 			   "DELETE FROM SYS_TABLES\n"

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (vasil.dimov:3444 to 3445) vasil.dimov7 Sep