Merge sinisa@bk-internal.mysql.com:/home/bk/mysql-4.0

into sinisa.nasamreza.org:/mnt/work/mysql-4.0

37 files changed, 400 insertions, 86 deletions

diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh
index 65c960d13fa..7ce6ffe14af 100644
--- a/mysql-test/mysql-test-run.sh
+++ b/mysql-test/mysql-test-run.sh
@@ -110,11 +110,11 @@ GREP=grep
 if test $? != 0; then exit 1; fi
 PRINTF=printf
 RM=rm
-TIME=`which time`
 if test $? != 0; then exit 1; fi
 TR=tr
 XARGS=`which xargs`
 if test $? != 0; then exit 1; fi
+SORT=sort
 
 # Are we using a source or a binary distribution?
 
@@ -409,7 +409,7 @@ fi
 
 if test ${COLUMNS:-0} -lt 80 ; then COLUMNS=80 ; fi
 E=`$EXPR $COLUMNS - 8`
-DASH72=`$ECHO '------------------------------------------------------------------------'|$CUT -c 1-$E`
+DASH72=`$ECHO '------------------------------------------'|$CUT -c 1-$E`
 
 # on source dist, we pick up freshly build executables
 # on binary, use what is installed
@@ -588,9 +588,8 @@ skip_test() {
    USERT="    ...."
    SYST="    ...."
    REALT="    ...."
-   timestr="$USERT $SYST $REALT"
    pname=`$ECHO "$1                        "|$CUT -c 1-24`
-   RES="$pname  $timestr"
+   RES="$pname"
    skip_inc
    $ECHO "$RES$RES_SPACE [ skipped ]"
 }
@@ -1217,31 +1216,15 @@ run_testcase ()
     $RM -f r/$tname.*reject
     mysql_test_args="-R r/$tname.result $EXTRA_MYSQL_TEST_OPT"
     if [ -z "$DO_CLIENT_GDB" ] ; then
-      mytime=`$TIME -p $MYSQL_TEST  $mysql_test_args < $tf 2> $TIMEFILE`
+      `$MYSQL_TEST  $mysql_test_args < $tf 2> $TIMEFILE`;
     else
       do_gdb_test "$mysql_test_args" "$tf"
     fi
 
     res=$?
 
-    if [ $res = 0 ]; then
-	mytime=`$CAT $TIMEFILE | $TAIL -3 | $TR '\n' ':'`
-
-	USERT=`$ECHO $mytime | $CUT -d : -f 2 | $CUT -d ' ' -f 2`
-        USERT=`prefix_to_8 $USERT`
-	SYST=`$ECHO $mytime | $CUT -d : -f 3 | $CUT -d ' ' -f 2`
-        SYST=`prefix_to_8 $SYST`
-	REALT=`$ECHO $mytime | $CUT -d : -f 1 | $CUT -d ' ' -f 2`
-        REALT=`prefix_to_8 $REALT`
-    else
-	USERT="    ...."
-	SYST="    ...."
-	REALT="    ...."
-    fi
-
-    timestr="$USERT $SYST $REALT"
     pname=`$ECHO "$tname                        "|$CUT -c 1-24`
-    RES="$pname  $timestr"
+    RES="$pname"
 
     if [ x$many_slaves = x1 ] ; then
      stop_slave 1
@@ -1373,7 +1356,7 @@ then
 fi
 
 $ECHO
-$ECHO " TEST                         USER   SYSTEM  ELAPSED        RESULT"
+$ECHO " TEST                           RESULT"
 $ECHO $DASH72
 
 if [ -z "$1" ] ;
@@ -1381,7 +1364,7 @@ then
  if [ x$RECORD = x1 ]; then
   $ECHO "Will not run in record mode without a specific test case."
  else
