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)

12 files changed, 562 insertions, 5 deletions

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;