Merge with 4.0.13

BitKeeper/etc/ignore:
  auto-union
BitKeeper/etc/logging_ok:
  auto-union
BUILD/SETUP.sh:
  Auto merged
BitKeeper/deleted/.del-libmysql.def~29fc6d70335f1c4c:
  Auto merged
Makefile.am:
  Auto merged
acinclude.m4:
  Auto merged
BitKeeper/triggers/post-commit:
  Auto merged
Build-tools/Do-compile:
  Auto merged
VC++Files/libmysql/libmysql.dsp:
  Auto merged
VC++Files/mysql.dsw:
  Auto merged
client/mysql.cc:
  Auto merged
client/mysqlbinlog.cc:
  Auto merged
client/mysqldump.c:
  Auto merged
include/config-win.h:
  Auto merged
include/my_base.h:
  Auto merged
include/my_global.h:
  Auto merged
include/my_pthread.h:
  Auto merged
include/my_sys.h:
  Auto merged
include/violite.h:
  Auto merged
innobase/buf/buf0flu.c:
  Auto merged
innobase/buf/buf0lru.c:
  Auto merged
innobase/include/buf0buf.h:
  Auto merged
innobase/include/buf0lru.h:
  Auto merged
innobase/include/row0mysql.h:
  Auto merged
innobase/include/srv0srv.h:
  Auto merged
innobase/lock/lock0lock.c:
  Auto merged
innobase/log/log0log.c:
  Auto merged
innobase/log/log0recv.c:
  Auto merged
innobase/os/os0file.c:
  Auto merged
innobase/row/row0mysql.c:
  Auto merged
innobase/row/row0sel.c:
  Auto merged
innobase/srv/srv0srv.c:
  Auto merged
innobase/srv/srv0start.c:
  Auto merged
innobase/trx/trx0sys.c:
  Auto merged
innobase/trx/trx0trx.c:
  Auto merged
innobase/ut/ut0ut.c:
  Auto merged
myisam/ft_boolean_search.c:
  Auto merged
myisam/mi_check.c:
  Auto merged
myisam/mi_key.c:
  Auto merged
myisam/mi_open.c:
  Auto merged
myisam/mi_range.c:
  Auto merged
myisam/mi_search.c:
  Auto merged
myisam/sort.c:
  Auto merged
mysql-test/r/delete.result:
  Auto merged
mysql-test/r/fulltext.result:
  Auto merged
mysql-test/r/innodb_handler.result:
  Auto merged
mysql-test/r/join.result:
  Auto merged
mysql-test/r/join_outer.result:
  Auto merged
mysql-test/r/key.result:
  Auto merged
mysql-test/r/multi_update.result:
  Auto merged
mysql-test/r/myisam.result:
  Auto merged
mysql-test/r/query_cache.result:
  Auto merged
mysql-test/r/select.result:
  Auto merged
mysql-test/r/variables.result:
  Auto merged
mysql-test/t/alter_table.test:
  Auto merged
mysql-test/t/ctype_latin1_de.test:
  Auto merged
mysql-test/t/delete.test:
  Auto merged
mysql-test/t/fulltext.test:
  Auto merged
mysql-test/t/innodb_handler.test:
  Auto merged
mysql-test/t/join.test:
  Auto merged
mysql-test/t/join_outer.test:
  Auto merged
mysql-test/t/key.test:
  Auto merged
mysql-test/t/multi_update.test:
  Auto merged
mysql-test/t/myisam.test:
  Auto merged
mysql-test/t/query_cache.test:
  Auto merged
mysql-test/t/repair.test:
  Auto merged
mysql-test/t/select_safe.test:
  Auto merged
mysql-test/t/type_decimal.test:
  Auto merged
mysql-test/t/variables.test:
  Auto merged
mysys/default.c:
  Auto merged
mysys/my_pthread.c:
  Auto merged
scripts/mysql_fix_privilege_tables.sh:
  Auto merged
scripts/mysqld_safe.sh:
  Auto merged
sql/ha_heap.h:
  Auto merged
sql/ha_innodb.h:
  Auto merged
sql/ha_myisam.cc:
  Auto merged
sql/handler.cc:
  Auto merged
sql/handler.h:
  Auto merged
sql/init.cc:
  Auto merged
sql/item.cc:
  Auto merged
sql/item_create.cc:
  Auto merged
sql/item_strfunc.cc:
  Auto merged
sql/item_sum.cc:
  Auto merged
sql/mini_client.cc:
  Auto merged
sql/net_serv.cc:
  Auto merged
sql/opt_range.cc:
  Auto merged
sql/records.cc:
  Auto merged
sql/slave.cc:
  Auto merged
sql/sql_acl.h:
  Auto merged
sql/sql_analyse.cc:
  Auto merged
sql/sql_class.cc:
  Auto merged
sql/sql_class.h:
  Auto merged
sql/sql_list.h:
  Auto merged
sql/sql_load.cc:
  Auto merged
sql/share/czech/errmsg.txt:
  Auto merged
sql/share/danish/errmsg.txt:
  Auto merged
sql/share/dutch/errmsg.txt:
  Auto merged
sql/share/english/errmsg.txt:
  Auto merged
sql/share/estonian/errmsg.txt:
  Auto merged
sql/share/french/errmsg.txt:
  Auto merged
sql/share/greek/errmsg.txt:
  Auto merged
sql/share/hungarian/errmsg.txt:
  Auto merged
sql/share/italian/errmsg.txt:
  Auto merged
sql/share/japanese/errmsg.txt:
  Auto merged
sql/share/korean/errmsg.txt:
  Auto merged
sql/share/norwegian-ny/errmsg.txt:
  Auto merged