-  for tf in $TESTDIR/*.$TESTSUFFIX
+  for tf in `ls -1 $TESTDIR/*.$TESTSUFFIX | $SORT`
   do
     run_testcase $tf
   done
diff --git a/mysql-test/r/ansi.result b/mysql-test/r/ansi.result
new file mode 100644
index 00000000000..f9f96310b73
--- /dev/null
+++ b/mysql-test/r/ansi.result
@@ -0,0 +1,10 @@
+drop table if exists t1;
+SELECT 'A' || 'B';
+'A' || 'B'
+AB
+CREATE TABLE t1 (id INT, id2 int);
+SELECT id,NULL,1,1.1,'a' FROM t1 GROUP BY id;
+id	NULL	1	1.1	a
+SELECT id FROM t1 GROUP BY id2;
+'t1.id' isn't in GROUP BY
+drop table t1;
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index a2ab0e97905..2e2aa41c671 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -25,13 +25,9 @@ create table t1 (a int not null auto_increment,primary key (a)) type=heap;
 The used table type doesn't support AUTO_INCREMENT columns
 create table t1 (a int not null,b text) type=heap;
 The used table type doesn't support BLOB/TEXT columns
-create table t1 (a int ,primary key(a)) type=heap;
-All parts of a PRIMARY KEY must be NOT NULL;  If you need NULL in a key, use UNIQUE instead
 drop table if exists t1;
 create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ord,ordid)) type=heap;
 The used table type doesn't support AUTO_INCREMENT columns
-create table t1 (ordid int(8), primary key (ordid));
-All parts of a PRIMARY KEY must be NOT NULL;  If you need NULL in a key, use UNIQUE instead
 create table not_existing_database.test (a int);
 Got one of the listed errors
 create table `a/a` (a int);
@@ -171,3 +167,12 @@ t1	CREATE TABLE `t1` (
 ) TYPE=MyISAM
 SET SESSION table_type=default;
 drop table t1;
+create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2));
+insert into t1 values ("a", 1), ("b", 2);
+insert into t1 values ("c", NULL);
+Column 'k2' cannot be null
+insert into t1 values (NULL, 3);
+Column 'k1' cannot be null
+insert into t1 values (NULL, NULL);
+Column 'k1' cannot be null
+drop table t1;
diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result
index 582ab894233..abc8245e69f 100644
--- a/mysql-test/r/delete.result
+++ b/mysql-test/r/delete.result
@@ -38,4 +38,15 @@ NULL	d	7
 delete   from t1 where misc > 5 and bool is null;
 select * from t1 where misc > 5 and bool is null;
 bool	not_null	misc
+select count(*) from t1;
+count(*)
+2
+delete from t1 where 1 > 2;
+select count(*) from t1;
+count(*)
+2
+delete from t1 where 3 > 2;
+select count(*) from t1;
+count(*)
+0
 drop table t1;
diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
index 4eed80c4cc9..8d05adcc1ba 100644
--- a/mysql-test/r/func_misc.result
+++ b/mysql-test/r/func_misc.result
@@ -10,3 +10,6 @@ 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);
 inet_ntoa(1099511627775)	inet_ntoa(4294902271)	inet_ntoa(511)
 NULL	255.255.1.255	0.0.1.255
+select length(format('nan', 2)) > 0;
+length(format('nan', 2)) > 0
+1
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index f113eb6ed49..d643070f7f9 100644
--- a/mysql-test/r/having.result
+++ b/mysql-test/r/having.result
@@ -69,4 +69,12 @@ select id, sum(qty) as sqty from t1 group by id having sqty>2;
 id	sqty
 1	5
 2	9
+select sum(qty) as sqty from t1 group by id having count(id) > 0;
+sqty
+5
+9
+select sum(qty) as sqty from t1 group by id having count(distinct id) > 0;
+sqty
+5
+9
 drop table t1;
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index c98f85f93d2..e2dea324ff2 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -138,15 +138,6 @@ id	parent_id	level
 1008	102	2
 1010	102	2
 1015	102	2
-explain select level from t1 where level=1;
-table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	ref	level	level	1	const	12	Using where; Using index
-explain select level,id from t1 where level=1;
-table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	ref	level	level	1	const	12	Using where; Using index
-explain select level,id,parent_id from t1 where level=1;
-table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	ref	level	level	1	const	12	Using where
 select level,id from t1 where level=1;
 level	id
 1	1002
@@ -595,9 +586,6 @@ id	parent_id	level
 1009	102	2
 1025	102	2
 1016	102	2
-explain select level from t1 where level=1;
-table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	ref	level	level	1	const	6	Using where; Using index
 select level,id from t1 where level=1;
 level	id
 1	1004
@@ -1139,7 +1127,7 @@ a	b
 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);
 update t1,t2 set t1.a=t1.a+100;
 select * from t1;
@@ -1153,6 +1141,9 @@ a	b
 107	7
 108	8
 109	9
+110	10
+111	11
+112	12
 update t1,t2 set t1.a=t1.a+100 where t1.a=101;
 select * from t1;
 a	b
@@ -1165,6 +1156,9 @@ a	b
 107	7
 108	8
 109	9
+110	10
+111	11
+112	12
 update t1,t2 set t1.b=t1.b+10 where t1.b=2;
 select * from t1;
 a	b
@@ -1176,8 +1170,11 @@ a	b
 107	7
 108	8
 109	9
+110	10
+111	11
 102	12
-update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5;
+112	12
+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;
 a	b
 201	1
@@ -1188,18 +1185,21 @@ a	b
 107	7
 108	8
 109	9
+110	10
+111	11
 102	12
+112	12
 select * from t2;
 a	b
-1	5
-2	5
-3	5
-4	5
-5	5
-6	5
-7	5
-8	5
-9	5
+1	1
+2	2
+6	6
+7	7
+8	8
+9	9
+3	13
+4	14
+5	15
 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;
diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result
index 1cd9c9dfe79..31d35a681aa 100644
--- a/mysql-test/r/key.result
+++ b/mysql-test/r/key.result
@@ -42,7 +42,6 @@ price	area	type	transityes	shopsyes	schoolsyes	petsyes
 drop table t1;
 CREATE TABLE t1 (program enum('signup','unique','sliding') not null,  type enum('basic','sliding','signup'),  sites set('mt'),  PRIMARY KEY (program));
 ALTER TABLE t1 modify program enum('signup','unique','sliding');
-All parts of a PRIMARY KEY must be NOT NULL;  If you need NULL in a key, use UNIQUE instead
 drop table t1;
 CREATE TABLE t1 (
 name varchar(50) DEFAULT '' NOT NULL,
diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result
new file mode 100644
index 00000000000..d121a4e6c40
--- /dev/null
+++ b/mysql-test/r/loaddata.result
@@ -0,0 +1,11 @@
+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;
+a	b	c	d
+0000-00-00	NULL	0000-00-00	0000-00-00
+0000-00-00	0000-00-00	0000-00-00	0000-00-00
+2003-03-03	2003-03-03	2003-03-03	NULL
+2003-03-03	2003-03-03	2003-03-03	NULL
+drop table t1;
diff --git a/mysql-test/r/raid.result b/mysql-test/r/raid.result
index 41af50851e9..fd47a9451f6 100644
--- a/mysql-test/r/raid.result
+++ b/mysql-test/r/raid.result
@@ -1,3 +1,6 @@
+create database test_raid;
+create table test_raid.r1 (i int) raid_type=1;
+drop database test_raid;
 DROP TABLE IF EXISTS t1,t2;
 CREATE TABLE t1 (
 id int unsigned not null auto_increment primary key,
diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result
index adc09ded0e2..6c2107b2cf3 100644
--- a/mysql-test/r/repair.result
+++ b/mysql-test/r/repair.result
@@ -9,3 +9,7 @@ repair table t1 use_frm;
 Table	Op	Msg_type	Msg_text
 test.t1	repair	error	The handler for the table doesn't support repair
 drop table t1;
+repair table t1 use_frm;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	error	Table 'test.t1' doesn't exist
+create table t1 type=myisam SELECT 1,"table 1";
diff --git a/mysql-test/r/repair_part2.result b/mysql-test/r/repair_part2.result
new file mode 100644
index 00000000000..77aa98c3da9
--- /dev/null
+++ b/mysql-test/r/repair_part2.result
@@ -0,0 +1,8 @@
+repair table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	error	Can't open file: 't1.MYI'. (errno: 130)
+repair table t1 use_frm;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	warning	Number of rows changed from 0 to 1
+test.t1	repair	status	OK
+drop table t1;
diff --git a/mysql-test/r/rpl_loaddata.result b/mysql-test/r/rpl_loaddata.result
index c1518e8e29a..62071a07d0c 100644
--- a/mysql-test/r/rpl_loaddata.result
+++ b/mysql-test/r/rpl_loaddata.result
@@ -7,7 +7,7 @@ slave start;
 create table t1(a int not null auto_increment, b int, primary key(a) );
 load data infile '../../std_data/rpl_loaddata.dat' into table t1;
 create temporary table t2 (day date,id int(9),category enum('a','b','c'),name varchar(60));
-load data infile '../../std_data/rpl_loaddata2.dat' into table t2 fields terminated by ',' optionally enclosed by '%' escaped by '@' lines terminated by '\n##\n' ignore 1 lines;
+load data infile '../../std_data/rpl_loaddata2.dat' into table t2 fields terminated by ',' optionally enclosed by '%' escaped by '@' lines terminated by '\n##\n' starting by '>' ignore 1 lines;
 create table t3 (day date,id int(9),category enum('a','b','c'),name varchar(60));
 insert into t3 select * from t2;
 select * from t1;
diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result
index 4c326957c03..b74765696a2 100644
--- a/mysql-test/r/type_decimal.result
+++ b/mysql-test/r/type_decimal.result
@@ -357,3 +357,9 @@ select * from t1;
 a
 99999999999
 drop table t1;
+CREATE TABLE t1 (a_dec DECIMAL(-1,0));
+Too big column length for column 'a_dec' (max = 255). Use BLOB instead
+CREATE TABLE t1 (a_dec DECIMAL(-2,1));
+Too big column length for column 'a_dec' (max = 255). Use BLOB instead
+CREATE TABLE t1 (a_dec DECIMAL(-1,1));
+Too big column length for column 'a_dec' (max = 255). Use BLOB instead
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 2af9d5a3584..970e83c6752 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -1,4 +1,4 @@
-drop table if exists t1,t2,t3;
+drop table if exists t1,t2,t3,t4,t5,t6;
 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);
@@ -202,3 +202,60 @@ a
 11
 set SQL_SELECT_LIMIT=DEFAULT;
 drop table t1,t2;
+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";
+uid	rl	g1	cid	gg
+1	NULL	V1	NULL	1
+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";
+uid	rl	g1	cid	gg
+(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");
+uid	rl	g1	cid	gg
+1	NULL	V1	NULL	1
+drop table t1,t2,t3,t4,t5,t6;
diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index 86647e845a9..11aff8fe50a 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -138,3 +138,31 @@ a	b
 111	100
 111	100
 drop table t1;
+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;
+id_param	nom_option	valid
+185	test	1
+drop table t1;
+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;
+F1	F2	F3	cnt	groupid
+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
+2	0	1	2	4
+2	2	0	1	7
+drop table t1;
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index 1cc48d2aeac..7c054f55acd 100644
--- a/mysql-test/r/variables.result
+++ b/mysql-test/r/variables.result
@@ -108,17 +108,14 @@ show global variables like 'table_type';
 Variable_name	Value
 table_type	INNODB
 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';
-Variable_name	Value
-myisam_max_sort_file_size	10000
+set GLOBAL myisam_max_sort_file_size=2000000;
 show global variables like 'myisam_max_sort_file_size';
 Variable_name	Value
-myisam_max_sort_file_size	20000
-set myisam_max_sort_file_size=default;
+myisam_max_sort_file_size	1048576
+set GLOBAL myisam_max_sort_file_size=default;
 show variables like 'myisam_max_sort_file_size';
 Variable_name	Value
-myisam_max_sort_file_size	20000
+myisam_max_sort_file_size	FILE_SIZE
 set global net_retry_count=10, session net_retry_count=10;
 set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300;
 set session net_buffer_length=2048, net_write_timeout=500, net_read_timeout=600;
@@ -211,6 +208,10 @@ set @@global.sql_auto_is_null=1;
 Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL
 select @@global.sql_auto_is_null;
 Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL
+set myisam_max_sort_file_size=100;
+Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
+set myisam_max_extra_sort_file_size=100;
+Variable 'myisam_max_extra_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
 set autocommit=1;
 set big_tables=1;
 select @@autocommit, @@big_tables;
@@ -264,11 +265,11 @@ select @@max_user_connections;
 @@max_user_connections
 100
 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;
 @@myisam_max_extra_sort_file_size
 100
-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;
diff --git a/mysql-test/std_data/loaddata1.dat b/mysql-test/std_data/loaddata1.dat
new file mode 100644
index 00000000000..c9e8549b211
--- /dev/null
+++ b/mysql-test/std_data/loaddata1.dat
@@ -0,0 +1,3 @@
+,\N,NULL,,
+00,0,000000,,
+2003-03-03, 20030303,030303,\N
diff --git a/mysql-test/std_data/rpl_loaddata2.dat b/mysql-test/std_data/rpl_loaddata2.dat
index 7a3d4ea7695..b883d9dcd58 100644
--- a/mysql-test/std_data/rpl_loaddata2.dat
+++ b/mysql-test/std_data/rpl_loaddata2.dat
@@ -1,8 +1,8 @@
-2003-01-21,6328,%a%,%aaaaa%
+>2003-01-21,6328,%a%,%aaaaa%
 ##
-2003-02-22,2461,b,%a a a @@ @% @b ' " a%
+>2003-02-22,2461,b,%a a a @@ @% @b ' " a%
 ##
-2003-03-22,2161,%c%,%asdf%
+>2003-03-22,2161,%c%,%asdf%
 ##
-2003-04-22,2416,%a%,%bbbbb%
+>2003-04-22,2416,%a%,%bbbbb%
 ##
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 68d68929f07..86c3f6be0f5 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -24,11 +24,9 @@ drop table if exists t1,t2;
 !$1167 create table t1 (b char(0) not null, index(b));
 !$1164 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;
 
 !$1164 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);
@@ -119,3 +117,13 @@ 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);
+drop table t1;
diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test
index 13fa617b3cf..904d959d148 100644
--- a/mysql-test/t/delete.test
+++ b/mysql-test/t/delete.test
@@ -53,5 +53,11 @@ 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/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/having.test b/mysql-test/t/having.test
index fd972fea1ad..cb6fa85ffde 100644
--- a/mysql-test/t/having.test
+++ b/mysql-test/t/having.test
@@ -63,4 +63,6 @@ drop table t1;
 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 d65b9b1638e..dc3c76f1a91 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -40,9 +40,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;
-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;
+# 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;
@@ -339,7 +339,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;
@@ -776,7 +776,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
@@ -792,7 +792,7 @@ 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;
 
diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test
index 1996c793880..cb80d88aee8 100644
--- a/mysql-test/t/key.test
+++ b/mysql-test/t/key.test
@@ -54,12 +54,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/raid.test b/mysql-test/t/raid.test
index 8b608c1069f..4dbaf84a836 100644
--- a/mysql-test/t/raid.test
+++ b/mysql-test/t/raid.test
@@ -5,6 +5,9 @@ show variables like "have_raid";
 # Test of raided tables
 #
 
+create database test_raid;
+create table test_raid.r1 (i int) raid_type=1;
+drop database test_raid;
 DROP TABLE IF EXISTS t1,t2;
 CREATE TABLE t1 (
 id int unsigned not null auto_increment primary key,
diff --git a/mysql-test/t/repair.test b/mysql-test/t/repair.test
index b901fb3467f..159fc090653 100644
--- a/mysql-test/t/repair.test
+++ b/mysql-test/t/repair.test
@@ -8,3 +8,12 @@ repair table t1 use_frm;
 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/rpl_loaddata.test b/mysql-test/t/rpl_loaddata.test
index 2acb67dfce2..1f34aa9d3f9 100644
--- a/mysql-test/t/rpl_loaddata.test
+++ b/mysql-test/t/rpl_loaddata.test
@@ -10,8 +10,7 @@ create table t1(a int not null auto_increment, b int, primary key(a) );
 load data infile '../../std_data/rpl_loaddata.dat' into table t1;
 
 create temporary table t2 (day date,id int(9),category enum('a','b','c'),name varchar(60));
-#load data infile '../../std_data/rpl_loaddata2.dat' into table t2 fields terminated by ',' optionaly enclosed by '%' escaped by '@' lines terminated by '\n%%\n' ignore 1 lines;
- load data infile '../../std_data/rpl_loaddata2.dat' into table t2 fields terminated by ',' optionally enclosed by '%' escaped by '@' lines terminated by '\n##\n' ignore 1 lines;
+load data infile '../../std_data/rpl_loaddata2.dat' into table t2 fields terminated by ',' optionally enclosed by '%' escaped by '@' lines terminated by '\n##\n' starting by '>' ignore 1 lines;
 
 create table t3 (day date,id int(9),category enum('a','b','c'),name varchar(60));
 insert into t3 select * from t2;
diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test
index 7d5d719592c..7aedc051905 100644
--- a/mysql-test/t/type_decimal.test
+++ b/mysql-test/t/type_decimal.test
@@ -230,3 +230,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 6e6b43ebe3b..7262b8f78d7 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -2,7 +2,7 @@
 # Test of unions
 #
 
-drop table if exists t1,t2,t3;
+drop table if exists t1,t2,t3,t4,t5,t6;
 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);
@@ -98,3 +98,62 @@ set SQL_SELECT_LIMIT=2;
 (select * from t1 limit 1) union (select * from t2 limit 3);
 set SQL_SELECT_LIMIT=DEFAULT;
 drop table t1,t2;
+
+#
+# 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;
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index 359ae815197..a455b308158 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -91,3 +91,35 @@ 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;
+
+#
+# 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 639a28edd38..fa39906fd6a 100644
--- a/mysql-test/t/variables.test
+++ b/mysql-test/t/variables.test
@@ -69,10 +69,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;
@@ -137,6 +137,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
 
@@ -181,9 +185,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;