21 files changed, 450 insertions, 99 deletions
diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh
index 745cde325f9..edfff5c4d46 100644
--- a/mysql-test/mysql-test-run.sh
+++ b/mysql-test/mysql-test-run.sh
@@ -47,13 +47,17 @@ which ()
 
 sleep_until_file_deleted ()
 {
-  file=$1
+  pid=$1;
+  file=$2
   loop=$SLEEP_TIME_FOR_DELETE
   while (test $loop -gt 0)
   do
     if [ ! -r $file ]
     then
-      sleep $SLEEP_TIME_AFTER_RESTART
+      if test $pid != "0"
+      then
+        wait_for_pid $pid
+      fi
       return
     fi
     sleep 1
@@ -79,6 +83,13 @@ sleep_until_file_created ()
   exit 1;
 }
 
+# For the future
+
+wait_for_pid()
+{
+  pid=$1
+}
+
 # No paths below as we can't be sure where the program is!
 
 SED=sed
@@ -152,6 +163,7 @@ TOT_TEST=0
 USERT=0
 SYST=0
 REALT=0
+FAST_START=""
 MYSQL_TMP_DIR=$MYSQL_TEST_DIR/var/tmp
 SLAVE_LOAD_TMPDIR=../../var/tmp #needs to be same length to test logging
 RES_SPACE="      "
@@ -319,7 +331,7 @@ while test $# -gt 0; do
       VALGRIND="valgrind --alignment=8 --leak-check=yes"
       EXTRA_MASTER_MYSQLD_OPT="$EXTRA_MASTER_MYSQLD_OPT --skip-safemalloc"
       EXTRA_SLAVE_MYSQLD_OPT="$EXTRA_SLAVE_MYSQLD_OPT --skip-safemalloc"
-      SLEEP_TIME_AFTER_RESTART=60
+      #SLEEP_TIME_AFTER_RESTART=120
       SLEEP_TIME_FOR_DELETE=60
       ;;
     --valgrind-options=*)
@@ -340,6 +352,9 @@ while test $# -gt 0; do
        --debug=d:t:i:O,$MYSQL_TEST_DIR/var/log/slave.trace"
       EXTRA_MYSQL_TEST_OPT="$EXTRA_MYSQL_TEST_OPT --debug"
       ;;
+    --fast)
+      FAST_START=1
+      ;;
     -- )  shift; break ;;
     --* ) $ECHO "Unrecognized option: $1"; exit 1 ;;
     * ) break ;;