sql/share/norwegian/errmsg.txt:
  Auto merged
sql/share/portuguese/errmsg.txt:
  Auto merged
sql/share/romanian/errmsg.txt:
  Auto merged
sql/share/russian/errmsg.txt:
  Auto merged
sql/share/slovak/errmsg.txt:
  Auto merged
sql/share/spanish/errmsg.txt:
  Auto merged
sql/share/swedish/errmsg.txt:
  Auto merged
sql/share/ukrainian/errmsg.txt:
  Auto merged
sql/unireg.h:
  Auto merged
sql-bench/crash-me.sh:
  Auto merged
sql-bench/test-transactions.sh:
  Auto merged
strings/ctype-tis620.c:
  Auto merged
tests/grant.res:
  Auto merged
sql/log_event.cc:
  Merge with 4.0.13
  Cleaned up comment syntax

32 files changed, 554 insertions, 71 deletions

diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index 64b657f1146..fc22bc85aee 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -88,6 +88,7 @@ create table t1 (n1 int not null, n2 int, n3 int, n4 float,
                 key (n3, n4, n1, n2),
                 key (n4, n1, n2, n3) );
 alter table t1 disable keys;
+show keys from t1;
 #let $1=10000;
 let $1=10;
 while ($1)
@@ -96,6 +97,7 @@ while ($1)
  dec $1;
 }
 alter table t1 enable keys;
+show keys from t1;
 drop table t1;
 
 #
@@ -117,3 +119,19 @@ alter table t1 rename t2;
 alter table t2 rename t1, add c char(10) comment "no comment";
 show columns from t1;
 drop table t1;
+
+# implicit analyze
+
+create table t1 (a int, b int);
+let $1=100;
+while ($1)
+{
+ eval insert into t1 values(1,$1), (2,$1), (3, $1);
+ dec $1;
+}
+alter table t1 add unique (a,b), add key (b);
+show keys from t1;
+analyze table t1;
+show keys from t1;
+drop table t1;
+
diff --git a/mysql-test/t/ansi-master.opt b/mysql-test/t/ansi-master.opt
new file mode 100644
index 00000000000..6bf7a4f30e2
--- /dev/null
+++ b/mysql-test/t/ansi-master.opt
@@ -0,0 +1 @@
+--ansi
diff --git a/mysql-test/t/ansi.test b/mysql-test/t/ansi.test
new file mode 100644
index 00000000000..e1ac8ffd4f9
--- /dev/null
+++ b/mysql-test/t/ansi.test
@@ -0,0 +1,17 @@
+#
+# Test of ansi mode
+#
+
+drop table if exists t1;
+
+# Test some functions that works different in ansi mode
+
+SELECT 'A' || 'B';
+
+# Test GROUP BY behaviour
+
+CREATE TABLE t1 (id INT, id2 int);
+SELECT id,NULL,1,1.1,'a' FROM t1 GROUP BY id;
+--error 1055
+SELECT id FROM t1 GROUP BY id2;
+drop table t1;
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index cda9307804b..3a02e5d67e8 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -27,11 +27,9 @@ drop table if exists t1,t2;
 !$1167 create table t1 (b char(0) not null, index(b));
 create table t1 (a int not null auto_increment,primary key (a)) type=heap;
 !$1163 create table t1 (a int not null,b text) type=heap;
-!$1171 create table t1 (a int ,primary key(a)) type=heap;
 drop table if exists t1;
 
 !$1075 create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ord,ordid)) type=heap;
-!$1171 create table t1 (ordid int(8), primary key (ordid));
 
 -- error 1044,1
 create table not_existing_database.test (a int);
@@ -124,6 +122,37 @@ drop table t1;
 create table t1 select if('2002'='2002','Y','N');
 select * from t1;
 drop table if exists t1;
+
+#
+# Test default table type
+#
+SET SESSION table_type="heap";
+SELECT @@table_type;
+CREATE TABLE t1 (a int not null);
+show create table t1;
+drop table t1;
+# Test what happens when using a non existing table type
+SET SESSION table_type="gemini";
+SELECT @@table_type;
+CREATE TABLE t1 (a int not null);
+show create table t1;
+SET SESSION table_type=default;
+drop table t1;
+
+
+#
+# ISO requires that primary keys are implicitly NOT NULL
+#
+create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2));
+insert into t1 values ("a", 1), ("b", 2);
+!$1048 insert into t1 values ("c", NULL);
+!$1048 insert into t1 values (NULL, 3);
+!$1048 insert into t1 values (NULL, NULL);
+
+#
+# Test create with foreign keys
+#
+
 create table t1 (a int, key(a));
 create table t2 (b int, foreign key(b) references t1(a), key(b));
 drop table if exists t1,t2;
diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test
index e0591913f68..b63af87601b 100644
--- a/mysql-test/t/ctype_latin1_de.test
+++ b/mysql-test/t/ctype_latin1_de.test
@@ -47,3 +47,26 @@ select * from t1 where a like "test%";
 select * from t1 where a like "te_t"; 
 select * from t1 where match a against ("te*" in boolean mode)+0;
 drop table t1;
