Merge with 4.0.4

BitKeeper/etc/logging_ok:
  auto-union
client/mysqldump.c:
  Auto merged
include/Makefile.am:
  Auto merged
include/my_base.h:
  Auto merged
include/my_sys.h:
  Auto merged
include/sslopt-case.h:
  Auto merged
libmysql/libmysql.c:
  Auto merged
libmysql/libmysql.def:
  Auto merged
libmysqld/lib_sql.cc:
  Auto merged
myisam/ft_boolean_search.c:
  Auto merged
myisam/mi_check.c:
  Auto merged
myisam/mi_create.c:
  Auto merged
myisam/myisamchk.c:
  Auto merged
mysql-test/mysql-test-run.sh:
  Auto merged
mysql-test/r/create.result:
  Auto merged
mysql-test/r/innodb.result:
  Auto merged
mysql-test/r/myisam.result:
  Auto merged
mysql-test/r/select.result:
  Auto merged
mysql-test/r/type_set.result:
  Auto merged
mysql-test/t/create.test:
  Auto merged
mysql-test/t/myisam.test:
  Auto merged
sql/Makefile.am:
  Auto merged
sql/filesort.cc:
  Auto merged
sql/ha_innodb.cc:
  Auto merged
sql/ha_myisam.cc:
  Auto merged
sql/handler.h:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_strfunc.cc:
  Auto merged
sql/item_strfunc.h:
  Auto merged
sql/lex.h:
  Auto merged
sql/log.cc:
  Auto merged
sql/log_event.cc:
  Auto merged
sql/mysql_priv.h:
  Auto merged
sql/mysqld.cc:
  Auto merged
sql/net_pkg.cc:
  Auto merged
sql/net_serv.cc:
  Auto merged
sql/opt_range.cc:
  Auto merged
sql/set_var.h:
  Auto merged
sql/slave.cc:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_class.h:
  Auto merged
sql/sql_insert.cc:
  Auto merged
sql/sql_lex.cc:
  Auto merged
sql/sql_lex.h:
  Auto merged
sql/sql_load.cc:
  Auto merged
sql/share/english/errmsg.txt:
  Auto merged
sql/sql_table.cc:
  Auto merged
sql/sql_udf.cc:
  Auto merged
sql/sql_union.cc:
  Auto merged
sql/unireg.h:
  Auto merged
sql/sql_cache.cc:
  Code cleanup (removed ifdef)

39 files changed, 2746 insertions, 206 deletions

diff --git a/mysql-test/README b/mysql-test/README
index c5dc3e219de..7c6efe7940e 100644
--- a/mysql-test/README
+++ b/mysql-test/README
@@ -1,6 +1,10 @@
 This directory contains a test suite for mysql daemon. To run
 the currently existing test cases, simply execute ./mysql-test-run in
 this directory. It will fire up the newly built mysqld and test it.
+
+If you want to run the test with a running MySQL server use the --external
+option to mysql-test-run.
+
 Note that you do not have to have to do make install, and you could
 actually have a co-existing MySQL installation - the tests will not
 conflict with it.
@@ -13,8 +17,7 @@ http://www.mysql.com/doc/M/y/MySQL_test_suite.html
 
 You can create your own test cases. To create a test case:
 
- cd t
- vi test_case_name.test
+ xeamacs t/test_case_name.test
 
  in the file, put a set of SQL commands that will create some tables,
  load test data, run some queries to manipulate it.
diff --git a/mysql-test/create-test-result b/mysql-test/create-test-result
index bfd64f32fc5..a50b4c87641 100755
--- a/mysql-test/create-test-result
+++ b/mysql-test/create-test-result
@@ -32,7 +32,7 @@ result_file=$RESULT_DIR/$test_name.result
 
 touch $result_file
 echo "Running the test case against empty file, will fail, but don't worry"
-./mysql-test-run --do-test=$test_name
+./mysql-test-run --local $test_name
 
 reject_file=$result_file.reject
 
diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh
index 8b5ec5191b0..3107737ca45 100644
--- a/mysql-test/mysql-test-run.sh
+++ b/mysql-test/mysql-test-run.sh
@@ -176,7 +176,7 @@ USER_TEST=
 
 EXTRA_MASTER_OPT=""
 EXTRA_MYSQL_TEST_OPT=""
-USE_RUNNING_SERVER=1
+USE_RUNNING_SERVER=""
 DO_GCOV=""
 DO_GDB=""
 MANUAL_GDB=""
@@ -204,6 +204,7 @@ while test $# -gt 0; do
     --slave-binary=*)
       SLAVE_MYSQLD=`$ECHO "$1" | $SED -e "s;--slave-binary=;;"` ;;
     --local)   USE_RUNNING_SERVER="" ;;
+    --extern)   USE_RUNNING_SERVER="1" ;;
     --tmpdir=*) MYSQL_TMP_DIR=`$ECHO "$1" | $SED -e "s;--tmpdir=;;"` ;;
     --local-master)
       MASTER_MYPORT=3306;
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 1d6d69465da..159421e8f88 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -50,10 +50,10 @@ PRIMARY KEY (GROUP_ID,LANG_ID),
 KEY NAME (NAME));
 ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null;
 SHOW FULL COLUMNS FROM t1;
-Field	Type	Null	Key	Default	Extra	Privileges
-GROUP_ID	int(10) unsigned		PRI	0		select,insert,update,references
-LANG_ID	smallint(5) unsigned		PRI	0		select,insert,update,references
-NAME	char(80)		MUL			select,insert,update,references
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+GROUP_ID	int(10) unsigned		PRI	0		select,insert,update,references	
+LANG_ID	smallint(5) unsigned		PRI	0		select,insert,update,references	
+NAME	char(80) character set latin1		MUL			select,insert,update,references	
 DROP TABLE t1;
 create table t1 (n int);
 insert into t1 values(9),(3),(12),(10);
@@ -120,5 +120,5 @@ alter table t2 rename t1, add c char(10) comment "no comment";
 show columns from t1;
 Field	Type	Null	Key	Default	Extra
 i	int(10) unsigned		PRI	NULL	auto_increment
-c	char(10)	YES		NULL	
+c	char(10) character set latin1	YES		NULL	
 drop table t1;
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index 1e832a128e2..9b56b2b5160 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -72,10 +72,10 @@ create table t1(x varchar(50) );
 create table t2 select x from t1 where 1=2;
 describe t1;
 Field	Type	Null	Key	Default	Extra
-x	varchar(50)	YES		NULL	
+x	varchar(50) character set latin1	YES		NULL	
 describe t2;
 Field	Type	Null	Key	Default	Extra
-x	char(50)	YES		NULL	
+x	char(50) character set latin1	YES		NULL	
 drop table t2;
 create table t2 select now() as a , curtime() as b, curdate() as c , 1+1 as d , 1.0 + 1 as e , 33333333333333333 + 3 as f;
 describe t2;
@@ -94,6 +94,24 @@ d	date			0000-00-00
 t	time			00:00:00	
 dt	datetime			0000-00-00 00:00:00	
 drop table t1,t2;
+create table t1 (a tinyint);
+create table t2 (a int) select * from t1;
+describe t1;
+Field	Type	Null	Key	Default	Extra
+a	tinyint(4)	YES		NULL	
+describe t2;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+drop table if exists t2;
+create table t2 (a int, a float) select * from t1;
+Duplicate column name 'a'
+drop table if exists t2;
+create table t2 (a int) select a as b, a+1 as b from t1;
+Duplicate column name 'b'
+drop table if exists t2;
+create table t2 (b int) select a as b, a+1 as b from t1;
+Duplicate column name 'b'
+drop table if exists t1,t2;
 create table t1 (a int not null, b int, primary key(a), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b));
 show create table t1;
 Table	Create Table
@@ -132,7 +150,7 @@ t1	CREATE TABLE `t1` (
   KEY `b_29` (`b`),
   KEY `b_30` (`b`),
   KEY `b_31` (`b`)
-) TYPE=MyISAM
+) TYPE=MyISAM CHARSET=latin1
 drop table t1;
 create table t1 select if(1,'1','0'), month("2002-08-02");
 drop table t1;