@@ -736,12 +751,19 @@ EOF
 
 manager_term()
 {
-  ident=$1
+  pid=$1
+  ident=$2
   shift
   if [ $USE_MANAGER = 0 ] ; then
-    $MYSQLADMIN --no-defaults -uroot --socket=$MYSQL_TMP_DIR/$ident.sock -O \
-    connect_timeout=5 -O shutdown_timeout=20 shutdown >> $MYSQL_MANAGER_LOG 2>&1
-    return
+    $MYSQLADMIN --no-defaults -uroot --socket=$MYSQL_TMP_DIR/$ident.sock --connect_timeout=5 --shutdown_timeout=20 shutdown >> $MYSQL_MANAGER_LOG 2>&1
+    res=$?
+    # Some systems require an extra connect
+    $MYSQLADMIN --no-defaults -uroot --socket=$MYSQL_TMP_DIR/$ident.sock --connect_timeout=1 ping >> $MYSQL_MANAGER_LOG 2>&1
+    if test $res = 0
+    then
+      wait_for_pid $pid
+    fi
+    return $res
   fi
   $MYSQL_MANAGER_CLIENT $MANAGER_QUIET_OPT --user=$MYSQL_MANAGER_USER \
    --password=$MYSQL_MANAGER_PW  --port=$MYSQL_MANAGER_PORT <<EOF
@@ -983,12 +1005,13 @@ stop_slave ()
   fi
   if [ x$this_slave_running = x1 ]
   then
-    manager_term $slave_ident
+    pid=`$CAT $slave_pid`
+    manager_term $pid $slave_ident
     if [ $? != 0 ] && [ -f $slave_pid ]
     then # try harder!
       $ECHO "slave not cooperating with mysqladmin, will try manual kill"
-      kill `$CAT $slave_pid`
-      sleep_until_file_deleted $slave_pid
+      kill $pid
+      sleep_until_file_deleted $pid $slave_pid
       if [ -f $slave_pid ] ; then
         $ECHO "slave refused to die. Sending SIGKILL"
         kill -9 `$CAT $slave_pid`
@@ -1007,12 +1030,13 @@ stop_master ()
 {
   if [ x$MASTER_RUNNING = x1 ]
   then
-    manager_term master
+    pid=`$CAT $MASTER_MYPID`
+    manager_term $pid master
     if [ $? != 0 ] && [ -f $MASTER_MYPID ]
     then # try harder!
       $ECHO "master not cooperating with mysqladmin, will try manual kill"
-      kill `$CAT $MASTER_MYPID`
-      sleep_until_file_deleted $MASTER_MYPID
+      kill $pid
+      sleep_until_file_deleted $pid $MASTER_MYPID
       if [ -f $MASTER_MYPID ] ; then
         $ECHO "master refused to die. Sending SIGKILL"
         kill -9 `$CAT $MASTER_MYPID`
@@ -1233,14 +1257,19 @@ run_testcase ()
 
 if [ -z "$USE_RUNNING_SERVER" ]
 then
-  # Ensure that no old mysqld test servers are running
-  $MYSQLADMIN --no-defaults --socket=$MASTER_MYSOCK -u root -O connect_timeout=5 -O shutdown_timeout=20 shutdown > /dev/null 2>&1
-  $MYSQLADMIN --no-defaults --socket=$SLAVE_MYSOCK -u root -O connect_timeout=5 -O shutdown_timeout=20 shutdown > /dev/null 2>&1
-  $MYSQLADMIN --no-defaults --host=$hostname --port=$MASTER_MYPORT -u root -O connect_timeout=5 -O shutdown_timeout=20 shutdown > /dev/null 2>&1
-  $MYSQLADMIN --no-defaults --host=$hostname --port=$SLAVE_MYPORT -u root -O connect_timeout=5 -O shutdown_timeout=20 shutdown > /dev/null 2>&1
-  $MYSQLADMIN --no-defaults --host=$hostname --port=`expr $SLAVE_MYPORT + 1` -u root -O connect_timeout=5 -O shutdown_timeout=20 shutdown > /dev/null 2>&1
-  sleep_until_file_deleted $MASTER_MYPID
-  sleep_until_file_deleted $SLAVE_MYPID
+  if [ -z "$FAST_START" ]
+  then
+    # Ensure that no old mysqld test servers are running
+    $MYSQLADMIN --no-defaults --socket=$MASTER_MYSOCK -u root -O connect_timeout=5 -O shutdown_timeout=20 shutdown > /dev/null 2>&1
+    $MYSQLADMIN --no-defaults --socket=$SLAVE_MYSOCK -u root -O connect_timeout=5 -O shutdown_timeout=20 shutdown > /dev/null 2>&1
+    $MYSQLADMIN --no-defaults --host=$hostname --port=$MASTER_MYPORT -u root -O connect_timeout=5 -O shutdown_timeout=20 shutdown > /dev/null 2>&1
+    $MYSQLADMIN --no-defaults --host=$hostname --port=$SLAVE_MYPORT -u root -O connect_timeout=5 -O shutdown_timeout=20 shutdown > /dev/null 2>&1
+    $MYSQLADMIN --no-defaults --host=$hostname --port=`expr $SLAVE_MYPORT + 1` -u root -O connect_timeout=5 -O shutdown_timeout=20 shutdown > /dev/null 2>&1
+    sleep_until_file_deleted 0 $MASTER_MYPID
+    sleep_until_file_deleted 0 $SLAVE_MYPID
+  else
+    rm $MASTER_MYPID $SLAVE_MYPID
+  fi
 
   # Kill any running managers
   if [ -f "$MANAGER_PID_FILE" ]
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 159421e8f88..cba486109ac 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -1,4 +1,4 @@
-drop table if exists t1;
+drop table if exists t1,t2;
 create table t1 (
 col1 int not null auto_increment primary key,
 col2 varchar(30) not null,
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index fec5ece8ddf..809eb22f987 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -198,6 +198,30 @@ a
 select distinct 1 from t1,t3 where t1.a=t3.a;
 1
 1
+explain SELECT distinct t1.a from t1;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	index	NULL	PRIMARY	4	NULL	2	Using index
+explain SELECT distinct t1.a from t1 order by a desc;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	index	NULL	PRIMARY	4	NULL	2	Using index
+explain SELECT t1.a from t1 group by a order by a desc;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	index	NULL	PRIMARY	4	NULL	2	Using index
+explain SELECT distinct t1.a from t1 order by a desc limit 1;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	index	NULL	PRIMARY	4	NULL	2	Using index
+explain SELECT distinct a from t3 order by a desc limit 2;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t3	index	NULL	a	5	NULL	204	Using index
+explain SELECT distinct a,b from t3 order by a+1;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t3	ALL	NULL	NULL	NULL	NULL	204	Using temporary; Using filesort
+explain SELECT distinct a,b from t3 order by a limit 10;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t3	index	NULL	a	5	NULL	204	Using temporary
+explain SELECT a,b from t3 group by a,b order by a+1;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t3	ALL	NULL	NULL	NULL	NULL	204	Using temporary; Using filesort
 drop table t1,t2,t3,t4;
 CREATE TABLE t1 (name varchar(255));
 INSERT INTO t1 VALUES ('aa'),('ab'),('ac'),('ad'),('ae');
diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result
index f1c0de2f88a..811a16fff6c 100644
--- a/mysql-test/r/func_math.result
+++ b/mysql-test/r/func_math.result
@@ -31,9 +31,10 @@ log10(100)	log10(18)	log10(-4)	log10(0)	log10(NULL)
 select pow(10,log10(10)),power(2,4);
 pow(10,log10(10))	power(2,4)
 10.000000	16.000000
+set @@rand_seed1=10000000,@@rand_seed2=1000000;
 select rand(999999),rand();
 rand(999999)	rand()
-0.014231365187309	0.8078568166195
+0.014231365187309	0.028870999839968
 select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(1);
 PI()	sin(pi()/2)	cos(pi()/2)	abs(tan(pi()))	cot(1)	asin(1)	acos(0)	atan(1)
 3.141593	1.000000	0.000000	0.000000	0.64209262	1.570796	1.570796	0.785398
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index b5a35b981db..696d8200b70 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -207,6 +207,14 @@ Documentation		0
 Host communication		0
 kkkkkkkkkkk lllllllllll		3
 Test Procedures		0
+select value,description,COUNT(bug_id) from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA" group by value having COUNT(bug_id) IN (0,2);
+value	description	COUNT(bug_id)
+BBBBBBBBBBBBB - conversion		2
+BBBBBBBBBBBBB - eeeeeeeee		0
+BBBBBBBBBBBBB - generic		2
+Documentation		0
+Host communication		0
+Test Procedures		0
 drop table t1,t2;
 create table t1 (foo int);
 insert into t1 values (1);
@@ -234,6 +242,13 @@ userid	count(*)
 3	3
 2	1
 1	2
+select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3);
+userid	count(*)
+3	3
+1	2
+select userid,count(*) from t1 group by userid desc having 3  IN (1,COUNT(*));
+userid	count(*)
+3	3
 explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	spID	spID	5	NULL	2	Using where; Using index
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 81064143872..467436a2f85 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1021,3 +1021,40 @@ id	code	name
 7	4	Matt
 COMMIT;
 DROP TABLE t1;
+drop table if exists t1,t2;
+create table t1 (n int(10), d int(10)) type=innodb;
+create table t2 (n int(10), d int(10)) type=innodb;
+insert into t1 values(1,1),(1,2);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+n	d
+1	10
+1	10
+select * from t2;
+n	d
+1	30
+2	20
+drop table t1,t2;
+create table t1 (a int, b int) type=innodb;
+insert into t1 values(20,null);
+select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
+t2.b=t3.a;
+b	ifnull(t2.b,"this is null")
+NULL	this is null
+select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
+t2.b=t3.a order by 1;
+b	ifnull(t2.b,"this is null")
+NULL	this is null
+insert into t1 values(10,null);
+select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
+t2.b=t3.a order by 1;
+b	ifnull(t2.b,"this is null")
+NULL	this is null
+NULL	this is null
+drop table t1;
+create table t1 (a varchar(10) not null) type=myisam;
+create table t2 (b varchar(10) not null unique) type=innodb;
+select t1.a from t1,t2 where t1.a=t2.b;
+a
+drop table t1,t2;
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index ce3f7e90f6b..7d1f5bd53f6 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -20,7 +20,7 @@ count(*)
 10
 select count(*) from t2 where t = "bbb";
 count(*)
-10
+50
 select count(*) from t2 where id2 > 90;
 count(*)
 50
@@ -70,71 +70,61 @@ create table t1(id1 int not null  primary key, t varchar(100)) pack_keys = 1;
 create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1;
 delete t1  from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500;
 drop table t1,t2;
-DROP TABLE IF EXISTS a,b,c;
-CREATE TABLE a (
+CREATE TABLE t1 (
 id int(11) NOT NULL default '0',
 name varchar(10) default NULL,
 PRIMARY KEY  (id)
 ) TYPE=MyISAM;
-INSERT INTO a VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
-CREATE TABLE b (
+INSERT INTO t1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
+CREATE TABLE t2 (
 id int(11) NOT NULL default '0',
 name varchar(10) default NULL,
 PRIMARY KEY  (id)
 ) TYPE=MyISAM;
-INSERT INTO b VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
-CREATE TABLE c (
+INSERT INTO t2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
+CREATE TABLE t3 (
 id int(11) NOT NULL default '0',
 mydate datetime default NULL,
 PRIMARY KEY  (id)
 ) TYPE=MyISAM;
-INSERT INTO c VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22
+INSERT INTO t3 VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22
 00:00:00'),(7,'2002-07-22 00:00:00');
-delete a,b,c from a,b,c
-where to_days(now())-to_days(c.mydate)>=30
-and c.id=a.id and c.id=b.id;
-select * from c;
+delete t1,t2,t3 from t1,t2,t3 where to_days(now())-to_days(t3.mydate)>=30 and t3.id=t1.id and t3.id=t2.id;
+select * from t3;
 id	mydate
 1	2002-02-04 00:00:00
 5	2002-05-12 00:00:00
 6	2002-06-22 00:00:00
 7	2002-07-22 00:00:00
-DROP TABLE IF EXISTS a,b,c;
-drop table if exists parent, child;
-CREATE TABLE IF NOT EXISTS `parent` (
+DROP TABLE IF EXISTS t1,t2,t3;
+CREATE TABLE IF NOT EXISTS `t1` (
 `id` int(11) NOT NULL auto_increment,
 `tst` text,
 `tst1` text,
 PRIMARY KEY  (`id`)
 ) TYPE=MyISAM;
-CREATE TABLE IF NOT EXISTS `child` (
+CREATE TABLE IF NOT EXISTS `t2` (
 `ID` int(11) NOT NULL auto_increment,
 `ParId` int(11) default NULL,
 `tst` text,
 `tst1` text,
 PRIMARY KEY  (`ID`),
-KEY `IX_ParId_child` (`ParId`),
-FOREIGN KEY (`ParId`) REFERENCES `test.parent` (`id`)
+KEY `IX_ParId_t2` (`ParId`),
+FOREIGN KEY (`ParId`) REFERENCES `t1` (`id`)
 ) TYPE=MyISAM;
-INSERT INTO parent(tst,tst1)
-VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE");
-INSERT INTO child(ParId)
-VALUES(1), (2), (3);
-select * from child;
+INSERT INTO t1(tst,tst1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE");
+INSERT INTO t2(ParId) VALUES(1), (2), (3);
+select * from t2;
 ID	ParId	tst	tst1
 1	1	NULL	NULL
 2	2	NULL	NULL
 3	3	NULL	NULL
-UPDATE child, parent
-SET child.tst = parent.tst,
-child.tst1 = parent.tst1
-WHERE child.ParId = parent.Id;
-select * from child;
+UPDATE t2, t1 SET t2.tst = t1.tst, t2.tst1 = t1.tst1 WHERE t2.ParId = t1.Id;
+select * from t2;
 ID	ParId	tst	tst1
 1	1	MySQL	MySQL AB
 2	2	MSSQL	Microsoft
 3	3	ORACLE	ORACLE
-drop table parent, child;
 drop table if exists t1, t2 ;
 create table t1 (n numeric(10));
 create table t2 (n numeric(10));
@@ -176,3 +166,73 @@ n	d
 2	20
 unlock tables;
 drop table t1,t2;
+set sql_safe_updates=1;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
+You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
+set sql_safe_updates=0;
+drop table t1,t2;
+set timestamp=1038401397;
+create table t1 (n int(10) not null primary key, d int(10), t timestamp);
+create table t2 (n int(10) not null primary key, d int(10), t timestamp);
+insert into t1 values(1,1,NULL);
+insert into t2 values(1,10,NULL),(2,20,NULL);
+set timestamp=1038000000;
+UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
+select * from t1;
+n	d	t
+1	10	20021123002000
+select * from t2;
+n	d	t
+1	10	20021127154957
+2	20	20021127154957
+UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n;
+You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '1=2 WHERE t1.n=t2.n' at line 1
+drop table t1,t2;
+set timestamp=0;
+set sql_safe_updates=0;
+create table t1 (n int(10) not null primary key, d int(10));
+create table t2 (n int(10) not null primary key, d int(10));
+insert into t1 values(1,1), (3,3);
+insert into t2 values(1,10),(2,20);
+UPDATE t2 left outer join t1 on t1.n=t2.n  SET t1.d=t2.d;
+select * from t1;
+n	d
+1	10
+3	3
+select * from t2;
+n	d
+1	10
+2	20
+drop table t1,t2;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1),(1,2);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+n	d
+1	10
+1	10
+select * from t2;
+n	d
+1	30
+2	20
+drop table t1,t2;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1),(3,2);
+insert into t2 values(1,10),(1,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+n	d
+1	10
+3	2
+select * from t2;
+n	d
+1	30
+1	30
+drop table t1,t2;
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index 07724a56025..cdea66cbf58 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -6,7 +6,7 @@ select 1 | NULL,1 & NULL,1+NULL,1-NULL;
 NULL	NULL	NULL	NULL
 select NULL=NULL,NULL<>NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0;
 NULL=NULL	NULL<>NULL	IFNULL(NULL,1.1)+0	IFNULL(NULL,1) | 0
-NULL	NULL	1.1	1
+NULL	NULL	1	1
 select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null;
 strcmp("a",NULL)	(1<NULL)+0.0	NULL regexp "a"	null like "a%"	"a%" like null
 NULL	NULL	NULL	NULL	NULL
@@ -56,3 +56,20 @@ indexed_field
 NULL
 NULL
 DROP TABLE t1;
+create table t1 (a int, b int) type=myisam;
+insert into t1 values(20,null);
+select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
+t2.b=t3.a;
+b	ifnull(t2.b,"this is null")
+NULL	this is null
+select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
+t2.b=t3.a order by 1;
+b	ifnull(t2.b,"this is null")
+NULL	this is null
+insert into t1 values(10,null);
+select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
+t2.b=t3.a order by 1;
+b	ifnull(t2.b,"this is null")
+NULL	this is null
+NULL	this is null
+drop table t1;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 94c93dbc3dc..46343eb8248 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3478,3 +3478,16 @@ a	a	a
 2	2	2
 3	3	3
 drop table t1;
+drop table if exists t1,t2;
+CREATE TABLE t1 (  aa char(2),  id int(11) NOT NULL auto_increment,  t2_id int(11) NOT NULL default '0',  PRIMARY KEY  (id),  KEY replace_id (t2_id)) TYPE=MyISAM;
+INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522);
+CREATE TABLE t2 ( id int(11) NOT NULL auto_increment,  PRIMARY KEY  (id)) TYPE=MyISAM;
+INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);
+select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0   order by t1.id   LIMIT 0, 5;
+aa	id	t2_id	id
+2	8299	2517	2517
+3	8301	2518	2518
+4	8302	2519	2519
+5	8303	2520	2520
+6	8304	2521	2521
+drop table if exists t1,t2;
diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result
index 45f879e182b..7c8d10cf0a6 100644
--- a/mysql-test/r/temp_table.result
+++ b/mysql-test/r/temp_table.result
@@ -72,6 +72,11 @@ id	val	elt(two.val,'one','two')
 2	1	one
 4	2	two
 drop table t1,t2;
+create temporary table t1 (a int not null);
+insert into t1 values (1),(1);
+alter table t1 add primary key (a);
+Duplicate entry '1' for key 1
+drop table t1;
 drop table if exists t1;
 CREATE TABLE t1 (
 d datetime default NULL
diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result
index bd5e9f04992..088f3b205b9 100644
--- a/mysql-test/r/type_timestamp.result
+++ b/mysql-test/r/type_timestamp.result
@@ -1,11 +1,31 @@
-drop table if exists t1;
-CREATE TABLE t1 ( t timestamp);
+drop table if exists t1,t2;
+CREATE TABLE t1 (a int, t timestamp);
+CREATE TABLE t2 (a int, t datetime);
 SET TIMESTAMP=1234;
-insert into t1 values(NULL);
+insert into t1 values(1,NULL);
+insert into t1 values(2,"2002-03-03");
+SET TIMESTAMP=1235;
+insert into t1 values(3,NULL);
+SET TIMESTAMP=1236;
+insert into t1 (a) values(4);
+insert into t2 values(5,"2002-03-04"),(6,NULL),(7,"2002-03-05"),(8,"00-00-00");
+SET TIMESTAMP=1237;
+insert into t1 select * from t2;
+SET TIMESTAMP=1238;
+insert into t1 (a) select a+1 from t2 where a=8;
 select * from t1;
-t
-19700101032034
-drop table t1;
+a	t
+1	19700101032034
+2	20020303000000
+3	19700101032035
+4	19700101032036
+5	20020304000000
+6	19700101032037
+7	20020305000000
+8	00000000000000
+9	19700101032038
+drop table t1,t2;
+SET TIMESTAMP=1234;
 CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, PRIMARY KEY (id));
 INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00");
 SELECT stamp FROM t1 WHERE id="myKey";
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index 2b329f3ec6e..1c3987e2a31 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -2,7 +2,7 @@
 # Test of alter table
 #
 
-drop table if exists t1;
+drop table if exists t1,t2;
 create table t1 (
 col1 int not null auto_increment primary key,
 col2 varchar(30) not null,
diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index aaffea3c5a5..7f75b6b1687 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -88,6 +88,16 @@ select distinct t1.a from t1,t3 where t1.a=t3.a;
 #flush status;
 select distinct 1 from t1,t3 where t1.a=t3.a;
 #show status like 'Handler%';
+
+explain SELECT distinct t1.a from t1;
+explain SELECT distinct t1.a from t1 order by a desc;
+explain SELECT t1.a from t1 group by a order by a desc;
+explain SELECT distinct t1.a from t1 order by a desc limit 1;
+explain SELECT distinct a from t3 order by a desc limit 2;
+explain SELECT distinct a,b from t3 order by a+1;
+explain SELECT distinct a,b from t3 order by a limit 10;
+explain SELECT a,b from t3 group by a,b order by a+1;
+
 drop table t1,t2,t3,t4;
 
 CREATE TABLE t1 (name varchar(255));
diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test
index 74e8a5ce092..bd125dafd53 100644
--- a/mysql-test/t/func_math.test
+++ b/mysql-test/t/func_math.test
@@ -13,6 +13,7 @@ select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL);
 select log2(8),log2(15),log2(-2),log2(0),log2(NULL);
 select log10(100),log10(18),log10(-4),log10(0),log10(NULL);
 select pow(10,log10(10)),power(2,4);
+set @@rand_seed1=10000000,@@rand_seed2=1000000;
 select rand(999999),rand();
 select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(1);
 select degrees(pi()),radians(360);
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 4670feca500..4682463c11f 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -208,6 +208,7 @@ INSERT INTO t2 VALUES ('Web Interface','AAAAAAAA-AAA','id0001','','');
 INSERT INTO t2 VALUES ('Host communication','AAAAA','id0001','','');
 select value,description,bug_id from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA";
 select value,description,COUNT(bug_id) from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA" group by value;
+select value,description,COUNT(bug_id) from t2 left join t1 on t2.program=t1.product and t2.value=t1.component where program="AAAAA" group by value having COUNT(bug_id) IN (0,2);
 
 drop table t1,t2;
 
@@ -236,6 +237,8 @@ CREATE TABLE t1 (
 INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3);
 explain select userid,count(*) from t1 group by userid desc;
 select userid,count(*) from t1 group by userid desc;
+select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3);
+select userid,count(*) from t1 group by userid desc having 3  IN (1,COUNT(*));
 explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
 explain select spid,count(*) from t1 where spid between 1 and 2 group by spid;
 select spid,count(*) from t1 where spid between 1 and 2 group by spid;
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index e6d57899082..eb5b0c9efd2 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -660,3 +660,39 @@ insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
 select id, code, name from t1 order by id;
 COMMIT;
 DROP TABLE t1;
+
+#
+# Test of multi-table-update
+#
+drop table if exists t1,t2;
+create table t1 (n int(10), d int(10)) type=innodb;
+create table t2 (n int(10), d int(10)) type=innodb;
+insert into t1 values(1,1),(1,2);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+select * from t2;
+drop table t1,t2;
+
+#
+# Testing of IFNULL
+#
+create table t1 (a int, b int) type=innodb;
+insert into t1 values(20,null);
+select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
+t2.b=t3.a;
+select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
+t2.b=t3.a order by 1;
+insert into t1 values(10,null);
+select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
+t2.b=t3.a order by 1;
+drop table t1;
+
+#
+# Test of read_through not existing const_table
+#
+
+create table t1 (a varchar(10) not null) type=myisam;
+create table t2 (b varchar(10) not null unique) type=innodb;
+select t1.a from t1,t2 where t1.a=t2.b;
+drop table t1,t2;
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index b3a51ff65bc..b79b0749c82 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -1,9 +1,6 @@
 #
-# Only run the test if we are using --big-test, because this test takes a
-# long time
+# Test of update statement that uses many tables.
 #
-#-- require r/big_test.require
-#eval select $BIG_TEST as using_big_test;
 
 drop table if exists t1,t2,t3;
 create table t1(id1 int not null auto_increment primary key, t char(12));
@@ -80,67 +77,59 @@ while ($1)
 enable_query_log;
 delete t1  from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500;
 drop table t1,t2;
-DROP TABLE IF EXISTS a,b,c;
-CREATE TABLE a (
+
+CREATE TABLE t1 (
   id int(11) NOT NULL default '0',
   name varchar(10) default NULL,
   PRIMARY KEY  (id)
 ) TYPE=MyISAM;
-INSERT INTO a VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
-CREATE TABLE b (
+INSERT INTO t1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
+CREATE TABLE t2 (
   id int(11) NOT NULL default '0',
   name varchar(10) default NULL,
   PRIMARY KEY  (id)
 ) TYPE=MyISAM;
-INSERT INTO b VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
-CREATE TABLE c (
+INSERT INTO t2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
+CREATE TABLE t3 (
   id int(11) NOT NULL default '0',
   mydate datetime default NULL,
   PRIMARY KEY  (id)
 ) TYPE=MyISAM;
-INSERT INTO c VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22
+INSERT INTO t3 VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22
 00:00:00'),(7,'2002-07-22 00:00:00');
-delete a,b,c from a,b,c
-where to_days(now())-to_days(c.mydate)>=30
-and c.id=a.id and c.id=b.id;
-select * from c;
-DROP TABLE IF EXISTS a,b,c;
-drop table if exists parent, child;
-CREATE TABLE IF NOT EXISTS `parent` (
+delete t1,t2,t3 from t1,t2,t3 where to_days(now())-to_days(t3.mydate)>=30 and t3.id=t1.id and t3.id=t2.id;
+select * from t3;
+DROP TABLE IF EXISTS t1,t2,t3;
+
+CREATE TABLE IF NOT EXISTS `t1` (
   `id` int(11) NOT NULL auto_increment,
   `tst` text,
   `tst1` text,
   PRIMARY KEY  (`id`)
 ) TYPE=MyISAM;
 
-CREATE TABLE IF NOT EXISTS `child` (
+CREATE TABLE IF NOT EXISTS `t2` (
   `ID` int(11) NOT NULL auto_increment,
   `ParId` int(11) default NULL,
   `tst` text,
   `tst1` text,
   PRIMARY KEY  (`ID`),
-  KEY `IX_ParId_child` (`ParId`),
-  FOREIGN KEY (`ParId`) REFERENCES `test.parent` (`id`)
+  KEY `IX_ParId_t2` (`ParId`),
+  FOREIGN KEY (`ParId`) REFERENCES `t1` (`id`)
 ) TYPE=MyISAM;
 
-INSERT INTO parent(tst,tst1)
-VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE");
+INSERT INTO t1(tst,tst1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE");
 
-INSERT INTO child(ParId)
-VALUES(1), (2), (3);
+INSERT INTO t2(ParId) VALUES(1), (2), (3);
 
-select * from child;
-
-UPDATE child, parent
-SET child.tst = parent.tst,
-child.tst1 = parent.tst1
-WHERE child.ParId = parent.Id;
+select * from t2;
 
-select * from child;
+UPDATE t2, t1 SET t2.tst = t1.tst, t2.tst1 = t1.tst1 WHERE t2.ParId = t1.Id;
 
+select * from t2;
 
-drop table parent, child;
 drop table if exists t1, t2 ;
+
 create table t1 (n numeric(10));
 create table t2 (n numeric(10));
 insert into t2 values (1),(2),(4),(8),(16),(32);
@@ -174,3 +163,54 @@ select * from t1;
 select * from t2;
 unlock tables;
 drop table t1,t2;
+
+#
+# Test safe updates and timestamps
+#
+set sql_safe_updates=1;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1);
+insert into t2 values(1,10),(2,20);
+--error 1175
+UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
+set sql_safe_updates=0;
+drop table t1,t2;
+set timestamp=1038401397;
+create table t1 (n int(10) not null primary key, d int(10), t timestamp);
+create table t2 (n int(10) not null primary key, d int(10), t timestamp);
+insert into t1 values(1,1,NULL);
+insert into t2 values(1,10,NULL),(2,20,NULL);
+set timestamp=1038000000;
+UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
+select * from t1;
+select * from t2;
+--error 1064
+UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n;
+drop table t1,t2;
+set timestamp=0;
+set sql_safe_updates=0;
+create table t1 (n int(10) not null primary key, d int(10));
+create table t2 (n int(10) not null primary key, d int(10));
+insert into t1 values(1,1), (3,3);
+insert into t2 values(1,10),(2,20);
+UPDATE t2 left outer join t1 on t1.n=t2.n  SET t1.d=t2.d;
+select * from t1;
+select * from t2;
+drop table t1,t2;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1),(1,2);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+select * from t2;
+drop table t1,t2;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1),(3,2);
+insert into t2 values(1,10),(1,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+select * from t2;
+drop table t1,t2;
diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test
index 8bd9e806118..ad32e0be6ff 100644
--- a/mysql-test/t/null.test
+++ b/mysql-test/t/null.test
@@ -34,3 +34,17 @@ SELECT * FROM t1 WHERE indexed_field=NULL;
 SELECT * FROM t1 WHERE indexed_field IS NULL;
 SELECT * FROM t1 WHERE indexed_field<=>NULL;
 DROP TABLE t1;
+
+#
+# Testing of IFNULL
+#
+create table t1 (a int, b int) type=myisam;
+insert into t1 values(20,null);
+select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
+t2.b=t3.a;
+select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
+t2.b=t3.a order by 1;
+insert into t1 values(10,null);
+select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
+t2.b=t3.a order by 1;
+drop table t1;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 34ad496f285..64287dc4170 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -1799,5 +1799,11 @@ select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a));
 # natural join
 select * from t1 natural join (t1 as t2 left join t1 as t3 using (a));
 select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1;
-
 drop table t1;
+drop table if exists t1,t2;
+CREATE TABLE t1 (  aa char(2),  id int(11) NOT NULL auto_increment,  t2_id int(11) NOT NULL default '0',  PRIMARY KEY  (id),  KEY replace_id (t2_id)) TYPE=MyISAM;
+INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522);
+CREATE TABLE t2 ( id int(11) NOT NULL auto_increment,  PRIMARY KEY  (id)) TYPE=MyISAM;
+INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);
+select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0   order by t1.id   LIMIT 0, 5;
+drop table if exists t1,t2;
diff --git a/mysql-test/t/temp_table.test b/mysql-test/t/temp_table.test
index 10168cf13c7..665e690a322 100644
--- a/mysql-test/t/temp_table.test
+++ b/mysql-test/t/temp_table.test
@@ -62,6 +62,15 @@ select one.id, two.val, elt(two.val,'one','two') from t1 one, t2 two where two.i
 drop table t1,t2;
 
 #
+# Test of failed ALTER TABLE on temporary table
+#
+create temporary table t1 (a int not null);
+insert into t1 values (1),(1);
+-- error 1062
+alter table t1 add primary key (a);
+drop table t1;
+
+#
 # In MySQL 4.0.4 doing a GROUP BY on a NULL column created a disk based
 # temporary table when a memory based one would be good enough.
 
diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test
index 19af6b0c49c..2929184df93 100644
--- a/mysql-test/t/type_timestamp.test
+++ b/mysql-test/t/type_timestamp.test
@@ -2,14 +2,25 @@
 # Test timestamp
 #
 
-drop table if exists t1;
-CREATE TABLE t1 ( t timestamp);
+drop table if exists t1,t2;
+CREATE TABLE t1 (a int, t timestamp);
+CREATE TABLE t2 (a int, t datetime);
 SET TIMESTAMP=1234;
-insert into t1 values(NULL);
+insert into t1 values(1,NULL);
+insert into t1 values(2,"2002-03-03");
+SET TIMESTAMP=1235;
+insert into t1 values(3,NULL);
+SET TIMESTAMP=1236;
+insert into t1 (a) values(4);
+insert into t2 values(5,"2002-03-04"),(6,NULL),(7,"2002-03-05"),(8,"00-00-00");
+SET TIMESTAMP=1237;
+insert into t1 select * from t2;
+SET TIMESTAMP=1238;
+insert into t1 (a) select a+1 from t2 where a=8;
 select * from t1;
-drop table t1;
-
+drop table t1,t2;
 
+SET TIMESTAMP=1234;
 CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, PRIMARY KEY (id));
 INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00");
 SELECT stamp FROM t1 WHERE id="myKey";