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


mysql-test/r/heap.result:
  Test of CREATE TABLE ... type=MEMORY
mysql-test/r/innodb.result:
  Added multi-update-test
mysql-test/r/multi_update.result:
  Added multi-update-test
mysql-test/t/heap.test:
  Test of CREATE TABLE ... type=MEMORY
mysql-test/t/innodb.test:
  Added multi-update-test
mysql-test/t/multi_update.test:
  Added multi-update-test
sql/ha_innodb.h:
  Allow optimzation of multi-table-update also for InnoDB tables
sql/handler.h:
  Allow optimzation of multi-update also for InnoDB tables
sql/key.cc:
  After merge fix
sql/lex.h:
  MEMORY is alias for HEAP
sql/sql_test.cc:
  Fixed wrong printf
sql/sql_update.cc:
  Fixed bug in multi-table-update where a row could be updated several times
sql/sql_yacc.yy:
  MEMORY is alias for HEAP

6 files changed, 201 insertions, 2 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;
diff --git a/mysql-test/t/heap.test b/mysql-test/t/heap.test
index ef7e3239a96..d1e647ae182 100644
--- a/mysql-test/t/heap.test
+++ b/mysql-test/t/heap.test
@@ -17,7 +17,7 @@ select * from t1;
 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/t/innodb.test b/mysql-test/t/innodb.test
index 320047b68fa..2f7285e7898 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -769,3 +769,31 @@ 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;
 drop table t1;
+
+#
+# Test multi update with different join methods
+#
+
+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);
+
+# Full join, without key
+update t1,t2 set t1.a=t1.a+100;
+select * from t1;
+
+# unique key
+update t1,t2 set t1.a=t1.a+100 where t1.a=101;
+select * from t1;
+
+# ref key
+update t1,t2 set t1.b=t1.b+10 where t1.b=2;
+select * from t1;
+
+# Range key (in t1)
+update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5;
+select * from t1;
+select * from t2;
+
+drop table t1,t2;
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 088b355a17c..d5543f69bd5 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -224,3 +224,31 @@ 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;
+
+#
+# Test multi update with different join methods
+#
+
+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);
+
+# Full join, without key
+update t1,t2 set t1.a=t1.a+100;
+select * from t1;
+
+# unique key
+update t1,t2 set t1.a=t1.a+100 where t1.a=101;
+select * from t1;
+
+# ref key
+update t1,t2 set t1.b=t1.b+10 where t1.b=2;
+select * from t1;
+
+# Range key (in t1)
+update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5;
+select * from t1;
+select * from t2;
+
+drop table t1,t2;