Allow one to use MERGE tables with tables from different databases

Added command 'replace_column' to mysqltest


client/mysqltest.c:
  Added user command replace_column
mysql-test/r/merge.result:
  Update of tests
mysql-test/t/innodb.test:
  User replace_column to make tests repeatable
mysql-test/t/merge.test:
  New tests for testing MERGE tables with multiple databases
scripts/mysql_create_system_tables.sh:
  Fixed syntax error
sql/ha_myisammrg.cc:
  Allow one to use MERGE tables with tables from different databases
sql/mysql_priv.h:
  Added function
sql/sql_parse.cc:
  Allow one to use MERGE tables with tables from different databases
sql/sql_show.cc:
  Extended append_identifier()

1 files changed, 25 insertions, 13 deletions

diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index 323931ae3eb..f970aafe516 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -1,4 +1,5 @@
 drop table if exists t1,t2,t3,t4,t5,t6;
+drop database if exists mysqltest;
 create table t1 (a int not null primary key auto_increment, message char(20));
 create table t2 (a int not null primary key auto_increment, message char(20));
 INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
@@ -174,15 +175,26 @@ t3	CREATE TABLE `t3` (
   `a` int(11) NOT NULL default '0',
   `b` char(20) default NULL,
   KEY `a` (`a`)
-) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`)
 create table t4 (a int not null, b char(10), key(a)) type=MERGE UNION=(t1,t2);
 select * from t4;
 ERROR HY000: Can't open file: 't4.MRG'. (errno: 143)
-create table t5 (a int not null, b char(10), key(a)) type=MERGE UNION=(test.t1,test_2.t2);
-ERROR HY000: Incorrect table definition; All MERGE tables must be in the same database
-drop table if exists t5,t4,t3,t1,t2;
-Warnings:
-Note	1051	Unknown table 't5'
+alter table t4 add column c int;
+ERROR HY000: Can't open file: 't4.MRG'. (errno: 143)
+create database mysqltest;
+create table mysqltest.t6 (a int not null primary key auto_increment, message char(20));
+create table t5 (a int not null, b char(20), key(a)) type=MERGE UNION=(test.t1,mysqltest.t6);
+show create table t5;
+Table	Create Table
+t5	CREATE TABLE `t5` (
+  `a` int(11) NOT NULL default '0',
+  `b` char(20) default NULL,
+  KEY `a` (`a`)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`mysqltest`.`t6`)
+alter table t5 type=myisam;
+drop table t5, mysqltest.t6;
+drop database mysqltest;
+drop table if exists t4,t3,t1,t2;
 create table t1 (c char(10)) type=myisam;
 create table t2 (c char(10)) type=myisam;
 create table t3 (c char(10)) union=(t1,t2) type=merge;
@@ -251,14 +263,14 @@ t3	CREATE TABLE `t3` (
   `incr` int(11) NOT NULL default '0',
   `othr` int(11) NOT NULL default '0',
   PRIMARY KEY  (`incr`)
-) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`)
 alter table t3 drop primary key;
 show create table t3;
 Table	Create Table
 t3	CREATE TABLE `t3` (
   `incr` int(11) NOT NULL default '0',
   `othr` int(11) NOT NULL default '0'
-) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`)
 drop table t3,t2,t1;
 create table t1 (a int not null, key(a)) type=merge;
 select * from t1;
@@ -294,21 +306,21 @@ t4	CREATE TABLE `t4` (
   `a` int(11) NOT NULL default '0',
   `b` int(11) NOT NULL default '0',
   KEY `a` (`a`,`b`)
-) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`)
 show create table t5;
 Table	Create Table
 t5	CREATE TABLE `t5` (
   `a` int(11) NOT NULL default '0',
   `b` int(11) NOT NULL auto_increment,
   PRIMARY KEY  (`a`,`b`)
-) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`)
 show create table t6;
 Table	Create Table
 t6	CREATE TABLE `t6` (
   `a` int(11) NOT NULL default '0',
   `b` int(11) NOT NULL auto_increment,
   PRIMARY KEY  (`a`,`b`)
-) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=LAST UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
 insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL);
 insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL);
 select * from t3 order by b,a limit 3;
@@ -373,7 +385,7 @@ t4	CREATE TABLE `t4` (
   `a` int(11) NOT NULL default '0',
   `b` int(11) NOT NULL default '0',
   KEY `a` (`a`,`b`)
-) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2,t3)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(`t1`,`t2`,`t3`)
 select * from t4 order by a,b;
 a	b
 1	1
@@ -399,7 +411,7 @@ t4	CREATE TABLE `t4` (
   `a` int(11) NOT NULL default '0',
   `b` int(11) NOT NULL default '0',
   KEY `a` (`a`,`b`)
-) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(t1,t2,t3)
+) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`t1`,`t2`,`t3`)
 insert into t4 values (4,1),(4,2);
 select * from t1 order by a,b;
 a	b