Allow optimzation of multi-table-update also for InnoDB tables

MEMORY is alias for HEAP for CREATE TABLE ... TYPE=HEAP
Fixed bug in multi-table-update where a row could be updated several times

3 files changed, 144 insertions, 1 deletions

diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result
index 73642d7f751..d8905085e34 100644
--- a/mysql-test/r/heap.result
+++ b/mysql-test/r/heap.result
@@ -23,7 +23,7 @@ a	b
 4	6
 alter table t1 add c int not null, add key (c,a);
 drop table t1;
-create table t1 (a int not null,b int not null, primary key (a)) type=heap comment="testing heaps";
+create table t1 (a int not null,b int not null, primary key (a)) type=memory comment="testing heaps";
 insert into t1 values(1,1),(2,2),(3,3),(4,4);
 delete from t1 where a > 0;
 select * from t1;
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index bbabe0a04d6..48db5412da7 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1122,3 +1122,82 @@ my-test-1	my-test-2
 COMMIT;
 set autocommit=1;
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) type=innodb;
+INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
+SELECT * from t1;
+a	b
+1	1
+102	2
+103	3
+4	4
+5	5
+6	6
+7	7
+8	8
+9	9
+drop table t1;
+CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) type=innodb;
+CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) type=innodb;
+INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+update t1,t2 set t1.a=t1.a+100;
+select * from t1;
+a	b
+101	1
+102	2
+103	3
+104	4
+105	5
+106	6
+107	7
+108	8
+109	9
+update t1,t2 set t1.a=t1.a+100 where t1.a=101;
+select * from t1;
+a	b
+201	1
+102	2
+103	3
+104	4
+105	5
+106	6
+107	7
+108	8
+109	9
+update t1,t2 set t1.b=t1.b+10 where t1.b=2;
+select * from t1;
+a	b
+201	1
+103	3
+104	4
+105	5
+106	6
+107	7
+108	8
+109	9
+102	12
+update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5;
+select * from t1;
+a	b
+201	1
+103	5
+104	6
+106	6
+105	7
+107	7
+108	8
+109	9
+102	12
+select * from t2;
+a	b
+1	5
+2	5
+3	5
+4	5
+5	5
+6	5
+7	5
+8	5
+9	5
+drop table t1,t2;
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index fe028a4cb95..0f3644f15bc 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -246,3 +246,67 @@ INSERT INTO t3 VALUES (1,'jedan'),(2,'dva');
 update t1,t2 set t1.naziv="aaaa" where t1.broj=t2.broj;
 update t1,t2,t3 set t1.naziv="bbbb", t2.naziv="aaaa" where t1.broj=t2.broj and t2.broj=t3.broj;
 drop table if exists t1,t2,t3;
+CREATE TABLE t1 (a int not null primary key, b int not null, key (b));
+CREATE TABLE t2 (a int not null primary key, b int not null, key (b));
+INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+update t1,t2 set t1.a=t1.a+100;
+select * from t1;
+a	b
+101	1
+102	2
+103	3
+104	4
+105	5
+106	6
+107	7
+108	8
+109	9
+update t1,t2 set t1.a=t1.a+100 where t1.a=101;
+select * from t1;
+a	b
+201	1
+102	2
+103	3
+104	4
+105	5
+106	6
+107	7
+108	8
+109	9
+update t1,t2 set t1.b=t1.b+10 where t1.b=2;
+select * from t1;
+a	b
+201	1
+102	12
+103	3
+104	4
+105	5
+106	6
+107	7
+108	8
+109	9
+update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5;
+select * from t1;
+a	b
+201	1
+102	12
+103	5
+104	6
+105	7
+106	6
+107	7
+108	8
+109	9
+select * from t2;
+a	b
+1	3
+2	3
+3	3
+4	3
+5	3
+6	3
+7	3
+8	3
+9	3
+drop table t1,t2;