+
+#
+# Test bug report #152 (problem with index on latin1_de)
+#
+
+create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word));
+insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae');
+update t1 set word2=word;
+select word, word=0xdf as t from t1 having t > 0;
+select word, word=cast(0xdf AS CHAR) as t from t1 having t > 0;
+select * from t1 where word=0xDF;
+select * from t1 where word=CAST(0xDF as CHAR);
+select * from t1 where word2=0xDF;
+select * from t1 where word2=CAST(0xDF as CHAR);
+select * from t1 where word='ae';
+select * from t1 where word= 0xe4 or word=CAST(0xe4 as CHAR);
+select * from t1 where word between 0xDF and 0xDF;
+select * from t1 where word between CAST(0xDF AS CHAR) and CAST(0xDF AS CHAR);
+select * from t1 where word like 'ae';
+select * from t1 where word like 'AE';
+select * from t1 where word like 0xDF;
+select * from t1 where word like CAST(0xDF as CHAR);
+drop table t1;
diff --git a/mysql-test/t/ctype_ujis-master.opt b/mysql-test/t/ctype_ujis-master.opt
new file mode 100644
index 00000000000..1f4183d5027
--- /dev/null
+++ b/mysql-test/t/ctype_ujis-master.opt
@@ -0,0 +1 @@
+--default-character-set=ujis
diff --git a/mysql-test/t/ctype_ujis.test b/mysql-test/t/ctype_ujis.test
new file mode 100644
index 00000000000..cd1dc965000
--- /dev/null
+++ b/mysql-test/t/ctype_ujis.test
@@ -0,0 +1,13 @@
+#
+# Tests with the ujis character set
+#
+drop table if exists t1;
+
+#
+# Test problem with LEFT()
+#
+
+create table t1 (c text);
+insert into t1 values (0xa4a2),(0xa4a3);
+select hex(left(c,1)) from t1 group by c;
+drop table t1;
diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test
index 91216ff9c3a..f1f751d728b 100644
--- a/mysql-test/t/delete.test
+++ b/mysql-test/t/delete.test
@@ -64,4 +64,10 @@ select * from t1 where misc > 5 and bool is null;
 delete   from t1 where misc > 5 and bool is null;
 select * from t1 where misc > 5 and bool is null;
 
+select count(*) from t1;
+delete from t1 where 1 > 2;
+select count(*) from t1;
+delete from t1 where 3 > 2;
+select count(*) from t1;
+
 drop table t1;
diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test
index 214236268fa..1c579ddcc62 100644
--- a/mysql-test/t/fulltext.test
+++ b/mysql-test/t/fulltext.test
@@ -175,3 +175,16 @@ repair table t1;
 select * from t1 where match (a) against ('aaaa');
 drop table t1;
 
+#
+# bug 283 by jocelyn fournier <joc@presence-pc.com>
+# FULLTEXT index on a TEXT filed converted to a CHAR field doesn't work anymore
+# 
+
+drop table if exists t1;
+create table t1 ( ref_mag text not null, fulltext (ref_mag));
+insert into t1 values ('test');
+select ref_mag from t1 where match ref_mag against ('+test' in boolean mode);
+alter table t1 change ref_mag ref_mag char (255) not null;
+select ref_mag from t1 where match ref_mag against ('+test' in boolean mode);
+drop table t1;
+
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index c78509d3869..aca9530ffc3 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -326,6 +326,26 @@ explain
 select min(a4) from t1  where (a4 + 0.01) between 0.07 and 0.08;
 explain
 select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME';
+drop table t1, t2;
 
-# Clean up
-drop table if exists t1, t2;
\ No newline at end of file
+
+#
+# Test of group function and NULL values
+#
+
+CREATE TABLE t1 (a int, b int);
+select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1;
+select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
+insert into t1 values (1,null);
+select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
+insert into t1 values (1,null);
+insert into t1 values (2,null);
+select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
+select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
+insert into t1 values (2,1);
+select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
+select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
+insert into t1 values (3,1);
+select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
+select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
+drop table t1;
diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test
index d48b17e87af..be64c170fa1 100644
--- a/mysql-test/t/func_misc.test
+++ b/mysql-test/t/func_misc.test
@@ -7,3 +7,8 @@ select format(1.5555,0),format(123.5555,1),format(1234.5555,2),format(12345.5555
 select inet_ntoa(inet_aton("255.255.255.255.255.255.255.255"));
 select inet_aton("255.255.255.255.255"),inet_aton("255.255.1.255"),inet_aton("0.1.255");
 select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511);
+
+#
+# Test for core dump with nan
+#
+select length(format('nan', 2)) > 0;
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 912bed1955c..d99c7a87f88 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -44,12 +44,15 @@ CREATE TABLE t2 (
 INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1');
 INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1');
 INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1');
+INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1');
+INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1');
 
 SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid;
 SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid ORDER BY NULL;
 SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2  GROUP BY t2.userid;
 SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2  GROUP BY t2.userid;
 SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2  GROUP BY t2.userid ORDER BY NULL;
+EXPLAIN SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2  GROUP BY t2.userid ORDER BY NULL;
 drop table t1,t2;
 
 #
@@ -246,7 +249,7 @@ CREATE TABLE t1 (
   key (score)
 );
 
-INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3);
+INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3),(6,3,3),(7,3,3);
 explain select userid,count(*) from t1 group by userid desc;
 explain select userid,count(*) from t1 group by userid desc order by null;
 select userid,count(*) from t1 group by userid desc;
@@ -265,8 +268,6 @@ explain select sql_big_result score,count(*) from t1 group by score desc order b
 select sql_big_result score,count(*) from t1 group by score desc;
 drop table t1;
 
-#
-
 # not purely group_by bug, but group_by is involved...
 
 create table t1 (a date default null, b date default null);
@@ -275,7 +276,6 @@ select a,min(b) c,count(distinct rand()) from t1 group by a having c<a + interva
 drop table t1;
 
 # Compare with hash keys
-#
 
 CREATE TABLE t1 (a char(1));
 INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL);
@@ -410,15 +410,7 @@ drop table t1,t2;
 #
 
 create table t1 (a int, b int);
