Merge

mysql-test/r/metadata.result:
  Auto merged
sql/item.h:
  Auto merged
sql/item_func.h:
  Auto merged
sql/sql_parse.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/sql_union.cc:
  Auto merged
mysql-test/r/union.result:
  SCCS merged
mysql-test/t/union.test:
  SCCS merged

20 files changed, 1921 insertions, 512 deletions

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index c22d0d3bec8..2fb18ca92c7 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -747,7 +747,7 @@ insert into t1 values (now());
 create table t2 select f2 from (select max(now()) f2 from t1) a;
 show columns from t2;
 Field	Type	Null	Key	Default	Extra
-f2	datetime			0000-00-00 00:00:00	
+f2	datetime	YES		NULL	
 drop table t2;
 create table t2 select f2 from (select now() f2 from t1) a;
 show columns from t2;
diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result
index 5248f6d8936..790c1ec3794 100644
--- a/mysql-test/r/metadata.result
+++ b/mysql-test/r/metadata.result
@@ -55,8 +55,33 @@ id	data	data
 2	female	no
 select t1.id from t1 union select t2.id from t2;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def	test	t1	t1	id	id	1	3	1	Y	32768	0	63
+def				id	id	1	4	1	Y	32768	0	63
 id
 1
 2
 drop table t1,t2;
+create table t1 ( a int, b varchar(30), primary key(a));
+insert into t1 values (1,'one');
+insert into t1 values (2,'two');
+set @arg00=1 ;
+select @arg00 FROM t1 where a=1 union distinct select 1 FROM t1 where a=1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Name	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def				@arg00	@arg00	8	20	1	Y	32768	0	63
+@arg00
+1
+select * from (select @arg00) aaa;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Name	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def			aaa	@arg00	@arg00	8	20	1	Y	32768	0	63
+@arg00
+1
+select 1 union select 1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Name	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def				1	1	8	20	1	N	32769	0	63
+1
+1
+select * from (select 1 union select 1) aaa;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Name	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def			aaa	1	1	8	20	1	N	32769	0	63
+1
+1
+drop table t1;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index fb42f68398f..8f17f545d1a 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1873,6 +1873,380 @@ SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
 a
 1
 3
+SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
+a
+3
+SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
+a
+1
+SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
+a
+2
+SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
+a
+1
+3
+SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2);
+a
+3
+SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2);
+a
+1
+SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2);
+a
+2
+SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2);
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2);
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2);
+a
+1
+3
+SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+a
+3
+SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+a
+1
+SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+a
+2
+SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+a
+1
+3
+SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+a
+3
+SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+a
+1
+SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+a
+2
+SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+a
+1
+3
+SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+a
+3
+SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+a
+1
+SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+a
+2
+SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+a
+1
+3
+SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+a
+3
+SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+a
+1
+SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+a
+2
+SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+a
+1
+3
+SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
+ERROR 21000: Operand should contain 1 column(s)
+SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
+ERROR 21000: Operand should contain 1 column(s)
+SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);
+ERROR 21000: Operand should contain 1 column(s)
+SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a FROM t1 WHERE b = 2);
+ERROR 21000: Operand should contain 1 column(s)
+SELECT a FROM t1 WHERE a > ALL (SELECT a,2 FROM t1 WHERE b = 2);
+ERROR 21000: Operand should contain 1 column(s)
+SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a,2 FROM t1 WHERE b = 2);
+ERROR 21000: Operand should contain 1 column(s)
+SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2);
+ERROR 21000: Operand should contain 1 column(s)
+SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
+ERROR 21000: Operand should contain 1 column(s)
+SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
+ERROR 21000: Operand should contain 2 column(s)
+SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
+ERROR 21000: Operand should contain 1 column(s)
+SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
+a
+SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
+ERROR 21000: Operand should contain 2 column(s)
+SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
+ERROR 21000: Operand should contain 1 column(s)
+SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
+a
+1
+2
+3
+SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
+a
+2
+SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2);
+a
+1
+3
+SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
+a
+2
+SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2);
+a
+1
+3
+SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2);
+a
+2
+SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2);
+a
+1
+3
+SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2);
+a
+2
+SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2);
+a
+1
+3
+SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
+a
+3
+SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
+a
+1
+SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
+a
+2
+SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
+a
+1
+3
+SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 group by a);
+a
+3
+SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 group by a);
+a
+1
+SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 group by a);
+a
+2
+SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 group by a);
+a
+1
+3
+SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
+a
+3
+SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
+a
+1
+SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
+a
+2
+SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
+a
+1
+3
+SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 group by a HAVING a = 2);
+a
+3
+SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 group by a HAVING a = 2);
+a
+1
+SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 group by a HAVING a = 2);
+a
+2
+SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 group by a HAVING a = 2);
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 group by a HAVING a = 2);
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2);
+a
+1
+3
+SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
+concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-')
+0-
+0-
+1-
+SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;
+concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-')
+1-
+0-
+0-
+SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;
+concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-')
+0-
+1-
+0-
+DROP TABLE t1;
+CREATE TABLE t1 ( a double, b double );
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
+SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
+a
+3
+SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
+a
+1
+SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
+a
+2
+SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
+a
+1
+3
+SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2e0);
+a
+3
+SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2e0);
+a
+1
+SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2e0);
+a
+2
+SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2e0);
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2e0);
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0);
+a
+1
+3
+DROP TABLE t1;
+CREATE TABLE t1 ( a char(1), b char(1));
+INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
+SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
+a
+3
+SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
+a
+1
+SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
+a
+2
+SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
+a
+1
+3
+SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = '2');
+a
+3
+SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = '2');
+a
+1
+SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = '2');
+a
+2
+SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = '2');
+a
+2
+3
+SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = '2');
+a
+1
+2
+SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = '2');
+a
+1
+3
 DROP TABLE t1;
 create table t1 (a int, b int);
 insert into t1 values (1,2),(3,4);
@@ -2128,7 +2502,7 @@ drop table t1;
 create table t1 (a1 int);
 create table t2 (b1 int);
 select * from t1 where a2 > any(select b1 from t2);
-ERROR 42S22: Unknown column 'a2' in 'scalar IN/ALL/ANY subquery'
+ERROR 42S22: Unknown column 'a2' in 'IN/ALL/ANY subquery'
 select * from t1 where a1 > any(select b1 from t2);
 a1
 drop table t1,t2;
@@ -2267,4 +2641,59 @@ pass	userid	parentid	parentgroup	childid	groupname	grouptypeid	crse	categoryid	c
 1	5141	12	group2	12	group2	5	1	1	87	Oct04
 1	5141	12	group2	12	group2	5	1	2	88	Oct04
 1	5141	12	group2	12	group2	5	1	2	89	Oct04
-drop table if exists t1, t2, t3, t4, t5;
+drop table t1, t2, t3, t4, t5;
+create table t1 (a int);
+insert into t1 values (1), (2), (3);
+SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
+1
+1
+1
+1
+drop table t1;
+create table t1 (a int);
+create table t2 (a int);
+insert into t1 values (1),(2);
+insert into t2 values (0),(1),(2),(3);
+select a from t2 where a in (select a from t1);
+a
+1
+2
+select a from t2 having a in (select a from t1);
+a
+1
+2
+prepare stmt1 from "select a from t2 where a in (select a from t1)";
+execute stmt1;
+a
+1
+2
+execute stmt1;
+a
+1
+2
+deallocate prepare stmt1;
+prepare stmt1 from "select a from t2 having a in (select a from t1)";
+execute stmt1;
+a
+1
+2
+execute stmt1;
+a
+1
+2
+deallocate prepare stmt1;
+drop table t1, t2;
+create table t1 (a int, b int);
+insert into t1 values (1,2);
+select 1 = (select * from t1);
+ERROR 21000: Operand should contain 1 column(s)
+select (select * from t1) = 1;
+ERROR 21000: Operand should contain 2 column(s)
+select (1,2) = (select a from t1);
+ERROR 21000: Operand should contain 2 column(s)
+select (select a from t1) = (1,2);
+ERROR 21000: Operand should contain 1 column(s)
+select (1,2,3) = (select * from t1);
+ERROR 21000: Operand should contain 3 column(s)
+select (select * from t1) = (1,2,3);
+ERROR 21000: Operand should contain 2 column(s)
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index bfa169af417..96baf36d151 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -655,7 +655,7 @@ f
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `f` binary(24) default NULL
+  `f` varbinary(24) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT y from t2 UNION select da from t2;
@@ -666,7 +666,7 @@ y
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `y` binary(10) default NULL
+  `y` varbinary(10) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT y from t2 UNION select dt from t2;
@@ -677,7 +677,7 @@ y
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `y` binary(19) default NULL
+  `y` varbinary(19) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT da from t2 UNION select dt from t2;
@@ -699,7 +699,7 @@ testc
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `dt` binary(19) default NULL
+  `dt` varbinary(19) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT dt from t2 UNION select sv from t2;
@@ -710,7 +710,7 @@ testv
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `dt` binary(19) default NULL
+  `dt` varbinary(19) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT sc from t2 UNION select sv from t2;
@@ -732,7 +732,7 @@ tetetetetest
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `dt` blob
+  `dt` longblob
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT sv from t2 UNION select b from t2;
@@ -755,7 +755,7 @@ tetetetetest
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `i` blob
+  `i` longblob
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT sv from t2 UNION select tx from t2;
@@ -766,7 +766,7 @@ teeeeeeeeeeeest
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `sv` text
+  `sv` longtext
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT b from t2 UNION select tx from t2;
@@ -1069,7 +1069,7 @@ create table t1 as
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `_latin1'test' collate latin1_bin` char(4) character set latin1 collate latin1_bin NOT NULL default ''
+  `_latin1'test' collate latin1_bin` varchar(4) character set latin1 collate latin1_bin NOT NULL default ''
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select count(*) from t1;
 count(*)
@@ -1082,7 +1082,7 @@ create table t1 as
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `test` char(4) character set latin1 collate latin1_bin NOT NULL default ''
+  `test` varchar(4) character set latin1 collate latin1_bin NOT NULL default ''
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select count(*) from t1;
 count(*)
@@ -1095,7 +1095,7 @@ create table t1 as
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `test` char(4) character set latin1 collate latin1_bin NOT NULL default ''
+  `test` varchar(4) character set latin1 collate latin1_bin NOT NULL default ''
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select count(*) from t1;
 count(*)
@@ -1200,3 +1200,38 @@ select concat('value is: ', @val) union select 'some text';
 concat('value is: ', @val)
 value is: 6
 some text
