List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:October 28 2010 8:10am
Subject:bzr push into mysql-next-mr-bugfixing branch (olav.sandstaa:3269 to 3270)
Bug#57372
View as plain text  
 3270 Olav Sandstaa	2010-10-28
      Fix for Bug#57372 Multi-table updates and deletes fail when running with ICP against InnoDB
      
      For multi-table update and delete statements the same handler object
      is used for both doing the query part and the update part of the
      statement. When ICP is used a select condition will be pushed down to
      the storage engine. If this is accepted by the storage engine it will
      be used during execution of the query part of the statement but it
      might also be evaluated during the update part of the statement. This
      can lead to either not finding the record to update or result in wrong
      record being updated.
      
      Using ICP with InnoDB this has not given any issues this far as
      InnoDB has not accepted ICP for statements that updates the
      table. This restriction is about to be removed from InnoDB. After this change
      using ICP with InnoDB might cause wrong results if we have pushed an
      index condition for multi-table update and delete statements. 
      
      This patch solves this issue by not pushing down index conditions to
      the storage engine when the statement is either a multi-table delete
      or update statement.
     @ mysql-test/include/icp_tests.inc
        Test case for Bug#57372 "Multi-table updates and deletes fail when 
        running with ICP gainst InnoDB.
     @ mysql-test/r/innodb_icp.result
        Result file for test case for Bug#57372 "Multi-table updates and 
        deletes fail when running with ICP gainst InnoDB.
     @ mysql-test/r/innodb_icp_all.result
        Result file for test case for Bug#57372 "Multi-table updates and 
        deletes fail when running with ICP gainst InnoDB.
     @ mysql-test/r/innodb_icp_none.result
        Result file for test case for Bug#57372 "Multi-table updates and 
        deletes fail when running with ICP gainst InnoDB.
     @ mysql-test/r/myisam_icp.result
        Result file for test case for Bug#57372 "Multi-table updates and 
        deletes fail when running with ICP gainst InnoDB.
     @ mysql-test/r/myisam_icp_all.result
        Result file for test case for Bug#57372 "Multi-table updates and 
        deletes fail when running with ICP gainst InnoDB.
     @ mysql-test/r/myisam_icp_none.result
        Result file for test case for Bug#57372 "Multi-table updates and 
        deletes fail when running with ICP gainst InnoDB.
     @ sql/sql_select.cc
        For multi-table delete and update statements the same handler is used
        for both doing the query part and the update part. If an index
        condition has been pushed down to the storage engine it will be 
        evaluated during execution of the query part but it might also be
        executed during the update part. This can lead to wrong result. 
        This patch solves this problem by restricting the server to not push
        down index conditions if the statement is either a multi-table update or
        delete statement.

    modified:
      mysql-test/include/icp_tests.inc
      mysql-test/r/innodb_icp.result
      mysql-test/r/innodb_icp_all.result
      mysql-test/r/innodb_icp_none.result
      mysql-test/r/myisam_icp.result
      mysql-test/r/myisam_icp_all.result
      mysql-test/r/myisam_icp_none.result
      sql/sql_select.cc
 3269 Roy Lyseng	2010-10-26
      on a query with a materialized semi-join.
      Bug#50019: Wrong result for IN-query with materialization.
      
      When a subquery is subject to a semijoin transformation, its tables
      are merged to the outer query and are later treated as regular tables. 
      One possible optimization to apply after semijoin transformation is
      equality propagation.
      Equality propagation is done after query execution plan is chosen.
      It substitutes fields from tables being retrieved later for fields from
      tables being retrieved earlier.
      However, equality propagation can't be applied as is when using the
      semijoin materialization strategy. In this case, data from
      materialized semijoin tables are saved to a temporary table first
      before being involved in the join.
      
      This has two consequences for equality propagation:
      1. Equalities that exist within the materialized tables must be processed
         within the materialization operation, and
      2. Equalities between the materialized table and the outer query must be
         processed like regular equalities.
      
      Example: suppose we have a join order:
      
        ot1 ot2  SJ-Mat(it1  it2  it3)  ot3
      
      and equality ot2.col = it1.col = it2.col
      If we're looking for best substitute for it2.col, we should pick
      it1.col and not ot2.col, because the equality it1.col=it2.col
      can be processed during the materialization.
      
      For a field that is not in a materialized semijoin we can use any field,
      even those that are embedded in a materialized semijoin. This is because
      such fields are "copied back" to their original join-tab structures when
      the materialized temporary table is being read.
      
      Now we have added a new function Item_equal::get_subst_item() that accepts
      as a parameter a field being substituted and checks whether it belongs
      to a materialized semijoin.
      The field to substitute will be from the same materialized semijoin nest
      (if supplied field is within such nest), otherwise it will be the first
      field in the multiple equality.
      
      The new checks rely on the first_sj_inner_tab and first_sj_inner_tab
      fields of the join-tab. These fields are therefore set as soon as
      possible after the join strategy is fixed (before they were only used
      by the DuplicateWeedout strategy, and were not needed until later).
      
      Also fixed problems with pushdown of SJM-aware predicates during
      make_join_select():
       - Wrong predicates were sometimes generated,
       - make_cond_after_sjm() was called at the wrong position in the join
         sequence.
       - make_cond_after_sjm() was never actually considering the pushed-down
         SJM predicates.
      
      mysql-test/include/subquery_sj.inc
        Bug#45174: Incorrectly applied equality propagation caused wrong result
        on a query with a materialized semi-join.
        Bug#50019: Wrong result for IN-query with materialization.
      
      mysql-test/r/subquery_mat_all.result
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
        Bug#45174: Incorrectly applied equality propagation caused wrong result
        on a query with a materialized semi-join.
        Bug#50019: Wrong result for IN-query with materialization.
        Results for three new tests added.
        Some tests using semijoin materialization show that where clause
        has moved from the outer query into the materialized inner query.
        This is caused by the changed call to get_subst_item() in
        eliminate_item_equal().
        Ex: select * from ot where a in(select b from it where b>0);
        The clause "b>0" is now evaluated on the inner query materialization.
        Performance-wise this is never worse than before when using
        MaterializeScan and usually better than before for MaterializeLookup.
        (For MaterializeLookup, it is worse when it is more efficient to move
         the clause to the outer query. The best possible solution for this
         case is probably to evaluate the clause in both queries, this can
         be subject for a later feature development.)
        Another test that applies the same condition to both the outer and
        the inner query is added, to show the plan for such types of queries.
        Earlier wrong semijoin materialization test results are corrected.
       
      sql/item.cc
        Bug#45174: Incorrectly applied equality propagation caused wrong result
        on a query with a materialized semi-join.
      
        Calling new get_subst_item() function instead of get_first().
      
      sql/item_cmpfunc.cc
        Bug#45174: Incorrectly applied equality propagation caused wrong result
        on a query with a materialized semi-join.
      
        New function Item_equal::get_subst_item() that accepts as argument
        a field being substituted.
      
      sql/item_cmpfunc.h
        Bug#45174: Incorrectly applied equality propagation caused wrong result
        on a query with a materialized semi-join.
      
        New function Item_equal::get_subst_item() that accepts as argument
        a field being substituted.
      
      sql/sql_select.cc
        Bug#45174: Incorrectly applied equality propagation caused wrong result
        on a query with a materialized semi-join.
        Bug#50019: Wrong result for IN-query with materialization.
      
        Setting fields first_sj_inner_tab and last_sj_inner_tab moved from
        setup_semijoin_dups_elimination() to get_best_combination(), so they
        are set as early as possible after join order optimization.
        
        In make_join_select(), the test that determined when to pushdown
        SJM-specific predicates was wrong, in addition to improving the
        comments.
      
        The logic of eliminate_item_equal() has been simplified and
        adjusted so that it generates equalities that are useful also
        when the semijoin materialization strategy is being used.
        Some simplification was possible by taking advantage of the new
        Item_equal::get_subst_item() function.
      
        In make_cond_for_table_from_pred(), a number of comments has been
        added, and TAB characters are replaced by spaces.
      
        In make_cond_after_sjm(), make sure that it handles equalities
        generated for semijoin materialization (with marker=3).
        Added comments and removed TAB characters.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_mat_all.result
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
      sql/item.cc
      sql/item_cmpfunc.cc
      sql/item_cmpfunc.h
      sql/sql_select.cc