-insert into t1 values (1, 4);
-insert into t1 values (10, 40);
-insert into t1 values (1, 4);
-insert into t1 values (10, 43);
-insert into t1 values (1, 4);
-insert into t1 values (10, 41);
-insert into t1 values (1, 4);
-insert into t1 values (10, 43);
-insert into t1 values (1, 4);
+insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
 select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a;
 select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a;
 select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a;
diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test
index 4bd56400f98..7d5fbee011f 100644
--- a/mysql-test/t/having.test
+++ b/mysql-test/t/having.test
@@ -64,3 +64,13 @@ select Fld1, max(Fld2) from t1 group by Fld1 having avg(Fld2) is not null;
 select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null;
 select Fld1, max(Fld2) from t1 group by Fld1 having variance(Fld2) is not null;
 drop table t1;
+
+#
+# Test problem with count(distinct) in having
+#
+create table t1 (id int not null, qty int not null);
+insert into t1 values (1,2),(1,3),(2,4),(2,5);
+select id, sum(qty) as sqty from t1 group by id having sqty>2;
+select sum(qty) as sqty from t1 group by id having count(id) > 0;
+select sum(qty) as sqty from t1 group by id having count(distinct id) > 0;
+drop table t1;
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index bf1756ede5c..a0cb104df48 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -45,12 +45,9 @@ update ignore t1 set id=id+1; # This will change all rows
 select * from t1;
 update ignore t1 set id=1023 where id=1010;
 select * from t1 where parent_id=102;
---replace_result 12 # 6 #
-explain select level from t1 where level=1;
---replace_result 12 # 6 #
-explain select level,id from t1 where level=1;
---replace_result 12 # 6 # 5 #
-explain select level,id,parent_id from t1 where level=1;
+# explain select level from t1 where level=1;
+# explain select level,id from t1 where level=1;
+# explain select level,id,parent_id from t1 where level=1;
 select level,id from t1 where level=1;
 select level,id,parent_id from t1 where level=1;
 optimize table t1;
@@ -348,7 +345,7 @@ update ignore t1 set id=id+1; # This will change all rows
 select * from t1;
 update ignore t1 set id=1023 where id=1010;
 select * from t1 where parent_id=102;
-explain select level from t1 where level=1;
+# explain select level from t1 where level=1;
 select level,id from t1 where level=1;
 select level,id,parent_id from t1 where level=1;
 select level,id from t1 where level=1 order by id;
@@ -784,7 +781,7 @@ drop table t1;
 
 CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) type=innodb;
 CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) type=innodb;
-INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12);
 INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
 
 # Full join, without key
@@ -800,11 +797,77 @@ update t1,t2 set t1.b=t1.b+10 where t1.b=2;
 select * from t1;
 
 # Range key (in t1)
-update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5;
+update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100;
 select * from t1;
 select * from t2;
 
 drop table t1,t2;
+CREATE TABLE t2 (   NEXT_T         BIGINT NOT NULL PRIMARY KEY) TYPE=MyISAM;
+CREATE TABLE t1 (  B_ID           INTEGER NOT NULL PRIMARY KEY) TYPE=InnoDB;
+SET AUTOCOMMIT=0;
+INSERT INTO t1 ( B_ID ) VALUES ( 1 );
+INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );
+-- error 1196
+ROLLBACK;
+SELECT * FROM t1;
+drop table  t1,t2;
+create table t1  ( pk         int primary key,    parent     int not null,    child      int not null,       index (parent)  ) type = innodb;
+insert into t1 values   (1,0,4),  (2,1,3),  (3,2,1),  (4,1,2);
+select distinct  parent,child   from t1   order by parent;
+drop table t1;
+
+#
+# Test that MySQL priorities clustered indexes
+#
+create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) type=innodb;
+create table t2 (a int not null auto_increment primary key, b int);
+insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
+insert into t2 (a) select b from t1;
+insert into t1 (b) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+insert into t2 (a) select b from t1;
+insert into t1 (a) select b from t2;
+select count(*) from t1;
+explain select * from t1 where c between 1 and 10000;
+update t1 set c=a;
+explain select * from t1 where c between 1 and 10000;
+drop table t1,t2;
+
+#
+# Test of UPDATE ... ORDER BY
+#
+
+create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) type=innodb;
+
+insert into t1 (id) values (null),(null),(null),(null),(null);
+update t1 set fk=69 where fk is null order by id limit 1;
+SELECT * from t1;
+drop table t1;
+
+create table t1 (a int not null, b int not null, key (a));
+insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
+SET @tmp=0;
+update t1 set b=(@tmp:=@tmp+1) order by a;
+update t1 set b=99 where a=1 order by b asc limit 1;
+update t1 set b=100 where a=1 order by b desc limit 2;
+update t1 set a=a+10+b where a=1 order by b;
+select * from t1 order by a,b;
+drop table t1;
+
+
 CREATE TABLE t1 (col1 int(1))TYPE=InnoDB;
 CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP(+0),INDEX stamp_idx
 (stamp))TYPE=InnoDB;
diff --git a/mysql-test/t/innodb_handler.test b/mysql-test/t/innodb_handler.test
index 32c8741577c..cf6ac9870ce 100644
--- a/mysql-test/t/innodb_handler.test
+++ b/mysql-test/t/innodb_handler.test
@@ -75,5 +75,11 @@ alter table t1 type=innodb;
 --error 1109
 handler t2 read first;
 