diff --git a/mysql-test/r/ctype_many.result b/mysql-test/r/ctype_many.result
new file mode 100644
index 00000000000..ddc3989282b
--- /dev/null
+++ b/mysql-test/r/ctype_many.result
@@ -0,0 +1,1393 @@
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (
+comment CHAR(32) CHARACTER SET latin1 NOT NULL,
+koi8_ru_f CHAR(32) CHARACTER SET koi8_ru NOT NULL
+) CHARSET=latin5;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `comment` char(32) character set latin1 NOT NULL default '',
+  `koi8_ru_f` char(32) character set koi8_ru NOT NULL default ''
+) TYPE=MyISAM CHARSET=latin5
+ALTER TABLE t1 CHANGE comment comment CHAR(32) CHARACTER SET latin2 NOT NULL;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `comment` char(32) character set latin2 NOT NULL default '',
+  `koi8_ru_f` char(32) character set koi8_ru NOT NULL default ''
+) TYPE=MyISAM CHARSET=latin5
+ALTER TABLE t1 ADD latin5_f CHAR(32) NOT NULL;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `comment` char(32) character set latin2 NOT NULL default '',
+  `koi8_ru_f` char(32) character set koi8_ru NOT NULL default '',
+  `latin5_f` char(32) character set latin5 NOT NULL default ''
+) TYPE=MyISAM CHARSET=latin5
+ALTER TABLE t1 CHARSET=latin2;
+ALTER TABLE t1 ADD latin2_f CHAR(32) NOT NULL;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `comment` char(32) character set latin2 NOT NULL default '',
+  `koi8_ru_f` char(32) character set koi8_ru NOT NULL default '',
+  `latin5_f` char(32) character set latin5 NOT NULL default '',
+  `latin2_f` char(32) character set latin2 NOT NULL default ''
+) TYPE=MyISAM CHARSET=latin2
+ALTER TABLE t1 DROP latin2_f, DROP latin5_f;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `comment` char(32) character set latin2 NOT NULL default '',
+  `koi8_ru_f` char(32) character set koi8_ru NOT NULL default ''
+) TYPE=MyISAM CHARSET=latin2
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('a','LAT SMALL A');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('b','LAT SMALL B');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('c','LAT SMALL C');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('d','LAT SMALL D');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('e','LAT SMALL E');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('f','LAT SMALL F');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('g','LAT SMALL G');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('h','LAT SMALL H');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('i','LAT SMALL I');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('j','LAT SMALL J');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('k','LAT SMALL K');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('l','LAT SMALL L');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('m','LAT SMALL M');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('n','LAT SMALL N');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('o','LAT SMALL O');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('p','LAT SMALL P');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('q','LAT SMALL Q');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('r','LAT SMALL R');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('s','LAT SMALL S');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('t','LAT SMALL T');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('u','LAT SMALL U');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('v','LAT SMALL V');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('w','LAT SMALL W');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('x','LAT SMALL X');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('y','LAT SMALL Y');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('z','LAT SMALL Z');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('A','LAT CAPIT A');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('B','LAT CAPIT B');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('C','LAT CAPIT C');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('D','LAT CAPIT D');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('E','LAT CAPIT E');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('F','LAT CAPIT F');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('G','LAT CAPIT G');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('H','LAT CAPIT H');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('I','LAT CAPIT I');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('J','LAT CAPIT J');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('K','LAT CAPIT K');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('L','LAT CAPIT L');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('M','LAT CAPIT M');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('N','LAT CAPIT N');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('O','LAT CAPIT O');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('P','LAT CAPIT P');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Q','LAT CAPIT Q');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('R','LAT CAPIT R');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('S','LAT CAPIT S');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('T','LAT CAPIT T');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('U','LAT CAPIT U');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('V','LAT CAPIT V');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('W','LAT CAPIT W');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('X','LAT CAPIT X');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Y','LAT CAPIT Y');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Z','LAT CAPIT Z');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Á','CYR SMALL A');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Â','CYR SMALL BE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('×','CYR SMALL VE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ç','CYR SMALL GE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ä','CYR SMALL DE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Å','CYR SMALL IE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('£','CYR SMALL IO');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ö','CYR SMALL ZHE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ú','CYR SMALL ZE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('É','CYR SMALL I');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ë','CYR SMALL KA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ì','CYR SMALL EL');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Í','CYR SMALL EM');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Î','CYR SMALL EN');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ï','CYR SMALL O');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ð','CYR SMALL PE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ò','CYR SMALL ER');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ó','CYR SMALL ES');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ô','CYR SMALL TE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Õ','CYR SMALL U');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Æ','CYR SMALL EF');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('È','CYR SMALL HA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ã','CYR SMALL TSE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Þ','CYR SMALL CHE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Û','CYR SMALL SHA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ý','CYR SMALL SCHA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ß','CYR SMALL HARD SIGN');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ù','CYR SMALL YERU');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ø','CYR SMALL SOFT SIGN');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ü','CYR SMALL E');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('À','CYR SMALL YU');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ñ','CYR SMALL YA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('á','CYR CAPIT A');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('â','CYR CAPIT BE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('÷','CYR CAPIT VE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ç','CYR CAPIT GE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ä','CYR CAPIT DE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('å','CYR CAPIT IE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('³','CYR CAPIT IO');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ö','CYR CAPIT ZHE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ú','CYR CAPIT ZE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('é','CYR CAPIT I');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ë','CYR CAPIT KA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ì','CYR CAPIT EL');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('í','CYR CAPIT EM');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('î','CYR CAPIT EN');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ï','CYR CAPIT O');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ð','CYR CAPIT PE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ò','CYR CAPIT ER');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ó','CYR CAPIT ES');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ô','CYR CAPIT TE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('õ','CYR CAPIT U');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('æ','CYR CAPIT EF');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('è','CYR CAPIT HA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ã','CYR CAPIT TSE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('þ','CYR CAPIT CHE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('û','CYR CAPIT SHA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ý','CYR CAPIT SCHA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ÿ','CYR CAPIT HARD SIGN');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ù','CYR CAPIT YERU');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ø','CYR CAPIT SOFT SIGN');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ü','CYR CAPIT E');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('à','CYR CAPIT YU');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ñ','CYR CAPIT YA');
+SELECT CONVERT(koi8_ru_f USING utf8),MIN(comment),COUNT(*) FROM t1 GROUP BY 1;
+CONVERT(koi8_ru_f USING utf8)	MIN(comment)	COUNT(*)
+a	LAT CAPIT A	2
+b	LAT CAPIT B	2
+c	LAT CAPIT C	2
+d	LAT CAPIT D	2
+e	LAT CAPIT E	2
+f	LAT CAPIT F	2
+g	LAT CAPIT G	2
+h	LAT CAPIT H	2
+i	LAT CAPIT I	2
+j	LAT CAPIT J	2
+k	LAT CAPIT K	2
+l	LAT CAPIT L	2
+m	LAT CAPIT M	2
+n	LAT CAPIT N	2
+o	LAT CAPIT O	2
+p	LAT CAPIT P	2
+q	LAT CAPIT Q	2
+r	LAT CAPIT R	2
+s	LAT CAPIT S	2
+t	LAT CAPIT T	2
+u	LAT CAPIT U	2
+v	LAT CAPIT V	2
+w	LAT CAPIT W	2
+x	LAT CAPIT X	2
+y	LAT CAPIT Y	2
+z	LAT CAPIT Z	2
+а	CYR CAPIT A	2
+б	CYR CAPIT BE	2
+в	CYR CAPIT VE	2
+г	CYR CAPIT GE	2
+д	CYR CAPIT DE	2
+е	CYR CAPIT IE	4
+ж	CYR CAPIT ZHE	2
+з	CYR CAPIT ZE	2
+и	CYR CAPIT I	2
+к	CYR CAPIT KA	2
+л	CYR CAPIT EL	2
+м	CYR CAPIT EM	2
+н	CYR CAPIT EN	2
+о	CYR CAPIT O	2
+п	CYR CAPIT PE	2
+Ñ€	CYR CAPIT ER	2
+�	CYR CAPIT ES	2
+Ñ‚	CYR CAPIT TE	2
+у	CYR CAPIT U	2
+Ñ„	CYR CAPIT EF	2
+Ñ…	CYR CAPIT HA	2
+ц	CYR CAPIT TSE	2
+ч	CYR CAPIT CHE	2
+ш	CYR CAPIT SHA	2
+щ	CYR CAPIT SCHA	2
+ÑŠ	CYR CAPIT HARD SIGN	2
+Ñ‹	CYR CAPIT YERU	2
+ь	CYR CAPIT SOFT SIGN	2
+�	CYR CAPIT E	2
+ÑŽ	CYR CAPIT YU	2
+�	CYR CAPIT YA	2
+ALTER TABLE t1 ADD utf8_f CHAR(32) CHARACTER SET utf8 NOT NULL;
+UPDATE t1 SET utf8_f=CONVERT(koi8_ru_f USING utf8);
+SELECT * FROM t1;
+comment	koi8_ru_f	utf8_f
+LAT SMALL A	a	a
+LAT SMALL B	b	b
+LAT SMALL C	c	c
+LAT SMALL D	d	d
+LAT SMALL E	e	e
+LAT SMALL F	f	f
+LAT SMALL G	g	g
+LAT SMALL H	h	h
+LAT SMALL I	i	i
+LAT SMALL J	j	j
+LAT SMALL K	k	k
+LAT SMALL L	l	l
+LAT SMALL M	m	m
+LAT SMALL N	n	n
+LAT SMALL O	o	o
+LAT SMALL P	p	p
+LAT SMALL Q	q	q
+LAT SMALL R	r	r
+LAT SMALL S	s	s
+LAT SMALL T	t	t
+LAT SMALL U	u	u
+LAT SMALL V	v	v
+LAT SMALL W	w	w
+LAT SMALL X	x	x
+LAT SMALL Y	y	y
+LAT SMALL Z	z	z
+LAT CAPIT A	A	A
+LAT CAPIT B	B	B
+LAT CAPIT C	C	C
+LAT CAPIT D	D	D
+LAT CAPIT E	E	E
+LAT CAPIT F	F	F
+LAT CAPIT G	G	G
+LAT CAPIT H	H	H
+LAT CAPIT I	I	I
+LAT CAPIT J	J	J
+LAT CAPIT K	K	K
+LAT CAPIT L	L	L
+LAT CAPIT M	M	M
+LAT CAPIT N	N	N
+LAT CAPIT O	O	O
+LAT CAPIT P	P	P
+LAT CAPIT Q	Q	Q
+LAT CAPIT R	R	R
+LAT CAPIT S	S	S
+LAT CAPIT T	T	T
+LAT CAPIT U	U	U
+LAT CAPIT V	V	V
+LAT CAPIT W	W	W
+LAT CAPIT X	X	X
+LAT CAPIT Y	Y	Y
+LAT CAPIT Z	Z	Z
+CYR SMALL A	Á	а
+CYR SMALL BE	Â	б
+CYR SMALL VE	×	в
+CYR SMALL GE	Ç	г
+CYR SMALL DE	Ä	д
+CYR SMALL IE	Å	е
+CYR SMALL IO	£	Ñ‘
+CYR SMALL ZHE	Ö	ж
+CYR SMALL ZE	Ú	з
+CYR SMALL I	É	и
+CYR SMALL KA	Ë	к
+CYR SMALL EL	Ì	л
+CYR SMALL EM	Í	м
+CYR SMALL EN	Î	н
+CYR SMALL O	Ï	о
+CYR SMALL PE	Ð	п
+CYR SMALL ER	Ò	Ñ€
+CYR SMALL ES	Ó	Ñ�
+CYR SMALL TE	Ô	Ñ‚
+CYR SMALL U	Õ	у
+CYR SMALL EF	Æ	Ñ„
+CYR SMALL HA	È	Ñ…
+CYR SMALL TSE	Ã	ц
+CYR SMALL CHE	Þ	ч
+CYR SMALL SHA	Û	ш
+CYR SMALL SCHA	Ý	щ
+CYR SMALL HARD SIGN	ß	ÑŠ
+CYR SMALL YERU	Ù	Ñ‹
+CYR SMALL SOFT SIGN	Ø	ÑŒ
+CYR SMALL E	Ü	Ñ�
+CYR SMALL YU	À	ÑŽ
+CYR SMALL YA	Ñ	Ñ�
+CYR CAPIT A	á	Ð�
+CYR CAPIT BE	â	Б
+CYR CAPIT VE	÷	Ð’
+CYR CAPIT GE	ç	Г
+CYR CAPIT DE	ä	Д
+CYR CAPIT IE	å	Е
+CYR CAPIT IO	³	Ð�
+CYR CAPIT ZHE	ö	Ж
+CYR CAPIT ZE	ú	З
+CYR CAPIT I	é	И
+CYR CAPIT KA	ë	К
+CYR CAPIT EL	ì	Л
+CYR CAPIT EM	í	Ðœ
+CYR CAPIT EN	î	Ð�
+CYR CAPIT O	ï	О
+CYR CAPIT PE	ð	П
+CYR CAPIT ER	ò	Р
+CYR CAPIT ES	ó	С
+CYR CAPIT TE	ô	Т
+CYR CAPIT U	õ	У
+CYR CAPIT EF	æ	Ф
+CYR CAPIT HA	è	Ð¥
+CYR CAPIT TSE	ã	Ц
+CYR CAPIT CHE	þ	Ч
+CYR CAPIT SHA	û	Ш
+CYR CAPIT SCHA	ý	Щ
+CYR CAPIT HARD SIGN	ÿ	Ъ
+CYR CAPIT YERU	ù	Ы
+CYR CAPIT SOFT SIGN	ø	Ь
+CYR CAPIT E	ü	Э
+CYR CAPIT YU	à	Ю
+CYR CAPIT YA	ñ	Я
+SELECT koi8_ru_f,MIN(comment) FROM t1 GROUP BY 1;
+koi8_ru_f	MIN(comment)
+a	LAT CAPIT A
+b	LAT CAPIT B
+c	LAT CAPIT C
+d	LAT CAPIT D
+e	LAT CAPIT E
+f	LAT CAPIT F
+g	LAT CAPIT G
+h	LAT CAPIT H
+i	LAT CAPIT I
+j	LAT CAPIT J
+k	LAT CAPIT K
+l	LAT CAPIT L
+m	LAT CAPIT M
+n	LAT CAPIT N
+o	LAT CAPIT O
+p	LAT CAPIT P
+q	LAT CAPIT Q
+r	LAT CAPIT R
+s	LAT CAPIT S
+t	LAT CAPIT T
+u	LAT CAPIT U
+v	LAT CAPIT V
+w	LAT CAPIT W
+x	LAT CAPIT X
+y	LAT CAPIT Y
+z	LAT CAPIT Z
+Á	CYR CAPIT A
+Â	CYR CAPIT BE
+×	CYR CAPIT VE
+Ç	CYR CAPIT GE
+Ä	CYR CAPIT DE
+Å	CYR CAPIT IE
+£	CYR CAPIT IO
+Ö	CYR CAPIT ZHE
+Ú	CYR CAPIT ZE
+É	CYR CAPIT I
+Ë	CYR CAPIT KA
+Ì	CYR CAPIT EL
+Í	CYR CAPIT EM
+Î	CYR CAPIT EN
+Ï	CYR CAPIT O
+Ð	CYR CAPIT PE
+Ò	CYR CAPIT ER
+Ó	CYR CAPIT ES
+Ô	CYR CAPIT TE
+Õ	CYR CAPIT U
+Æ	CYR CAPIT EF
+È	CYR CAPIT HA
+Ã	CYR CAPIT TSE
+Þ	CYR CAPIT CHE
+Û	CYR CAPIT SHA
+Ý	CYR CAPIT SCHA
+ß	CYR CAPIT HARD SIGN
+Ù	CYR CAPIT YERU
+Ø	CYR CAPIT SOFT SIGN
+Ü	CYR CAPIT E
+À	CYR CAPIT YU
+Ñ	CYR CAPIT YA
+SELECT utf8_f,MIN(comment) FROM t1 GROUP BY 1;
+utf8_f	MIN(comment)
+a	LAT CAPIT A
+b	LAT CAPIT B
+c	LAT CAPIT C
+d	LAT CAPIT D
+e	LAT CAPIT E
+f	LAT CAPIT F
+g	LAT CAPIT G
+h	LAT CAPIT H
+i	LAT CAPIT I
+j	LAT CAPIT J
+k	LAT CAPIT K
+l	LAT CAPIT L
+m	LAT CAPIT M
+n	LAT CAPIT N
+o	LAT CAPIT O
+p	LAT CAPIT P
+q	LAT CAPIT Q
+r	LAT CAPIT R
+s	LAT CAPIT S
+t	LAT CAPIT T
+u	LAT CAPIT U
+v	LAT CAPIT V
+w	LAT CAPIT W
+x	LAT CAPIT X
+y	LAT CAPIT Y
+z	LAT CAPIT Z
+а	CYR CAPIT A
+б	CYR CAPIT BE
+в	CYR CAPIT VE
+г	CYR CAPIT GE
+д	CYR CAPIT DE
+е	CYR CAPIT IE
+ж	CYR CAPIT ZHE
+з	CYR CAPIT ZE
+и	CYR CAPIT I
+к	CYR CAPIT KA
+л	CYR CAPIT EL
+м	CYR CAPIT EM
+н	CYR CAPIT EN
+о	CYR CAPIT O
+п	CYR CAPIT PE
+Ñ€	CYR CAPIT ER
+�	CYR CAPIT ES
+Ñ‚	CYR CAPIT TE
+у	CYR CAPIT U
+Ñ„	CYR CAPIT EF
+Ñ…	CYR CAPIT HA
+ц	CYR CAPIT TSE
+ч	CYR CAPIT CHE
+ш	CYR CAPIT SHA
+щ	CYR CAPIT SCHA
+ÑŠ	CYR CAPIT HARD SIGN
+Ñ‹	CYR CAPIT YERU
+ь	CYR CAPIT SOFT SIGN
+�	CYR CAPIT E
+ÑŽ	CYR CAPIT YU
+�	CYR CAPIT YA
+SELECT DISTINCT koi8_ru_f FROM t1;
+koi8_ru_f
+a
+b
+c
+d
+e
+f
+g
+h
+i
+j
+k
+l
+m
+n
+o
+p
+q
+r
+s
+t
+u
+v
+w
+x
+y
+z
+Á
+Â
+×
+Ç
+Ä
+Å
+£
+Ö
+Ú
+É
+Ë
+Ì
+Í
+Î
+Ï
+Ð
+Ò
+Ó
+Ô
+Õ
+Æ
+È
+Ã
+Þ
+Û
+Ý
+ß
+Ù
+Ø
+Ü
+À
+Ñ
+SELECT DISTINCT utf8_f FROM t1;
+utf8_f
+a
+b
+c
+d
+e
+f
+g
+h
+i
+j
+k
+l
+m
+n
+o
+p
+q
+r
+s
+t
+u
+v
+w
+x
+y
+z
+а
+б
+в
+г
+д
+е
+ж
+з
+и
+к
+л
+м
+н
+о
+п
+Ñ€
+�
+Ñ‚
+у
+Ñ„
+Ñ…
+ц
+ч
+ш
+щ
+ÑŠ
+Ñ‹
+ь
+�
+ÑŽ
+�
+SELECT lower(koi8_ru_f) FROM t1 ORDER BY 1 DESC;
+lower(koi8_ru_f)
+Ñ
+Ñ
+À
+À
+Ü
+Ü
+Ø
+Ø
+Ù
+Ù
+ß
+ß
+Ý
+Ý
+Û
+Û
+Þ
+Þ
+Ã
+Ã
+È
+È
+Æ
+Æ
+Õ
+Õ
+Ô
+Ô
+Ó
+Ó
+Ò
+Ò
+Ð
+Ð
+Ï
+Ï
+Î
+Î
+Í
+Í
+Ì
+Ì
+Ë
+Ë
+É
+É
+Ú
+Ú
+Ö
+Ö
+£
+£
+Å
+Å
+Ä
+Ä
+Ç
+Ç
+×
+×
+Â
+Â
+Á
+Á
+z
+z
+y
+y
+x
+x
+w
+w
+v
+v
+u
+u
+t
+t
+s
+s
+r
+r
+q
+q
+p
+p
+o
+o
+n
+n
+m
+m
+l
+l
+k
+k
+j
+j
+i
+i
+h
+h
+g
+g
+f
+f
+e
+e
+d
+d
+c
+c
+b
+b
+a
+a
+SELECT lower(utf8_f) FROM t1 ORDER BY 1 DESC;
+lower(utf8_f)
+�
+�
+ÑŽ
+ÑŽ
+�
+�
+ь
+ь
+Ñ‹
+Ñ‹
+ÑŠ
+ÑŠ
+щ
+щ
+ш
+ш
+ч
+ч
+ц
+ц
+Ñ…
+Ñ…
+Ñ„
+Ñ„
+у
+у
+Ñ‚
+Ñ‚
+�
+�
+Ñ€
+Ñ€
+п
+п
+о
+о
+н
+н
+м
+м
+л
+л
+к
+к
+и
+и
+з
+з
+ж
+ж
+е
+Ñ‘
+е
+Ñ‘
+д
+д
+г
+г
+в
+в
+б
+б
+а
+а
+z
+z
+y
+y
+x
+x
+w
+w
+v
+v
+u
+u
+t
+t
+s
+s
+r
+r
+q
+q
+p
+p
+o
+o
+n
+n
+m
+m
+l
+l
+k
+k
+j
+j
+i
+i
+h
+h
+g
+g
+f
+f
+e
+e
+d
+d
+c
+c
+b
+b
+a
+a
+SELECT t11.comment,t12.comment 
+FROM t1 t11,t1 t12 WHERE CONVERT(t11.koi8_ru_f USING utf8)=t12.utf8_f
+ORDER BY t11.koi8_ru_f,t11.comment,t12.comment;
+comment	comment
+LAT CAPIT A	LAT CAPIT A
+LAT CAPIT A	LAT SMALL A
+LAT SMALL A	LAT CAPIT A
+LAT SMALL A	LAT SMALL A
+LAT CAPIT B	LAT CAPIT B
+LAT CAPIT B	LAT SMALL B
+LAT SMALL B	LAT CAPIT B
+LAT SMALL B	LAT SMALL B
+LAT CAPIT C	LAT CAPIT C
+LAT CAPIT C	LAT SMALL C
+LAT SMALL C	LAT CAPIT C
+LAT SMALL C	LAT SMALL C
+LAT CAPIT D	LAT CAPIT D
+LAT CAPIT D	LAT SMALL D
+LAT SMALL D	LAT CAPIT D
+LAT SMALL D	LAT SMALL D
+LAT CAPIT E	LAT CAPIT E
+LAT CAPIT E	LAT SMALL E
+LAT SMALL E	LAT CAPIT E
+LAT SMALL E	LAT SMALL E
+LAT CAPIT F	LAT CAPIT F
+LAT CAPIT F	LAT SMALL F
+LAT SMALL F	LAT CAPIT F
+LAT SMALL F	LAT SMALL F
+LAT CAPIT G	LAT CAPIT G
+LAT CAPIT G	LAT SMALL G
+LAT SMALL G	LAT CAPIT G
+LAT SMALL G	LAT SMALL G
+LAT CAPIT H	LAT CAPIT H
+LAT CAPIT H	LAT SMALL H
+LAT SMALL H	LAT CAPIT H
+LAT SMALL H	LAT SMALL H
+LAT CAPIT I	LAT CAPIT I
+LAT CAPIT I	LAT SMALL I
+LAT SMALL I	LAT CAPIT I
+LAT SMALL I	LAT SMALL I
+LAT CAPIT J	LAT CAPIT J
+LAT CAPIT J	LAT SMALL J
+LAT SMALL J	LAT CAPIT J
+LAT SMALL J	LAT SMALL J
+LAT CAPIT K	LAT CAPIT K
+LAT CAPIT K	LAT SMALL K
+LAT SMALL K	LAT CAPIT K
+LAT SMALL K	LAT SMALL K
+LAT CAPIT L	LAT CAPIT L
+LAT CAPIT L	LAT SMALL L
+LAT SMALL L	LAT CAPIT L
+LAT SMALL L	LAT SMALL L
+LAT CAPIT M	LAT CAPIT M
+LAT CAPIT M	LAT SMALL M
+LAT SMALL M	LAT CAPIT M
+LAT SMALL M	LAT SMALL M
+LAT CAPIT N	LAT CAPIT N
+LAT CAPIT N	LAT SMALL N
+LAT SMALL N	LAT CAPIT N
+LAT SMALL N	LAT SMALL N
+LAT CAPIT O	LAT CAPIT O
+LAT CAPIT O	LAT SMALL O
+LAT SMALL O	LAT CAPIT O
+LAT SMALL O	LAT SMALL O
+LAT CAPIT P	LAT CAPIT P
+LAT CAPIT P	LAT SMALL P
+LAT SMALL P	LAT CAPIT P
+LAT SMALL P	LAT SMALL P
+LAT CAPIT Q	LAT CAPIT Q
+LAT CAPIT Q	LAT SMALL Q
+LAT SMALL Q	LAT CAPIT Q
+LAT SMALL Q	LAT SMALL Q
+LAT CAPIT R	LAT CAPIT R
+LAT CAPIT R	LAT SMALL R
+LAT SMALL R	LAT CAPIT R
+LAT SMALL R	LAT SMALL R
+LAT CAPIT S	LAT CAPIT S
+LAT CAPIT S	LAT SMALL S
+LAT SMALL S	LAT CAPIT S
+LAT SMALL S	LAT SMALL S
+LAT CAPIT T	LAT CAPIT T
+LAT CAPIT T	LAT SMALL T
+LAT SMALL T	LAT CAPIT T
+LAT SMALL T	LAT SMALL T
+LAT CAPIT U	LAT CAPIT U
+LAT CAPIT U	LAT SMALL U
+LAT SMALL U	LAT CAPIT U
+LAT SMALL U	LAT SMALL U
+LAT CAPIT V	LAT CAPIT V
+LAT CAPIT V	LAT SMALL V
+LAT SMALL V	LAT CAPIT V
+LAT SMALL V	LAT SMALL V
+LAT CAPIT W	LAT CAPIT W
+LAT CAPIT W	LAT SMALL W
+LAT SMALL W	LAT CAPIT W
+LAT SMALL W	LAT SMALL W
+LAT CAPIT X	LAT CAPIT X
+LAT CAPIT X	LAT SMALL X
+LAT SMALL X	LAT CAPIT X
+LAT SMALL X	LAT SMALL X
+LAT CAPIT Y	LAT CAPIT Y
+LAT CAPIT Y	LAT SMALL Y
+LAT SMALL Y	LAT CAPIT Y
+LAT SMALL Y	LAT SMALL Y
+LAT CAPIT Z	LAT CAPIT Z
+LAT CAPIT Z	LAT SMALL Z
+LAT SMALL Z	LAT CAPIT Z
+LAT SMALL Z	LAT SMALL Z
+CYR CAPIT A	CYR CAPIT A
+CYR CAPIT A	CYR SMALL A
+CYR SMALL A	CYR CAPIT A
+CYR SMALL A	CYR SMALL A
+CYR CAPIT BE	CYR CAPIT BE
+CYR CAPIT BE	CYR SMALL BE
+CYR SMALL BE	CYR CAPIT BE
+CYR SMALL BE	CYR SMALL BE
+CYR CAPIT VE	CYR CAPIT VE
+CYR CAPIT VE	CYR SMALL VE
+CYR SMALL VE	CYR CAPIT VE
+CYR SMALL VE	CYR SMALL VE
+CYR CAPIT GE	CYR CAPIT GE
+CYR CAPIT GE	CYR SMALL GE
+CYR SMALL GE	CYR CAPIT GE
+CYR SMALL GE	CYR SMALL GE
+CYR CAPIT DE	CYR CAPIT DE
+CYR CAPIT DE	CYR SMALL DE
+CYR SMALL DE	CYR CAPIT DE
+CYR SMALL DE	CYR SMALL DE
+CYR CAPIT IE	CYR CAPIT IE
+CYR CAPIT IE	CYR CAPIT IO
+CYR CAPIT IE	CYR SMALL IE
+CYR CAPIT IE	CYR SMALL IO
+CYR SMALL IE	CYR CAPIT IE
+CYR SMALL IE	CYR CAPIT IO
+CYR SMALL IE	CYR SMALL IE
+CYR SMALL IE	CYR SMALL IO
+CYR CAPIT IO	CYR CAPIT IE
+CYR CAPIT IO	CYR CAPIT IO
+CYR CAPIT IO	CYR SMALL IE
+CYR CAPIT IO	CYR SMALL IO
+CYR SMALL IO	CYR CAPIT IE
+CYR SMALL IO	CYR CAPIT IO
+CYR SMALL IO	CYR SMALL IE
+CYR SMALL IO	CYR SMALL IO
+CYR CAPIT ZHE	CYR CAPIT ZHE
+CYR CAPIT ZHE	CYR SMALL ZHE
+CYR SMALL ZHE	CYR CAPIT ZHE
+CYR SMALL ZHE	CYR SMALL ZHE
+CYR CAPIT ZE	CYR CAPIT ZE
+CYR CAPIT ZE	CYR SMALL ZE
+CYR SMALL ZE	CYR CAPIT ZE
+CYR SMALL ZE	CYR SMALL ZE
+CYR CAPIT I	CYR CAPIT I
+CYR CAPIT I	CYR SMALL I
+CYR SMALL I	CYR CAPIT I
+CYR SMALL I	CYR SMALL I
+CYR CAPIT KA	CYR CAPIT KA
+CYR CAPIT KA	CYR SMALL KA
+CYR SMALL KA	CYR CAPIT KA
+CYR SMALL KA	CYR SMALL KA
+CYR CAPIT EL	CYR CAPIT EL
+CYR CAPIT EL	CYR SMALL EL
+CYR SMALL EL	CYR CAPIT EL
+CYR SMALL EL	CYR SMALL EL
+CYR CAPIT EM	CYR CAPIT EM
+CYR CAPIT EM	CYR SMALL EM
+CYR SMALL EM	CYR CAPIT EM
+CYR SMALL EM	CYR SMALL EM
+CYR CAPIT EN	CYR CAPIT EN
+CYR CAPIT EN	CYR SMALL EN
+CYR SMALL EN	CYR CAPIT EN
+CYR SMALL EN	CYR SMALL EN
+CYR CAPIT O	CYR CAPIT O
+CYR CAPIT O	CYR SMALL O
+CYR SMALL O	CYR CAPIT O
+CYR SMALL O	CYR SMALL O
+CYR CAPIT PE	CYR CAPIT PE
+CYR CAPIT PE	CYR SMALL PE
+CYR SMALL PE	CYR CAPIT PE
+CYR SMALL PE	CYR SMALL PE
+CYR CAPIT ER	CYR CAPIT ER
+CYR CAPIT ER	CYR SMALL ER
+CYR SMALL ER	CYR CAPIT ER
+CYR SMALL ER	CYR SMALL ER
+CYR CAPIT ES	CYR CAPIT ES
+CYR CAPIT ES	CYR SMALL ES
+CYR SMALL ES	CYR CAPIT ES
+CYR SMALL ES	CYR SMALL ES
+CYR CAPIT TE	CYR CAPIT TE
+CYR CAPIT TE	CYR SMALL TE
+CYR SMALL TE	CYR CAPIT TE
+CYR SMALL TE	CYR SMALL TE
+CYR CAPIT U	CYR CAPIT U
+CYR CAPIT U	CYR SMALL U
+CYR SMALL U	CYR CAPIT U
+CYR SMALL U	CYR SMALL U
+CYR CAPIT EF	CYR CAPIT EF
+CYR CAPIT EF	CYR SMALL EF
+CYR SMALL EF	CYR CAPIT EF
+CYR SMALL EF	CYR SMALL EF
+CYR CAPIT HA	CYR CAPIT HA
+CYR CAPIT HA	CYR SMALL HA
+CYR SMALL HA	CYR CAPIT HA
+CYR SMALL HA	CYR SMALL HA
+CYR CAPIT TSE	CYR CAPIT TSE
+CYR CAPIT TSE	CYR SMALL TSE
+CYR SMALL TSE	CYR CAPIT TSE
+CYR SMALL TSE	CYR SMALL TSE
+CYR CAPIT CHE	CYR CAPIT CHE
+CYR CAPIT CHE	CYR SMALL CHE
+CYR SMALL CHE	CYR CAPIT CHE
+CYR SMALL CHE	CYR SMALL CHE
+CYR CAPIT SHA	CYR CAPIT SHA
+CYR CAPIT SHA	CYR SMALL SHA
+CYR SMALL SHA	CYR CAPIT SHA
+CYR SMALL SHA	CYR SMALL SHA
+CYR CAPIT SCHA	CYR CAPIT SCHA
+CYR CAPIT SCHA	CYR SMALL SCHA
+CYR SMALL SCHA	CYR CAPIT SCHA
+CYR SMALL SCHA	CYR SMALL SCHA
+CYR CAPIT HARD SIGN	CYR CAPIT HARD SIGN
+CYR CAPIT HARD SIGN	CYR SMALL HARD SIGN
+CYR SMALL HARD SIGN	CYR CAPIT HARD SIGN
+CYR SMALL HARD SIGN	CYR SMALL HARD SIGN
+CYR CAPIT YERU	CYR CAPIT YERU
+CYR CAPIT YERU	CYR SMALL YERU
+CYR SMALL YERU	CYR CAPIT YERU
+CYR SMALL YERU	CYR SMALL YERU
+CYR CAPIT SOFT SIGN	CYR CAPIT SOFT SIGN
+CYR CAPIT SOFT SIGN	CYR SMALL SOFT SIGN
+CYR SMALL SOFT SIGN	CYR CAPIT SOFT SIGN
+CYR SMALL SOFT SIGN	CYR SMALL SOFT SIGN
+CYR CAPIT E	CYR CAPIT E
+CYR CAPIT E	CYR SMALL E
+CYR SMALL E	CYR CAPIT E
+CYR SMALL E	CYR SMALL E
+CYR CAPIT YU	CYR CAPIT YU
+CYR CAPIT YU	CYR SMALL YU
+CYR SMALL YU	CYR CAPIT YU
+CYR SMALL YU	CYR SMALL YU
+CYR CAPIT YA	CYR CAPIT YA
+CYR CAPIT YA	CYR SMALL YA
+CYR SMALL YA	CYR CAPIT YA
+CYR SMALL YA	CYR SMALL YA
+SELECT t11.comment,t12.comment 
+FROM t1 t11,t1 t12 
+WHERE t11.koi8_ru_f=CONVERT(t12.utf8_f USING koi8_ru)
+ORDER BY t12.utf8_f,t11.comment,t12.comment;
+comment	comment
+LAT CAPIT A	LAT CAPIT A
+LAT CAPIT A	LAT SMALL A
+LAT SMALL A	LAT CAPIT A
+LAT SMALL A	LAT SMALL A
+LAT CAPIT B	LAT CAPIT B
+LAT CAPIT B	LAT SMALL B
+LAT SMALL B	LAT CAPIT B
+LAT SMALL B	LAT SMALL B
+LAT CAPIT C	LAT CAPIT C
+LAT CAPIT C	LAT SMALL C
+LAT SMALL C	LAT CAPIT C
+LAT SMALL C	LAT SMALL C
+LAT CAPIT D	LAT CAPIT D
+LAT CAPIT D	LAT SMALL D
+LAT SMALL D	LAT CAPIT D
+LAT SMALL D	LAT SMALL D
+LAT CAPIT E	LAT CAPIT E
+LAT CAPIT E	LAT SMALL E
+LAT SMALL E	LAT CAPIT E
+LAT SMALL E	LAT SMALL E
+LAT CAPIT F	LAT CAPIT F
+LAT CAPIT F	LAT SMALL F
+LAT SMALL F	LAT CAPIT F
+LAT SMALL F	LAT SMALL F
+LAT CAPIT G	LAT CAPIT G
+LAT CAPIT G	LAT SMALL G
+LAT SMALL G	LAT CAPIT G
+LAT SMALL G	LAT SMALL G
+LAT CAPIT H	LAT CAPIT H
+LAT CAPIT H	LAT SMALL H
+LAT SMALL H	LAT CAPIT H
+LAT SMALL H	LAT SMALL H
+LAT CAPIT I	LAT CAPIT I
+LAT CAPIT I	LAT SMALL I
+LAT SMALL I	LAT CAPIT I
+LAT SMALL I	LAT SMALL I
+LAT CAPIT J	LAT CAPIT J
+LAT CAPIT J	LAT SMALL J
+LAT SMALL J	LAT CAPIT J
+LAT SMALL J	LAT SMALL J
+LAT CAPIT K	LAT CAPIT K
+LAT CAPIT K	LAT SMALL K
+LAT SMALL K	LAT CAPIT K
+LAT SMALL K	LAT SMALL K
+LAT CAPIT L	LAT CAPIT L
+LAT CAPIT L	LAT SMALL L
+LAT SMALL L	LAT CAPIT L
+LAT SMALL L	LAT SMALL L
+LAT CAPIT M	LAT CAPIT M
+LAT CAPIT M	LAT SMALL M
+LAT SMALL M	LAT CAPIT M
+LAT SMALL M	LAT SMALL M
+LAT CAPIT N	LAT CAPIT N
+LAT CAPIT N	LAT SMALL N
+LAT SMALL N	LAT CAPIT N
+LAT SMALL N	LAT SMALL N
+LAT CAPIT O	LAT CAPIT O
+LAT CAPIT O	LAT SMALL O
+LAT SMALL O	LAT CAPIT O
+LAT SMALL O	LAT SMALL O
+LAT CAPIT P	LAT CAPIT P
+LAT CAPIT P	LAT SMALL P
+LAT SMALL P	LAT CAPIT P
+LAT SMALL P	LAT SMALL P
+LAT CAPIT Q	LAT CAPIT Q
+LAT CAPIT Q	LAT SMALL Q
+LAT SMALL Q	LAT CAPIT Q
+LAT SMALL Q	LAT SMALL Q
+LAT CAPIT R	LAT CAPIT R
+LAT CAPIT R	LAT SMALL R
+LAT SMALL R	LAT CAPIT R
+LAT SMALL R	LAT SMALL R
+LAT CAPIT S	LAT CAPIT S
+LAT CAPIT S	LAT SMALL S
+LAT SMALL S	LAT CAPIT S
+LAT SMALL S	LAT SMALL S
+LAT CAPIT T	LAT CAPIT T
+LAT CAPIT T	LAT SMALL T
+LAT SMALL T	LAT CAPIT T
+LAT SMALL T	LAT SMALL T
+LAT CAPIT U	LAT CAPIT U
+LAT CAPIT U	LAT SMALL U
+LAT SMALL U	LAT CAPIT U
+LAT SMALL U	LAT SMALL U
+LAT CAPIT V	LAT CAPIT V
+LAT CAPIT V	LAT SMALL V
+LAT SMALL V	LAT CAPIT V
+LAT SMALL V	LAT SMALL V
+LAT CAPIT W	LAT CAPIT W
+LAT CAPIT W	LAT SMALL W
+LAT SMALL W	LAT CAPIT W
+LAT SMALL W	LAT SMALL W
+LAT CAPIT X	LAT CAPIT X
+LAT CAPIT X	LAT SMALL X
+LAT SMALL X	LAT CAPIT X
+LAT SMALL X	LAT SMALL X
+LAT CAPIT Y	LAT CAPIT Y
+LAT CAPIT Y	LAT SMALL Y
+LAT SMALL Y	LAT CAPIT Y
+LAT SMALL Y	LAT SMALL Y
+LAT CAPIT Z	LAT CAPIT Z
+LAT CAPIT Z	LAT SMALL Z
+LAT SMALL Z	LAT CAPIT Z
+LAT SMALL Z	LAT SMALL Z
+CYR CAPIT A	CYR CAPIT A
+CYR CAPIT A	CYR SMALL A
+CYR SMALL A	CYR CAPIT A
+CYR SMALL A	CYR SMALL A
+CYR CAPIT BE	CYR CAPIT BE
+CYR CAPIT BE	CYR SMALL BE
+CYR SMALL BE	CYR CAPIT BE
+CYR SMALL BE	CYR SMALL BE
+CYR CAPIT VE	CYR CAPIT VE
+CYR CAPIT VE	CYR SMALL VE
+CYR SMALL VE	CYR CAPIT VE
+CYR SMALL VE	CYR SMALL VE
+CYR CAPIT GE	CYR CAPIT GE
+CYR CAPIT GE	CYR SMALL GE
+CYR SMALL GE	CYR CAPIT GE
+CYR SMALL GE	CYR SMALL GE
+CYR CAPIT DE	CYR CAPIT DE
+CYR CAPIT DE	CYR SMALL DE
+CYR SMALL DE	CYR CAPIT DE
+CYR SMALL DE	CYR SMALL DE
+CYR CAPIT IE	CYR CAPIT IE
+CYR CAPIT IE	CYR SMALL IE
+CYR CAPIT IO	CYR CAPIT IO
+CYR CAPIT IO	CYR SMALL IO
+CYR SMALL IE	CYR CAPIT IE
+CYR SMALL IE	CYR SMALL IE
+CYR SMALL IO	CYR CAPIT IO
+CYR SMALL IO	CYR SMALL IO
+CYR CAPIT ZHE	CYR CAPIT ZHE
+CYR CAPIT ZHE	CYR SMALL ZHE
+CYR SMALL ZHE	CYR CAPIT ZHE
+CYR SMALL ZHE	CYR SMALL ZHE
+CYR CAPIT ZE	CYR CAPIT ZE
+CYR CAPIT ZE	CYR SMALL ZE
+CYR SMALL ZE	CYR CAPIT ZE
+CYR SMALL ZE	CYR SMALL ZE
+CYR CAPIT I	CYR CAPIT I
+CYR CAPIT I	CYR SMALL I
+CYR SMALL I	CYR CAPIT I
+CYR SMALL I	CYR SMALL I
+CYR CAPIT KA	CYR CAPIT KA
+CYR CAPIT KA	CYR SMALL KA
+CYR SMALL KA	CYR CAPIT KA
+CYR SMALL KA	CYR SMALL KA
+CYR CAPIT EL	CYR CAPIT EL
+CYR CAPIT EL	CYR SMALL EL
+CYR SMALL EL	CYR CAPIT EL
+CYR SMALL EL	CYR SMALL EL
+CYR CAPIT EM	CYR CAPIT EM
+CYR CAPIT EM	CYR SMALL EM
+CYR SMALL EM	CYR CAPIT EM
+CYR SMALL EM	CYR SMALL EM
+CYR CAPIT EN	CYR CAPIT EN
+CYR CAPIT EN	CYR SMALL EN
+CYR SMALL EN	CYR CAPIT EN
+CYR SMALL EN	CYR SMALL EN
+CYR CAPIT O	CYR CAPIT O
+CYR CAPIT O	CYR SMALL O
+CYR SMALL O	CYR CAPIT O
+CYR SMALL O	CYR SMALL O
+CYR CAPIT PE	CYR CAPIT PE
+CYR CAPIT PE	CYR SMALL PE
+CYR SMALL PE	CYR CAPIT PE
+CYR SMALL PE	CYR SMALL PE
+CYR CAPIT ER	CYR CAPIT ER
+CYR CAPIT ER	CYR SMALL ER
+CYR SMALL ER	CYR CAPIT ER
+CYR SMALL ER	CYR SMALL ER
+CYR CAPIT ES	CYR CAPIT ES
+CYR CAPIT ES	CYR SMALL ES
+CYR SMALL ES	CYR CAPIT ES
+CYR SMALL ES	CYR SMALL ES
+CYR CAPIT TE	CYR CAPIT TE
+CYR CAPIT TE	CYR SMALL TE
+CYR SMALL TE	CYR CAPIT TE
+CYR SMALL TE	CYR SMALL TE
+CYR CAPIT U	CYR CAPIT U
+CYR CAPIT U	CYR SMALL U
+CYR SMALL U	CYR CAPIT U
+CYR SMALL U	CYR SMALL U
+CYR CAPIT EF	CYR CAPIT EF
+CYR CAPIT EF	CYR SMALL EF
+CYR SMALL EF	CYR CAPIT EF
+CYR SMALL EF	CYR SMALL EF
+CYR CAPIT HA	CYR CAPIT HA
+CYR CAPIT HA	CYR SMALL HA
+CYR SMALL HA	CYR CAPIT HA
+CYR SMALL HA	CYR SMALL HA
+CYR CAPIT TSE	CYR CAPIT TSE
+CYR CAPIT TSE	CYR SMALL TSE
+CYR SMALL TSE	CYR CAPIT TSE
+CYR SMALL TSE	CYR SMALL TSE
+CYR CAPIT CHE	CYR CAPIT CHE
+CYR CAPIT CHE	CYR SMALL CHE
+CYR SMALL CHE	CYR CAPIT CHE
+CYR SMALL CHE	CYR SMALL CHE
+CYR CAPIT SHA	CYR CAPIT SHA
+CYR CAPIT SHA	CYR SMALL SHA
+CYR SMALL SHA	CYR CAPIT SHA
+CYR SMALL SHA	CYR SMALL SHA
+CYR CAPIT SCHA	CYR CAPIT SCHA
+CYR CAPIT SCHA	CYR SMALL SCHA
+CYR SMALL SCHA	CYR CAPIT SCHA
+CYR SMALL SCHA	CYR SMALL SCHA
+CYR CAPIT HARD SIGN	CYR CAPIT HARD SIGN
+CYR CAPIT HARD SIGN	CYR SMALL HARD SIGN
+CYR SMALL HARD SIGN	CYR CAPIT HARD SIGN
+CYR SMALL HARD SIGN	CYR SMALL HARD SIGN
+CYR CAPIT YERU	CYR CAPIT YERU
+CYR CAPIT YERU	CYR SMALL YERU
+CYR SMALL YERU	CYR CAPIT YERU
+CYR SMALL YERU	CYR SMALL YERU
+CYR CAPIT SOFT SIGN	CYR CAPIT SOFT SIGN
+CYR CAPIT SOFT SIGN	CYR SMALL SOFT SIGN
+CYR SMALL SOFT SIGN	CYR CAPIT SOFT SIGN
+CYR SMALL SOFT SIGN	CYR SMALL SOFT SIGN
+CYR CAPIT E	CYR CAPIT E
+CYR CAPIT E	CYR SMALL E
+CYR SMALL E	CYR CAPIT E
+CYR SMALL E	CYR SMALL E
+CYR CAPIT YU	CYR CAPIT YU
+CYR CAPIT YU	CYR SMALL YU
+CYR SMALL YU	CYR CAPIT YU
+CYR SMALL YU	CYR SMALL YU
+CYR CAPIT YA	CYR CAPIT YA
+CYR CAPIT YA	CYR SMALL YA
+CYR SMALL YA	CYR CAPIT YA
+CYR SMALL YA	CYR SMALL YA
+ALTER TABLE t1 ADD ucs2_f CHAR(32) CHARACTER SET ucs2 NOT NULL;
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0391,'GREEK CAPIT ALPHA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0392,'GREEK CAPIT BETA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0393,'GREEK CAPIT GAMMA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0394,'GREEK CAPIT DELTA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0395,'GREEK CAPIT EPSILON');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B1,'GREEK SMALL ALPHA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B2,'GREEK SMALL BETA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B3,'GREEK SMALL GAMMA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B4,'GREEK SMALL DELTA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B5,'GREEK SMALL EPSILON');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0531,'ARMENIAN CAPIT AYB');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0532,'ARMENIAN CAPIT BEN');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0533,'ARMENIAN CAPIT GIM');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0534,'ARMENIAN CAPIT DA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0535,'ARMENIAN CAPIT ECH');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0536,'ARMENIAN CAPIT ZA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0561,'ARMENIAN SMALL YAB');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0562,'ARMENIAN SMALL BEN');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0563,'ARMENIAN SMALL GIM');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0564,'ARMENIAN SMALL DA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0565,'ARMENIAN SMALL ECH');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0566,'ARMENIAN SMALL ZA');
+ALTER TABLE t1 ADD armscii8_f CHAR(32) CHARACTER SET armscii8 NOT NULL;
+ALTER TABLE t1 ADD greek_f CHAR(32) CHARACTER SET greek NOT NULL;
+UPDATE t1 SET greek_f=CONVERT(ucs2_f USING greek) WHERE comment LIKE 'GRE%';
+UPDATE t1 SET armscii8_f=CONVERT(ucs2_f USING armscii8) WHERE comment LIKE 'ARM%';
+UPDATE t1 SET utf8_f=CONVERT(ucs2_f USING utf8) WHERE utf8_f='';
+UPDATE t1 SET ucs2_f=CONVERT(utf8_f USING ucs2) WHERE ucs2_f='';
+SELECT min(comment),count(*) FROM t1 GROUP BY ucs2_f;
+min(comment)	count(*)
+LAT CAPIT A	2
+LAT CAPIT B	2
+LAT CAPIT C	2
+LAT CAPIT D	2
+LAT CAPIT E	2
+LAT CAPIT F	2
+LAT CAPIT G	2
+LAT CAPIT H	2
+LAT CAPIT I	2
+LAT CAPIT J	2
+LAT CAPIT K	2
+LAT CAPIT L	2
+LAT CAPIT M	2
+LAT CAPIT N	2
+LAT CAPIT O	2
+LAT CAPIT P	2
+LAT CAPIT Q	2
+LAT CAPIT R	2
+LAT CAPIT S	2
+LAT CAPIT T	2
+LAT CAPIT U	2
+LAT CAPIT V	2
+LAT CAPIT W	2
+LAT CAPIT X	2
+LAT CAPIT Y	2
+LAT CAPIT Z	2
+GREEK CAPIT ALPHA	2
+GREEK CAPIT BETA	2
+GREEK CAPIT GAMMA	2
+GREEK CAPIT DELTA	2
+GREEK CAPIT EPSILON	2
+CYR CAPIT A	2
+CYR CAPIT BE	2
+CYR CAPIT VE	2
+CYR CAPIT GE	2
+CYR CAPIT DE	2
+CYR CAPIT IE	4
+CYR CAPIT ZHE	2
+CYR CAPIT ZE	2
+CYR CAPIT I	2
+CYR CAPIT KA	2
+CYR CAPIT EL	2
+CYR CAPIT EM	2
+CYR CAPIT EN	2
+CYR CAPIT O	2
+CYR CAPIT PE	2
+CYR CAPIT ER	2
+CYR CAPIT ES	2
+CYR CAPIT TE	2
+CYR CAPIT U	2
+CYR CAPIT EF	2
+CYR CAPIT HA	2
+CYR CAPIT TSE	2
+CYR CAPIT CHE	2
+CYR CAPIT SHA	2
+CYR CAPIT SCHA	2
+CYR CAPIT HARD SIGN	2
+CYR CAPIT YERU	2
+CYR CAPIT SOFT SIGN	2
+CYR CAPIT E	2
+CYR CAPIT YU	2
+CYR CAPIT YA	2
+ARMENIAN CAPIT AYB	2
+ARMENIAN CAPIT BEN	2
+ARMENIAN CAPIT GIM	2
+ARMENIAN CAPIT DA	2
+ARMENIAN CAPIT ECH	2
+ARMENIAN CAPIT ZA	2
+DROP TABLE t1;
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
new file mode 100644
index 00000000000..e05be96c6b7
--- /dev/null
+++ b/mysql-test/r/derived.result
@@ -0,0 +1,20 @@
+drop table if exists t1,t2,t3;
+CREATE TABLE t1 (a int not null, b char (10) not null);
+insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
+CREATE TABLE t2 (a int not null, b char (10) not null);
+insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');
+select t1.a,t3.y from t1,(select a as y from t2  where b='c') as t3  where t1.a = t3.y;
+a	y
+3	3
+3	3
+select t1.a,t3.a from t1,(select * from t2  where b='c') as t3  where t1.a = t3.a;
+a	a
+3	3
+3	3
+CREATE TABLE t3 (a int not null, b char (10) not null);
+insert into t3 values (3,'f'),(4,'y'),(5,'z'),(6,'c');
+select t1.a,t4.y from t1,(select t2.a as y from t2,(select t3.b from t3 where t3.a>3) as t5  where t2.b=t5.b) as t4  where t1.a = t4.y;
+a	y
+3	3
+3	3
+drop table if exists  t1.t2,t3;
diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result
index b312f5fbc3d..2b666b11362 100644
--- a/mysql-test/r/fulltext.result
+++ b/mysql-test/r/fulltext.result
@@ -138,10 +138,10 @@ show create table t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
   `ticket` int(11) default NULL,
