bzr commit into mysql-5.1-bugteam branch (mhansson:2846) Bug#43580

[Martin Hansson]

#At file:///data0/martin/bzr/5.1bt-bug43580/ based on revid:gshchepa@stripped

 2846 Martin Hansson	2009-04-07
      Bug#43580: Issue with Innodb on multi-table update
      
      Multi-updates on-the-fly (updating the first table while updating
      other tables) were considered safe when an update to another
      table depended on the value written to the first.
      Fixed by not disallowing on-the-fly updates when a written field
      is read in the same statement.
     @ mysql-test/r/innodb_mysql.result
        Bug#43580: Expected result.
     @ mysql-test/t/innodb_mysql.test
        Bug#43580: Test case.
     @ sql/sql_update.cc
        Bug#43580: The fix and updated comment.

    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-07 09:10:14 +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-07 09:10:14 +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-07 09:10:14 +0000
@@ -1375,6 +1375,9 @@ int multi_update::prepare(List<Item> &no
     a row in this table will never be read twice. This is true under
     the following conditions:
 
+    - The updated field is not read during the update. In this case 
+      some other field might be written to that depends on this read.
+
     - We are doing a table scan and the data is in a separate file (MyISAM) or
       if we don't update a clustered key.
 
@@ -1412,6 +1415,9 @@ 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 written fields are read during the update. */
+    if (bitmap_is_overlapping(table->read_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/[e-mail]