added collation processing in UNION merging

temporary table BLOB now is longblob


mysql-test/r/create.result:
  blob size changed for safety
mysql-test/r/type_blob.result:
  blob size changed for safety
mysql-test/r/type_ranges.result:
  blob size changed for safety
mysql-test/r/union.result:
  blob size changed for safety
  new tests of UNION types merging
mysql-test/t/union.test:
  new tests of UNION types merging
sql/field.h:
  blob size changed for safety
sql/item.cc:
  processing of collation added
  added comment
sql/item.h:
  joining of UNION fields may failed now, because of incompatibility of collations
sql/sql_union.cc:
  joining of UNION fields may failed now, because of incompatibility of collations

5 files changed, 92 insertions, 10 deletions

diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index 28f06f0bf47..fe613e3c7fe 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -404,7 +404,7 @@ d	date			0000-00-00
 e	char(1)				
 f	datetime			0000-00-00 00:00:00	
 g	time			00:00:00	
-h	mediumblob				
+h	longblob				
 dd	time			00:00:00	
 select * from t2;
 a	b	c	d	e	f	g	h	dd
diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result
index d1147e55119..c521aa0296c 100644
--- a/mysql-test/r/type_blob.result
+++ b/mysql-test/r/type_blob.result
@@ -508,5 +508,5 @@ drop table t1;
 create table t1 select load_file('../../std_data/words.dat');
 show full fields from t1;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
-load_file('../../std_data/words.dat')	mediumblob	NULL	YES		NULL		select,insert,update,references	
+load_file('../../std_data/words.dat')	longblob	NULL	YES		NULL		select,insert,update,references	
 drop table t1;
diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
index b665f78bbce..512df8fbaa9 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -271,8 +271,8 @@ Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 auto	bigint(17) unsigned	NULL		PRI	0		select,insert,update,references	
 t1	bigint(1)	NULL			0		select,insert,update,references	
 t2	char(1)	latin1_swedish_ci					select,insert,update,references	
-t3	mediumtext	latin1_swedish_ci					select,insert,update,references	
-t4	mediumtext	latin1_bin					select,insert,update,references	
+t3	longtext	latin1_swedish_ci					select,insert,update,references	
+t4	longtext	latin1_bin					select,insert,update,references	
 select * from t2;
 auto	t1	t2	t3	t4
 11	1	a	aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 16640c78cb8..d01ecbc39b7 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -556,8 +556,8 @@ t1	CREATE TABLE `t1` (
   `a` double(4,1) NOT NULL default '0.0'
 ) TYPE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
-create table t2 (it1 tinyint, it2 tinyint not null, i int not null, ib bigint, f float, d double, y year, da date, dt datetime, sc char(10), sv varchar(10), b blob);
-insert into t2 values (NULL, 1, 3, 4, 1.5, 2.5, 1972, '1972-10-22', '1972-10-22 11:50', 'testc', 'testv', 'tetetetetest');
+create table t2 (it1 tinyint, it2 tinyint not null, i int not null, ib bigint, f float, d double, y year, da date, dt datetime, sc char(10), sv varchar(10), b blob, tx text);
+insert into t2 values (NULL, 1, 3, 4, 1.5, 2.5, 1972, '1972-10-22', '1972-10-22 11:50', 'testc', 'testv', 'tetetetetest', 'teeeeeeeeeeeest');
 create table t1 SELECT it2 from t2 UNION select it1 from t2;
 select * from t1;
 it2
@@ -720,7 +720,7 @@ tetetetetest
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `dt` blob
+  `dt` longblob
 ) TYPE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT sv from t2 UNION select b from t2;
@@ -731,7 +731,7 @@ tetetetetest
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `sv` blob
+  `sv` longblob
 ) TYPE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT i from t2 UNION select d from t2 UNION select b from t2;
@@ -745,4 +745,61 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `i` blob
 ) TYPE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+create table t1 SELECT sv from t2 UNION select tx from t2;
+select * from t1;
+sv
+testv
+teeeeeeeeeeeest
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `sv` text
+) TYPE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+create table t1 SELECT b from t2 UNION select tx from t2;
+select * from t1;
+b
+tetetetetest
+teeeeeeeeeeeest
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` longblob
+) TYPE=MyISAM DEFAULT CHARSET=latin1
 drop table t1,t2;
+create table t1 (d decimal(10,1));
+create table t2 (d decimal(10,9));
+insert into t1 values ("100000000.0");
+insert into t2 values ("1.23456780");
+create table t3 select * from t2 union select * from t1;
+select * from t3;
+d
+1.234567800
+100000000.0
+show create table t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `d` decimal(10,9) default NULL
+) TYPE=MyISAM DEFAULT CHARSET=latin1
+drop table t1,t2,t3;
+create table t1 select 1 union select -1;
+select * from t1;
+1
+1
+-1
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `1` bigint(1) NOT NULL default '0'
+) TYPE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+create table t1 select _latin1"test" union select _latin2"testt" ;
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'UNION'
+create table t1 select _latin2"test" union select _latin2"testt" ;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `test` char(5) character set latin2 NOT NULL default ''
+) TYPE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 9e02daf22fa..b758d57fa39 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -321,8 +321,8 @@ select * from t1;
 show create table t1;
 drop table t1;
 
-create table t2 (it1 tinyint, it2 tinyint not null, i int not null, ib bigint, f float, d double, y year, da date, dt datetime, sc char(10), sv varchar(10), b blob);
-insert into t2 values (NULL, 1, 3, 4, 1.5, 2.5, 1972, '1972-10-22', '1972-10-22 11:50', 'testc', 'testv', 'tetetetetest');
+create table t2 (it1 tinyint, it2 tinyint not null, i int not null, ib bigint, f float, d double, y year, da date, dt datetime, sc char(10), sv varchar(10), b blob, tx text);
+insert into t2 values (NULL, 1, 3, 4, 1.5, 2.5, 1972, '1972-10-22', '1972-10-22 11:50', 'testc', 'testv', 'tetetetetest', 'teeeeeeeeeeeest');
 
 create table t1 SELECT it2 from t2 UNION select it1 from t2;
 select * from t1;
@@ -391,4 +391,29 @@ drop table t1;
 create table t1 SELECT i from t2 UNION select d from t2 UNION select b from t2;
 select * from t1;
 show create table t1;
+drop table t1;
+create table t1 SELECT sv from t2 UNION select tx from t2;
+select * from t1;
+show create table t1;
+drop table t1;
+create table t1 SELECT b from t2 UNION select tx from t2;
+select * from t1;
+show create table t1;
 drop table t1,t2;
+create table t1 (d decimal(10,1));
+create table t2 (d decimal(10,9));
+insert into t1 values ("100000000.0");
+insert into t2 values ("1.23456780");
+create table t3 select * from t2 union select * from t1;
+select * from t3;
+show create table t3;
+drop table t1,t2,t3;
+create table t1 select 1 union select -1;
+select * from t1;
+show create table t1;
+drop table t1;
+-- error 1266
+create table t1 select _latin1"test" union select _latin2"testt" ;
+create table t1 select _latin2"test" union select _latin2"testt" ;
+show create table t1;
+drop table t1;
\ No newline at end of file