=== modified file 'mysql-test/include/icp_tests.inc'

=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc	2010-06-20 07:16:27 +0000
+++ b/mysql-test/include/icp_tests.inc	2010-10-28 08:08:34 +0000
@@ -402,3 +402,32 @@
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#57372 "Multi-table updates and deletes fail when running with ICP 
+--echo #            against InnoDB"
+--echo #
+
+CREATE TABLE t1 (
+  a INT KEY, 
+  b INT
+) ENGINE = INNODB;
+
+CREATE TABLE t2 (
+  a INT KEY, 
+  b INT
+) ENGINE = INNODB;
+
+INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+
+UPDATE t1, t2 
+SET t1.a = t1.a + 100, t2.b = t1.a + 10 
+WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100;
+
+--sorted_result
+SELECT * FROM t1;
+--sorted_result
+SELECT * FROM t2;
+
+DROP TABLE t1, t2;
+

=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	2010-08-23 11:28:58 +0000
+++ b/mysql-test/r/innodb_icp.result	2010-10-28 08:08:34 +0000
@@ -368,5 +368,37 @@
 f1
 b
 DROP TABLE t1;
+#
+# Bug#57372 "Multi-table updates and deletes fail when running with ICP 
+#            against InnoDB"
+#
+CREATE TABLE t1 (
+a INT KEY, 
+b INT
+) ENGINE = INNODB;
+CREATE TABLE t2 (
+a INT KEY, 
+b INT
+) ENGINE = INNODB;
+INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+UPDATE t1, t2 
+SET t1.a = t1.a + 100, t2.b = t1.a + 10 
+WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100;
+SELECT * FROM t1;
+a	b
+1	101
+102	102
+103	103
+104	104
+5	105
+SELECT * FROM t2;
+a	b
+1	1
+2	12
+3	13
+4	14
+5	5
+DROP TABLE t1, t2;
 set storage_engine= @save_storage_engine;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/innodb_icp_all.result'
