#At file:///data0/martin/bzr/5.1bt-bug43580/ based on revid:gshchepa@stripped
2846 Martin Hansson 2009-04-08
Bug#43580: Issue with Innodb on multi-table update
This is an incremental patch, to be used for discussion.
Certain multi-updates gave different results on InnoDB from
MyISAM due to on-the-fly updates being used on the latter and
there are dependencies between the updates.
Fixed by turning off on-the-fly updates when dependencies are
present.
@ mysql-test/r/innodb_mysql.result
Bug#43580: Test result.
@ mysql-test/t/innodb_mysql.test
Bug#43580: Test case.
@ sql/sql_update.cc
Bug#43580: The fix
modified:
mysql-test/r/innodb_mysql.result
mysql-test/t/innodb_mysql.test
sql/sql_update.cc
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result 2009-03-30 08:44:17 +0000
+++ b/mysql-test/r/innodb_mysql.result 2009-04-08 10:13:40 +0000
@@ -2021,4 +2021,31 @@ DROP TABLE t4;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
+CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE = INNODB;
+CREATE TABLE t2 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
+CREATE TABLE t3 (a INT, b INT KEY, KEY (a)) ENGINE = INNODB;
+CREATE TABLE t4 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+INSERT INTO t3 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105), (6, 106);
+INSERT INTO t4 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;
+SELECT * FROM t2;
+a b
+1 1
+2 12
+3 13
+4 14
+5 5
+UPDATE t3, t4 SET t3.a = t3.a + 100, t4.b = t3.a + 10
+WHERE t3.a BETWEEN 2 AND 4 AND t4.a = t3.b - 100;
+SELECT * FROM t4;
+a b
+1 1
+2 12
+3 13
+4 14
+5 5
+DROP TABLE t1, t2, t3, t4;
End of 5.1 tests
=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test 2009-03-27 16:08:14 +0000
+++ b/mysql-test/t/innodb_mysql.test 2009-04-08 10:13:40 +0000
@@ -332,4 +332,31 @@ DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
+#
+# Bug#43580: Issue with Innodb on multi-table update
+#
+CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE = INNODB;
+CREATE TABLE t2 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
+
+CREATE TABLE t3 (a INT, b INT KEY, KEY (a)) ENGINE = INNODB;
+CREATE TABLE t4 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
+
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+
+INSERT INTO t3 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105), (6, 106);
+INSERT INTO t4 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;
+--sorted_result
+SELECT * FROM t2;
+
+UPDATE t3, t4 SET t3.a = t3.a + 100, t4.b = t3.a + 10
+WHERE t3.a BETWEEN 2 AND 4 AND t4.a = t3.b - 100;
+--sorted_result
+SELECT * FROM t4;
+
+DROP TABLE t1, t2, t3, t4;
+
--echo End of 5.1 tests
=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc 2009-03-05 10:23:46 +0000
+++ b/sql/sql_update.cc 2009-04-08 10:13:40 +0000
@@ -1274,6 +1274,16 @@ int multi_update::prepare(List<Item> &no
DBUG_RETURN(1);
}
+ for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf)
+ {
+ TABLE *table= table_ref->table;
+ if (tables_to_update & table->map)
+ {
+ table->read_set= &table->tmp_set;
+ bitmap_clear_all(table->read_set);
+ }
+ }
+
/*
We have to check values after setup_tables to get covering_keys right in
reference tables
@@ -1282,6 +1292,16 @@ int multi_update::prepare(List<Item> &no
if (setup_fields(thd, 0, *values, MARK_COLUMNS_READ, 0, 0))
DBUG_RETURN(1);
+ for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf)
+ {
+ TABLE *table= table_ref->table;
+ if (tables_to_update & table->map)
+ {
+ table->read_set= &table->def_read_set;
+ bitmap_union(table->read_set, &table->def_read_set);
+ }
+ }
+
/*
Save tables beeing updated in update_tables
update_table->shared is position for table
@@ -1375,6 +1395,8 @@ int multi_update::prepare(List<Item> &no
a row in this table will never be read twice. This is true under
the following conditions:
+ - Not other update depends on the read value.
+
- We are doing a table scan and the data is in a separate file (MyISAM) or
if we don't update a clustered key.
@@ -1388,6 +1410,8 @@ int multi_update::prepare(List<Item> &no
WARNING
This code is a bit dependent of how make_join_readinfo() works.
+ The field table->tmp_set is used for keeping a set fields for
+ on which an update to another table depends. See multi_update::prepare.
RETURN
0 Not safe to update
@@ -1412,6 +1436,12 @@ static bool safe_update_on_fly(THD *thd,
/* If range search on index */
if (join_tab->quick)
return !join_tab->quick->is_keys_used(table->write_set);
+ /*
+ Make sure that no fields written in other tables depend on fields
+ written to this table.
+ */
+ if (bitmap_is_overlapping(&table->tmp_set, table->write_set))
+ return FALSE;
/* If scanning in clustered key */
if ((table->file->ha_table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) &&
table->s->primary_key < MAX_KEY)
Attachment: [text/bzr-bundle] bzr/mhansson@mysql.com-20090408101340-6q8imt2dvkdjkexc.bundle
