List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:October 28 2010 8:08am
Subject:bzr commit into mysql-next-mr-bugfixing branch (olav.sandstaa:3270) Bug#57372
View as plain text  
#At file:///export/home/tmp/mysql/opt-bug57372/ based on revid:roy.lyseng@stripped

 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
=== 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););


Attachment: [text/bzr-bundle] bzr/olav.sandstaa@oracle.com-20101028080834-ghs007fm0geomvw3.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (olav.sandstaa:3270) Bug#57372Olav Sandstaa28 Oct