--- a/mysql-test/r/innodb_icp_all.result	2010-08-23 11:28:58 +0000
+++ b/mysql-test/r/innodb_icp_all.result	2010-10-28 08:08:34 +0000
@@ -368,5 +368,37 @@
 f1
 b
 DROP TABLE t1;
+#
+# Bug#57372 "Multi-table updates and deletes fail when running with ICP 
+#            against InnoDB"
+#
+CREATE TABLE t1 (
+a INT KEY, 
+b INT
+) ENGINE = INNODB;
+CREATE TABLE t2 (
+a INT KEY, 
+b INT
+) ENGINE = INNODB;
+INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+UPDATE t1, t2 
+SET t1.a = t1.a + 100, t2.b = t1.a + 10 
+WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100;
+SELECT * FROM t1;
+a	b
+1	101
+102	102
+103	103
+104	104
+5	105
+SELECT * FROM t2;
+a	b
+1	1
+2	12
+3	13
+4	14
+5	5
+DROP TABLE t1, t2;
 set storage_engine= @save_storage_engine;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result	2010-09-01 13:46:08 +0000
+++ b/mysql-test/r/innodb_icp_none.result	2010-10-28 08:08:34 +0000
@@ -367,5 +367,37 @@
 f1
 b
 DROP TABLE t1;
+#
+# Bug#57372 "Multi-table updates and deletes fail when running with ICP 
+#            against InnoDB"
+#
+CREATE TABLE t1 (
+a INT KEY, 
+b INT
+) ENGINE = INNODB;
+CREATE TABLE t2 (
+a INT KEY, 
+b INT
+) ENGINE = INNODB;
+INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+UPDATE t1, t2 
+SET t1.a = t1.a + 100, t2.b = t1.a + 10 
+WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100;
+SELECT * FROM t1;
+a	b
+1	101
+102	102
+103	103
+104	104
+5	105
+SELECT * FROM t2;
+a	b
+1	1
+2	12
+3	13
+4	14
+5	5
+DROP TABLE t1, t2;
 set storage_engine= @save_storage_engine;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result	2010-06-18 08:45:53 +0000