-drop table if exists t1;
+drop table t1;
+CREATE TABLE t1 (  no1 smallint(5) NOT NULL default '0',  no2 int(10) NOT NULL default '0',  PRIMARY KEY  (no1,no2)) TYPE=InnoDB;
+INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2);
+HANDLER t1 OPEN;
+HANDLER t1 READ `primary` = (1, 1000);
+HANDLER t1 READ `primary` PREV;
+DROP TABLE t1;
 
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 5fd96d6687c..882aec1006a 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -272,3 +272,49 @@ INSERT INTO t2 VALUES ('rivercats','cust',20);
 SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
 SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE lr.siteid = 'rivercats' AND emp.emp_id = 'psmith';
 drop table t1,t2;
+
+#
+# Problem with internal list handling when reducing WHERE
+#
+
+CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255));
+CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255));
+INSERT INTO t1 VALUES (1, 'A');
+INSERT INTO t2 VALUES (1, 'B');
+
+SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND (Value1 = 'A' AND Value2 <> 'B');
+SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND Value1 = 'A' AND Value2 <> 'B';
+SELECT * FROM t1 NATURAL JOIN t2 WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1;
+drop table t1,t2;
+
+#
+# Test combination of join methods
+#
+
+create table t1 (i int);
+create table t2 (i int);
+create table t3 (i int);
+insert into t1 values(1),(2);
+insert into t2 values(2),(3);
+insert into t3 values (2),(4);
+
+select * from t1 natural left join t2;
+select * from t1 left join t2 on (t1.i=t2.i);
+select * from t1 natural left join t2 natural left join t3;
+select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i);
+
+select * from t3 natural right join t2;
+select * from t3 right join t2 on (t3.i=t2.i);
+select * from t3 natural right join t2 natural right join t1;
+select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i);
+
+select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
+select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
+select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i;
+select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
+
+select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i;
+select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
+select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i;
+select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
+drop table t1,t2,t3;
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index c57fb8273fe..49f13abe7c2 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -249,7 +249,7 @@ drop table t1,t2;
 
 create table t1 (id int not null, str char(10), index(str));
 insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar");
-select * from t1 where str is not null;
+select * from t1 where str is not null order by id;
 select * from t1 where str is null;
 drop table t1;
 
@@ -417,3 +417,12 @@ explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
 select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
 select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30;
 drop table t1,t2;
+drop table if exists t3;
+create table t1 (i int);
+create table t2 (i int);
+create table t3 (i int);
+insert into t1 values(1),(2);
+insert into t2 values(2),(3);
+insert into t3 values(2),(4);
+select * from t1 natural left join t2 natural left join t3;
+drop table t1,t2,t3;
diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test
index 4fd8cc86dd5..de97c639812 100644
--- a/mysql-test/t/key.test
+++ b/mysql-test/t/key.test
@@ -57,12 +57,12 @@ INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
 drop table t1;
 
 #
-# problem med primary key
+# No longer a problem with primary key
 #
 
 CREATE TABLE t1 (program enum('signup','unique','sliding') not null,  type enum('basic','sliding','signup'),  sites set('mt'),  PRIMARY KEY (program));
-# The following should give an error for wrong primary key
-!$1171 ALTER TABLE t1 modify program enum('signup','unique','sliding');
+# This no longer give an error for wrong primary key
+ALTER TABLE t1 modify program enum('signup','unique','sliding');
 drop table t1;
 
 #
diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test
new file mode 100644
index 00000000000..ceb5c47af11
--- /dev/null
+++ b/mysql-test/t/loaddata.test
@@ -0,0 +1,11 @@
+#
+# Some simple test of load data
+#
+
+drop table if exists t1;
+
+create table t1 (a date, b date, c date not null, d date);
+load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',';
+load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES;
+SELECT * from t1;
+drop table t1;
diff --git a/mysql-test/t/multi_update-master.opt b/mysql-test/t/multi_update-master.opt
new file mode 100644
index 00000000000..9f1a29461ff
--- /dev/null
+++ b/mysql-test/t/multi_update-master.opt
@@ -0,0 +1 @@
+--set-variable=tmp_table_size=1024
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 390dd58d806..8e560cab455 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -255,8 +255,16 @@ update t1,t2 set t1.b=t1.b+10 where t1.b=2;
 select * from t1;
 
 # Range key (in t1)
-update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5;
+update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t2.a=t1.a-100;
 select * from t1;
 select * from t2;
 
 drop table t1,t2;
+CREATE TABLE t3 (  KEY1 varchar(50) NOT NULL default '',  PARAM_CORR_DISTANCE_RUSH double default NULL,  PARAM_CORR_DISTANCE_GEM double default NULL,  PARAM_AVG_TARE double default NULL,  PARAM_AVG_NB_DAYS double default NULL,  PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL,  PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL,  PARAM_SCENARIO_COSTS varchar(50) default NULL,  PARAM_DEFAULT_WAGON_COST double default NULL,  tmp int(11) default NULL,  PRIMARY KEY  (KEY1)) TYPE=MyISAM;
+INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL);
+create table t1 (A varchar(1));
+insert into t1 values  ("A") ,("B"),("C"),("D");
+create table t2(Z varchar(15));
+insert into t2(Z)  select concat(a.a,b.a,c.a,d.a) from t1 as a, t1 as b, t1 as c, t1 as d;
+update t2,t3 set Z =param_scenario_costs;
+drop table t1,t2,t3;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 8e1f304a5a5..5235eddd671 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -334,9 +334,21 @@ SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,des
 drop table t1,t2;
 
 #
-# Test of test_if_subkey() function
+# Test order by with NULL values
 #
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (1, 2);
+INSERT INTO t1 VALUES (3, 4);
+INSERT INTO t1 VALUES (5, NULL);
+SELECT * FROM t1 ORDER BY b;
+SELECT * FROM t1 ORDER BY b DESC;
+SELECT * FROM t1 ORDER BY (a + b);
+SELECT * FROM t1 ORDER BY (a + b) DESC;
+DROP TABLE t1;
 