+CREATE TABLE t1 (
+a ENUM('ä','ö','ü') character set utf8 not null default 'ü',
+b ENUM("one", "two") character set utf8,
+c ENUM("one", "two")
+);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` enum('ä','ö','ü') character set utf8 NOT NULL default 'ü',
+  `b` enum('one','two') character set utf8 default NULL,
+  `c` enum('one','two') default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values ('ä', 'one', 'one'), ('ö', 'two', 'one'), ('ü', NULL, NULL);
+create table t2 select NULL union select a from t1;
+show columns from t2;
+Field	Type	Null	Key	Default	Extra
+NULL	enum('ä','ö','ü')	YES		NULL	
+drop table t2;
+create table t2 select a from t1 union select NULL;
+show columns from t2;
+Field	Type	Null	Key	Default	Extra
+a	enum('ä','ö','ü')	YES		NULL	
+drop table t2;
+create table t2 select a from t1 union select a from t1;
+show columns from t2;
+Field	Type	Null	Key	Default	Extra
+a	char(1)				
+drop table t2;
+create table t2 select a from t1 union select c from t1;
+ERROR HY000: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation 'UNION'
+create table t2 select a from t1 union select b from t1;
+show columns from t2;
+Field	Type	Null	Key	Default	Extra
+a	varchar(3)	YES		NULL	
+drop table t2, t1;
diff --git a/mysql-test/t/metadata.test b/mysql-test/t/metadata.test
index d11cb62b04e..ebd58ef4ebb 100644
--- a/mysql-test/t/metadata.test
+++ b/mysql-test/t/metadata.test
@@ -34,4 +34,17 @@ select t1.id, t1.data, t2.data from t1, t2 where t1.id = t2.id order by t1.id;
 select t1.id from t1 union select t2.id from t2;
 drop table t1,t2;
 
+#
+# variables union and derived tables metadata test
+#
+create table t1 ( a int, b varchar(30), primary key(a));
+insert into t1 values (1,'one');
+insert into t1 values (2,'two');
+set @arg00=1 ;
+select @arg00 FROM t1 where a=1 union distinct select 1 FROM t1 where a=1;
+select * from (select @arg00) aaa;
+select 1 union select 1;
+select * from (select 1 union select 1) aaa;
+drop table t1;
+
 --disable_metadata
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index b1689e30959..1882a04936c 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1148,6 +1148,7 @@ SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
 SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
 SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
 SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
+# with index
 ALTER TABLE t1 ADD INDEX (a);
 SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
 SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
@@ -1161,8 +1162,145 @@ SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 WHERE b = 2 );
 SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 WHERE b = 2 );
 SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 WHERE b = 2 );
 SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 WHERE b = 2 );
+# having clause test
+SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2);
+# union test
+SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 UNION SELECT a FROM t1 WHERE b = 2);
+# union + having test
+SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 HAVING a = 2 UNION SELECT a FROM t1 HAVING a = 2);
+# row tests
+# < > >= <= and = ALL/ <> ANY do not support row operation
+-- error 1241
+SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
+-- error 1241
+SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
+-- error 1241
+SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);
+-- error 1241
+SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a FROM t1 WHERE b = 2);
+-- error 1241
+SELECT a FROM t1 WHERE a > ALL (SELECT a,2 FROM t1 WHERE b = 2);
+-- error 1241
+SELECT a FROM t1 WHERE (1,2) > ALL (SELECT a,2 FROM t1 WHERE b = 2);
+-- error 1241
+SELECT a FROM t1 WHERE (1,2) = ALL (SELECT a,2 FROM t1 WHERE b = 2);
+-- error 1241
+SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
+# following should be converted to IN
+-- error 1241
+SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
+-- error 1241
+SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
+-- error 1241
+SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
+-- error 1241
+SELECT a FROM t1 WHERE a <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a,2 FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2);
+SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2);
+SELECT a FROM t1 WHERE (a,1) <> ALL (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2);
+# without optimisation
+SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
+SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
+SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
+SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
+SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
+SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
+SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2 group by a);
+SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2 group by a);
+SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2 group by a);
+SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
+SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2 group by a);
+SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2 group by a);
+# without optimisation + having
+SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
+SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
+SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
+SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
+SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
+SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
+SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 group by a HAVING a = 2);
+SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 group by a HAVING a = 2);
+SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 group by a HAVING a = 2);
+SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 group by a HAVING a = 2);
+SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 group by a HAVING a = 2);
+SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 group by a HAVING a = 2);
+# EXISTS in string contence
+SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a), '-') from t1 a;
+SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a), '-') from t1 a;
+SELECT concat(EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a), '-') from t1 a;
+DROP TABLE t1;
+CREATE TABLE t1 ( a double, b double );
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
+SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
+SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
+SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
+SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
+SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
+SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
+SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = 2e0);
+SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = 2e0);
+SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = 2e0);
+SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = 2e0);
+SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = 2e0);
+SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = 2e0);
+DROP TABLE t1;
+CREATE TABLE t1 ( a char(1), b char(1));
+INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
+SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
+SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
+SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
+SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
+SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
+SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
+SELECT a FROM t1 WHERE a > ALL (SELECT a FROM t1 WHERE b = '2');
+SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t1 WHERE b = '2');
+SELECT a FROM t1 WHERE a = ALL (SELECT a FROM t1 WHERE b = '2');
+SELECT a FROM t1 WHERE a >= ALL (SELECT a FROM t1 WHERE b = '2');
+SELECT a FROM t1 WHERE a <= ALL (SELECT a FROM t1 WHERE b = '2');
+SELECT a FROM t1 WHERE a <> ALL (SELECT a FROM t1 WHERE b = '2');
 DROP TABLE t1;
 
+
 #
 # SELECT(EXISTS * ...)optimisation
 #
@@ -1438,8 +1576,9 @@ select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx;
 select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx from DUAL;
 drop table t1;
 
+#
 # Test for BUG#8218
-
+#
 CREATE TABLE t1 (
   categoryId int(11) NOT NULL,
   courseId int(11) NOT NULL,
@@ -1536,5 +1675,50 @@ join
 group by 
   groupstuff.groupname, colhead , t2.courseid;
 
-drop table if exists t1, t2, t3, t4, t5;
+drop table t1, t2, t3, t4, t5;
 
+#
+# Transformation in left expression of subquery (BUG#8888)
+#
+create table t1 (a int);
+insert into t1 values (1), (2), (3);
+SELECT 1 FROM t1 WHERE (SELECT 1) in (SELECT 1);
+drop table t1;
+
+#
+# subselect into HAVING clause (code covarage improvement)
+#
+create table t1 (a int);
+create table t2 (a int);
+insert into t1 values (1),(2);
+insert into t2 values (0),(1),(2),(3);
+select a from t2 where a in (select a from t1);
+select a from t2 having a in (select a from t1);
+prepare stmt1 from "select a from t2 where a in (select a from t1)";
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+prepare stmt1 from "select a from t2 having a in (select a from t1)";
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+drop table t1, t2;
+
+#
+# single row subqueries and row operations (code covarage improvement)
+#
+create table t1 (a int, b int);
+insert into t1 values (1,2);
+-- error 1241
+select 1 = (select * from t1);
+-- error 1241
+select (select * from t1) = 1;
+-- error 1241
+select (1,2) = (select a from t1);
+-- error 1241
+select (select a from t1) = (1,2);
+-- error 1241
+select (1,2,3) = (select * from t1);
+-- error 1241
+select (select * from t1) = (1,2,3);
+drop table t1
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 26dc544482e..94cbd71c00c 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -717,3 +717,28 @@ drop table t1;
 #
 set @val:=6;
 select concat('value is: ', @val) union select 'some text';
+
+#
+# Enum merging test
+#
+CREATE TABLE t1 (
+  a ENUM('ä','ö','ü') character set utf8 not null default 'ü',
+  b ENUM("one", "two") character set utf8,
+  c ENUM("one", "two")
+);
+show create table t1;
+insert into t1 values ('ä', 'one', 'one'), ('ö', 'two', 'one'), ('ü', NULL, NULL);
+create table t2 select NULL union select a from t1;
+show columns from t2;
+drop table t2;
+create table t2 select a from t1 union select NULL;
+show columns from t2;
+drop table t2;
+create table t2 select a from t1 union select a from t1;
+show columns from t2;
+drop table t2;
+-- error 1267
+create table t2 select a from t1 union select c from t1;
+create table t2 select a from t1 union select b from t1;
+show columns from t2;
+drop table t2, t1;
diff --git a/sql/field.cc b/sql/field.cc
index 2baf1bf84fd..bf581e62f34 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -48,6 +48,793 @@ const char field_separator=',';
 
 #define DOUBLE_TO_STRING_CONVERSION_BUFFER_SIZE 320
 
+/*
+  Rules for merging different types of fields in UNION
+
+  NOTE: to avoid 256*256 table, gap in table types numeration is skiped
+  following #defines describe that gap and how to canculate number of fields
+  and index of field in thia array.
+*/
+#define FIELDTYPE_TEAR_FROM (MYSQL_TYPE_NEWDATE+1)
+#define FIELDTYPE_TEAR_TO   (MYSQL_TYPE_ENUM-1)
+#define FIELDTYPE_NUM (FIELDTYPE_TEAR_FROM + (255-FIELDTYPE_TEAR_TO))
+inline int field_type2index (enum_field_types field_type)
+{
+  return (field_type < FIELDTYPE_TEAR_FROM ?
+          field_type :
+          ((int)FIELDTYPE_TEAR_FROM) + (field_type - FIELDTYPE_TEAR_TO) - 1);
+}
+
+static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
+{
+  /* MYSQL_TYPE_DECIMAL -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_DECIMAL,     MYSQL_TYPE_DECIMAL,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_DECIMAL,     MYSQL_TYPE_DECIMAL,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_DOUBLE,      MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_DECIMAL,     MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_DECIMAL,     MYSQL_TYPE_DECIMAL,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_VAR_STRING,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_TINY -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_DECIMAL,     MYSQL_TYPE_TINY,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_SHORT,       MYSQL_TYPE_LONG,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_FLOAT,       MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_TINY,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_LONGLONG,    MYSQL_TYPE_INT24,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_VAR_STRING,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_SHORT -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_DECIMAL,     MYSQL_TYPE_SHORT,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_SHORT,       MYSQL_TYPE_LONG,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_FLOAT,       MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_SHORT,       MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_LONGLONG,    MYSQL_TYPE_INT24,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_SHORT,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_VAR_STRING,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_LONG -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_DECIMAL,     MYSQL_TYPE_LONG,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_LONG,        MYSQL_TYPE_LONG,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_DOUBLE,      MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_LONG,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_LONGLONG,    MYSQL_TYPE_LONG,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_LONG,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_VAR_STRING,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_FLOAT -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_DOUBLE,      MYSQL_TYPE_FLOAT,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_FLOAT,       MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_FLOAT,       MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_FLOAT,       MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_DOUBLE,      MYSQL_TYPE_FLOAT,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_FLOAT,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_VAR_STRING,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_DOUBLE -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_DOUBLE,      MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_DOUBLE,      MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_DOUBLE,      MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_DOUBLE,      MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_DOUBLE,      MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_VAR_STRING,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_NULL -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_DECIMAL,     MYSQL_TYPE_TINY,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_SHORT,       MYSQL_TYPE_LONG,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_FLOAT,       MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_NULL,        MYSQL_TYPE_TIMESTAMP,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_LONGLONG,    MYSQL_TYPE_INT24,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_NEWDATE,     MYSQL_TYPE_TIME,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_DATETIME,    MYSQL_TYPE_YEAR,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_NEWDATE,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_ENUM,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_SET,         MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_GEOMETRY
+  },
+  /* MYSQL_TYPE_TIMESTAMP -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_TIMESTAMP,   MYSQL_TYPE_TIMESTAMP,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_DATETIME,    MYSQL_TYPE_DATETIME,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_DATETIME,    MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_DATETIME,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_LONGLONG -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_DECIMAL,     MYSQL_TYPE_LONGLONG,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_LONGLONG,    MYSQL_TYPE_LONGLONG,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_DOUBLE,      MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_LONGLONG,    MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_LONGLONG,    MYSQL_TYPE_LONG,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_LONGLONG,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_VAR_STRING,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_INT24 -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_DECIMAL,     MYSQL_TYPE_INT24,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_INT24,       MYSQL_TYPE_LONG,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_FLOAT,       MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_INT24,       MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_LONGLONG,    MYSQL_TYPE_INT24,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_INT24,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_VAR_STRING,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_DATE -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_NEWDATE,     MYSQL_TYPE_DATETIME,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_NEWDATE,     MYSQL_TYPE_DATETIME,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_DATETIME,    MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_NEWDATE,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_TIME -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_TIME,        MYSQL_TYPE_DATETIME,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_DATETIME,    MYSQL_TYPE_TIME,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_DATETIME,    MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_DATETIME,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_DATETIME -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_DATETIME,    MYSQL_TYPE_DATETIME,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_DATETIME,    MYSQL_TYPE_DATETIME,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_DATETIME,    MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_DATETIME,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_YEAR -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_DECIMAL,     MYSQL_TYPE_TINY,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_SHORT,       MYSQL_TYPE_LONG,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_FLOAT,       MYSQL_TYPE_DOUBLE,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_YEAR,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_LONGLONG,    MYSQL_TYPE_INT24,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_YEAR,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_VAR_STRING,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_NEWDATE -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_NEWDATE,     MYSQL_TYPE_DATETIME,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_NEWDATE,     MYSQL_TYPE_DATETIME,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_DATETIME,    MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_NEWDATE,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_ENUM -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_ENUM,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_VAR_STRING,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_SET -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_SET,         MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_VAR_STRING,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_TINY_BLOB -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_TINY_BLOB,   MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_TINY_BLOB,   MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_TINY_BLOB,   MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_TINY_BLOB,   MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_TINY_BLOB,   MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_TINY_BLOB,   MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_TINY_BLOB,   MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_TINY_BLOB,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_TINY_BLOB,   MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_TINY_BLOB,   MYSQL_TYPE_TINY_BLOB
+  },
+  /* MYSQL_TYPE_MEDIUM_BLOB -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_MEDIUM_BLOB,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_MEDIUM_BLOB,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_MEDIUM_BLOB,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_MEDIUM_BLOB,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_MEDIUM_BLOB,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_MEDIUM_BLOB,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_MEDIUM_BLOB,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_MEDIUM_BLOB,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_MEDIUM_BLOB,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_MEDIUM_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_MEDIUM_BLOB,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_MEDIUM_BLOB
+  },
+  /* MYSQL_TYPE_LONG_BLOB -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_LONG_BLOB,   MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_LONG_BLOB,   MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_LONG_BLOB,   MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_LONG_BLOB,   MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_LONG_BLOB,   MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_LONG_BLOB,   MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_LONG_BLOB,   MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_LONG_BLOB,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_LONG_BLOB,   MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_LONG_BLOB,   MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_LONG_BLOB,   MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_LONG_BLOB,   MYSQL_TYPE_LONG_BLOB
+  },
+  /* MYSQL_TYPE_BLOB -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_BLOB,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_BLOB,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_BLOB,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_BLOB,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_BLOB,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_BLOB,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_BLOB,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_BLOB,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_BLOB,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_BLOB,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_BLOB
+  },
+  /* MYSQL_TYPE_VAR_STRING -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_VAR_STRING,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING
+  },
+  /* MYSQL_TYPE_STRING -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_STRING,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_STRING,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_STRING,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_STRING,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_STRING,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_STRING,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_STRING,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_STRING,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_STRING
+  },
+  /* MYSQL_TYPE_GEOMETRY -> */
+  {
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+    MYSQL_TYPE_GEOMETRY,    MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_NEWDATE      <14>
+    MYSQL_TYPE_VAR_STRING,
+  //<246>                   MYSQL_TYPE_ENUM
+                            MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+    MYSQL_TYPE_VAR_STRING,  MYSQL_TYPE_TINY_BLOB,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VAR_STRING,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+    MYSQL_TYPE_STRING,      MYSQL_TYPE_GEOMETRY
+  }
+};
+
+/*
+  Return type of which can carry value of both given types in UNION result
+
+  SYNOPSIS
+    Field::field_type_merge()
+    a, b  types for merging
+
+  RETURN
+    type of field
+*/
+
+enum_field_types Field::field_type_merge(enum_field_types a,
+                                         enum_field_types b)
+{
+  DBUG_ASSERT(a < FIELDTYPE_TEAR_FROM || a > FIELDTYPE_TEAR_TO);
+  DBUG_ASSERT(b < FIELDTYPE_TEAR_FROM || b > FIELDTYPE_TEAR_TO);
+  return field_types_merge_rules[field_type2index(a)]
+                                [field_type2index(b)];
+}
+
+
+static Item_result field_types_result_type [FIELDTYPE_NUM]=
+{
+  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
+  REAL_RESULT,              INT_RESULT,
+  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
+  INT_RESULT,               INT_RESULT,
+  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
+  REAL_RESULT,              REAL_RESULT,
+  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
+  STRING_RESULT,            STRING_RESULT,
+  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
+  INT_RESULT,               INT_RESULT,
+  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
+  STRING_RESULT,            STRING_RESULT,
+  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
+  STRING_RESULT,            INT_RESULT,
+  //MYSQL_TYPE_NEWDATE      <14>
+  STRING_RESULT,
+  //<246>                   MYSQL_TYPE_ENUM
+                            STRING_RESULT,
+  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
+  STRING_RESULT,            STRING_RESULT,
+  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
+  STRING_RESULT,            STRING_RESULT,
+  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
+  STRING_RESULT,            STRING_RESULT,
+  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
+  STRING_RESULT,            STRING_RESULT
+};
+
+
+/*
+  Detect Item_result by given field type of UNION merge result
+
+  SYNOPSIS
+    Field::result_merge_type()
+    field_type  given field type
+
+  RETURN
+    Item_result (type of internal MySQL expression result)
+*/
+
+Item_result Field::result_merge_type(enum_field_types field_type)
+{
+  DBUG_ASSERT(field_type < FIELDTYPE_TEAR_FROM || field_type
+              > FIELDTYPE_TEAR_TO);
+  return field_types_result_type[field_type2index(field_type)];
+}
+
 /*****************************************************************************
   Static help functions
 *****************************************************************************/
@@ -164,156 +951,6 @@ static bool test_if_real(const char *str,int length, CHARSET_INFO *cs)
 #endif
 
 
-/*
- Tables of filed type compatibility.
-
- There are tables for every type, table consist of list of types in which
- given type can be converted without data lost, list should be ended with
- FIELD_CAST_STOP
-*/
-static Field::field_cast_enum field_cast_decimal[]=
-{Field::FIELD_CAST_DECIMAL,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_tiny[]=
-{Field::FIELD_CAST_TINY,
- Field::FIELD_CAST_SHORT, Field::FIELD_CAST_MEDIUM, Field::FIELD_CAST_LONG,
- Field::FIELD_CAST_LONGLONG,
- Field::FIELD_CAST_FLOAT, Field::FIELD_CAST_DOUBLE,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_short[]=
-{Field::FIELD_CAST_SHORT,
- Field::FIELD_CAST_MEDIUM, Field::FIELD_CAST_LONG, Field::FIELD_CAST_LONGLONG,
- Field::FIELD_CAST_FLOAT, Field::FIELD_CAST_DOUBLE,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_medium[]=
-{Field::FIELD_CAST_MEDIUM,
- Field::FIELD_CAST_LONG, Field::FIELD_CAST_LONGLONG,
- Field::FIELD_CAST_DOUBLE,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_long[]=
-{Field::FIELD_CAST_LONG,
- Field::FIELD_CAST_LONGLONG,
- Field::FIELD_CAST_DOUBLE,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_longlong[]=
-{Field::FIELD_CAST_LONGLONG,
- Field::FIELD_CAST_DOUBLE,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_float[]=
-{Field::FIELD_CAST_FLOAT,
- Field::FIELD_CAST_DOUBLE,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_double[]=
-{Field::FIELD_CAST_DOUBLE,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_null[]=
-{Field::FIELD_CAST_NULL,
- Field::FIELD_CAST_DECIMAL, Field::FIELD_CAST_TINY, Field::FIELD_CAST_SHORT,
- Field::FIELD_CAST_MEDIUM, Field::FIELD_CAST_LONG, Field::FIELD_CAST_LONGLONG,
- Field::FIELD_CAST_FLOAT, Field::FIELD_CAST_DOUBLE,
- Field::FIELD_CAST_TIMESTAMP, Field::FIELD_CAST_YEAR,
- Field::FIELD_CAST_DATE, Field::FIELD_CAST_NEWDATE,
- Field::FIELD_CAST_TIME, Field::FIELD_CAST_DATETIME,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB,
- Field::FIELD_CAST_GEOM, Field::FIELD_CAST_ENUM, Field::FIELD_CAST_SET,
- Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_timestamp[]=
-{Field::FIELD_CAST_TIMESTAMP,
- Field::FIELD_CAST_DATETIME,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_year[]=
-{Field::FIELD_CAST_YEAR,
- Field::FIELD_CAST_SHORT, Field::FIELD_CAST_MEDIUM, Field::FIELD_CAST_LONG,
- Field::FIELD_CAST_LONGLONG,
- Field::FIELD_CAST_FLOAT, Field::FIELD_CAST_DOUBLE,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_date[]=
-{Field::FIELD_CAST_DATE,
- Field::FIELD_CAST_DATETIME,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_newdate[]=
-{Field::FIELD_CAST_NEWDATE,
- Field::FIELD_CAST_DATE,
- Field::FIELD_CAST_DATETIME,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_time[]=
-{Field::FIELD_CAST_TIME,
- Field::FIELD_CAST_DATETIME,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_datetime[]=
-{Field::FIELD_CAST_DATETIME,
- Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_string[]=
-{Field::FIELD_CAST_STRING,
- Field::FIELD_CAST_VARSTRING, Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_varstring[]=
-{Field::FIELD_CAST_VARSTRING,
- Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_blob[]=
-{Field::FIELD_CAST_BLOB,
- Field::FIELD_CAST_STOP};
-/*
-  Geometrical, enum and set fields can be casted only to expressions
-*/
-static Field::field_cast_enum field_cast_geom[]=
-{Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_enum[]=
-{Field::FIELD_CAST_STOP};
-static Field::field_cast_enum field_cast_set[]=
-{Field::FIELD_CAST_STOP};
-// Array of pointers on conversion table for all fields types casting
-static Field::field_cast_enum *field_cast_array[]=
-{0, //FIELD_CAST_STOP
- field_cast_decimal, field_cast_tiny, field_cast_short,
- field_cast_medium, field_cast_long, field_cast_longlong,
- field_cast_float, field_cast_double,
- field_cast_null,
- field_cast_timestamp, field_cast_year, field_cast_date, field_cast_newdate,
- field_cast_time, field_cast_datetime,
- field_cast_string, field_cast_varstring, field_cast_blob,
- field_cast_geom, field_cast_enum, field_cast_set
-};
-
-
-/*
-  Check if field of given type can store a value of this field.
-
-  SYNOPSIS
-    type   type for test
-
-  RETURN
-    1 can
-    0 can not
-*/
-
-bool Field::field_cast_compatible(Field::field_cast_enum type)
-{
-  DBUG_ASSERT(type != FIELD_CAST_STOP);
-  Field::field_cast_enum *array= field_cast_array[field_cast_type()];
-  while (*array != FIELD_CAST_STOP)
-  {
-    if (*(array++) == type)
-      return 1;
-  }
-  return 0;
-}
-
-
 /****************************************************************************
 ** Functions for the base classes
 ** This is an unpacked number.
@@ -373,9 +1010,15 @@ void Field_num::add_zerofill_and_unsigned(String &res) const
 void Field_num::make_field(Send_field *field)
 {
   /* table_cache_key is not set for temp tables */
-  field->db_name= (orig_table->table_cache_key ? orig_table->table_cache_key :
-		   "");
-  field->org_table_name= orig_table->real_name;
+  if (orig_table->table_cache_key)
+  {
+    field->db_name= orig_table->table_cache_key;
+    field->org_table_name= orig_table->real_name;
+  }
+  else
+  {
+    field->db_name= field->org_table_name= "";
+  }
   field->table_name= orig_table->table_name;
   field->col_name=field->org_col_name=field_name;
   field->charsetnr= charset()->number;
@@ -389,9 +1032,15 @@ void Field_num::make_field(Send_field *field)
 void Field_str::make_field(Send_field *field)
 {
   /* table_cache_key is not set for temp tables */
-  field->db_name= (orig_table->table_cache_key ? orig_table->table_cache_key :
-		   "");
-  field->org_table_name= orig_table->real_name;
+  if (orig_table->table_cache_key)
+  {
+    field->db_name= orig_table->table_cache_key;
+    field->org_table_name= orig_table->real_name;
+  }
+  else
+  {
+    field->db_name= field->org_table_name= "";
+  }
   field->table_name= orig_table->table_name;
   field->col_name=field->org_col_name=field_name;
   field->charsetnr= charset()->number;
@@ -6057,40 +6706,6 @@ Field *make_field(char *ptr, uint32 field_length,
 }
 
 
-/*
-  Check if field_type is appropriate field type
-  to create field for tmp table using
-  item->tmp_table_field() method
-
-  SYNOPSIS
-    field_types_to_be_kept()
-    field_type     - field type
-
-  NOTE
-    it is used in function get_holder_example_field()
-    from item.cc
-
-  RETURN
-    1 - can use item->tmp_table_field() method
-    0 - can not use item->tmp_table_field() method
-
-*/
-
-bool field_types_to_be_kept(enum_field_types field_type)
-{
-  switch (field_type)
-  {
-    case FIELD_TYPE_DATE:
-    case FIELD_TYPE_NEWDATE:
-    case FIELD_TYPE_TIME:
-    case FIELD_TYPE_DATETIME:
-      return 1;
-    default:
-      return 0;
-  }
-}
-
-
 /* Create a field suitable for create of table */
 
 create_field::create_field(Field *old_field,Field *orig_field)
diff --git a/sql/field.h b/sql/field.h
index b5d88504939..5dc124aba35 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -31,6 +31,17 @@ class Protocol;
 struct st_cache_field;
 void field_conv(Field *to,Field *from);
 
+inline uint get_enum_pack_length(int elements)
+{
+  return elements < 256 ? 1 : 2;
+}
+
+inline uint get_set_pack_length(int elements)
+{
+  uint len= (elements + 7) / 8;
+  return len > 4 ? 8 : len;
+}
+
 class Field
 {
   Field(const Item &);				/* Prevent use of these */
@@ -75,17 +86,6 @@ public:
     GEOM_GEOMETRYCOLLECTION = 7
   };
   enum imagetype { itRAW, itMBR};
-  enum field_cast_enum
-  {
-    FIELD_CAST_STOP, FIELD_CAST_DECIMAL, FIELD_CAST_TINY, FIELD_CAST_SHORT,
-    FIELD_CAST_MEDIUM, FIELD_CAST_LONG, FIELD_CAST_LONGLONG,
-    FIELD_CAST_FLOAT, FIELD_CAST_DOUBLE,
-    FIELD_CAST_NULL,
-    FIELD_CAST_TIMESTAMP, FIELD_CAST_YEAR, FIELD_CAST_DATE, FIELD_CAST_NEWDATE,
-    FIELD_CAST_TIME, FIELD_CAST_DATETIME,
-    FIELD_CAST_STRING, FIELD_CAST_VARSTRING, FIELD_CAST_BLOB,
-    FIELD_CAST_GEOM, FIELD_CAST_ENUM, FIELD_CAST_SET
-  };
 
   utype		unireg_check;
   uint32	field_length;		// Length of field
@@ -119,6 +119,8 @@ public:
   virtual String *val_str(String*,String *)=0;
   virtual Item_result result_type () const=0;
   virtual Item_result cmp_type () const { return result_type(); }
+  static enum_field_types field_type_merge(enum_field_types, enum_field_types);
+  static Item_result result_merge_type(enum_field_types);
   bool eq(Field *field) { return ptr == field->ptr && null_ptr == field->null_ptr; }
   virtual bool eq_def(Field *field);
   virtual uint32 pack_length() const { return (uint32) field_length; }
@@ -290,8 +292,6 @@ public:
                             int cuted_increment);
   void set_datetime_warning(const uint level, const uint code, 
                             double nr, timestamp_type ts_type);
-  virtual field_cast_enum field_cast_type()= 0;
-  bool field_cast_compatible(field_cast_enum type);
   /* maximum possible display length */
   virtual uint32 max_length()= 0;
   friend bool reopen_table(THD *,struct st_table *,bool);
@@ -398,7 +398,6 @@ public:
   bool zero_pack() const { return 0; }
   void sql_type(String &str) const;
   uint32 max_length() { return field_length; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_DECIMAL; }
 };
 
 
@@ -430,7 +429,6 @@ public:
   uint32 pack_length() const { return 1; }
   void sql_type(String &str) const;
   uint32 max_length() { return 4; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_TINY; }
 };
 
 
@@ -467,7 +465,6 @@ public:
   uint32 pack_length() const { return 2; }
   void sql_type(String &str) const;
   uint32 max_length() { return 6; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_SHORT; }
 };
 
 
@@ -499,7 +496,6 @@ public:
   uint32 pack_length() const { return 3; }
   void sql_type(String &str) const;
   uint32 max_length() { return 8; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_MEDIUM; }
 };
 
 
@@ -536,7 +532,6 @@ public:
   uint32 pack_length() const { return 4; }
   void sql_type(String &str) const;
   uint32 max_length() { return 11; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_LONG; }
 };
 
 
@@ -576,7 +571,6 @@ public:
   void sql_type(String &str) const;
   bool can_be_compared_as_longlong() const { return TRUE; }
   uint32 max_length() { return 20; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_LONGLONG; }
 };
 #endif
 
@@ -611,7 +605,6 @@ public:
   uint32 pack_length() const { return sizeof(float); }
   void sql_type(String &str) const;
   uint32 max_length() { return 24; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_FLOAT; }
 };
 
 
@@ -646,7 +639,6 @@ public:
   uint32 pack_length() const { return sizeof(double); }
   void sql_type(String &str) const;
   uint32 max_length() { return 53; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_DOUBLE; }
 };
 
 
@@ -677,7 +669,6 @@ public:
   void sql_type(String &str) const;
   uint size_of() const { return sizeof(*this); }
   uint32 max_length() { return 4; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_NULL; }
 };
 
 
@@ -729,7 +720,6 @@ public:
   }
   bool get_date(TIME *ltime,uint fuzzydate);
   bool get_time(TIME *ltime);
-  field_cast_enum field_cast_type() { return FIELD_CAST_TIMESTAMP; }
   timestamp_auto_set_type get_auto_set_type() const;
 };
 
@@ -753,7 +743,6 @@ public:
   bool send_binary(Protocol *protocol);
   void sql_type(String &str) const;
   bool can_be_compared_as_longlong() const { return TRUE; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_YEAR; }
 };
 
 
@@ -786,7 +775,6 @@ public:
   void sql_type(String &str) const;
   bool can_be_compared_as_longlong() const { return TRUE; }
   bool zero_pack() const { return 1; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_DATE; }
 };
 
 class Field_newdate :public Field_str {
@@ -818,7 +806,6 @@ public:
   bool zero_pack() const { return 1; }
   bool get_date(TIME *ltime,uint fuzzydate);
   bool get_time(TIME *ltime);
-  field_cast_enum field_cast_type() { return FIELD_CAST_NEWDATE; }
 };
 
 
@@ -853,7 +840,6 @@ public:
   void sql_type(String &str) const;
   bool can_be_compared_as_longlong() const { return TRUE; }
   bool zero_pack() const { return 1; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_TIME; }
 };
 
 
@@ -891,7 +877,6 @@ public:
   bool zero_pack() const { return 1; }
   bool get_date(TIME *ltime,uint fuzzydate);
   bool get_time(TIME *ltime);
-  field_cast_enum field_cast_type() { return FIELD_CAST_DATETIME; }
 };
 
 
@@ -937,7 +922,6 @@ public:
   enum_field_types real_type() const { return FIELD_TYPE_STRING; }
   bool has_charset(void) const
   { return charset() == &my_charset_bin ? FALSE : TRUE; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_STRING; }
 };
 
 
@@ -986,7 +970,6 @@ public:
   enum_field_types real_type() const { return FIELD_TYPE_VAR_STRING; }
   bool has_charset(void) const
   { return charset() == &my_charset_bin ? FALSE : TRUE; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_VARSTRING; }
 };
 
 
@@ -1081,7 +1064,6 @@ public:
   uint size_of() const { return sizeof(*this); }
   bool has_charset(void) const
   { return charset() == &my_charset_bin ? FALSE : TRUE; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_BLOB; }
   uint32 max_length();
 };
 
@@ -1111,7 +1093,6 @@ public:
 
   void get_key_image(char *buff,uint length, CHARSET_INFO *cs,imagetype type);
   void set_key_image(char *buff,uint length, CHARSET_INFO *cs);
-  field_cast_enum field_cast_type() { return FIELD_CAST_GEOM; }
 };
 #endif /*HAVE_SPATIAL*/
 
@@ -1155,7 +1136,6 @@ public:
   bool has_charset(void) const { return TRUE; }
   /* enum and set are sorted as integers */
   CHARSET_INFO *sort_charset(void) const { return &my_charset_bin; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_ENUM; }
 };
 
 
@@ -1181,7 +1161,6 @@ public:
   void sql_type(String &str) const;
   enum_field_types real_type() const { return FIELD_TYPE_SET; }
   bool has_charset(void) const { return TRUE; }
-  field_cast_enum field_cast_type() { return FIELD_CAST_SET; }
 };
 
 
@@ -1268,7 +1247,6 @@ int set_field_to_null(Field *field);
 int set_field_to_null_with_conversions(Field *field, bool no_conversions);
 bool test_if_int(const char *str, int length, const char *int_end,
 		 CHARSET_INFO *cs);
-bool field_types_to_be_kept(enum_field_types field_type);
 
 /*
   The following are for the interface with the .frm file
diff --git a/sql/item.cc b/sql/item.cc
index 2250bd9b32c..8bc6b878868 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1700,10 +1700,10 @@ Field *Item::tmp_table_field_from_field_type(TABLE *table)
   case MYSQL_TYPE_NULL:
     return new Field_null((char*) 0, max_length, Field::NONE,
 			  name, table, &my_charset_bin);
-  case MYSQL_TYPE_NEWDATE:
   case MYSQL_TYPE_INT24:
     return new Field_medium((char*) 0, max_length, null_ptr, 0, Field::NONE,
 			    name, table, 0, unsigned_flag);
+  case MYSQL_TYPE_NEWDATE:
   case MYSQL_TYPE_DATE:
     return new Field_date(maybe_null, name, table, &my_charset_bin);
   case MYSQL_TYPE_TIME:
@@ -2701,204 +2701,268 @@ void Item_cache_row::bring_value()
 }
 
 
-/*
-  Returns field for temporary table dependind on item type
-
-  SYNOPSIS
-    get_holder_example_field()
-    thd            - thread handler
-    item           - pointer to item
-    table          - empty table object
-
-  NOTE
-    It is possible to return field for Item_func 
-    items only if field type of this item is 
-    date or time or datetime type.
-    also see function field_types_to_be_kept() from
-    field.cc
-
-  RETURN
-    # - field
-    0 - no field
-*/
-
-Field *get_holder_example_field(THD *thd, Item *item, TABLE *table)
-{
-  DBUG_ASSERT(table);
-
-  Item_func *tmp_item= 0;
-  if (item->type() == Item::FIELD_ITEM)
-    return (((Item_field*) item)->field);
-  if (item->type() == Item::FUNC_ITEM)
-    tmp_item= (Item_func *) item;
-  else if (item->type() == Item::SUM_FUNC_ITEM)
-  {
-    Item_sum *item_sum= (Item_sum *) item;
-    if (item_sum->keep_field_type())
-    {
-      if (item_sum->args[0]->type() == Item::FIELD_ITEM)
-        return (((Item_field*) item_sum->args[0])->field);
-      if (item_sum->args[0]->type() == Item::FUNC_ITEM)
-        tmp_item= (Item_func *) item_sum->args[0];
-    }
-  }
-  return (tmp_item && field_types_to_be_kept(tmp_item->field_type()) ?
-          tmp_item->tmp_table_field(table) : 0);
-}
-
-
-Item_type_holder::Item_type_holder(THD *thd, Item *item, TABLE *table)
-  :Item(thd, item), item_type(item->result_type()),
-   orig_type(item_type)
+Item_type_holder::Item_type_holder(THD *thd, Item *item)
+  :Item(thd, item), enum_set_typelib(0), fld_type(get_real_type(item))
 {
   DBUG_ASSERT(item->fixed);
 
-  /*
-    It is safe assign pointer on field, because it will be used just after
-    all JOIN::prepare calls and before any SELECT execution
-  */
-  field_example= get_holder_example_field(thd, item, table);
-  max_length= real_length(item);
+  max_length= display_length(item);
   maybe_null= item->maybe_null;
   collation.set(item->collation);
+  get_full_info(item);
 }
 
 
 /*
-  STRING_RESULT, REAL_RESULT, INT_RESULT, ROW_RESULT
+  Return expression type of Item_type_holder
 
-  ROW_RESULT should never appear in Item_type_holder::join_types,
-  but it is included in following table just to make table full
-  (there DBUG_ASSERT in function to catch ROW_RESULT)
+  SYNOPSIS
+    Item_type_holder::result_type()
+
+  RETURN
+     Item_result (type of internal MySQL expression result)
 */
-static Item_result type_convertor[4][4]=
-{{STRING_RESULT, STRING_RESULT, STRING_RESULT, ROW_RESULT},
- {STRING_RESULT, REAL_RESULT,   REAL_RESULT,   ROW_RESULT},
- {STRING_RESULT, REAL_RESULT,   INT_RESULT,    ROW_RESULT},
- {ROW_RESULT,    ROW_RESULT,    ROW_RESULT,    ROW_RESULT}};
+
+Item_result Item_type_holder::result_type() const
+{
+  return Field::result_merge_type(fld_type);
+}
 
 
 /*
-  Values of 'from' field can be stored in 'to' field.
+  Find real field type of item
 
   SYNOPSIS
-    is_attr_compatible()
-    from        Item which values should be saved
-    to          Item where values should be saved
+    Item_type_holder::get_real_type()
 
   RETURN
-    1   can be saved
-    0   can not be saved
+    type of field which should be created to store item value
 */
 
-inline bool is_attr_compatible(Item *from, Item *to)
-{
-  return ((to->max_length >= from->max_length) &&
-          (to->maybe_null || !from->maybe_null) &&
-          (to->result_type() != STRING_RESULT ||
-           from->result_type() != STRING_RESULT ||
-          (from->collation.collation == to->collation.collation)));
-}
-
-
-bool Item_type_holder::join_types(THD *thd, Item *item, TABLE *table)
+enum_field_types Item_type_holder::get_real_type(Item *item)
 {
-  uint32 new_length= real_length(item);
-  bool use_new_field= 0, use_expression_type= 0;
-  Item_result new_result_type= type_convertor[item_type][item->result_type()];
-  Field *field= get_holder_example_field(thd, item, table);
-  bool item_is_a_field= (field != NULL);
-  /*
-    Check if both items point to fields: in this case we
-    can adjust column types of result table in the union smartly.
-  */
-  if (field_example && item_is_a_field)
+  switch(item->type())
   {
-    /* Can 'field_example' field store data of the column? */
-    if ((use_new_field=
-         (!field->field_cast_compatible(field_example->field_cast_type()) ||
-          !is_attr_compatible(item, this))))
-    {
-      /*
-        The old field can't store value of the new field.
-        Check if the new field can store value of the old one.
-      */
-      use_expression_type|=
-        (!field_example->field_cast_compatible(field->field_cast_type()) ||
-         !is_attr_compatible(this, item));
-    }
-  }
-  else if (field_example || item_is_a_field)
+  case FIELD_ITEM:
   {
     /*
-      Expression types can't be mixed with field types, we have to use
-      expression types.
+      Item_fields::field_type ask Field_type() but sometimes field return
+      a different type, like for enum/set, so we need to ask real type.
     */
-    use_new_field= 1;                           // make next if test easier
-    use_expression_type= 1;
+    Field *field= ((Item_field *) item)->field;
+    enum_field_types type= field->real_type();
+    /* work around about varchar type field detection */
+    if (type == MYSQL_TYPE_STRING && field->type() == MYSQL_TYPE_VAR_STRING)
+      return MYSQL_TYPE_VAR_STRING;
+    return type;
   }
-
-  /* Check whether size/type of the result item should be changed */
-  if (use_new_field ||
-      (new_result_type != item_type) || (new_length > max_length) ||
-      (!maybe_null && item->maybe_null) ||
-      (item_type == STRING_RESULT && 
-       collation.collation != item->collation.collation))
+  case SUM_FUNC_ITEM:
   {
-    const char *old_cs,*old_derivation;
-    if (use_expression_type || !item_is_a_field)
-      field_example= 0;
-    else
+    /*
+      Argument of aggregate function sometimes should be asked about field
+      type
+    */
+    Item_sum *item_sum= (Item_sum *) item;
+    if (item_sum->keep_field_type())
+      return get_real_type(item_sum->args[0]);
+    break;
+  }
+  case FUNC_ITEM:
+    if (((Item_func *) item)->functype() == Item_func::VAR_VALUE_FUNC)
     {
       /*
-        It is safe to assign a pointer to field here, because it will be used
-        before any table is closed.
+        There are work around of problem with changing variable type on the
+        fly and variable always report "string" as field type to get
+        acceptable information for client in send_field, so we make field
+        type from expression type.
       */
-      field_example= field;
+      switch (item->result_type())
+      {
+      case STRING_RESULT:
+        return MYSQL_TYPE_VAR_STRING;
+      case INT_RESULT:
+        return MYSQL_TYPE_LONGLONG;
+      case REAL_RESULT:
+        return MYSQL_TYPE_DOUBLE;
+      case ROW_RESULT:
+      default:
+        DBUG_ASSERT(0);
+        return MYSQL_TYPE_VAR_STRING;
+      }
     }
+    break;
+  default:
+    break;
+  }
+  return item->field_type();
+}
+
+/*
+  Find field type which can carry current Item_type_holder type and
+  type of given Item.
 
+  SYNOPSIS
+    Item_type_holder::join_types()
+    thd     thread handler
+    item    given item to join its parameters with this item ones
+
+  RETURN
+    TRUE   error - types are incompatible
+    FALSE  OK
+*/
+
+bool Item_type_holder::join_types(THD *thd, Item *item)
+{
+  max_length= max(max_length, display_length(item));
+  fld_type= Field::field_type_merge(fld_type, get_real_type(item));
+  if (Field::result_merge_type(fld_type) == STRING_RESULT)
+  {
+    const char *old_cs, *old_derivation;
     old_cs= collation.collation->name;
     old_derivation= collation.derivation_name();
-    if (item_type == STRING_RESULT && collation.aggregate(item->collation))
+    if (collation.aggregate(item->collation))
     {
       my_error(ER_CANT_AGGREGATE_2COLLATIONS, MYF(0),
 	       old_cs, old_derivation,
 	       item->collation.collation->name,
 	       item->collation.derivation_name(),
 	       "UNION");
-      return 1;
+      return TRUE;
     }
-
-    max_length= max(max_length, new_length);
-    decimals= max(decimals, item->decimals);
-    maybe_null|= item->maybe_null;
-    item_type= new_result_type;
   }
-  DBUG_ASSERT(item_type != ROW_RESULT);
-  return 0;
+  decimals= max(decimals, item->decimals);
+  maybe_null|= item->maybe_null;
+  get_full_info(item);
+  return FALSE;
 }
 
+/*
+  Calculate lenth for merging result for given Item type
 
-uint32 Item_type_holder::real_length(Item *item)
+  SYNOPSIS
+    Item_type_holder::real_length()
+    item  Item for lrngth detection
+
+  RETURN
+    length
+*/
+
+uint32 Item_type_holder::display_length(Item *item)
 {
   if (item->type() == Item::FIELD_ITEM)
     return ((Item_field *)item)->max_disp_length();
 
-  switch (item->result_type())
+  switch (item->field_type())
   {
-  case STRING_RESULT:
+  case MYSQL_TYPE_DECIMAL:
+  case MYSQL_TYPE_TIMESTAMP:
+  case MYSQL_TYPE_DATE:
+  case MYSQL_TYPE_TIME:
+  case MYSQL_TYPE_DATETIME:
+  case MYSQL_TYPE_YEAR:
+  case MYSQL_TYPE_NEWDATE:
+  case MYSQL_TYPE_ENUM:
+  case MYSQL_TYPE_SET:
+  case MYSQL_TYPE_TINY_BLOB:
+  case MYSQL_TYPE_MEDIUM_BLOB:
+  case MYSQL_TYPE_LONG_BLOB:
+  case MYSQL_TYPE_BLOB:
+  case MYSQL_TYPE_VAR_STRING:
+  case MYSQL_TYPE_STRING:
+  case MYSQL_TYPE_GEOMETRY:
     return item->max_length;
-  case REAL_RESULT:
+  case MYSQL_TYPE_TINY:
+    return 4;
+  case MYSQL_TYPE_SHORT:
+    return 6;
+  case MYSQL_TYPE_LONG:
+    return 11;
+  case MYSQL_TYPE_FLOAT:
+    return 25;
+  case MYSQL_TYPE_DOUBLE:
     return 53;
-  case INT_RESULT:
+  case MYSQL_TYPE_NULL:
+    return 4;
+  case MYSQL_TYPE_LONGLONG:
     return 20;
-  case ROW_RESULT:
+  case MYSQL_TYPE_INT24:
+    return 8;
   default:
     DBUG_ASSERT(0); // we should never go there
     return 0;
   }
 }
 
+
+/*
+  Make temporary table field according collected information about type
+  of UNION result
+
+  SYNOPSIS
+    Item_type_holder::make_field_by_type()
+    table  temporary table for which we create fields
+
+  RETURN
+    created field
+*/
+
+Field *Item_type_holder::make_field_by_type(TABLE *table)
+{
+  if (fld_type == MYSQL_TYPE_ENUM || fld_type == MYSQL_TYPE_SET)
+  {
+    DBUG_ASSERT(enum_set_typelib);
+    /*
+      The field functions defines a field to be not null if null_ptr is not 0
+    */
+    uchar *null_ptr= maybe_null ? (uchar*) "" : 0;
+
+    if (fld_type == MYSQL_TYPE_ENUM)
+      return new Field_enum((char *) 0, max_length, null_ptr, 0,
+		 Field::NONE, name,
+		 table, get_enum_pack_length(enum_set_typelib->count),
+		 enum_set_typelib, collation.collation);
+    else
+      return new Field_set((char *) 0, max_length, null_ptr, 0,
+		 Field::NONE, name,
+		 table, get_set_pack_length(enum_set_typelib->count),
+		 enum_set_typelib, collation.collation);
+  }
+  return tmp_table_field_from_field_type(table);
+}
+
+
+/*
+  Get full information from Item about enum/set fields to be able to create
+  them later
+
+  SYNOPSIS
+    Item_type_holder::get_full_info
+    item    Item for information collection
+*/
+void Item_type_holder::get_full_info(Item *item)
+{
+  if (fld_type == MYSQL_TYPE_ENUM ||
+      fld_type == MYSQL_TYPE_SET)
+  {
+    /*
+      We can have enum/set type after merging only if we have one enum/set
+      field and number of NULL fields
+    */
+    DBUG_ASSERT((enum_set_typelib &&
+                 get_real_type(item) == MYSQL_TYPE_NULL) ||
+                (!enum_set_typelib &&
+                 item->type() == Item::FIELD_ITEM &&
+                 (get_real_type(item) == MYSQL_TYPE_ENUM ||
+                  get_real_type(item) == MYSQL_TYPE_SET) &&
+                 ((Field_enum*)((Item_field *) item)->field)->typelib));
+    if (!enum_set_typelib)
+    {
+      enum_set_typelib= ((Field_enum*)((Item_field *) item)->field)->typelib;
+    }
+  }
+}
+
+
 double Item_type_holder::val()
 {
   DBUG_ASSERT(0); // should never be called
diff --git a/sql/item.h b/sql/item.h
index a90bb184449..f44b4bed98d 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1332,32 +1332,32 @@ public:
 
 
 /*
-  Used to store type. name, length of Item for UNIONS & derived table
+  Item_type_holder used to store type. name, length of Item for UNIONS &
+  derived tables.
+
+  Item_type_holder do not need cleanup() because its time of live limited by
+  single SP/PS execution.
 */
 class Item_type_holder: public Item
 {
 protected:
-  Item_result item_type;
-  Item_result orig_type;
-  Field *field_example;
+  TYPELIB *enum_set_typelib;
+  enum_field_types fld_type;
+
+  void get_full_info(Item *item);
 public:
-  Item_type_holder(THD*, Item*, TABLE *);
+  Item_type_holder(THD*, Item*);
 
-  Item_result result_type () const { return item_type; }
+  Item_result result_type() const;
+  virtual enum_field_types field_type() const { return fld_type; };
   enum Type type() const { return TYPE_HOLDER; }
   double val();
   longlong val_int();
   String *val_str(String*);
-  bool join_types(THD *thd, Item *, TABLE *);
-  Field *example() { return field_example; }
-  static uint32 real_length(Item *item);
-  void cleanup()
-  {
-    DBUG_ENTER("Item_type_holder::cleanup");
-    Item::cleanup();
-    item_type= orig_type;
-    DBUG_VOID_RETURN;
-  }
+  bool join_types(THD *thd, Item *);
+  Field *make_field_by_type(TABLE *table);
+  static uint32 display_length(Item *item);
+  static enum_field_types get_real_type(Item *);
 };
 
 
diff --git a/sql/item_func.h b/sql/item_func.h
index bfc3bb0de8b..3a309f4ae99 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -51,7 +51,7 @@ public:
 		  SP_CONTAINS_FUNC,SP_OVERLAPS_FUNC,
 		  SP_STARTPOINT,SP_ENDPOINT,SP_EXTERIORRING,
 		  SP_POINTN,SP_GEOMETRYN,SP_INTERIORRINGN,
-		  NOT_FUNC, NOT_ALL_FUNC, NOW_FUNC};
+		  NOT_FUNC, NOT_ALL_FUNC, NOW_FUNC, VAR_VALUE_FUNC};
   enum optimize_type { OPTIMIZE_NONE,OPTIMIZE_KEY,OPTIMIZE_OP, OPTIMIZE_NULL };
   enum Type type() const { return FUNC_ITEM; }
   virtual enum Functype functype() const   { return UNKNOWN_FUNC; }
@@ -981,6 +981,7 @@ public:
     select @t1:=1,@t1,@t:="hello",@t from foo where (@t1:= t2.b)
   */
   enum_field_types field_type() const  { return MYSQL_TYPE_STRING; }
+  enum Functype functype() const   { return VAR_VALUE_FUNC; }
   const char *func_name() const { return "get_user_var"; }
   bool const_item() const;
   table_map used_tables() const
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 3a1e1918e55..301740c50ef 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -369,25 +369,13 @@ Item_singlerow_subselect::select_transformer(JOIN *join)
     }
     substitution= select_lex->item_list.head();
     /*
-      as far as we moved content to upper leven, field which depend of
+      as far as we moved content to upper level, field which depend of
       'upper' select is not really dependent => we remove this dependence
     */
     substitution->walk(&Item::remove_dependence_processor,
 		       (byte *) select_lex->outer_select());
-    if (join->conds || join->having)
-    {
-      Item *cond;
-      if (!join->having)
-	cond= join->conds;
-      else if (!join->conds)
-	cond= join->having;
-      else
-	if (!(cond= new Item_cond_and(join->conds, join->having)))
-	  goto err;
-      if (!(substitution= new Item_func_if(cond, substitution,
-					   new Item_null())))
-	goto err;
-   }
+    /* SELECT without FROM clause can't have WHERE or HAVING clause */
+    DBUG_ASSERT(join->conds == 0 && join->having == 0);
     return RES_REDUCE;
   }
   return RES_OK;
@@ -542,7 +530,7 @@ bool Item_in_subselect::test_limit(SELECT_LEX_UNIT *unit)
 
 Item_in_subselect::Item_in_subselect(Item * left_exp,
 				     st_select_lex *select_lex):
-  Item_exists_subselect(), transformed(0), upper_item(0)
+  Item_exists_subselect(), optimizer(0), transformed(0), upper_item(0)
 {
   DBUG_ENTER("Item_in_subselect::Item_in_subselect");
   left_expr= left_exp;
@@ -616,8 +604,14 @@ String *Item_exists_subselect::val_str(String *str)
   return str;
 }
 
+
 double Item_in_subselect::val()
 {
+  /*
+    As far as Item_in_subselect called only from Item_in_optimizer this
+    method should not be used
+  */
+  DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
   if (exec())
   {
@@ -630,6 +624,7 @@ double Item_in_subselect::val()
   return (double) value;
 }
 
+
 longlong Item_in_subselect::val_int()
 {
   DBUG_ASSERT(fixed == 1);
@@ -644,8 +639,14 @@ longlong Item_in_subselect::val_int()
   return value;
 }
 
+
 String *Item_in_subselect::val_str(String *str)
 {
+  /*
+    As far as Item_in_subselect called only from Item_in_optimizer this
+    method should not be used
+  */
+  DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
   if (exec())
   {
@@ -669,18 +670,9 @@ Item_subselect::trans_res
 Item_in_subselect::single_value_transformer(JOIN *join,
 					    Comp_creator *func)
 {
-  const char *save_where= thd->where;
   DBUG_ENTER("Item_in_subselect::single_value_transformer");
 
-  if (changed)
-  {
-    DBUG_RETURN(RES_OK);
-  }
-
   SELECT_LEX *select_lex= join->select_lex;
-  Item_arena *arena, backup;
-  arena= thd->change_arena_if_needed(&backup);
-  thd->where= "scalar IN/ALL/ANY subquery";
 
   /*
     Check that the right part of the subselect contains no more than one
@@ -689,7 +681,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
   if (select_lex->item_list.elements > 1)
   {
     my_error(ER_OPERAND_COLUMNS, MYF(0), 1);
-    goto err;
+    DBUG_RETURN(RES_ERROR);
   }
 
   /*
@@ -709,11 +701,12 @@ Item_in_subselect::single_value_transformer(JOIN *join,
     if (substitution)
     {
       // It is second (third, ...) SELECT of UNION => All is done
-      goto ok;
+      DBUG_RETURN(RES_OK);
     }
 
     Item *subs;
     if (!select_lex->group_list.elements &&
+        !select_lex->having &&
 	!select_lex->with_sum_func &&
 	!(select_lex->next_select()))
     {
@@ -748,7 +741,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 	reference
       */
       if (item->fix_fields(thd, join->tables_list, 0))
-	goto err;
+	DBUG_RETURN(RES_ERROR);
       /* we added aggregate function => we have to change statistic */
       count_field_types(&join->tmp_table_param, join->all_fields, 0);
 
@@ -764,25 +757,16 @@ Item_in_subselect::single_value_transformer(JOIN *join,
       if (upper_item)
         upper_item->set_sub_test(item);
     }
-    // left expression belong to outer select
-    SELECT_LEX *current= thd->lex->current_select, *up;
-    thd->lex->current_select= up= current->return_after_parsing();
-    if (!left_expr->fixed && 
-        left_expr->fix_fields(thd, up->get_table_list(), &left_expr))
-    {
-      thd->lex->current_select= current;
-      goto err;
-    }
-    thd->lex->current_select= current;
+    /* fix fields is already called for  left expression */
     substitution= func->create(left_expr, subs);
-    goto ok;
+    DBUG_RETURN(RES_OK);
   }
 
   if (!substitution)
   {
     //first call for this unit
     SELECT_LEX_UNIT *unit= select_lex->master_unit();
-    substitution= optimizer= new Item_in_optimizer(left_expr, this);
+    substitution= optimizer;
 
     SELECT_LEX *current= thd->lex->current_select, *up;
 
@@ -791,7 +775,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
     if (!optimizer || optimizer->fix_left(thd, up->get_table_list(), 0))
     {
       thd->lex->current_select= current;
-      goto err;
+      DBUG_RETURN(RES_ERROR);
     }
     thd->lex->current_select= current;
 
@@ -835,7 +819,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
     if (join->having->fix_fields(thd, join->tables_list, 0))
     {
       select_lex->having_fix_field= 0;
-      goto err;
+      DBUG_RETURN(RES_ERROR);
     }
     select_lex->having_fix_field= 0;
   }
@@ -865,7 +849,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 	if (join->having->fix_fields(thd, join->tables_list, 0))
 	{
 	  select_lex->having_fix_field= 0;
-	  goto err;
+	  DBUG_RETURN(RES_ERROR);
 	}
 	select_lex->having_fix_field= 0;
 	item= new Item_cond_or(item,
@@ -879,7 +863,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
       */
       select_lex->where= join->conds= and_items(join->conds, item);
       if (join->conds->fix_fields(thd, join->tables_list, 0))
-	goto err;
+	DBUG_RETURN(RES_ERROR);
     }
     else
     {
@@ -901,7 +885,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 				     0))
 	{
 	  select_lex->having_fix_field= 0;
-	  goto err;
+	  DBUG_RETURN(RES_ERROR);
 	}
 	select_lex->having_fix_field= 0;
       }
@@ -919,54 +903,34 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 	  push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
 		       ER_SELECT_REDUCED, warn_buff);
 	}
-	if (arena)
-	  thd->restore_backup_item_arena(arena, &backup);
 	DBUG_RETURN(RES_REDUCE);
       }
     }
   }
 
-ok:
-  if (arena)
-    thd->restore_backup_item_arena(arena, &backup);
-  thd->where= save_where;
   DBUG_RETURN(RES_OK);
-
-err:
-  if (arena)
-    thd->restore_backup_item_arena(arena, &backup);
-  DBUG_RETURN(RES_ERROR);
 }
 
 
 Item_subselect::trans_res
 Item_in_subselect::row_value_transformer(JOIN *join)
 {
-  const char *save_where= thd->where;
   DBUG_ENTER("Item_in_subselect::row_value_transformer");
 
-  if (changed)
-  {
-    DBUG_RETURN(RES_OK);
-  }
-  Item_arena *arena, backup;
   Item *item= 0;
   SELECT_LEX *select_lex= join->select_lex;
 
-  thd->where= "row IN/ALL/ANY subquery";
-  arena= thd->change_arena_if_needed(&backup);
-
   if (select_lex->item_list.elements != left_expr->cols())
   {
     my_error(ER_OPERAND_COLUMNS, MYF(0), left_expr->cols());
-    goto err;
+    DBUG_RETURN(RES_ERROR);
   }
 
   if (!substitution)
   {
     //first call for this unit
     SELECT_LEX_UNIT *unit= select_lex->master_unit();
-    substitution= optimizer= new Item_in_optimizer(left_expr, this);
+    substitution= optimizer;
 
     SELECT_LEX *current= thd->lex->current_select, *up;
     thd->lex->current_select= up= current->return_after_parsing();
@@ -974,7 +938,7 @@ Item_in_subselect::row_value_transformer(JOIN *join)
     if (!optimizer || optimizer->fix_left(thd, up->get_table_list(), 0))
     {
       thd->lex->current_select= current;
-      goto err;
+      DBUG_RETURN(RES_ERROR);
     }
 
     // we will refer to apper level cache array => we have to save it in PS
@@ -993,7 +957,7 @@ Item_in_subselect::row_value_transformer(JOIN *join)
       DBUG_ASSERT(left_expr->fixed && select_lex->ref_pointer_array[i]->fixed);
       if (select_lex->ref_pointer_array[i]->
           check_cols(left_expr->el(i)->cols()))
-        goto err;
+        DBUG_RETURN(RES_ERROR);
       Item *func= new Item_ref_null_helper(this,
 					   select_lex->ref_pointer_array+i,
 					   (char *) "<no matter>",
@@ -1021,7 +985,7 @@ Item_in_subselect::row_value_transformer(JOIN *join)
     if (join->having->fix_fields(thd, join->tables_list, 0))
     {
       select_lex->having_fix_field= 0;
-      goto err;
+      DBUG_RETURN(RES_ERROR);
     }
     select_lex->having_fix_field= 0;
   }
@@ -1034,27 +998,112 @@ Item_in_subselect::row_value_transformer(JOIN *join)
     */
     select_lex->where= join->conds= and_items(join->conds, item);
     if (join->conds->fix_fields(thd, join->tables_list, 0))
-      goto err;
+      DBUG_RETURN(RES_ERROR);
   }
-  if (arena)
-    thd->restore_backup_item_arena(arena, &backup);
-  thd->where= save_where;
   DBUG_RETURN(RES_OK);
-
-err:
-  if (arena)
-    thd->restore_backup_item_arena(arena, &backup);
-  DBUG_RETURN(RES_ERROR);
 }
 
 
 Item_subselect::trans_res
 Item_in_subselect::select_transformer(JOIN *join)
 {
+  return select_in_like_transformer(join, &eq_creator);
+}
+
+
+/*
+  Prepare IN/ALL/ANY/SOME subquery transformation and call appropriate
+  transformation function
+
+  SYNOPSIS
+    Item_in_subselect::select_in_like_transformer()
+    join    JOIN object of transforming subquery
+    func    creator of condition function of subquery
+
+  DESCRIPTION
+    To decide which transformation procedure (scalar or row) applicable here
+    we have to call fix_fields() for left expression to be able to call
+    cols() method on it. Also this method make arena management for
+    underlying transformation methods.
+
+  RETURN
+    RES_OK      OK
+    RES_REDUCE  OK, and current subquery was reduced during transformation
+    RES_ERROR   Error
+*/
+
+Item_subselect::trans_res
+Item_in_subselect::select_in_like_transformer(JOIN *join, Comp_creator *func)
+{
+  Item_arena *arena, backup;
+  SELECT_LEX *current= thd->lex->current_select, *up;
+  const char *save_where= thd->where;
+  Item_subselect::trans_res res= RES_ERROR;
+  bool result;
+
+  DBUG_ENTER("Item_in_subselect::select_in_like_transformer");
+
+  if (changed)
+  {
+    DBUG_RETURN(RES_OK);
+  }
+
+  thd->where= "IN/ALL/ANY subquery";
+
+  /*
+    In some optimisation cases we will not need this Item_in_optimizer
+    object, but we can't know it here, but here we need address correct
+    reference on left expresion.
+  */
+  if (!optimizer)
+  {
+    arena= thd->change_arena_if_needed(&backup);
+    result= (!(optimizer= new Item_in_optimizer(left_expr, this)));
+    if (arena)
+      thd->restore_backup_item_arena(arena, &backup);
+    if (result)
+      goto err;
+  }
+
+  thd->lex->current_select= up= current->return_after_parsing();
+  result= (!left_expr->fixed &&
+           left_expr->fix_fields(thd, up->get_table_list(),
+                                 optimizer->arguments()));
+  /* fix_fields can change reference to left_expr, we need reassign it */
+  left_expr= optimizer->arguments()[0];
+
+  thd->lex->current_select= current;
+  if (result)
+    goto err;
+
   transformed= 1;
+  arena= thd->change_arena_if_needed(&backup);
+  /*
+    Both transformers call fix_fields() only for Items created inside them,
+    and all that items do not make permanent changes in current item arena
+    which allow to us call them with changed arena (if we do not know nature
+    of Item, we have to call fix_fields() for it only with original arena to
+    avoid memory leack)
+  */
   if (left_expr->cols() == 1)
-    return single_value_transformer(join, &eq_creator);
-  return row_value_transformer(join);
+    res= single_value_transformer(join, func);
+  else
+  {
+    /* we do not support row operation for ALL/ANY/SOME */
+    if (func != &eq_creator)
+    {
+      if (arena)
+        thd->restore_backup_item_arena(arena, &backup);
+      my_error(ER_OPERAND_COLUMNS, MYF(0), 1);
+      DBUG_RETURN(RES_ERROR);
+    }
+    res= row_value_transformer(join);
+  }
+  if (arena)
+    thd->restore_backup_item_arena(arena, &backup);
+err:
+  thd->where= save_where;
+  DBUG_RETURN(res);
 }
 
 
@@ -1077,7 +1126,7 @@ Item_allany_subselect::select_transformer(JOIN *join)
   transformed= 1;
   if (upper_item)
     upper_item->show= 1;
-  return single_value_transformer(join, func);
+  return select_in_like_transformer(join, func);
 }
 
 
@@ -1191,7 +1240,7 @@ int subselect_single_select_engine::prepare()
 
 int subselect_union_engine::prepare()
 {
-  return unit->prepare(thd, result, SELECT_NO_UNLOCK);
+  return unit->prepare(thd, result, SELECT_NO_UNLOCK, "");
 }
 
 int subselect_uniquesubquery_engine::prepare()
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index ab2d441ed7a..7582429fbb3 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -220,8 +220,8 @@ public:
 
   Item_in_subselect(Item * left_expr, st_select_lex *select_lex);
   Item_in_subselect()
-    :Item_exists_subselect(), abort_on_null(0), transformed(0), upper_item(0)
-     
+    :Item_exists_subselect(), optimizer(0), abort_on_null(0), transformed(0),
+     upper_item(0)
   {}
 
   subs_type substype() { return IN_SUBS; }
@@ -232,8 +232,8 @@ public:
     was_null= 0;
   }
   trans_res select_transformer(JOIN *join);
-  trans_res single_value_transformer(JOIN *join,
-				     Comp_creator *func);
+  trans_res select_in_like_transformer(JOIN *join, Comp_creator *func);
+  trans_res single_value_transformer(JOIN *join, Comp_creator *func);
   trans_res row_value_transformer(JOIN * join);
   longlong val_int();
   double val();
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 3e627243b9f..e9f9b432c21 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -123,7 +123,7 @@ static int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit,
     DBUG_RETURN(1); // out of memory
 
   // st_select_lex_unit::prepare correctly work for single select
-  if ((res= unit->prepare(thd, derived_result, 0)))
+  if ((res= unit->prepare(thd, derived_result, 0, org_table_list->alias)))
     goto exit;
 
 	
@@ -161,7 +161,7 @@ static int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit,
     if (is_union)
     {
       // execute union without clean up
-      if (!(res= unit->prepare(thd, derived_result, SELECT_NO_UNLOCK)))
+      if (!(res= unit->prepare(thd, derived_result, SELECT_NO_UNLOCK, "")))
 	res= unit->exec();
     }
     else
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index f48ff42bbf8..927982e444f 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -389,7 +389,8 @@ public:
   void exclude_tree();
 
   /* UNION methods */
-  int prepare(THD *thd, select_result *result, ulong additional_options);
+  int prepare(THD *thd, select_result *result, ulong additional_options,
+              const char *tmp_table_alias);
   int exec();
   int cleanup();
   inline void unclean() { cleaned= 0; }
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index b699577d5e4..09579432856 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4539,9 +4539,7 @@ bool add_field_to_list(THD *thd, char *field_name, enum_field_types type,
         net_printf(thd,ER_TOO_BIG_SET,field_name); /* purecov: inspected */
         DBUG_RETURN(1);				/* purecov: inspected */
       }
-      new_field->pack_length= (interval_list->elements + 7) / 8;
-      if (new_field->pack_length > 4)
-        new_field->pack_length=8;
+      new_field->pack_length= get_set_pack_length(interval_list->elements);
 
       List_iterator<String> it(*interval_list);
       String *tmp;
@@ -4558,7 +4556,7 @@ bool add_field_to_list(THD *thd, char *field_name, enum_field_types type,
   case FIELD_TYPE_ENUM:
     {
       // Should be safe
-      new_field->pack_length= interval_list->elements < 256 ? 1 : 2; 
+      new_field->pack_length= get_enum_pack_length(interval_list->elements);
 
       List_iterator<String> it(*interval_list);
       String *tmp;
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 1dc46aef4da..b57f88ac172 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1079,7 +1079,7 @@ static int mysql_test_select(Prepared_statement *stmt,
   thd->used_tables= 0;                        // Updated by setup_fields
 
   // JOIN::prepare calls
-  if (unit->prepare(thd, 0, 0))
+  if (unit->prepare(thd, 0, 0, ""))
   {
     send_error(thd);
     goto err_prep;
@@ -1228,7 +1228,7 @@ static int select_like_statement_test(Prepared_statement *stmt,
   thd->used_tables= 0;                        // Updated by setup_fields
 
   // JOIN::prepare calls
-  if (lex->unit.prepare(thd, 0, 0))
+  if (lex->unit.prepare(thd, 0, 0, ""))
   {
     res= thd->net.report_error ? -1 : 1;
   }
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index c79912e7eae..95a7ba514b7 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4837,14 +4837,7 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type,
     return create_tmp_field_from_item(thd, item, table, copy_func, modify_item,
                                       convert_blob_length);
   case Item::TYPE_HOLDER:
-  {
-    Field *example= ((Item_type_holder *)item)->example();
-    if (example)
-      return create_tmp_field_from_field(thd, example, item, table, 0,
-                                         convert_blob_length);
-    return create_tmp_field_from_item(thd, item, table, copy_func, 0,
-                                      convert_blob_length);
-  }
+    return ((Item_type_holder *)item)->make_field_by_type(table);
   default:					// Dosen't have to be stored
     return 0;
   }
@@ -5341,8 +5334,6 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
     if (create_myisam_tmp_table(table,param,select_options))
       goto err;
   }
-  /* Set table_name for easier debugging */
-  table->table_name= base_name(tmpname);
   if (!open_tmp_table(table))
     DBUG_RETURN(table);
 
@@ -9592,7 +9583,8 @@ int mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
     unit->fake_select_lex->select_number= UINT_MAX; // jost for initialization
     unit->fake_select_lex->type= "UNION RESULT";
     unit->fake_select_lex->options|= SELECT_DESCRIBE;
-    if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE)))
+    if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE,
+                             "")))
       res= unit->exec();
     res|= unit->cleanup();
   }
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 793b6e57b6e..a54fb613fd2 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -29,7 +29,7 @@ int mysql_union(THD *thd, LEX *lex, select_result *result,
 {
   DBUG_ENTER("mysql_union");
   int res= 0;
-  if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK)))
+  if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK, "")))
     res= unit->exec();
   res|= unit->cleanup();
   DBUG_RETURN(res);
@@ -142,7 +142,8 @@ st_select_lex_unit::init_prepare_fake_select_lex(THD *thd)
 
 
 int st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
-				ulong additional_options)
+				ulong additional_options,
+                                const char *tmp_table_alias)
 {
   SELECT_LEX *lex_select_save= thd_arg->lex->current_select;
   SELECT_LEX *sl, *first_select;
@@ -252,7 +253,7 @@ int st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
       while ((item_tmp= it++))
       {
 	/* Error's in 'new' will be detected after loop */
-	types.push_back(new Item_type_holder(thd_arg, item_tmp, empty_table));
+	types.push_back(new Item_type_holder(thd_arg, item_tmp));
       }
 
       if (thd_arg->is_fatal_error)
@@ -271,8 +272,7 @@ int st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
       Item *type, *item_tmp;
       while ((type= tp++, item_tmp= it++))
       {
-        if (((Item_type_holder*)type)->join_types(thd_arg, item_tmp,
-                                                  empty_table))
+        if (((Item_type_holder*)type)->join_types(thd_arg, item_tmp))
 	  DBUG_RETURN(-1);
       }
     }
@@ -304,7 +304,7 @@ int st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
 				  (first_select_in_union()->options |
 				   thd_arg->options |
 				   TMP_TABLE_ALL_COLUMNS),
-				  HA_POS_ERROR, (char*) "")))
+				  HA_POS_ERROR, (char *) tmp_table_alias)))
       goto err;
     table->file->extra(HA_EXTRA_WRITE_CACHE);
     table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);