-  `inhalt` text,
+  `inhalt` text character set latin1,
   KEY `tig` (`ticket`),
   FULLTEXT KEY `tix` (`inhalt`)
-) TYPE=MyISAM
+) TYPE=MyISAM CHARSET=latin1
 select * from t2 where MATCH inhalt AGAINST (NULL);
 ticket	inhalt
 select * from t2 where  MATCH inhalt AGAINST ('foobar');
diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result
index 13f452e26d8..eb3bae0fea2 100644
--- a/mysql-test/r/heap.result
+++ b/mysql-test/r/heap.result
@@ -68,7 +68,7 @@ explain select * from t1 where a in (869751,736494,226312,802616);
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	range	uniq_id	uniq_id	4	NULL	4	where used; Using index
 drop table t1;
-create table t1 (x int not null, y int not null, key x(x), unique y(y))
+create table t1 (x int not null, y int not null, key x (x), unique y (y))
 type=heap;
 insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
 select * from t1 where x=1;
diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result
new file mode 100644
index 00000000000..c3080389999
--- /dev/null
+++ b/mysql-test/r/heap_btree.result
@@ -0,0 +1,218 @@
+drop table if exists t1;
+create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100;
+insert into t1 values(1,1),(2,2),(3,3),(4,4);
+delete from t1 where a=1 or a=0;
+show keys from t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
+t1	0	PRIMARY	1	a	A	NULL	NULL	NULL		BTREE	
+select * from t1;
+a	b
+2	2
+3	3
+4	4
+select * from t1 where a=4;
+a	b
+4	4
+update t1 set b=5 where a=4;
+update t1 set b=b+1 where a>=3;
+replace t1 values (3,3);
+select * from t1;
+a	b
+2	2
+3	3
+4	6
+alter table t1 add c int not null, add key using BTREE (c,a);
+drop table t1;
+create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps";
+insert into t1 values(1,1),(2,2),(3,3),(4,4);
+delete from t1 where a > 0;
+select * from t1;
+a	b
+drop table t1;
+create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps";
+insert into t1 values(1,1),(2,2),(3,3),(4,4);
+alter table t1 modify a int not null auto_increment, type=myisam, comment="new myisam table";
+select * from t1;
+a	b
+1	1
+2	2
+3	3
+4	4
+drop table t1;
+create table t1 (a int not null) type=heap;
+insert into t1 values (869751),(736494),(226312),(802616);
+select * from t1 where a > 736494;
+a
+869751
+802616
+alter table t1 add unique uniq_id using BTREE (a);
+select * from t1 where a > 736494;
+a
+802616
+869751
+select * from t1 where a = 736494;
+a
+736494
+select * from t1 where a=869751 or a=736494;
+a
+736494
+869751
+select * from t1 where a in (869751,736494,226312,802616);
+a
+226312
+736494
+802616
+869751
+alter table t1 type=myisam;
+explain select * from t1 where a in (869751,736494,226312,802616);
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	range	uniq_id	uniq_id	4	NULL	4	where used; Using index
+drop table t1;
+create table t1 (x int not null, y int not null, key x  using BTREE (x), unique y  using BTREE (y))
+type=heap;
+insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
+select * from t1 where x=1;
+x	y
+1	1
+1	3
+select * from t1,t1 as t2 where t1.x=t2.y;
+x	y	x	y
+1	1	1	1
+2	2	2	2
+1	3	1	1
+2	4	2	2
+2	5	2	2
+2	6	2	2
+explain select * from t1,t1 as t2 where t1.x=t2.y;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	x	NULL	NULL	NULL	6	
+t2	eq_ref	y	y	4	t1.x	1	
+drop table t1;
+create table t1 (a int) type=heap;
+insert into t1 values(1);
+select max(a) from t1;
+max(a)
+1
+drop table t1;
+CREATE TABLE t1 ( a int not null default 0, b int not null default 0,  key  using BTREE (a,b),  key  using BTREE (b)  ) TYPE=HEAP;
+insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
+select * from t1 where a=1;
+a	b
+1	1
+1	2
+1	3
+1	4
+1	5
+1	6
+insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
+select * from t1 where a=1;
+a	b
+1	1
+1	1
+1	2
+1	2
+1	3
+1	3
+1	4
+1	4
+1	5
+1	5
+1	6
+1	6
+explain select * from tx where a=x order by a,b;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+tx	ref	a	a	x	const	x	where used
+explain select * from tx where a=x order by b;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+tx	ref	a	a	x	const	x	where used
+select * from t1 where b=1;
+a	b
+1	1
+1	1
+explain select * from tx where b=x;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+tx	ref	b	b	x	const	x	where used
+drop table t1;
+create table t1 (id int unsigned not null, primary key  using BTREE (id)) type=HEAP;
+insert into t1 values(1);
+select max(id) from t1;
+max(id)
+1
+insert into t1 values(2);
+select max(id) from t1;
+max(id)
+2
+replace into t1 values(1);
+drop table t1;
+create table t1 (n int) type=heap;
+drop table t1;
+create table t1 (n int) type=heap;
+drop table if exists t1;
+CREATE table t1(f1 int not null,f2 char(20) not 
+null,index(f2)) type=heap;
+INSERT into t1 set f1=12,f2="bill";
+INSERT into t1 set f1=13,f2="bill";
+INSERT into t1 set f1=14,f2="bill";
+INSERT into t1 set f1=15,f2="bill";
+INSERT into t1 set f1=16,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+delete from t1 where f2="bill";
+select * from t1;
+f1	f2
+16	ted
+12	ted
+12	ted
+12	ted
+12	ted
+drop table t1;
+create table t1 (btn char(10) not null, key using BTREE (btn)) type=heap;
+insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
+explain select * from t1 where btn like "q%";
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	btn	NULL	NULL	NULL	14	where used
+select * from t1 where btn like "q%";
+btn
+alter table t1 add column new_col char(1) not null, add key using BTREE (btn,new_col), drop key btn;
+update t1 set new_col=btn;
+explain select * from t1 where btn="a";
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ref	btn	btn	10	const	1	where used
+explain select * from t1 where btn="a" and new_col="a";
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ref	btn	btn	11	const,const	1	where used
+drop table t1;
+CREATE TABLE t1 (
+a int default NULL,
+b int default NULL,
+KEY a using BTREE (a),
+UNIQUE b using BTREE (b)
+) type=heap;
+INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);
+SELECT * FROM t1 WHERE a=NULL;
+a	b
+explain SELECT * FROM t1 WHERE a IS NULL;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ref	a	a	5	const	1	where used
+SELECT * FROM t1 WHERE a<=>NULL;
+a	b
+NULL	99
+SELECT * FROM t1 WHERE b=NULL;
+a	b
+explain SELECT * FROM t1 WHERE b IS NULL;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ref	b	b	5	const	1	where used
+SELECT * FROM t1 WHERE b<=>NULL;
+a	b
+99	NULL
+INSERT INTO t1 VALUES (1,3);
+Duplicate entry '3' for key 1
+DROP TABLE t1;
+CREATE TABLE t1 (a int not null, primary key using BTREE (a)) type=heap;
+INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
+DELETE from t1 where a < 100;
+SELECT * from t1;
+a
+DROP TABLE t1;
diff --git a/mysql-test/r/heap_hash.result b/mysql-test/r/heap_hash.result
new file mode 100644
index 00000000000..9b7f2cca6bc
--- /dev/null
+++ b/mysql-test/r/heap_hash.result
@@ -0,0 +1,205 @@
+drop table if exists t1;
+create table t1 (a int not null,b int not null, primary key using HASH (a)) type=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100;
+insert into t1 values(1,1),(2,2),(3,3),(4,4);
+delete from t1 where a=1 or a=0;
+show keys from t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
+t1	0	PRIMARY	1	a	NULL	NULL	NULL	NULL		HASH	
+select * from t1;
+a	b
+2	2
+3	3
+4	4
+select * from t1 where a=4;
+a	b
+4	4
+update t1 set b=5 where a=4;
+update t1 set b=b+1 where a>=3;
+replace t1 values (3,3);
+select * from t1;
+a	b
+2	2
+3	3
+4	6
+alter table t1 add c int not null, add key using HASH (c,a);
+drop table t1;
+create table t1 (a int not null,b int not null, primary key using HASH (a)) type=heap comment="testing heaps";
+insert into t1 values(1,1),(2,2),(3,3),(4,4);
+delete from t1 where a > 0;
+select * from t1;
+a	b
+drop table t1;
+create table t1 (a int not null,b int not null, primary key using HASH (a)) type=heap comment="testing heaps";
+insert into t1 values(1,1),(2,2),(3,3),(4,4);
+alter table t1 modify a int not null auto_increment, type=myisam, comment="new myisam table";
+select * from t1;
+a	b
+1	1
+2	2
+3	3
+4	4
+drop table t1;
+create table t1 (a int not null) type=heap;
+insert into t1 values (869751),(736494),(226312),(802616);
+select * from t1 where a > 736494;
+a
+869751
+802616
+alter table t1 add unique uniq_id using HASH (a);
+select * from t1 where a > 736494;
+a
+869751
+802616
+select * from t1 where a = 736494;
+a
+736494
+select * from t1 where a=869751 or a=736494;
+a
+736494
+869751
+select * from t1 where a in (869751,736494,226312,802616);
+a
+226312
+736494
+802616
+869751
+alter table t1 type=myisam;
+explain select * from t1 where a in (869751,736494,226312,802616);
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	range	uniq_id	uniq_id	4	NULL	4	where used; Using index
+drop table t1;
+create table t1 (x int not null, y int not null, key x  using HASH (x), unique y  using HASH (y))
+type=heap;
+insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
+select * from t1 where x=1;
+x	y
+1	3
+1	1
+select * from t1,t1 as t2 where t1.x=t2.y;
+x	y	x	y
+1	1	1	1
+2	2	2	2
+1	3	1	1
+2	4	2	2
+2	5	2	2
+2	6	2	2
+explain select * from t1,t1 as t2 where t1.x=t2.y;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	x	NULL	NULL	NULL	6	
+t2	eq_ref	y	y	4	t1.x	1	
+drop table t1;
+create table t1 (a int) type=heap;
+insert into t1 values(1);
+select max(a) from t1;
+max(a)
+1
+drop table t1;
+CREATE TABLE t1 ( a int not null default 0, b int not null default 0,  key  using HASH (a),  key  using HASH (b)  ) TYPE=HEAP;
+insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
+select * from t1 where a=1;
+a	b
+1	6
+1	5
+1	4
+1	3
+1	2
+1	1
+insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
+select * from t1 where a=1;
+a	b
+1	6
+1	5
+1	4
+1	3
+1	2
+1	1
+1	6
+1	5
+1	4
+1	3
+1	2
+1	1
+drop table t1;
+create table t1 (id int unsigned not null, primary key  using HASH (id)) type=HEAP;
+insert into t1 values(1);
+select max(id) from t1;
+max(id)
+1
+insert into t1 values(2);
+select max(id) from t1;
+max(id)
+2
+replace into t1 values(1);
+drop table t1;
+create table t1 (n int) type=heap;
+drop table t1;
+create table t1 (n int) type=heap;
+drop table if exists t1;
+CREATE table t1(f1 int not null,f2 char(20) not 
+null,index(f2)) type=heap;
+INSERT into t1 set f1=12,f2="bill";
+INSERT into t1 set f1=13,f2="bill";
+INSERT into t1 set f1=14,f2="bill";
+INSERT into t1 set f1=15,f2="bill";
+INSERT into t1 set f1=16,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+delete from t1 where f2="bill";
+select * from t1;
+f1	f2
+16	ted
+12	ted
+12	ted
+12	ted
+12	ted
+drop table t1;
+create table t1 (btn char(10) not null, key using HASH (btn)) type=heap;
+insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
+explain select * from t1 where btn like "q%";
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	btn	NULL	NULL	NULL	14	where used
+select * from t1 where btn like "q%";
+btn
+alter table t1 add column new_col char(1) not null, add key using HASH (btn,new_col), drop key btn;
+update t1 set new_col=btn;
+explain select * from t1 where btn="a";
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	btn	NULL	NULL	NULL	14	where used
+explain select * from t1 where btn="a" and new_col="a";
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ref	btn	btn	11	const,const	10	where used
+drop table t1;
+CREATE TABLE t1 (
+a int default NULL,
+b int default NULL,
+KEY a using HASH (a),
+UNIQUE b using HASH (b)
+) type=heap;
+INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);
+SELECT * FROM t1 WHERE a=NULL;
+a	b
+explain SELECT * FROM t1 WHERE a IS NULL;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ref	a	a	5	const	10	where used
+SELECT * FROM t1 WHERE a<=>NULL;
+a	b
+NULL	99
+SELECT * FROM t1 WHERE b=NULL;
+a	b
+explain SELECT * FROM t1 WHERE b IS NULL;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ref	b	b	5	const	1	where used
+SELECT * FROM t1 WHERE b<=>NULL;
+a	b
+99	NULL
+INSERT INTO t1 VALUES (1,3);
+Duplicate entry '3' for key 1
+DROP TABLE t1;
+CREATE TABLE t1 (a int not null, primary key using HASH (a)) type=heap;
+INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
+DELETE from t1 where a < 100;
+SELECT * from t1;
+a
+DROP TABLE t1;
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 78b0da2769e..34c2eef3fea 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -805,9 +805,9 @@ create table t1 (a char(20), index (a(5))) type=innodb;
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` char(20) default NULL,
+  `a` char(20) character set latin1 default NULL,
   KEY `a` (`a`)
-) TYPE=InnoDB
+) TYPE=InnoDB CHARSET=latin1
 drop table t1;
 create temporary table t1 (a int not null auto_increment, primary key(a)) type=innodb;
 insert into t1 values (NULL),(NULL),(NULL);
diff --git a/mysql-test/r/isam.result b/mysql-test/r/isam.result
index d19352aad42..5cb218dc4ce 100644
--- a/mysql-test/r/isam.result
+++ b/mysql-test/r/isam.result
@@ -67,10 +67,10 @@ a	int(11)		PRI	0
 b	int(11)		MUL	0	
 c	int(11)			0	
 show full columns from t1;
-Field	Type	Null	Key	Default	Extra	Privileges
-a	int(11)		PRI	0		select,insert,update,references
-b	int(11)		MUL	0		select,insert,update,references
-c	int(11)			0		select,insert,update,references
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+a	int(11)		PRI	0		select,insert,update,references	
+b	int(11)		MUL	0		select,insert,update,references	
+c	int(11)			0		select,insert,update,references	
 show index from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
 t1	0	PRIMARY	1	a	A	4	NULL	NULL		BTREE	
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index b95352a9eaa..102f5520f48 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -172,9 +172,9 @@ show create table t3;
 Table	Create Table
 t3	CREATE TABLE `t3` (
   `a` int(11) NOT NULL default '0',
-  `b` char(20) default NULL,
+  `b` char(20) character set latin1 default NULL,
   KEY `a` (`a`)
-) TYPE=MRG_MyISAM UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2)
 create table t4 (a int not null, b char(10), key(a)) type=MERGE UNION=(t1,t2);
 select * from t4;
 Can't open file: 't4.MRG'. (errno: 143)
@@ -249,14 +249,14 @@ t3	CREATE TABLE `t3` (
   `incr` int(11) NOT NULL default '0',
   `othr` int(11) NOT NULL default '0',
   PRIMARY KEY  (`incr`)
-) TYPE=MRG_MyISAM UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2)
 alter table t3 drop primary key;
 show create table t3;
 Table	Create Table
 t3	CREATE TABLE `t3` (
   `incr` int(11) NOT NULL default '0',
   `othr` int(11) NOT NULL default '0'
-) TYPE=MRG_MyISAM UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2)
 drop table t3,t2,t1;
 create table t1 (a int not null) type=merge;
 select * from t1;
@@ -287,28 +287,28 @@ t3	CREATE TABLE `t3` (
   `a` int(11) NOT NULL default '0',
   `b` int(11) NOT NULL default '0',
   KEY `a` (`a`,`b`)
-) TYPE=MyISAM
+) TYPE=MyISAM CHARSET=latin1
 show create table t4;
 Table	Create Table
 t4	CREATE TABLE `t4` (
   `a` int(11) NOT NULL default '0',
   `b` int(11) NOT NULL default '0',
   KEY `a` (`a`,`b`)
-) TYPE=MRG_MyISAM UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2)
 show create table t5;
 Table	Create Table
 t5	CREATE TABLE `t5` (
   `a` int(11) NOT NULL default '0',
   `b` int(11) NOT NULL auto_increment,
   PRIMARY KEY  (`a`,`b`)
-) TYPE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(t1,t2)
 show create table t6;
 Table	Create Table
 t6	CREATE TABLE `t6` (
   `a` int(11) NOT NULL default '0',
   `b` int(11) NOT NULL auto_increment,
   PRIMARY KEY  (`a`,`b`)
-) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1,t2)
+) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=LAST UNION=(t1,t2)
 insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL);
 insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL);
 select * from t3 order by b,a limit 3;
@@ -373,7 +373,7 @@ t4	CREATE TABLE `t4` (
   `a` int(11) NOT NULL default '0',
   `b` int(11) NOT NULL default '0',
   KEY `a` (`a`,`b`)
-) TYPE=MRG_MyISAM UNION=(t1,t2,t3)
+) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2,t3)
 select * from t4 order by a,b;
 a	b
 1	1
@@ -399,7 +399,7 @@ t4	CREATE TABLE `t4` (
   `a` int(11) NOT NULL default '0',
   `b` int(11) NOT NULL default '0',
   KEY `a` (`a`,`b`)
-) TYPE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(t1,t2,t3)
+) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(t1,t2,t3)
 insert into t4 values (4,1),(4,2);
 select * from t1 order by a,b;
 a	b
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 739a47b41f1..f66d211a987 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -1,7 +1,7 @@
 drop table if exists t1;
 CREATE TABLE t1 (
 STRING_DATA char(255) default NULL,
-KEY STRING_DATA (STRING_DATA)
+KEY string_data (STRING_DATA)
 ) TYPE=MyISAM;
 INSERT INTO t1 VALUES ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
 INSERT INTO t1 VALUES ('DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
diff --git a/mysql-test/r/rollback.result b/mysql-test/r/rollback.result
index a5eb6f8729f..d87aa68dce4 100644
--- a/mysql-test/r/rollback.result
+++ b/mysql-test/r/rollback.result
@@ -4,9 +4,26 @@ begin work;
 insert into t1 values (4);
 insert into t1 values (5);
 rollback;
-Warning:  Some non-transactional changed tables couldn't be rolled back
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+select @@warning_count;
+@@warning_count
+1
+select @@error_count;
+@@error_count
+0
+show warnings;
+Level	Code	Message
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+show errors;
+Level	Code	Message
 select * from t1;
 n
 4
 5
+select @@warning_count;
+@@warning_count
+0
+show warnings;
+Level	Code	Message
 drop table t1;
diff --git a/mysql-test/r/rpl_log.result b/mysql-test/r/rpl_log.result
index 7d1843f95fc..01efce20e1d 100644
--- a/mysql-test/r/rpl_log.result
+++ b/mysql-test/r/rpl_log.result
@@ -17,24 +17,24 @@ drop table t1;
 show binlog events;
 Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
 master-bin.001	4	Start	1	4	Server ver: VERSION, Binlog ver: 3
-master-bin.001	79	Query	1	79	use test; create table t1(n int not null auto_increment primary key)
+master-bin.001	79	Query	1	79	use `test`; create table t1(n int not null auto_increment primary key)
 master-bin.001	172	Intvar	1	172	INSERT_ID=1
-master-bin.001	200	Query	1	200	use test; insert into t1 values (NULL)
-master-bin.001	263	Query	1	263	use test; drop table t1
-master-bin.001	311	Query	1	311	use test; create table t1 (word char(20) not null)
+master-bin.001	200	Query	1	200	use `test`; insert into t1 values (NULL)
+master-bin.001	263	Query	1	263	use `test`; drop table t1
+master-bin.001	311	Query	1	311	use `test`; create table t1 (word char(20) not null)
 master-bin.001	386	Create_file	1	386	db=test;table=t1;file_id=1;block_len=81
 master-bin.001	556	Exec_load	1	556	;file_id=1
-master-bin.001	579	Query	1	579	use test; drop table t1
+master-bin.001	579	Query	1	579	use `test`; drop table t1
 show binlog events from 79 limit 1;
 Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
-master-bin.001	79	Query	1	79	use test; create table t1(n int not null auto_increment primary key)
+master-bin.001	79	Query	1	79	use `test`; create table t1(n int not null auto_increment primary key)
 show binlog events from 79 limit 2;
 Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
-master-bin.001	79	Query	1	79	use test; create table t1(n int not null auto_increment primary key)
+master-bin.001	79	Query	1	79	use `test`; create table t1(n int not null auto_increment primary key)
 master-bin.001	172	Intvar	1	172	INSERT_ID=1
 show binlog events from 79 limit 2,1;
 Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
-master-bin.001	200	Query	1	200	use test; insert into t1 values (NULL)
+master-bin.001	200	Query	1	200	use `test`; insert into t1 values (NULL)
 flush logs;
 slave start;
 flush logs;
@@ -45,20 +45,20 @@ drop table t1;
 show binlog events;
 Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
 master-bin.001	4	Start	1	4	Server ver: VERSION, Binlog ver: 3
-master-bin.001	79	Query	1	79	use test; create table t1(n int not null auto_increment primary key)
+master-bin.001	79	Query	1	79	use `test`; create table t1(n int not null auto_increment primary key)
 master-bin.001	172	Intvar	1	172	INSERT_ID=1
-master-bin.001	200	Query	1	200	use test; insert into t1 values (NULL)
-master-bin.001	263	Query	1	263	use test; drop table t1
-master-bin.001	311	Query	1	311	use test; create table t1 (word char(20) not null)
+master-bin.001	200	Query	1	200	use `test`; insert into t1 values (NULL)
+master-bin.001	263	Query	1	263	use `test`; drop table t1
+master-bin.001	311	Query	1	311	use `test`; create table t1 (word char(20) not null)
 master-bin.001	386	Create_file	1	386	db=test;table=t1;file_id=1;block_len=81
 master-bin.001	556	Exec_load	1	556	;file_id=1
-master-bin.001	579	Query	1	579	use test; drop table t1
+master-bin.001	579	Query	1	579	use `test`; drop table t1
 master-bin.001	627	Rotate	1	627	master-bin.002;pos=4
 show binlog events in 'master-bin.002';
 Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
-master-bin.002	4	Query	1	4	use test; create table t1 (n int)
-master-bin.002	62	Query	1	62	use test; insert into t1 values (1)
-master-bin.002	122	Query	1	122	use test; drop table t1
+master-bin.002	4	Query	1	4	use `test`; create table t1 (n int)
+master-bin.002	62	Query	1	62	use `test`; insert into t1 values (1)
+master-bin.002	122	Query	1	122	use `test`; drop table t1
 show master logs;
 Log_name
 master-bin.001
@@ -82,9 +82,9 @@ slave-bin.001	588	Query	1	579	use test; drop table t1
 slave-bin.001	636	Rotate	2	636	slave-bin.002;pos=4
 show binlog events in 'slave-bin.002' from 4;
 Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
-slave-bin.002	4	Query	1	4	use test; create table t1 (n int)
-slave-bin.002	62	Query	1	62	use test; insert into t1 values (1)
-slave-bin.002	122	Query	1	122	use test; drop table t1
+slave-bin.002	57	Query	1	4	use `test`; create table t1 (n int)
+slave-bin.002	115	Query	1	62	use `test`; insert into t1 values (1)
+slave-bin.002	175	Query	1	122	use `test`; drop table t1
 show slave status;
 Master_Host	Master_User	Master_Port	Connect_retry	Master_Log_File	Read_Master_Log_Pos	Relay_Log_File	Relay_Log_Pos	Relay_Master_Log_File	Slave_IO_Running	Slave_SQL_Running	Replicate_do_db	Replicate_ignore_db	Last_errno	Last_error	Skip_counter	Exec_master_log_pos	Relay_log_space
 127.0.0.1	root	MASTER_PORT	1	master-bin.002	170	slave-relay-bin.002	957	master-bin.002	Yes	Yes			0		0	170	961
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 88220b75591..4ccc40d3bd6 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3219,23 +3219,23 @@ Tables_in_test (s%)
 show tables from test like "t?";
 Tables_in_test (t?)
 show full columns from t2;
-Field	Type	Null	Key	Default	Extra	Privileges
-auto	int(11)		PRI	NULL	auto_increment	select,insert,update,references
-fld1	int(6) unsigned zerofill		UNI	000000		select,insert,update,references
-companynr	tinyint(2) unsigned zerofill			00		select,insert,update,references
-fld3	char(30)		MUL			select,insert,update,references
-fld4	char(35)					select,insert,update,references
-fld5	char(35)					select,insert,update,references
-fld6	char(4)					select,insert,update,references
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+auto	int(11)		PRI	NULL	auto_increment	select,insert,update,references	
+fld1	int(6) unsigned zerofill		UNI	000000		select,insert,update,references	
+companynr	tinyint(2) unsigned zerofill			00		select,insert,update,references	
+fld3	char(30) character set latin1		MUL			select,insert,update,references	
+fld4	char(35) character set latin1					select,insert,update,references	
+fld5	char(35) character set latin1					select,insert,update,references	
+fld6	char(4) character set latin1					select,insert,update,references	
 show full columns from t2 from test like 'f%';
-Field	Type	Null	Key	Default	Extra	Privileges
-fld1	int(6) unsigned zerofill		UNI	000000		select,insert,update,references
-fld3	char(30)		MUL			select,insert,update,references
-fld4	char(35)					select,insert,update,references
-fld5	char(35)					select,insert,update,references
-fld6	char(4)					select,insert,update,references
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+fld1	int(6) unsigned zerofill		UNI	000000		select,insert,update,references	
+fld3	char(30) character set latin1		MUL			select,insert,update,references	
+fld4	char(35) character set latin1					select,insert,update,references	
+fld5	char(35) character set latin1					select,insert,update,references	
+fld6	char(4) character set latin1					select,insert,update,references	
 show full columns from t2 from test like 's%';
-Field	Type	Null	Key	Default	Extra	Privileges
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
 show keys from t2;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
 t2	0	PRIMARY	1	auto	A	1199	NULL	NULL		BTREE	
diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result
index 2c32d766a38..462abb29979 100644
--- a/mysql-test/r/show_check.result
+++ b/mysql-test/r/show_check.result
@@ -43,7 +43,7 @@ wait_timeout	28800
 show variables like "this_doesn't_exists%";
 Variable_name	Value
 show table status from test like "this_doesn't_exists%";
-Name	Type	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Create_options	Comment
+Name	Type	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Charset	Create_options	Comment
 show databases;
 Database
 mysql
@@ -77,24 +77,31 @@ show create table t1;
 Table	Create Table
 t1	CREATE TEMPORARY TABLE `t1` (
   `a` int(11) NOT NULL default '0'
-) TYPE=MyISAM
+) TYPE=MyISAM CHARSET=latin1
 alter table t1 rename t2;
 show create table t2;
 Table	Create Table
 t2	CREATE TEMPORARY TABLE `t2` (
   `a` int(11) NOT NULL default '0'
-) TYPE=MyISAM
+) TYPE=MyISAM CHARSET=latin1
 drop table t2;
 create table t1 (
 test_set set( 'val1', 'val2', 'val3' ) not null default '',
-name char(20) default 'O''Brien'
+name char(20) default 'O''Brien' comment 'O''Brien as default',
+c int not null comment 'int column'
   ) comment = 'it\'s a table' ;
 show create table t1 ;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `test_set` set('val1','val2','val3') NOT NULL default '',
-  `name` char(20) default 'O''Brien'
-) TYPE=MyISAM COMMENT='it''s a table'
+  `name` char(20) character set latin1 default 'O''Brien' COMMENT 'O''Brien as default',
+  `c` int(11) NOT NULL default '0' COMMENT 'int column'
+) TYPE=MyISAM CHARSET=latin1 COMMENT='it''s a table'
+show full columns from t1;
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+test_set	set('val1','val2','val3')					select,insert,update,references	
+name	char(20) character set latin1	YES		O'Brien		select,insert,update,references	O'Brien as default
+c	int(11)			0		select,insert,update,references	int column
 drop table t1;
 create table t1 (a int not null, unique aa (a));
 show create table t1;
@@ -102,7 +109,7 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) NOT NULL default '0',
   UNIQUE KEY `aa` (`a`)
-) TYPE=MyISAM
+) TYPE=MyISAM CHARSET=latin1
 drop table t1;
 create table t1 (a int not null, primary key (a));
 show create table t1;
@@ -110,7 +117,7 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) NOT NULL default '0',
   PRIMARY KEY  (`a`)
-) TYPE=MyISAM
+) TYPE=MyISAM CHARSET=latin1
 drop table t1;
 flush tables;
 show open tables;
@@ -126,25 +133,25 @@ show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) NOT NULL default '0',
-  `b` char(10) default NULL,
+  `b` char(10) character set latin1 default NULL,
   KEY `b` (`b`)
-) TYPE=MyISAM MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test'
+) TYPE=MyISAM CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test'
 alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0;
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) NOT NULL default '0',
-  `b` varchar(10) default NULL,
+  `b` varchar(10) character set latin1 default NULL,
   KEY `b` (`b`)
-) TYPE=MyISAM MIN_ROWS=10 MAX_ROWS=200 AVG_ROW_LENGTH=10 PACK_KEYS=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='test'
+) TYPE=MyISAM CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=200 AVG_ROW_LENGTH=10 PACK_KEYS=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='test'
 ALTER TABLE t1 AVG_ROW_LENGTH=0 CHECKSUM=0 COMMENT="" MIN_ROWS=0 MAX_ROWS=0 PACK_KEYS=DEFAULT DELAY_KEY_WRITE=0 ROW_FORMAT=default;
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) NOT NULL default '0',
-  `b` varchar(10) default NULL,
+  `b` varchar(10) character set latin1 default NULL,
   KEY `b` (`b`)
-) TYPE=MyISAM
+) TYPE=MyISAM CHARSET=latin1
 drop table t1;
 create table t1 (a decimal(9,2), b decimal (9,0), e double(9,2), f double(5,0), h float(3,2), i float(3,0));
 show columns from t1;
@@ -155,6 +162,14 @@ e	double(9,2)	YES		NULL
 f	double(5,0)	YES		NULL	
 h	float(3,2)	YES		NULL	
 i	float(3,0)	YES		NULL	
+show full columns from t1;
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+a	decimal(9,2)	YES		NULL		select,insert,update,references	
+b	decimal(9,0)	YES		NULL		select,insert,update,references	
+e	double(9,2)	YES		NULL		select,insert,update,references	
+f	double(5,0)	YES		NULL		select,insert,update,references	
+h	float(3,2)	YES		NULL		select,insert,update,references	
+i	float(3,0)	YES		NULL		select,insert,update,references	
 drop table t1;
 create table t1 (c decimal, d double, f float, r real);
 show columns from t1;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
new file mode 100644
index 00000000000..fb91bebd727
--- /dev/null
+++ b/mysql-test/r/subselect.result
@@ -0,0 +1,94 @@
+select (select 2);
+(select 2)
+2
+drop table if exists t1,t2,t3,t4,attend,clinic;
+create table t1 (a int);
+create table t2 (a int, b int);
+create table t3 (a int);
+create table t4 (a int, b int);
+insert into t1 values (2);
+insert into t2 values (1,7),(2,7);
+insert into t4 values (4,8),(3,8),(5,9);
+select (select a from t1 where t1.a=t2.a), a from t2;
+(select a from t1 where t1.a=t2.a)	a
+NULL	1
+2	2
+select (select a from t1 where t1.a=t2.b), a from t2;
+(select a from t1 where t1.a=t2.b)	a
+NULL	1
+NULL	2
+select (select a from t1), a from t2;
+(select a from t1)	a
+2	1
+2	2
+select (select a from t3), a from t2;
+(select a from t3)	a
+NULL	1
+NULL	2
+select * from t2 where t2.a=(select a from t1);
+a	b
+2	7
+insert into t3 values (6),(7),(3);
+select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
+a	b
+1	7
+2	7
+select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) 
+union (select * from t4 order by a limit 2) limit 3;
+a	b
+1	7
+2	7
+3	8
+select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) 
+union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
+a	b
+1	7
+2	7
+3	8
+4	8
+select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
+(select a from t3 where a<t2.a*4 order by 1 desc limit 1)	a
+3	1
+7	2
+select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
+(select * from t2 where a>1) as tt;
+(select t3.a from t3 where a<8 order by 1 desc limit 1)	a
+7	2
+select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
+a
+2
+select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1);
+a
+2
+select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1);
+a
+select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
+b	(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)
+8	7.5000
+8	4.5000
+9	7.5000
+select * from t3 where exists (select * from t2 where t2.b=t3.a);
+a
+7
+select * from t3 where not exists (select * from t2 where t2.b=t3.a);
+a
+6
+3
+insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9);
+select b,max(a) as ma from t4 group by b having b < (select max(t2.a)
+from t2 where t2.b=t4.b);
+b	ma
+select b,max(a) as ma from t4 group by b having b >= (select max(t2.a)
+from t2 where t2.b=t4.b);
+b	ma
+7	12
+create table attend (patient_uq int, clinic_uq int, index i1 (clinic_uq));
+create table clinic( uq int primary key, name char(25));
+insert into clinic values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
+insert into attend values (1,1),(1,2),(2,2),(1,3);
+select * from attend where exists (select * from clinic where uq = clinic_uq);
+patient_uq	clinic_uq
+1	1
+1	2
+2	2
+drop table t1,t2,t3,t4,attend,clinic;
diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result
index 5626bc0fe11..1eb595eb2a9 100644
--- a/mysql-test/r/symlink.result
+++ b/mysql-test/r/symlink.result
@@ -36,10 +36,10 @@ show create table t9;
 Table	Create Table
 t9	CREATE TABLE `t9` (
   `a` int(11) NOT NULL auto_increment,
-  `b` char(16) NOT NULL default '',
+  `b` char(16) character set latin1 NOT NULL default '',
   `c` int(11) NOT NULL default '0',
   PRIMARY KEY  (`a`)
-) TYPE=MyISAM
+) TYPE=MyISAM CHARSET=latin1
 alter table t9 rename t8, add column d int not null;
 alter table t8 rename t7;
 rename table t7 to t9;
@@ -57,9 +57,9 @@ show create table test_mysqltest.t9;
 Table	Create Table
 t9	CREATE TABLE `t9` (
   `a` int(11) NOT NULL auto_increment,
-  `b` char(16) NOT NULL default '',
+  `b` char(16) character set latin1 NOT NULL default '',
   `c` int(11) NOT NULL default '0',
   `d` int(11) NOT NULL default '0',
   PRIMARY KEY  (`a`)
-) TYPE=MyISAM
+) TYPE=MyISAM CHARSET=latin1
 drop database test_mysqltest;
diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result
index 2047eed6227..c99d22c2889 100644
--- a/mysql-test/r/type_blob.result
+++ b/mysql-test/r/type_blob.result
@@ -37,18 +37,18 @@ insert into t1 values (NULL,NULL,NULL,NULL);
 update t1 set c="",b=null where c="1";
 lock tables t1 READ;
 show full fields from t1;
-Field	Type	Null	Key	Default	Extra	Privileges
-t	text	YES		NULL		select,insert,update,references
-c	varchar(10)	YES		NULL		select,insert,update,references
-b	blob	YES		NULL		select,insert,update,references
-d	varchar(10) binary	YES		NULL		select,insert,update,references
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+t	text character set latin1	YES		NULL		select,insert,update,references	
+c	varchar(10) character set latin1	YES		NULL		select,insert,update,references	
+b	blob	YES		NULL		select,insert,update,references	
+d	varchar(10) binary	YES		NULL		select,insert,update,references	
 lock tables t1 WRITE;
 show full fields from t1;
-Field	Type	Null	Key	Default	Extra	Privileges
-t	text	YES		NULL		select,insert,update,references
-c	varchar(10)	YES		NULL		select,insert,update,references
-b	blob	YES		NULL		select,insert,update,references
-d	varchar(10) binary	YES		NULL		select,insert,update,references
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+t	text character set latin1	YES		NULL		select,insert,update,references	
+c	varchar(10) character set latin1	YES		NULL		select,insert,update,references	
+b	blob	YES		NULL		select,insert,update,references	
+d	varchar(10) binary	YES		NULL		select,insert,update,references	
 unlock tables;
 select t from t1 where t like "hello";
 t
diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result
index ee3bd077798..c0f0be246c9 100644
--- a/mysql-test/r/type_enum.result
+++ b/mysql-test/r/type_enum.result
@@ -1627,12 +1627,12 @@ show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` enum('','a','b') NOT NULL default ''
-) TYPE=MyISAM
+) TYPE=MyISAM CHARSET=latin1
 drop table t1;
 create table t1 (a enum (' ','a','b ') not null default 'b ');
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` enum('','a','b') NOT NULL default 'b'
-) TYPE=MyISAM
+) TYPE=MyISAM CHARSET=latin1
 drop table t1;
diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result
index e85bced353a..c6eca3abe8b 100644
--- a/mysql-test/r/type_float.result
+++ b/mysql-test/r/type_float.result
@@ -10,9 +10,9 @@ SELECT 1e1,1.e1,1.0e1,1e+1,1.e+1,1.0e+1,1e-1,1.e-1,1.0e-1;
 drop table if exists t1;
 create table t1 (f1 float(24),f2 float(52));
 show full columns from t1;
-Field	Type	Null	Key	Default	Extra	Privileges
-f1	float	YES		NULL		select,insert,update,references
-f2	double	YES		NULL		select,insert,update,references
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+f1	float	YES		NULL		select,insert,update,references	
+f2	double	YES		NULL		select,insert,update,references	
 insert into t1 values(10,10),(1e+5,1e+5),(1234567890,1234567890),(1e+10,1e+10),(1e+15,1e+15),(1e+20,1e+20),(1e+50,1e+50),(1e+150,1e+150);
 insert into t1 values(-10,-10),(1e-5,1e-5),(1e-10,1e-10),(1e-15,1e-15),(1e-20,1e-20),(1e-50,1e-50),(1e-150,1e-150);
 select * from t1;
@@ -69,19 +69,19 @@ min(a)
 drop table t1;
 create table t1 (f float, f2 float(24), f3 float(6,2), d double, d2 float(53), d3 double(10,3), de decimal, de2 decimal(6), de3 decimal(5,2), n numeric, n2 numeric(8), n3 numeric(5,6));
 show full columns from t1;
-Field	Type	Null	Key	Default	Extra	Privileges
-f	float	YES		NULL		select,insert,update,references
-f2	float	YES		NULL		select,insert,update,references
-f3	float(6,2)	YES		NULL		select,insert,update,references
-d	double	YES		NULL		select,insert,update,references
-d2	double	YES		NULL		select,insert,update,references
-d3	double(10,3)	YES		NULL		select,insert,update,references
-de	decimal(10,0)	YES		NULL		select,insert,update,references
-de2	decimal(6,0)	YES		NULL		select,insert,update,references
-de3	decimal(5,2)	YES		NULL		select,insert,update,references
-n	decimal(10,0)	YES		NULL		select,insert,update,references
-n2	decimal(8,0)	YES		NULL		select,insert,update,references
-n3	decimal(7,6)	YES		NULL		select,insert,update,references
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+f	float	YES		NULL		select,insert,update,references	
+f2	float	YES		NULL		select,insert,update,references	
+f3	float(6,2)	YES		NULL		select,insert,update,references	
+d	double	YES		NULL		select,insert,update,references	
+d2	double	YES		NULL		select,insert,update,references	
+d3	double(10,3)	YES		NULL		select,insert,update,references	
+de	decimal(10,0)	YES		NULL		select,insert,update,references	
+de2	decimal(6,0)	YES		NULL		select,insert,update,references	
+de3	decimal(5,2)	YES		NULL		select,insert,update,references	
+n	decimal(10,0)	YES		NULL		select,insert,update,references	
+n2	decimal(8,0)	YES		NULL		select,insert,update,references	
+n3	decimal(7,6)	YES		NULL		select,insert,update,references	
 drop table t1;
 create table t1 (a  decimal(7,3) not null, key (a));
 insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1");
diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
index fc7cc5255cf..01149b68935 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -38,31 +38,31 @@ KEY (ulonglong,ulong),
 KEY (options,flags)
 );
 show full fields from t1;
-Field	Type	Null	Key	Default	Extra	Privileges
-auto	int(5) unsigned		PRI	NULL	auto_increment	select,insert,update,references
-string	varchar(10)	YES		hello		select,insert,update,references
-tiny	tinyint(4)		MUL	0		select,insert,update,references
-short	smallint(6)		MUL	1		select,insert,update,references
-medium	mediumint(8)		MUL	0		select,insert,update,references
-long_int	int(11)			0		select,insert,update,references
-longlong	bigint(13)		MUL	0		select,insert,update,references
-real_float	float(13,1)		MUL	0.0		select,insert,update,references
-real_double	double(16,4)	YES		NULL		select,insert,update,references
-utiny	tinyint(3) unsigned		MUL	0		select,insert,update,references
-ushort	smallint(5) unsigned zerofill		MUL	00000		select,insert,update,references
-umedium	mediumint(8) unsigned		MUL	0		select,insert,update,references
-ulong	int(11) unsigned		MUL	0		select,insert,update,references
-ulonglong	bigint(13) unsigned		MUL	0		select,insert,update,references
-time_stamp	timestamp(14)	YES		NULL		select,insert,update,references
-date_field	date	YES		NULL		select,insert,update,references
-time_field	time	YES		NULL		select,insert,update,references
-date_time	datetime	YES		NULL		select,insert,update,references
-blob_col	blob	YES		NULL		select,insert,update,references
-tinyblob_col	tinyblob	YES		NULL		select,insert,update,references
-mediumblob_col	mediumblob					select,insert,update,references
-longblob_col	longblob					select,insert,update,references
-options	enum('one','two','tree')		MUL	one		select,insert,update,references
-flags	set('one','two','tree')					select,insert,update,references
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+auto	int(5) unsigned		PRI	NULL	auto_increment	select,insert,update,references	
+string	varchar(10) character set latin1	YES		hello		select,insert,update,references	
+tiny	tinyint(4)		MUL	0		select,insert,update,references	
+short	smallint(6)		MUL	1		select,insert,update,references	
+medium	mediumint(8)		MUL	0		select,insert,update,references	
+long_int	int(11)			0		select,insert,update,references	
+longlong	bigint(13)		MUL	0		select,insert,update,references	
+real_float	float(13,1)		MUL	0.0		select,insert,update,references	
+real_double	double(16,4)	YES		NULL		select,insert,update,references	
+utiny	tinyint(3) unsigned		MUL	0		select,insert,update,references	
+ushort	smallint(5) unsigned zerofill		MUL	00000		select,insert,update,references	
+umedium	mediumint(8) unsigned		MUL	0		select,insert,update,references	
+ulong	int(11) unsigned		MUL	0		select,insert,update,references	
+ulonglong	bigint(13) unsigned		MUL	0		select,insert,update,references	
+time_stamp	timestamp(14)	YES		NULL		select,insert,update,references	
+date_field	date	YES		NULL		select,insert,update,references	
+time_field	time	YES		NULL		select,insert,update,references	
+date_time	datetime	YES		NULL		select,insert,update,references	
+blob_col	blob	YES		NULL		select,insert,update,references	
+tinyblob_col	tinyblob	YES		NULL		select,insert,update,references	
+mediumblob_col	mediumblob					select,insert,update,references	
+longblob_col	longblob					select,insert,update,references	
+options	enum('one','two','tree')		MUL	one		select,insert,update,references	
+flags	set('one','two','tree')					select,insert,update,references	
 show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
 t1	0	PRIMARY	1	auto	A	0	NULL	NULL		BTREE	
@@ -168,57 +168,57 @@ drop table t2;
 create table t2 select * from t1;
 update t2 set string="changed" where auto=16;
 show full columns from t1;
-Field	Type	Null	Key	Default	Extra	Privileges
-auto	int(5) unsigned		MUL	NULL	auto_increment	select,insert,update,references
-string	varchar(10)	YES		new defaul		select,insert,update,references
-tiny	tinyint(4)		MUL	0		select,insert,update,references
-short	smallint(6)		MUL	0		select,insert,update,references
-medium	mediumint(8)		MUL	0		select,insert,update,references
-long_int	int(11)			0		select,insert,update,references
-longlong	bigint(13)		MUL	0		select,insert,update,references
-real_float	float(13,1)		MUL	0.0		select,insert,update,references
-real_double	double(16,4)	YES		NULL		select,insert,update,references
-utiny	tinyint(3) unsigned			0		select,insert,update,references
-ushort	smallint(5) unsigned zerofill			00000		select,insert,update,references
-umedium	mediumint(8) unsigned		MUL	0		select,insert,update,references
-ulong	int(11) unsigned		MUL	0		select,insert,update,references
-ulonglong	bigint(13) unsigned		MUL	0		select,insert,update,references
-time_stamp	timestamp(14)	YES		NULL		select,insert,update,references
-date_field	varchar(10)	YES		NULL		select,insert,update,references
-time_field	time	YES		NULL		select,insert,update,references
-date_time	datetime	YES		NULL		select,insert,update,references
-new_blob_col	varchar(20)	YES		NULL		select,insert,update,references
-tinyblob_col	tinyblob	YES		NULL		select,insert,update,references
-mediumblob_col	mediumblob					select,insert,update,references
-options	enum('one','two','tree')		MUL	one		select,insert,update,references
-flags	set('one','two','tree')					select,insert,update,references
-new_field	varchar(10)			new		select,insert,update,references
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+auto	int(5) unsigned		MUL	NULL	auto_increment	select,insert,update,references	
+string	varchar(10) character set latin1	YES		new defaul		select,insert,update,references	
+tiny	tinyint(4)		MUL	0		select,insert,update,references	
+short	smallint(6)		MUL	0		select,insert,update,references	
+medium	mediumint(8)		MUL	0		select,insert,update,references	
+long_int	int(11)			0		select,insert,update,references	
+longlong	bigint(13)		MUL	0		select,insert,update,references	
+real_float	float(13,1)		MUL	0.0		select,insert,update,references	
+real_double	double(16,4)	YES		NULL		select,insert,update,references	
+utiny	tinyint(3) unsigned			0		select,insert,update,references	
+ushort	smallint(5) unsigned zerofill			00000		select,insert,update,references	
+umedium	mediumint(8) unsigned		MUL	0		select,insert,update,references	
+ulong	int(11) unsigned		MUL	0		select,insert,update,references	
+ulonglong	bigint(13) unsigned		MUL	0		select,insert,update,references	
+time_stamp	timestamp(14)	YES		NULL		select,insert,update,references	
+date_field	varchar(10) character set latin1	YES		NULL		select,insert,update,references	
+time_field	time	YES		NULL		select,insert,update,references	
+date_time	datetime	YES		NULL		select,insert,update,references	
+new_blob_col	varchar(20) character set latin1	YES		NULL		select,insert,update,references	
+tinyblob_col	tinyblob	YES		NULL		select,insert,update,references	
+mediumblob_col	mediumblob					select,insert,update,references	
+options	enum('one','two','tree')		MUL	one		select,insert,update,references	
+flags	set('one','two','tree')					select,insert,update,references	
+new_field	varchar(10) character set latin1			new		select,insert,update,references	
 show full columns from t2;
-Field	Type	Null	Key	Default	Extra	Privileges
-auto	int(5) unsigned			0		select,insert,update,references
-string	varchar(10)	YES		new defaul		select,insert,update,references
-tiny	tinyint(4)			0		select,insert,update,references
-short	smallint(6)			0		select,insert,update,references
-medium	mediumint(8)			0		select,insert,update,references
-long_int	int(11)			0		select,insert,update,references
-longlong	bigint(13)			0		select,insert,update,references
-real_float	float(13,1)			0.0		select,insert,update,references
-real_double	double(16,4)	YES		NULL		select,insert,update,references
-utiny	tinyint(3) unsigned			0		select,insert,update,references
-ushort	smallint(5) unsigned zerofill			00000		select,insert,update,references
-umedium	mediumint(8) unsigned			0		select,insert,update,references
-ulong	int(11) unsigned			0		select,insert,update,references
-ulonglong	bigint(13) unsigned			0		select,insert,update,references
-time_stamp	timestamp(14)	YES		NULL		select,insert,update,references
-date_field	varchar(10)	YES		NULL		select,insert,update,references
-time_field	time	YES		NULL		select,insert,update,references
-date_time	datetime	YES		NULL		select,insert,update,references
-new_blob_col	varchar(20)	YES		NULL		select,insert,update,references
-tinyblob_col	tinyblob	YES		NULL		select,insert,update,references
-mediumblob_col	mediumblob					select,insert,update,references
-options	enum('one','two','tree')			one		select,insert,update,references
-flags	set('one','two','tree')					select,insert,update,references
-new_field	varchar(10)			new		select,insert,update,references
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+auto	int(5) unsigned			0		select,insert,update,references	
+string	varchar(10) character set latin1	YES		new defaul		select,insert,update,references	
+tiny	tinyint(4)			0		select,insert,update,references	
+short	smallint(6)			0		select,insert,update,references	
+medium	mediumint(8)			0		select,insert,update,references	
+long_int	int(11)			0		select,insert,update,references	
+longlong	bigint(13)			0		select,insert,update,references	
+real_float	float(13,1)			0.0		select,insert,update,references	
+real_double	double(16,4)	YES		NULL		select,insert,update,references	
+utiny	tinyint(3) unsigned			0		select,insert,update,references	
+ushort	smallint(5) unsigned zerofill			00000		select,insert,update,references	
+umedium	mediumint(8) unsigned			0		select,insert,update,references	
+ulong	int(11) unsigned			0		select,insert,update,references	
+ulonglong	bigint(13) unsigned			0		select,insert,update,references	
+time_stamp	timestamp(14)	YES		NULL		select,insert,update,references	
+date_field	varchar(10) character set latin1	YES		NULL		select,insert,update,references	
+time_field	time	YES		NULL		select,insert,update,references	
+date_time	datetime	YES		NULL		select,insert,update,references	
+new_blob_col	varchar(20) character set latin1	YES		NULL		select,insert,update,references	
+tinyblob_col	tinyblob	YES		NULL		select,insert,update,references	
+mediumblob_col	mediumblob					select,insert,update,references	
+options	enum('one','two','tree')			one		select,insert,update,references	
+flags	set('one','two','tree')					select,insert,update,references	
+new_field	varchar(10) character set latin1			new		select,insert,update,references	
 select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.time_field<>t2.time_field and (t1.time_field is not null or t2.time_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.new_blob_col<>t2.new_blob_col and (t1.new_blob_col is not null or t2.new_blob_col is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)) or (t1.new_field<>t2.new_field and (t1.new_field is not null or t2.new_field is not null)));
 auto	auto
 16	16
@@ -228,12 +228,12 @@ auto	auto
 drop table t2;
 create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, "a" as t2, repeat("a",256) as t3, binary repeat("b",256) as t4 from t1;
 show full columns from t2;
-Field	Type	Null	Key	Default	Extra	Privileges
-auto	bigint(17) unsigned		PRI	0		select,insert,update,references
-t1	bigint(1)			0		select,insert,update,references
-t2	char(1)					select,insert,update,references
-t3	mediumtext					select,insert,update,references
-t4	mediumblob					select,insert,update,references
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+auto	bigint(17) unsigned		PRI	0		select,insert,update,references	
+t1	bigint(1)			0		select,insert,update,references	
+t2	char(1) character set latin1					select,insert,update,references	
+t3	mediumtext character set latin1					select,insert,update,references	
+t4	mediumblob					select,insert,update,references	
 select * from t2;
 auto	t1	t2	t3	t4
 11	1	a	aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
@@ -251,10 +251,10 @@ create table t3 select * from t1, t2;
 Duplicate column name 'c'
 create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1, t2;
 show full columns from t3;
-Field	Type	Null	Key	Default	Extra	Privileges
-c1	int(11)	YES		NULL		select,insert,update,references
-c2	int(11)	YES		NULL		select,insert,update,references
-const	bigint(1)			0		select,insert,update,references
+Field	Type	Null	Key	Default	Extra	Privileges	Comment
+c1	int(11)	YES		NULL		select,insert,update,references	
+c2	int(11)	YES		NULL		select,insert,update,references	
+const	bigint(1)			0		select,insert,update,references	
 drop table t1,t2,t3;
 create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield));
 drop table t1;
diff --git a/mysql-test/r/type_set.result b/mysql-test/r/type_set.result
index 59f8744e2ec..b0ea1b69e59 100644
--- a/mysql-test/r/type_set.result
+++ b/mysql-test/r/type_set.result
@@ -4,12 +4,12 @@ show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` set('','a','b') NOT NULL default ''
-) TYPE=MyISAM
+) TYPE=MyISAM CHARSET=latin1
 drop table t1;
 create table t1 (a set (' ','a','b ') not null default 'b ');
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` set('','a','b') NOT NULL default 'b'
-) TYPE=MyISAM
+) TYPE=MyISAM CHARSET=latin1
 drop table t1;
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 896901dd8af..ec30db36cd4 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -88,7 +88,6 @@ explain (select a,b from t1 limit 2)  union all (select a,b from t2 order by a l
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ALL	NULL	NULL	NULL	NULL	4	
 t2	ALL	NULL	NULL	NULL	NULL	4	Using filesort
-t1	ALL	NULL	NULL	NULL	NULL	4	
 (select sql_calc_found_rows  a,b from t1 limit 2)  union all (select a,b from t2 order by a) limit 2;
 a	b
 1	a
diff --git a/mysql-test/t/README b/mysql-test/t/README
deleted file mode 100644
index e69de29bb2d..00000000000
--- a/mysql-test/t/README
+++ /dev/null
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index bb0d6dc0d64..47ee55620e4 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -83,6 +83,22 @@ describe t2;
 drop table t1,t2;
 
 #
+# Test of CREATE ... SELECT with duplicate fields
+#
+
+create table t1 (a tinyint);
+create table t2 (a int) select * from t1;                        
+describe t1;
+describe t2;
+drop table if exists t2;
+!$1060 create table t2 (a int, a float) select * from t1;               
+drop table if exists t2;
+!$1060 create table t2 (a int) select a as b, a+1 as b from t1;         
+drop table if exists t2;
+!$1060 create table t2 (b int) select a as b, a+1 as b from t1;         
+drop table if exists t1,t2;
+
+#
 # Test of primary key with 32 index
 #
 
diff --git a/mysql-test/t/ctype_many.test b/mysql-test/t/ctype_many.test
new file mode 100644
index 00000000000..1e89b1419d2
--- /dev/null
+++ b/mysql-test/t/ctype_many.test
@@ -0,0 +1,191 @@
+DROP TABLE IF EXISTS t1;
+
+CREATE TABLE t1 (
+  comment CHAR(32) CHARACTER SET latin1 NOT NULL,
+  koi8_ru_f CHAR(32) CHARACTER SET koi8_ru NOT NULL
+) CHARSET=latin5;
+
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 CHANGE comment comment CHAR(32) CHARACTER SET latin2 NOT NULL;
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 ADD latin5_f CHAR(32) NOT NULL;
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 CHARSET=latin2;
+ALTER TABLE t1 ADD latin2_f CHAR(32) NOT NULL;
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 DROP latin2_f, DROP latin5_f;
+SHOW CREATE TABLE t1;
+
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('a','LAT SMALL A');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('b','LAT SMALL B');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('c','LAT SMALL C');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('d','LAT SMALL D');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('e','LAT SMALL E');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('f','LAT SMALL F');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('g','LAT SMALL G');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('h','LAT SMALL H');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('i','LAT SMALL I');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('j','LAT SMALL J');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('k','LAT SMALL K');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('l','LAT SMALL L');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('m','LAT SMALL M');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('n','LAT SMALL N');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('o','LAT SMALL O');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('p','LAT SMALL P');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('q','LAT SMALL Q');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('r','LAT SMALL R');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('s','LAT SMALL S');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('t','LAT SMALL T');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('u','LAT SMALL U');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('v','LAT SMALL V');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('w','LAT SMALL W');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('x','LAT SMALL X');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('y','LAT SMALL Y');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('z','LAT SMALL Z');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('A','LAT CAPIT A');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('B','LAT CAPIT B');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('C','LAT CAPIT C');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('D','LAT CAPIT D');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('E','LAT CAPIT E');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('F','LAT CAPIT F');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('G','LAT CAPIT G');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('H','LAT CAPIT H');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('I','LAT CAPIT I');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('J','LAT CAPIT J');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('K','LAT CAPIT K');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('L','LAT CAPIT L');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('M','LAT CAPIT M');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('N','LAT CAPIT N');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('O','LAT CAPIT O');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('P','LAT CAPIT P');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Q','LAT CAPIT Q');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('R','LAT CAPIT R');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('S','LAT CAPIT S');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('T','LAT CAPIT T');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('U','LAT CAPIT U');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('V','LAT CAPIT V');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('W','LAT CAPIT W');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('X','LAT CAPIT X');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Y','LAT CAPIT Y');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Z','LAT CAPIT Z');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Á','CYR SMALL A');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Â','CYR SMALL BE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('×','CYR SMALL VE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ç','CYR SMALL GE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ä','CYR SMALL DE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Å','CYR SMALL IE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('£','CYR SMALL IO');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ö','CYR SMALL ZHE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ú','CYR SMALL ZE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('É','CYR SMALL I');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ë','CYR SMALL KA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ì','CYR SMALL EL');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Í','CYR SMALL EM');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Î','CYR SMALL EN');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ï','CYR SMALL O');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ð','CYR SMALL PE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ò','CYR SMALL ER');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ó','CYR SMALL ES');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ô','CYR SMALL TE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Õ','CYR SMALL U');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Æ','CYR SMALL EF');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('È','CYR SMALL HA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ã','CYR SMALL TSE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Þ','CYR SMALL CHE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Û','CYR SMALL SHA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ý','CYR SMALL SCHA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ß','CYR SMALL HARD SIGN');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ù','CYR SMALL YERU');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ø','CYR SMALL SOFT SIGN');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ü','CYR SMALL E');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('À','CYR SMALL YU');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ñ','CYR SMALL YA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('á','CYR CAPIT A');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('â','CYR CAPIT BE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('÷','CYR CAPIT VE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ç','CYR CAPIT GE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ä','CYR CAPIT DE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('å','CYR CAPIT IE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('³','CYR CAPIT IO');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ö','CYR CAPIT ZHE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ú','CYR CAPIT ZE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('é','CYR CAPIT I');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ë','CYR CAPIT KA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ì','CYR CAPIT EL');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('í','CYR CAPIT EM');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('î','CYR CAPIT EN');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ï','CYR CAPIT O');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ð','CYR CAPIT PE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ò','CYR CAPIT ER');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ó','CYR CAPIT ES');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ô','CYR CAPIT TE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('õ','CYR CAPIT U');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('æ','CYR CAPIT EF');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('è','CYR CAPIT HA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ã','CYR CAPIT TSE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('þ','CYR CAPIT CHE');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('û','CYR CAPIT SHA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ý','CYR CAPIT SCHA');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ÿ','CYR CAPIT HARD SIGN');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ù','CYR CAPIT YERU');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ø','CYR CAPIT SOFT SIGN');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ü','CYR CAPIT E');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('à','CYR CAPIT YU');
+INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ñ','CYR CAPIT YA');
+
+SELECT CONVERT(koi8_ru_f USING utf8),MIN(comment),COUNT(*) FROM t1 GROUP BY 1;
+
+ALTER TABLE t1 ADD utf8_f CHAR(32) CHARACTER SET utf8 NOT NULL;
+UPDATE t1 SET utf8_f=CONVERT(koi8_ru_f USING utf8);
+
+SELECT * FROM t1;
+
+SELECT koi8_ru_f,MIN(comment) FROM t1 GROUP BY 1;
+SELECT utf8_f,MIN(comment) FROM t1 GROUP BY 1;
+SELECT DISTINCT koi8_ru_f FROM t1;
+SELECT DISTINCT utf8_f FROM t1;
+SELECT lower(koi8_ru_f) FROM t1 ORDER BY 1 DESC;
+SELECT lower(utf8_f) FROM t1 ORDER BY 1 DESC;
+
+SELECT t11.comment,t12.comment 
+FROM t1 t11,t1 t12 WHERE CONVERT(t11.koi8_ru_f USING utf8)=t12.utf8_f
+ORDER BY t11.koi8_ru_f,t11.comment,t12.comment;
+
+SELECT t11.comment,t12.comment 
+FROM t1 t11,t1 t12 
+WHERE t11.koi8_ru_f=CONVERT(t12.utf8_f USING koi8_ru)
+ORDER BY t12.utf8_f,t11.comment,t12.comment;
+
+ALTER TABLE t1 ADD ucs2_f CHAR(32) CHARACTER SET ucs2 NOT NULL;
+
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0391,'GREEK CAPIT ALPHA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0392,'GREEK CAPIT BETA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0393,'GREEK CAPIT GAMMA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0394,'GREEK CAPIT DELTA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0395,'GREEK CAPIT EPSILON');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B1,'GREEK SMALL ALPHA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B2,'GREEK SMALL BETA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B3,'GREEK SMALL GAMMA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B4,'GREEK SMALL DELTA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B5,'GREEK SMALL EPSILON');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0531,'ARMENIAN CAPIT AYB');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0532,'ARMENIAN CAPIT BEN');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0533,'ARMENIAN CAPIT GIM');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0534,'ARMENIAN CAPIT DA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0535,'ARMENIAN CAPIT ECH');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0536,'ARMENIAN CAPIT ZA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0561,'ARMENIAN SMALL YAB');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0562,'ARMENIAN SMALL BEN');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0563,'ARMENIAN SMALL GIM');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0564,'ARMENIAN SMALL DA');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0565,'ARMENIAN SMALL ECH');
+INSERT INTO t1 (ucs2_f,comment) VALUES (0x0566,'ARMENIAN SMALL ZA');
+
+ALTER TABLE t1 ADD armscii8_f CHAR(32) CHARACTER SET armscii8 NOT NULL;
+ALTER TABLE t1 ADD greek_f CHAR(32) CHARACTER SET greek NOT NULL;
+UPDATE t1 SET greek_f=CONVERT(ucs2_f USING greek) WHERE comment LIKE 'GRE%';
+UPDATE t1 SET armscii8_f=CONVERT(ucs2_f USING armscii8) WHERE comment LIKE 'ARM%';
+UPDATE t1 SET utf8_f=CONVERT(ucs2_f USING utf8) WHERE utf8_f='';
+UPDATE t1 SET ucs2_f=CONVERT(utf8_f USING ucs2) WHERE ucs2_f='';
+SELECT min(comment),count(*) FROM t1 GROUP BY ucs2_f;
+DROP TABLE t1;
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
new file mode 100644
index 00000000000..76ef5fba351
--- /dev/null
+++ b/mysql-test/t/derived.test
@@ -0,0 +1,11 @@
+drop table if exists t1,t2,t3;
+CREATE TABLE t1 (a int not null, b char (10) not null);
+insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
+CREATE TABLE t2 (a int not null, b char (10) not null);
+insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');
+select t1.a,t3.y from t1,(select a as y from t2  where b='c') as t3  where t1.a = t3.y;
+select t1.a,t3.a from t1,(select * from t2  where b='c') as t3  where t1.a = t3.a;
+CREATE TABLE t3 (a int not null, b char (10) not null);
+insert into t3 values (3,'f'),(4,'y'),(5,'z'),(6,'c');
+select t1.a,t4.y from t1,(select t2.a as y from t2,(select t3.b from t3 where t3.a>3) as t5  where t2.b=t5.b) as t4  where t1.a = t4.y;
+drop table if exists  t1.t2,t3;
diff --git a/mysql-test/t/heap.test b/mysql-test/t/heap.test
index ef7e3239a96..f8b27d09a19 100644
--- a/mysql-test/t/heap.test
+++ b/mysql-test/t/heap.test
@@ -42,7 +42,7 @@ alter table t1 type=myisam;
 explain select * from t1 where a in (869751,736494,226312,802616);
 drop table t1;
 
-create table t1 (x int not null, y int not null, key x(x), unique y(y))
+create table t1 (x int not null, y int not null, key x (x), unique y (y))
 type=heap;
 insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
 select * from t1 where x=1;
diff --git a/mysql-test/t/heap_btree.test b/mysql-test/t/heap_btree.test
new file mode 100644
index 00000000000..e4b7d8674b9
--- /dev/null
+++ b/mysql-test/t/heap_btree.test
@@ -0,0 +1,147 @@
+#
+# Test of heap tables.
+#
+
+drop table if exists t1;
+create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100;
+insert into t1 values(1,1),(2,2),(3,3),(4,4);
+delete from t1 where a=1 or a=0;
+#show table status like "t1";
+show keys from t1;
+select * from t1;
+select * from t1 where a=4;
+update t1 set b=5 where a=4;
+update t1 set b=b+1 where a>=3;
+replace t1 values (3,3);
+select * from t1;
+alter table t1 add c int not null, add key using BTREE (c,a);
+drop table t1;
+
+create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps";
+insert into t1 values(1,1),(2,2),(3,3),(4,4);
+delete from t1 where a > 0;
+select * from t1;
+drop table t1;
+
+create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps";
+insert into t1 values(1,1),(2,2),(3,3),(4,4);
+alter table t1 modify a int not null auto_increment, type=myisam, comment="new myisam table";
+#show table status like "t1";
+select * from t1;
+drop table t1;
+
+create table t1 (a int not null) type=heap;
+insert into t1 values (869751),(736494),(226312),(802616);
+select * from t1 where a > 736494;
+alter table t1 add unique uniq_id using BTREE (a);
+select * from t1 where a > 736494;
+select * from t1 where a = 736494;
+select * from t1 where a=869751 or a=736494;
+select * from t1 where a in (869751,736494,226312,802616);
+alter table t1 type=myisam;
+explain select * from t1 where a in (869751,736494,226312,802616);
+drop table t1;
+
+create table t1 (x int not null, y int not null, key x  using BTREE (x), unique y  using BTREE (y))
+type=heap;
+insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
+select * from t1 where x=1;
+select * from t1,t1 as t2 where t1.x=t2.y;
+explain select * from t1,t1 as t2 where t1.x=t2.y;
+drop table t1;
+
+create table t1 (a int) type=heap;
+insert into t1 values(1);
+select max(a) from t1;
+drop table t1;
+
+CREATE TABLE t1 ( a int not null default 0, b int not null default 0,  key  using BTREE (a,b),  key  using BTREE (b)  ) TYPE=HEAP;
+insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
+select * from t1 where a=1; 
+insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
+select * from t1 where a=1;
+--replace_result 0 x 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x 11 x 12 x 13 x 14 x
+explain select * from t1 where a=1 order by a,b;
+--replace_result 0 x 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x 11 x 12 x 13 x 14 x
+explain select * from t1 where a=1 order by b;
+select * from t1 where b=1;
+--replace_result 0 x 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x 11 x 12 x 13 x 14 x
+explain select * from t1 where b=1;
+drop table t1;
+
+create table t1 (id int unsigned not null, primary key  using BTREE (id)) type=HEAP;
+insert into t1 values(1);
+select max(id) from t1; 
+insert into t1 values(2);
+select max(id) from t1; 
+replace into t1 values(1);
+drop table t1;
+
+create table t1 (n int) type=heap;
+drop table t1;
+
+create table t1 (n int) type=heap;
+drop table if exists t1;
+
+# Test of non unique index
+
+CREATE table t1(f1 int not null,f2 char(20) not 
+null,index(f2)) type=heap;
+INSERT into t1 set f1=12,f2="bill";
+INSERT into t1 set f1=13,f2="bill";
+INSERT into t1 set f1=14,f2="bill";
+INSERT into t1 set f1=15,f2="bill";
+INSERT into t1 set f1=16,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+delete from t1 where f2="bill";
+select * from t1;
+drop table t1;
+
+#
+# Test when using part key searches
+#
+
+create table t1 (btn char(10) not null, key using BTREE (btn)) type=heap;
+insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
+explain select * from t1 where btn like "q%";
+select * from t1 where btn like "q%";
+alter table t1 add column new_col char(1) not null, add key using BTREE (btn,new_col), drop key btn;
+update t1 set new_col=btn;
+explain select * from t1 where btn="a";
+explain select * from t1 where btn="a" and new_col="a";
+drop table t1;
+
+#
+# Test of NULL keys
+#
+
+CREATE TABLE t1 (
+  a int default NULL,
+  b int default NULL,
+  KEY a using BTREE (a),
+  UNIQUE b using BTREE (b)
+) type=heap;
+INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);
+SELECT * FROM t1 WHERE a=NULL;
+explain SELECT * FROM t1 WHERE a IS NULL;
+SELECT * FROM t1 WHERE a<=>NULL;
+SELECT * FROM t1 WHERE b=NULL;
+explain SELECT * FROM t1 WHERE b IS NULL;
+SELECT * FROM t1 WHERE b<=>NULL;
+
+--error 1062
+INSERT INTO t1 VALUES (1,3);
+DROP TABLE t1;
+
+#
+# Test when deleting all rows
+#
+
+CREATE TABLE t1 (a int not null, primary key using BTREE (a)) type=heap;
+INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
+DELETE from t1 where a < 100;
+SELECT * from t1;
+DROP TABLE t1;
diff --git a/mysql-test/t/heap_hash.test b/mysql-test/t/heap_hash.test
new file mode 100644
index 00000000000..5dbd2b4a928
--- /dev/null
+++ b/mysql-test/t/heap_hash.test
@@ -0,0 +1,140 @@
+#
+# Test of heap tables.
+#
+
+drop table if exists t1;
+create table t1 (a int not null,b int not null, primary key using HASH (a)) type=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100;
+insert into t1 values(1,1),(2,2),(3,3),(4,4);
+delete from t1 where a=1 or a=0;
+#show table status like "t1";
+show keys from t1;
+select * from t1;
+select * from t1 where a=4;
+update t1 set b=5 where a=4;
+update t1 set b=b+1 where a>=3;
+replace t1 values (3,3);
+select * from t1;
+alter table t1 add c int not null, add key using HASH (c,a);
+drop table t1;
+
+create table t1 (a int not null,b int not null, primary key using HASH (a)) type=heap comment="testing heaps";
+insert into t1 values(1,1),(2,2),(3,3),(4,4);
+delete from t1 where a > 0;
+select * from t1;
+drop table t1;
+
+create table t1 (a int not null,b int not null, primary key using HASH (a)) type=heap comment="testing heaps";
+insert into t1 values(1,1),(2,2),(3,3),(4,4);
+alter table t1 modify a int not null auto_increment, type=myisam, comment="new myisam table";
+#show table status like "t1";
+select * from t1;
+drop table t1;
+
+create table t1 (a int not null) type=heap;
+insert into t1 values (869751),(736494),(226312),(802616);
+select * from t1 where a > 736494;
+alter table t1 add unique uniq_id using HASH (a);
+select * from t1 where a > 736494;
+select * from t1 where a = 736494;
+select * from t1 where a=869751 or a=736494;
+select * from t1 where a in (869751,736494,226312,802616);
+alter table t1 type=myisam;
+explain select * from t1 where a in (869751,736494,226312,802616);
+drop table t1;
+
+create table t1 (x int not null, y int not null, key x  using HASH (x), unique y  using HASH (y))
+type=heap;
+insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
+select * from t1 where x=1;
+select * from t1,t1 as t2 where t1.x=t2.y;
+explain select * from t1,t1 as t2 where t1.x=t2.y;
+drop table t1;
+
+create table t1 (a int) type=heap;
+insert into t1 values(1);
+select max(a) from t1;
+drop table t1;
+
+CREATE TABLE t1 ( a int not null default 0, b int not null default 0,  key  using HASH (a),  key  using HASH (b)  ) TYPE=HEAP;
+insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
+select * from t1 where a=1; 
+insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
+select * from t1 where a=1;
+drop table t1;
+
+create table t1 (id int unsigned not null, primary key  using HASH (id)) type=HEAP;
+insert into t1 values(1);
+select max(id) from t1; 
+insert into t1 values(2);
+select max(id) from t1; 
+replace into t1 values(1);
+drop table t1;
+
+create table t1 (n int) type=heap;
+drop table t1;
+
+create table t1 (n int) type=heap;
+drop table if exists t1;
+
+# Test of non unique index
+
+CREATE table t1(f1 int not null,f2 char(20) not 
+null,index(f2)) type=heap;
+INSERT into t1 set f1=12,f2="bill";
+INSERT into t1 set f1=13,f2="bill";
+INSERT into t1 set f1=14,f2="bill";
+INSERT into t1 set f1=15,f2="bill";
+INSERT into t1 set f1=16,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+INSERT into t1 set f1=12,f2="ted";
+delete from t1 where f2="bill";
+select * from t1;
+drop table t1;
+
+#
+# Test when using part key searches
+#
+
+create table t1 (btn char(10) not null, key using HASH (btn)) type=heap;
+insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
+explain select * from t1 where btn like "q%";
+select * from t1 where btn like "q%";
+alter table t1 add column new_col char(1) not null, add key using HASH (btn,new_col), drop key btn;
+update t1 set new_col=btn;
+explain select * from t1 where btn="a";
+explain select * from t1 where btn="a" and new_col="a";
+drop table t1;
+
+#
+# Test of NULL keys
+#
+
+CREATE TABLE t1 (
+  a int default NULL,
+  b int default NULL,
+  KEY a using HASH (a),
+  UNIQUE b using HASH (b)
+) type=heap;
+INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);
+SELECT * FROM t1 WHERE a=NULL;
+explain SELECT * FROM t1 WHERE a IS NULL;
+SELECT * FROM t1 WHERE a<=>NULL;
+SELECT * FROM t1 WHERE b=NULL;
+explain SELECT * FROM t1 WHERE b IS NULL;
+SELECT * FROM t1 WHERE b<=>NULL;
+
+--error 1062
+INSERT INTO t1 VALUES (1,3);
+DROP TABLE t1;
+
+#
+# Test when deleting all rows
+#
+
+CREATE TABLE t1 (a int not null, primary key using HASH (a)) type=heap;
+INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
+DELETE from t1 where a < 100;
+SELECT * from t1;
+DROP TABLE t1;
diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test
index cba7b522650..c69b87f325a 100644
--- a/mysql-test/t/myisam.test
+++ b/mysql-test/t/myisam.test
@@ -5,7 +5,7 @@
 drop table if exists t1;
 CREATE TABLE t1 (
   STRING_DATA char(255) default NULL,
-  KEY STRING_DATA (STRING_DATA)
+  KEY string_data (STRING_DATA)
 ) TYPE=MyISAM;
 
 INSERT INTO t1 VALUES ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
diff --git a/mysql-test/t/rollback.test b/mysql-test/t/rollback.test
index 6ea2cca887f..bd37e69ae23 100644
--- a/mysql-test/t/rollback.test
+++ b/mysql-test/t/rollback.test
@@ -8,7 +8,12 @@ create table t1 (n int not null primary key) type=myisam;
 begin work;
 insert into t1 values (4);
 insert into t1 values (5);
-# Should give an error
-!$1196 rollback;
+rollback;
+select @@warning_count;
+select @@error_count;
+show warnings;
+show errors;
 select * from t1;
+select @@warning_count;
+show warnings;
 drop table t1;
diff --git a/mysql-test/t/rpl_log_pos.test b/mysql-test/t/rpl_log_pos.test
index f585fa233c5..b96d32c7fca 100644
--- a/mysql-test/t/rpl_log_pos.test
+++ b/mysql-test/t/rpl_log_pos.test
@@ -23,6 +23,7 @@ change master to master_log_pos=173;
 --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT
 slave start;
 sleep 2;
+--replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT
 show slave status;
 connection master;
 show master status;
diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test
index 5e10ebf23a3..eb15eac8bad 100644
--- a/mysql-test/t/show_check.test
+++ b/mysql-test/t/show_check.test
@@ -49,9 +49,11 @@ drop table t2;
 
 create table t1 (
   test_set set( 'val1', 'val2', 'val3' ) not null default '',
-  name char(20) default 'O''Brien'
+  name char(20) default 'O''Brien' comment 'O''Brien as default',
+  c int not null comment 'int column'
   ) comment = 'it\'s a table' ;
 show create table t1 ;
+show full columns from t1;
 drop table t1;
 
 create table t1 (a int not null, unique aa (a));
@@ -78,6 +80,7 @@ drop table t1;
 
 create table t1 (a decimal(9,2), b decimal (9,0), e double(9,2), f double(5,0), h float(3,2), i float(3,0));
 show columns from t1;
+show full columns from t1;
 drop table t1;
 
 # Check auto conversions of types
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
new file mode 100644
index 00000000000..16ba6c9e602
--- /dev/null
+++ b/mysql-test/t/subselect.test
@@ -0,0 +1,43 @@
+
+select (select 2);
+drop table if exists t1,t2,t3,t4,attend,clinic;
+create table t1 (a int);
+create table t2 (a int, b int);
+create table t3 (a int);
+create table t4 (a int, b int);
+insert into t1 values (2);
+insert into t2 values (1,7),(2,7);
+insert into t4 values (4,8),(3,8),(5,9);
+select (select a from t1 where t1.a=t2.a), a from t2;
+select (select a from t1 where t1.a=t2.b), a from t2;
+select (select a from t1), a from t2;
+select (select a from t3), a from t2;
+select * from t2 where t2.a=(select a from t1);
+insert into t3 values (6),(7),(3);
+select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1);
+select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) 
+union (select * from t4 order by a limit 2) limit 3;
+select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) 
+union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
+select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
+select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
+(select * from t2 where a>1) as tt;
+select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
+select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1);
+select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1);
+select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
+select * from t3 where exists (select * from t2 where t2.b=t3.a);
+select * from t3 where not exists (select * from t2 where t2.b=t3.a);
+insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9);
+select b,max(a) as ma from t4 group by b having b < (select max(t2.a)
+from t2 where t2.b=t4.b);
+select b,max(a) as ma from t4 group by b having b >= (select max(t2.a)
+from t2 where t2.b=t4.b);
+
+create table attend (patient_uq int, clinic_uq int, index i1 (clinic_uq));
+create table clinic( uq int primary key, name char(25));
+insert into clinic values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
+insert into attend values (1,1),(1,2),(2,2),(1,3);
+select * from attend where exists (select * from clinic where uq = clinic_uq);
+
+drop table t1,t2,t3,t4,attend,clinic;