BUG#5390 - problems with merge tables

Problem #1: INSERT...SELECT
INSERT ... SELECT with the same table on both sides (hidden
below a MERGE table) does now work by buffering the select result.
The duplicate detection works now after open_and_lock_tables() 
on the locks.
I did not find a test case that failed without the change in
sql_update.cc. I made the change anyway as it should in theory
fix a possible MERGE table problem with multi-table update.


mysql-test/r/create.result:
  BUG#5390 - problems with merge tables
  Removed a duplicate test.
mysql-test/r/merge.result:
  BUG#5390 - problems with merge tables
  Problem #1: INSERT...SELECT
  Added test results.
mysql-test/t/create.test:
  BUG#5390 - problems with merge tables
  Removed a duplicate test.
mysql-test/t/merge.test:
  BUG#5390 - problems with merge tables
  Problem #1: INSERT...SELECT
  Added tests.
sql/lock.cc:
  BUG#5390 - problems with merge tables
  Problem #1: INSERT...SELECT
  Added a new function to find a duplicate lock in a list of tables.
sql/mysql_priv.h:
  BUG#5390 - problems with merge tables
  Problem #1: INSERT...SELECT
  Added a declaration for the new function.
sql/sql_parse.cc:
  BUG#5390 - problems with merge tables
  Problem #1: INSERT...SELECT
  Changed the duplicate tables detection for INSERT ... SELECT
  to use the new function, which does also work for MERGE tables.
sql/sql_update.cc:
  BUG#5390 - problems with merge tables
  Changed the duplicate tables detection for UPDATE
  to use the new function, which does also work for MERGE tables.

4 files changed, 75 insertions, 18 deletions

diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index c99ad8960dc..b362cdf7f58 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -189,17 +189,6 @@ select * from t1;
 0	1	2
 0	0	1
 drop table t1;
-create table t1 select 1,2,3;
-create table if not exists t1 select 1,2;
-create table if not exists t1 select 1,2,3,4;
-Column count doesn't match value count at row 1
-create table if not exists t1 select 1;
-select * from t1;
-1	2	3
-1	2	3
-0	1	2
-0	0	1
-drop table t1;
 create table t1 (a int not null, b int, primary key (a));
 insert into t1 values (1,1);
 create table if not exists t1 select 2;
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index ffb715e3903..c6e9ec0d46f 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -645,3 +645,52 @@ select min(a), max(a) from t1;
 min(a)	max(a)
 9999999999.9990	9999999999.9990
 drop table t1, t2, t3;
+create table t1(a int);
+create table t2(a int);
+insert into t1 values (1);
+insert into t2 values (2);
+create table t3 (a int) engine=merge union=(t1, t2) insert_method=first;
+select * from t3;
+a
+1
+2
+insert t2 select * from t2;
+select * from t2;
+a
+2
+2
+insert t3 select * from t1;
+select * from t3;
+a
+1
+1
+2
+2
+insert t1 select * from t3;
+select * from t1;
+a
+1
+1
+1
+1
+2
+2
+select * from t2;
+a
+2
+2
+select * from t3;
+a
+1
+1
+1
+1
+2
+2
+2
+2
+check table t1, t2;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+test.t2	check	status	OK
+drop table t1, t2, t3;
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index ed2c76932da..644f4af15eb 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -158,13 +158,6 @@ create table if not exists t1 select 1,2,3,4;
 create table if not exists t1 select 1;
 select * from t1;
 drop table t1;
-create table t1 select 1,2,3;
-create table if not exists t1 select 1,2;
---error 1136
-create table if not exists t1 select 1,2,3,4;
-create table if not exists t1 select 1;
-select * from t1;
-drop table t1;
 
 #
 # Test create table if not exists with duplicate key error
diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test
index a3e3d487c67..ef12227d8f1 100644
--- a/mysql-test/t/merge.test
+++ b/mysql-test/t/merge.test
@@ -297,3 +297,29 @@ select min(a), max(a) from t1;
 flush tables;
 select min(a), max(a) from t1;
 drop table t1, t2, t3;
+
+#
+# BUG#5390 - problems with merge tables
+# Problem #1: INSERT...SELECT
+#
+#drop table if exists t1, t2, t3;
+create table t1(a int);
+create table t2(a int);
+insert into t1 values (1);
+insert into t2 values (2);
+create table t3 (a int) engine=merge union=(t1, t2) insert_method=first;
+select * from t3;
+#
+insert t2 select * from t2;
+select * from t2;
+#
+insert t3 select * from t1;
+select * from t3;
+#
+insert t1 select * from t3;
+select * from t1;
+select * from t2;
+select * from t3;
+check table t1, t2;
+drop table t1, t2, t3;
+