+++ b/mysql-test/r/myisam_icp.result	2010-10-28 08:08:34 +0000
@@ -366,4 +366,36 @@
 f1
 b
 DROP TABLE t1;
+#
+# Bug#57372 "Multi-table updates and deletes fail when running with ICP 
+#            against InnoDB"
+#
+CREATE TABLE t1 (
+a INT KEY, 
+b INT
+) ENGINE = INNODB;
+CREATE TABLE t2 (
+a INT KEY, 
+b INT
+) ENGINE = INNODB;
+INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+UPDATE t1, t2 
+SET t1.a = t1.a + 100, t2.b = t1.a + 10 
+WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100;
+SELECT * FROM t1;
+a	b
+1	101
+102	102
+103	103
+104	104
+5	105
+SELECT * FROM t2;
+a	b
+1	1
+2	12
+3	13
+4	14
+5	5
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp_all.result'
--- a/mysql-test/r/myisam_icp_all.result	2010-06-20 07:16:27 +0000
+++ b/mysql-test/r/myisam_icp_all.result	2010-10-28 08:08:34 +0000
@@ -366,4 +366,36 @@
 f1
 b
 DROP TABLE t1;
+#
+# Bug#57372 "Multi-table updates and deletes fail when running with ICP 
+#            against InnoDB"
+#
+CREATE TABLE t1 (
+a INT KEY, 
+b INT
+) ENGINE = INNODB;
+CREATE TABLE t2 (
+a INT KEY, 
+b INT
+) ENGINE = INNODB;
+INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+UPDATE t1, t2 
+SET t1.a = t1.a + 100, t2.b = t1.a + 10 
+WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100;
+SELECT * FROM t1;
+a	b
+1	101
+102	102
+103	103
+104	104
+5	105
+SELECT * FROM t2;
+a	b
+1	1
+2	12
+3	13
+4	14
+5	5
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result	2010-06-18 08:45:53 +0000
+++ b/mysql-test/r/myisam_icp_none.result	2010-10-28 08:08:34 +0000
@@ -365,4 +365,36 @@
 f1
 b
 DROP TABLE t1;
+#
+# Bug#57372 "Multi-table updates and deletes fail when running with ICP 
+#            against InnoDB"
+#
+CREATE TABLE t1 (
+a INT KEY, 
+b INT
+) ENGINE = INNODB;
+CREATE TABLE t2 (
+a INT KEY, 
+b INT
+) ENGINE = INNODB;
+INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+UPDATE t1, t2 
+SET t1.a = t1.a + 100, t2.b = t1.a + 10 
+WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100;
+SELECT * FROM t1;
+a	b
+1	101
+102	102
+103	103
+104	104
+5	105
+SELECT * FROM t2;
+a	b
+1	1
+2	12
+3	13
+4	14
+5	5
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-10-26 10:43:50 +0000
+++ b/sql/sql_select.cc	2010-10-28 08:08:34 +0000
@@ -10060,9 +10060,24 @@
 {
   DBUG_ENTER("push_index_cond");
   Item *idx_cond;
+
+  /*
+    We will only attempt to push down an index condition when the
+    following criteria are true:
+    1. The storage engine supports ICP.
+    2. The system variable for enabling ICP is ON.
+    3. The query is not a multi-table update or delete statement. The reason
+       for this requirement is that the same handler will be used 
+       both for doing the select/join and the update. The pushed index
+       condition might then also be applied by the storage engine
+       when doing the update part and result in either not finding
+       the record to update or updating the wrong record.
+  */
   if (tab->table->file->index_flags(keyno, 0, 1) &
       HA_DO_INDEX_COND_PUSHDOWN &&
-      tab->join->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN))
+      tab->join->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN) &&
+      tab->join->thd->lex->sql_command != SQLCOM_UPDATE_MULTI &&
+      tab->join->thd->lex->sql_command != SQLCOM_DELETE_MULTI)
   {
     DBUG_EXECUTE("where", print_where(tab->select_cond, "full cond",
                  QT_ORDINARY););

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-next-mr-bugfixing branch (olav.sandstaa:3269 to 3270)Bug#57372Olav Sandstaa28 Oct