merge 4.1->5.0

mysql-test/r/group_by.result:
  Auto merged
mysql-test/r/metadata.result:
  Auto merged
mysql-test/r/union.result:
  Auto merged
mysql-test/t/union.test:
  Auto merged
sql/item.h:
  Auto merged
sql/sql_parse.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
support-files/mysql.server.sh:
  Auto merged

9 files changed, 773 insertions, 9 deletions

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 3e06018226d..1cf1a19056b 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -783,7 +783,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	NO		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/group_by.result b/mysql-test/r/group_by.result
index a43e67d57e6..1c81878e5db 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -677,3 +677,28 @@ select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
 d
 10
 drop table t1;
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9);
+create table t2 (
+a int,
+b varchar(200) NOT NULL,
+c varchar(50) NOT NULL,
+d varchar(100) NOT NULL,
+primary key (a,b(132),c,d),
+key a (a,b)
+) charset=utf8;
+insert into t2 select 
+x3.a,  -- 3
+concat('val-', x3.a + 3*x4.a), -- 12
+concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120
+concat('val-', @a + 120*D.a)
+from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;
+delete from t2  where a = 2 and b = 'val-2' limit 30;
+explain select c from t2 where a = 2 and b = 'val-2' group by c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	PRIMARY,a	PRIMARY	400	const,const	6	Using where
+select c from t2 where a = 2 and b = 'val-2' group by c;
+c
+val-74
+val-98
+drop table t1,t2;
diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result
index 090bee976e8..b5d5785f0f1 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	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	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	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	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 738c011012d..036d1631592 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1883,6 +1883,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);
@@ -2138,7 +2512,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;
@@ -2277,7 +2651,63 @@ 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)
+drop table t1;
 create table t1 (df decimal(5,1));
 insert into t1 values(1.1);
 insert into t1 values(2.2);
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index b1dea29304e..ee09226797c 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -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;
@@ -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/group_by.test b/mysql-test/t/group_by.test
index afd479c520e..327be1b724b 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -489,3 +489,28 @@ select a,sum(b) from t1 where a=1 group by c having a=1;
 select a as d,sum(b) from t1 where a=1 group by c having d=1;
 select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
 drop table t1;
+
+# Test for BUG#9213 GROUP BY query on utf-8 key returns wrong results
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9);
+create table t2 (
+  a int,
+  b varchar(200) NOT NULL,
+  c varchar(50) NOT NULL,
+  d varchar(100) NOT NULL,
+  primary key (a,b(132),c,d),
+  key a (a,b)
+) charset=utf8;
+
+insert into t2 select 
+   x3.a,  -- 3
+   concat('val-', x3.a + 3*x4.a), -- 12
+   concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120
+   concat('val-', @a + 120*D.a)
+from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;
+delete from t2  where a = 2 and b = 'val-2' limit 30;
+
+explain select c from t2 where a = 2 and b = 'val-2' group by c;
+select c from t2 where a = 2 and b = 'val-2' group by c;
+drop table t1,t2;
+
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 cfcb32f37c8..a0ce971febc 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1153,6 +1153,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 );
@@ -1166,7 +1167,144 @@ 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
@@ -1443,8 +1581,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,
@@ -1541,10 +1680,57 @@ 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
+
+#
 #decimal-related tests
+#
 create table t1 (df decimal(5,1));
 insert into t1 values(1.1);
 insert into t1 values(2.2);
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 7924d4184ce..95b5d4c6aaf 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -735,3 +735,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;