#At file:///export/home/tmp/mysql/opt-bug57372/ based on revid:tor.didriksen@stripped
3262 Olav Sandstaa 2010-10-13
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-13 07:54:17 +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-13 07:54:17 +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-13 07:54:17 +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-13 07:54:17 +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-13 07:54:17 +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-13 07:54:17 +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-13 07:54:17 +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-09-30 14:53:11 +0000
+++ b/sql/sql_select.cc 2010-10-13 07:54:17 +0000
@@ -9988,9 +9988,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-20101013075417-izghdhs9lxyjfddm.bundle
| Thread |
|---|
| • bzr commit into mysql-next-mr-bugfixing branch (olav.sandstaa:3262) Bug#57372 | Olav Sandstaa | 13 Oct |
