#At file:///data0/martin/bzr/5.1bt-bug43580/ based on revid:gshchepa@stripped
2846 Martin Hansson 2009-04-15
Bug#43580: Issue with Innodb on multi-table update
Certain multi-updates gave different results on InnoDB as opposed
to MyISAM, due to on-the-fly updates being used on the former and
the update order matters.
Fixed by turning off on-the-fly updates when update order
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_base.cc
Bug#43580: Added a word of caution about using tmp_set here.
@ sql/sql_update.cc
Bug#43580: The fix
modified:
mysql-test/r/innodb_mysql.result
mysql-test/t/innodb_mysql.test
sql/sql_base.cc
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-15 09:32:03 +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-15 09:32:03 +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_base.cc'
--- a/sql/sql_base.cc 2009-03-27 12:55:14 +0000
+++ b/sql/sql_base.cc 2009-04-15 09:32:03 +0000
@@ -5585,6 +5585,13 @@ static void update_field_dependencies(TH
other_bitmap= table->read_set;
}
+ /*
+ The test-and-set mechanism in the bitmap is not reliable during
+ multi-UPDATE statements under MARK_COLUMNS_READ mode
+ (thd->mark_used_columns == MARK_COLUMNS_READ), as this bitmap contains
+ only those columns that are used in the SET clause. I.e they are being
+ set here. See multi_update::prepare()
+ */
if (bitmap_fast_test_and_set(current_bitmap, field->field_index))
{
if (thd->mark_used_columns == MARK_COLUMNS_WRITE)
=== 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-15 09:32:03 +0000
@@ -1275,6 +1275,22 @@ int multi_update::prepare(List<Item> &no
}
/*
+ We gather the set of columns read during evaluation of SET expression in
+ TABLE::tmp_set by pointing TABLE::read_set to it and then restore it after
+ setup_fields().
+ */
+ for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf)
+ {
+ TABLE *table= table_ref->table;
+ if (tables_to_update & table->map)
+ {
+ DBUG_ASSERT(table->read_set == &table->def_read_set);
+ 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 +1298,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->tmp_set);
+ }
+ }
+
/*
Save tables beeing updated in update_tables
update_table->shared is position for table
@@ -1375,6 +1401,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:
+ - No column is both written to and read in SET expressions.
+
- We are doing a table scan and the data is in a separate file (MyISAM) or
if we don't update a clustered key.
@@ -1389,6 +1417,9 @@ 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 track of which fields are
+ read during evaluation of the SET expression. See multi_update::prepare.
+
RETURN
0 Not safe to update
1 Safe to update
@@ -1412,6 +1443,8 @@ 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);
+ 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-20090415093203-d4q2pbmfdbhj290f.bundle