+#
+# Test of test_if_subkey() function
+#
 CREATE TABLE t1 (
   FieldKey varchar(36) NOT NULL default '',
   LongVal bigint(20) default NULL,
diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index 56eda7fa104..1078d19533c 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -147,9 +147,10 @@ create table t1 (a int not null);
 insert into t1 values (1),(2),(3);
 select * from t1;
 show status like "Qcache_queries_in_cache";
-select sql_cache * from t1;
+select sql_cache * from t1 union select * from t1;
 set query_cache_type=2;
-select sql_cache * from t1;
+select sql_cache * from t1 union select * from t1;
+select * from t1 union select sql_cache * from t1;
 show status like "Qcache_hits";
 show status like "Qcache_queries_in_cache";
 set query_cache_type=on;
@@ -429,7 +430,6 @@ select * from t1 where id=2;
 #
 # Load data invalidation test
 #
-
 create table t1 (word char(20) not null);
 select * from t1;
 show status like "Qcache_queries_in_cache";
@@ -440,7 +440,6 @@ drop table t1;
 #
 # INTO OUTFILE/DUMPFILE test
 #
-
 create table t1 (a int);
 insert into t1 values (1),(2),(3);
 show status like "Qcache_queries_in_cache";
@@ -448,3 +447,16 @@ select * from t1 into outfile "query_caceh.out.file";
 select * from t1 limit 1 into dumpfile "query_cache.dump.file";
 show status like "Qcache_queries_in_cache";
 drop table t1;
+
+#
+# test of SQL_SELECT_LIMIT
+#
+create table t1 (a int);
+insert into t1 values (1),(2);
+show status like "Qcache_queries_in_cache";
+select * from t1;
+SET OPTION SQL_SELECT_LIMIT=1;
+select * from t1;
+show status like "Qcache_queries_in_cache";
+SET OPTION SQL_SELECT_LIMIT=DEFAULT;
+drop table t1;
diff --git a/mysql-test/t/raid.test b/mysql-test/t/raid.test
index 38c740201bc..235add63c22 100644
--- a/mysql-test/t/raid.test
+++ b/mysql-test/t/raid.test
@@ -9,6 +9,14 @@ show variables like "have_raid";
 DROP TABLE IF EXISTS t1,t2;
 --enable_warnings
 
+#
+# Test dropping database with raid tables
+#
+
+create database test_$1;
+create table test_$1.r1 (i int) raid_type=1;
+drop database test_$1;
+
 CREATE TABLE t1 (
 id int unsigned not null auto_increment primary key,
 c char(255) not null
diff --git a/mysql-test/t/repair.test b/mysql-test/t/repair.test
index bdc5a57a07c..2657f91cd02 100644
--- a/mysql-test/t/repair.test
+++ b/mysql-test/t/repair.test
@@ -8,4 +8,15 @@ drop table if exists t1;
 
 create table t1 SELECT 1,"table 1";
 repair table t1 use_frm;
-drop table if exists t1;
+alter table t1 TYPE=HEAP;
+repair table t1 use_frm;
+drop table t1;
+
+# non-existent table
+repair table t1 use_frm;
+
+#
+# Create test table for repair2
+# The following must be last in this file
+
+create table t1 type=myisam SELECT 1,"table 1";
diff --git a/mysql-test/t/repair_part2-master.sh b/mysql-test/t/repair_part2-master.sh
new file mode 100644
index 00000000000..964bde06c18
--- /dev/null
+++ b/mysql-test/t/repair_part2-master.sh
@@ -0,0 +1 @@
+echo "1" > $MYSQL_TEST_DIR/var/master-data/test/t1.MYI
diff --git a/mysql-test/t/repair_part2.test b/mysql-test/t/repair_part2.test
new file mode 100644
index 00000000000..8c27e382dff
--- /dev/null
+++ b/mysql-test/t/repair_part2.test
@@ -0,0 +1,7 @@
+#
+# This test starts with a crashed t1.MYI file left over from repair.test
+#
+
+repair table t1;
+repair table t1 use_frm;
+drop table t1;
diff --git a/mysql-test/t/select_safe.test b/mysql-test/t/select_safe.test
index cd8bce6369c..be25c7c05d1 100644
--- a/mysql-test/t/select_safe.test
+++ b/mysql-test/t/select_safe.test
@@ -7,7 +7,7 @@ drop table if exists t1;
 --enable_warnings
 
 SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9;
-create table t1 (a int primary key, b char(20));
+create table t1 (a int auto_increment primary key, b char(20));
 insert into t1 values(1,"test");
 SELECT SQL_BUFFER_RESULT * from t1;
 update t1 set b="a" where a=1;
@@ -18,18 +18,40 @@ update t1 set b="a" where a=1;
 select 1 from t1,t1 as t2,t1 as t3,t1 as t4;
 
 # The following should give errors:
-!$1175 update t1 set b="a";
-!$1175 update t1 set b="a" where b="test";
-!$1175 delete from t1;
-!$1175 delete from t1 where b="test";
-!$1175 delete from t1 where a+0=1;
-!$1104 select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5;
+--error 1175
+update t1 set b="a";
+--error 1175
+update t1 set b="a" where b="test";
+--error 1175
+delete from t1;
+--error 1175
+delete from t1 where b="test";
+--error 1175
+delete from t1 where a+0=1;
+--error 1104
+select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5;
 
 # The following should be ok:
 update t1 set b="a" limit 1;
 update t1 set b="a" where b="b" limit 2; 
 delete from t1 where b="test" limit 1;
 delete from t1 where a+0=1 limit 2;
+
+# Test SQL_BIG_SELECTS
+
+SET MAX_JOIN_SIZE=2;
+SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS;
+insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
+--error 1104
+SELECT * from t1;
+SET SQL_BIG_SELECTS=1;
+SELECT * from t1;
+SET MAX_JOIN_SIZE=2;
+--error 1104
+SELECT * from t1;
+SET MAX_JOIN_SIZE=DEFAULT;
+SELECT * from t1;
+
 drop table t1;
 
 SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT;
diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test
index 053d0517904..8653c06a644 100644
--- a/mysql-test/t/type_decimal.test
+++ b/mysql-test/t/type_decimal.test
@@ -233,3 +233,13 @@ insert into t1 values("1e4294967297");
 select * from t1;
 drop table t1;
 
+#
+# Test of wrong decimal type
+#
+
+--error 1074
+CREATE TABLE t1 (a_dec DECIMAL(-1,0));
+--error 1074
+CREATE TABLE t1 (a_dec DECIMAL(-2,1));
+--error 1074
+CREATE TABLE t1 (a_dec DECIMAL(-1,1));
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 70e770cd2d3..6d857a4b40f 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -3,7 +3,7 @@
 #
 
 --disable_warnings
-drop table if exists t1,t2,t3;
+drop table if exists t1,t2,t3,t4,t5,t6;
 --enable_warnings
 
 CREATE TABLE t1 (a int not null, b char (10) not null);
@@ -19,7 +19,7 @@ select 0,'#' union select a,b from t1 union all select a,b from t2 union select
 select a,b from t1 union select a,b from t1;
 select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 group by b;
 
-#test alternate syntax for unions 
+# Test alternate syntax for unions 
 (select a,b from t1 limit 2)  union all (select a,b from t2 order by a) limit 4;
 (select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1);
 (select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1) order by b desc;
@@ -27,7 +27,7 @@ select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 g
 (select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1) order by t1.b;
 explain (select a,b from t1 limit 2)  union all (select a,b from t2 order by a limit 1) order by b desc;
 #(select sql_calc_found_rows  a,b from t1 limit 2)  union all (select a,b from t2 order by a) limit 2;
-select found_rows();
+#select found_rows();
 
 #
 # Test some error conditions with UNION
@@ -73,6 +73,19 @@ replace into t3 select a,b as c from t1 union all select a,b from t2;
 drop table t1,t2,t3;
 
 #
+# Test some unions without tables
+#
+--error 1096
+select * union select 1;
+select 1 as a,(select a union select a);
+--error 1054
+(select 1) union (select 2) order by 0;
+SELECT @a:=1 UNION SELECT @a:=@a+1;
+--error 1054
+(SELECT 1) UNION (SELECT 2) ORDER BY (SELECT a);
+(SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2);
+
+#
 # Test bug reported by joc@presence-pc.com
 #
 
@@ -104,12 +117,67 @@ set SQL_SELECT_LIMIT=2;
 set SQL_SELECT_LIMIT=DEFAULT;
 drop table t1,t2;
 
---error 1096
-select * union select 1;
-select 1 as a,(select a union select a);
---disable_warnings
-drop table if exists t1,t2;
---enable_warnings
+#
+# Test error with left join
+#
+
+CREATE TABLE t1 (
+  cid smallint(5) unsigned NOT NULL default '0',
+  cv varchar(250) NOT NULL default '',
+  PRIMARY KEY  (cid),
+  UNIQUE KEY cv (cv)
+) ;
+INSERT INTO t1 VALUES (8,'dummy');
+CREATE TABLE t2 (
+  cid bigint(20) unsigned NOT NULL auto_increment,
+  cap varchar(255) NOT NULL default '',
+  PRIMARY KEY  (cid),
+  KEY cap (cap)
+) ;
+CREATE TABLE t3 (
+  gid bigint(20) unsigned NOT NULL auto_increment,
+  gn varchar(255) NOT NULL default '',
+  must tinyint(4) default NULL,
+  PRIMARY KEY  (gid),
+  KEY gn (gn)
+) ;
+INSERT INTO t3 VALUES (1,'V1',NULL);
+CREATE TABLE t4 (
+  uid bigint(20) unsigned NOT NULL default '0',
+  gid bigint(20) unsigned default NULL,
+  rid bigint(20) unsigned default NULL,
+  cid bigint(20) unsigned default NULL,
+  UNIQUE KEY m (uid,gid,rid,cid),
+  KEY uid (uid),
+  KEY rid (rid),
+  KEY cid (cid),
+  KEY container (gid,rid,cid)
+) ;
+INSERT INTO t4 VALUES (1,1,NULL,NULL);
+CREATE TABLE t5 (
+  rid bigint(20) unsigned NOT NULL auto_increment,
+  rl varchar(255) NOT NULL default '',
+  PRIMARY KEY  (rid),
+  KEY rl (rl)
+) ;
+CREATE TABLE t6 (
+  uid bigint(20) unsigned NOT NULL auto_increment,
+  un varchar(250) NOT NULL default '',
+  uc smallint(5) unsigned NOT NULL default '0',
+  PRIMARY KEY  (uid),
+  UNIQUE KEY nc (un,uc),
+  KEY un (un)
+) ;
+INSERT INTO t6 VALUES (1,'test',8);
+
+SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc  = t1.cid AND t1.cv = "dummy" AND t6.un = "test";
+SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc  = t1.cid AND t1.cv = "dummy" AND t6.un = "test";
+(SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc  = t1.cid AND t1.cv = "dummy" AND t6.un = "test") UNION (SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc  = t1.cid AND t1.cv = "dummy" AND t6.un = "test");
+drop table t1,t2,t3,t4,t5,t6;
+
+#
+# Test for another bug with UNION and LEFT JOIN
+#
 CREATE TABLE t1 (  id int(3) unsigned default '0') TYPE=MyISAM;
 INSERT INTO t1 (id) VALUES("1");
 CREATE TABLE t2 ( id int(3) unsigned default '0',  id_master int(5) default '0',  text1 varchar(5) default NULL,  text2 varchar(5) default NULL) TYPE=MyISAM;
@@ -124,7 +192,6 @@ INSERT INTO t2 (id, id_master, text1, text2) VALUES("4", "1",
 SELECT 1 AS id_master, 1 AS id, NULL AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master;
 SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master;
 drop table if exists t1,t2;
-(SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2);
 
 #
 # Test of bug when using the same table multiple times
@@ -142,9 +209,3 @@ explain (select * from t1 where a=1) union (select * from t2 where a=1);
 explain (select * from t1 where a=1 and b=10) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a);
 explain (select * from t1 where a=1) union (select * from t1 where b=1);
 drop table t1,t2;
---error 1054
-(select 1) union (select 2) order by 0;
-
-SELECT @a:=1 UNION SELECT @a:=@a+1;
---error 1054
-(SELECT 1) UNION (SELECT 2) ORDER BY (SELECT a);
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index 6a7eac41a96..485af981fb4 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -84,12 +84,48 @@ drop table t1;
 # Test of ORDER BY
 #
 
-create table t1 (a int not null, b int not null);
-insert into t1 values (1,1),(1,2),(1,3);
-update t1 set b=4 where a=1 order by b asc limit 1;
-update t1 set b=4 where a=1 order by b desc limit 1;
+create table t1 (a int not null, b int not null, key (a));
+insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
+SET @tmp=0;
+update t1 set b=(@tmp:=@tmp+1) order by a;
+update t1 set b=99 where a=1 order by b asc limit 1;
+select * from t1 order by a,b;
+update t1 set b=100 where a=1 order by b desc limit 2;
+update t1 set a=a+10+b where a=1 order by b;
+select * from t1 order by a,b;
 create table t2 (a int not null, b int not null);
 insert into t2 values (1,1),(1,2),(1,3);
-select * from t1;
 update t1 set b=(select distinct 1 from (select * from t2) a);
 drop table t1,t2;
+
+#
+# Test with limit (Bug #393)
+#
+
+CREATE TABLE t1 (
+   `id_param` smallint(3) unsigned NOT NULL default '0',
+   `nom_option` char(40) NOT NULL default '',
+   `valid` tinyint(1) NOT NULL default '0',
+   KEY `id_param` (`id_param`,`nom_option`)
+ ) TYPE=MyISAM;
+
+INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1);
+
+UPDATE t1 SET nom_option='test' WHERE id_param=185 AND nom_option='600x1200' AND valid=1 LIMIT 1;
+select * from t1;
+drop table t1;
+
+#
+# Multi table update test from bugs
+#
+
+create table t1 (F1 VARCHAR(30), F2 VARCHAR(30), F3 VARCHAR(30), cnt int, groupid int, KEY groupid_index (groupid));
+
+insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6),
+('0','1','2',1,5), ('0','2','0',1,3), ('1','0','1',1,2),
+('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4),
+('2','2','0',1,7);
+
+delete from t1 using t1 m1,t1 m2 where m1.groupid=m2.groupid and (m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3);
+select * from t1;
+drop table t1;
diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test
index d1c8df64be2..4f244311a59 100644
--- a/mysql-test/t/variables.test
+++ b/mysql-test/t/variables.test
@@ -72,10 +72,10 @@ show local variables like 'table_type';
 show global variables like 'table_type';
 set GLOBAL query_cache_size=100000;
 
-set myisam_max_sort_file_size=10000, GLOBAL myisam_max_sort_file_size=20000;
-show variables like 'myisam_max_sort_file_size';
+set GLOBAL myisam_max_sort_file_size=2000000;
 show global variables like 'myisam_max_sort_file_size';
-set myisam_max_sort_file_size=default;
+set GLOBAL myisam_max_sort_file_size=default;
+--replace_result 2147483647 FILE_SIZE 9223372036854775807 FILE_SIZE
 show variables like 'myisam_max_sort_file_size';
 
 set global net_retry_count=10, session net_retry_count=10;
@@ -132,6 +132,10 @@ set @@concurrent_insert=1;
 set @@global.sql_auto_is_null=1;
 --error 1228
 select @@global.sql_auto_is_null;
+--error 1229
+set myisam_max_sort_file_size=100;
+--error 1229
+set myisam_max_extra_sort_file_size=100;
 
 # Test setting all variables
 
@@ -176,9 +180,9 @@ set max_tmp_tables=100;
 set global max_user_connections=100;
 select @@max_user_connections;
 set global max_write_lock_count=100;
-set myisam_max_extra_sort_file_size=100;
+set global myisam_max_extra_sort_file_size=100;
 select @@myisam_max_extra_sort_file_size;
-set myisam_max_sort_file_size=100;
+set global myisam_max_sort_file_size=100;
 set myisam_sort_buffer_size=100;
 set net_buffer_length=100;
 set net_read_timeout=100;
@@ -236,3 +240,10 @@ check table t1,t2;
 select max(a) +1, max(a) +2 into @xx,@yy from t1;
 drop table t1,t2;
 
+#
+# error conditions
+#
+
+--error 1193
+select @@xxxxxxxxxx;
+select 1;