Merge with 4.0.17

29 files changed, 457 insertions, 18 deletions

diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test
index 6cf286f86c0..f2a769d9f06 100644
--- a/mysql-test/t/auto_increment.test
+++ b/mysql-test/t/auto_increment.test
@@ -74,6 +74,25 @@ check table t1;
 drop table t1;
 
 #
+# Test negative values (Bug #1366)
+#
+
+create table t1 (a int not null auto_increment primary key);
+insert into t1 values (NULL);
+insert into t1 values (-1);
+select last_insert_id();
+insert into t1 values (NULL);
+select * from t1;
+drop table t1;
+
+create table t1 (a int not null auto_increment primary key) /*!41002 type=heap */;
+insert into t1 values (NULL);
+insert into t1 values (-1);
+select last_insert_id();
+insert into t1 values (NULL);
+select * from t1;
+drop table t1;
+#
 # last_insert_id() madness
 #
 create table t1 (i tinyint unsigned not null auto_increment primary key);
diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test
index 44bb753d6a1..835253e3134 100644
--- a/mysql-test/t/bdb.test
+++ b/mysql-test/t/bdb.test
@@ -789,3 +789,22 @@ select * from t1 where x <= 10 and x >= 7 order by x desc;
 select * from t1 where x <= 8 and x >= 5 order by x desc;
 select * from t1 where x < 8 and x > 5 order by x desc;
 drop table t1;
+
+#
+# Test of multi-table-updates (bug #1980).
+#
+
+create table t1 ( c char(8) not null ) type=bdb;
+insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
+insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
+
+alter table t1 add b char(8) not null;
+alter table t1 add a char(8) not null;
+alter table t1 add primary key (a,b,c);
+update t1 set a=c, b=c;
+
+create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) type=bdb;
+insert into t2 select * from t1;
+
+delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
+drop table t1,t2;
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 17ed4c03b5a..ce89cbe1b22 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -370,3 +370,11 @@ insert into t1 values (1);
 select max(a) as b from t1 having b=1;
 select a from t1 having a=1;
 drop table t1;
+#
+# Bug #1972: test for bit_and(), bit_or() and negative values
+# 
+create table t1 (col int);
+insert into t1 values (-1), (-2), (-3);
+select bit_and(col), bit_or(col) from t1;
+select SQL_BIG_RESULT bit_and(col), bit_or(col) from t1 group by col;
+drop table t1;
diff --git a/mysql-test/t/func_op.test b/mysql-test/t/func_op.test
index cb6ca58f193..33a2884b424 100644
--- a/mysql-test/t/func_op.test
+++ b/mysql-test/t/func_op.test
@@ -7,3 +7,12 @@ explain extended select 1+1,1-1,1+1*2,8/5,8%5,mod(8,5),mod(8,5)|0,-(1+1)*-2;
 select 1 | (1+1),5 & 3,bit_count(7) ;
 explain extended select 1 | (1+1),5 & 3,bit_count(7) ;
 select 1 << 32,1 << 63, 1 << 64, 4 >> 2, 4 >> 63, 1<< 63 >> 60;
+#
+# bug #1993: bit functions must be unsigned
+#
+select -1 | 0, -1 ^ 0, -1 & 0;
+select -1 | 1, -1 ^ 1, -1 & 1;
+select  1 | -1,  1 ^ -1,  1 & -1;
+select  0 | -1,  0 ^ -1,  0 & -1;
+select -1 >> 0, -1 << 0;
+select -1 >> 1, -1 << 1;
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index 0003f14c9ca..ad7b9b21b51 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -61,11 +61,13 @@ select aes_decrypt("a","a");
 select aes_decrypt(aes_encrypt("","a"),"a");
 select repeat('monty',5),concat('*',space(5),'*');
 select reverse('abc'),reverse('abcd');
-select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12');
-select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12');
+select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'), rpad(11, 10 , 22), rpad("ab", 10, 22);
+select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'), lpad(11, 10 , 22);
 select rpad(741653838,17,'0'),lpad(741653838,17,'0');
 select rpad('abcd',7,'ab'),lpad('abcd',7,'ab');
 select rpad('abcd',1,'ab'),lpad('abcd',1,'ab');
+select rpad('STRING', 20, CONCAT('p','a','d') );
+select lpad('STRING', 20, CONCAT('p','a','d') );
 
 select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD');
 select least(1,2,3) | greatest(16,32,8), least(5,4)*1,greatest(-1.0,1.0)*1,least(3,2,1)*1.0,greatest(1,1.1,1.0),least("10",9),greatest("A","B","0");
@@ -77,6 +79,7 @@ select quote('\'\"\\test');
 select quote(concat('abc\'', '\\cba'));
 select quote(1/0), quote('\0\Z');
 select length(quote(concat(char(0),"test")));
+select hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))));
 
 #
 # Wrong usage of functions
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 6f114fe18ae..06d0ff3fa1d 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -42,6 +42,18 @@ select yearweek("2000-01-06",1) as '2000', yearweek("2001-01-06",1) as '2001', y
 select week(19981231,2), week(19981231,3), week(20000101,2), week(20000101,3);
 select week(20001231,2),week(20001231,3);
 
+select week(19981231,0) as '0', week(19981231,1) as '1', week(19981231,2) as '2', week(19981231,3) as '3', week(19981231,4) as '4', week(19981231,5) as '5', week(19981231,6) as '6', week(19981231,7) as '7';
+select week(20000101,0) as '0', week(20000101,1) as '1', week(20000101,2) as '2', week(20000101,3) as '3', week(20000101,4) as '4', week(20000101,5) as '5', week(20000101,6) as '6', week(20000101,7) as '7';
+select week(20000106,0) as '0', week(20000106,1) as '1', week(20000106,2) as '2', week(20000106,3) as '3', week(20000106,4) as '4', week(20000106,5) as '5', week(20000106,6) as '6', week(20000106,7) as '7';
+select week(20001231,0) as '0', week(20001231,1) as '1', week(20001231,2) as '2', week(20001231,3) as '3', week(20001231,4) as '4', week(20001231,5) as '5', week(20001231,6) as '6', week(20001231,7) as '7';
+select week(20010101,0) as '0', week(20010101,1) as '1', week(20010101,2) as '2', week(20010101,3) as '3', week(20010101,4) as '4', week(20010101,5) as '5', week(20010101,6) as '6', week(20010101,7) as '7';
+
+select yearweek(20001231,0), yearweek(20001231,1), yearweek(20001231,2), yearweek(20001231,3), yearweek(20001231,4), yearweek(20001231,5), yearweek(20001231,6), yearweek(20001231,7);
+
+set default_week_format = 6;
+select week(20001231), week(20001231,6);
+set default_week_format = 0;
+
 set default_week_format = 2;
 select week(20001231),week(20001231,2),week(20001231,0);
 set default_week_format = 0;
@@ -194,6 +206,13 @@ select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2;
 drop table t1,t2,t3;
 
 #
+# Test unix timestamp
+#
+select @a:=FROM_UNIXTIME(1);
+select unix_timestamp(@a);
+select unix_timestamp('1969-12-01 19:00:01');
+
+#
 # Test types from + INTERVAL
 #
 
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 1e65bef19b7..d6d1922c10f 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -432,3 +432,27 @@ select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(q
 select count(*), case interval(qty,2,3,4,5,6,7,8) when -1 then NULL when 0 then "zero" when 1 then "one" when 2 then "two" end as category from t1 group by category;
 select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category;
 drop table t1;
+#
+# Tests for bug #1355: 'Using filesort' is missing in EXPLAIN when ORDER BY
+# NULL is used.
+#
+CREATE TABLE t1 (
+  userid int(10) unsigned,
+  score smallint(5) unsigned,
+  key (score)
+);
+INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3);
+# Here we select unordered GROUP BY into a temporary talbe, 
+# and then sort it with filesort (GROUP BY in MySQL 
+# implies sorted order of results)
+SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
+EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
+DROP TABLE t1;
+CREATE TABLE t1 (
+  i int(11) default NULL,
+  j int(11) default NULL
+);
+INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5);
+SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
+explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
+DROP TABLE t1;
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index 6f7a0e100c3..5add6247eaa 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -902,6 +902,37 @@ update t1 set a=a+10+b where a=1 order by b;
 select * from t1 order by a,b;
 drop table t1;
 
+#
+# Test of multi-table-updates (bug #1980).
+#
+
+create table t1 ( c char(8) not null ) type=innodb;
+insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
+insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
+
+alter table t1 add b char(8) not null;
+alter table t1 add a char(8) not null;
+alter table t1 add primary key (a,b,c);
+update t1 set a=c, b=c;
+
+create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) type=innodb;
+insert into t2 select * from t1;
+
+delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
+drop table t1,t2;
+
+#
+# test autoincrement with TRUNCATE
+#
+
+SET AUTOCOMMIT=1;
+create table t1 (a integer auto_increment primary key) type=innodb;
+insert into t1 (a) values (NULL),(NULL);
+truncate table t1;
+insert into t1 (a) values (NULL),(NULL);
+SELECT * from t1;
+drop table t1;
+
 
 CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;
 CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx
diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test
index 62d277bfad5..73b5f453bb4 100644
--- a/mysql-test/t/insert.test
+++ b/mysql-test/t/insert.test
@@ -71,7 +71,7 @@ drop table t1;
 create table t1 (email varchar(50));
 insert into t1 values ('sasha@mysql.com'),('monty@mysql.com'),('foo@hotmail.com'),('foo@aol.com'),('bar@aol.com');
 create table t2(id int not null auto_increment primary key, t2 varchar(50), unique(t2));
-insert into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1;
+insert delayed into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1;
 select * from t2;
 drop table t1,t2;
 
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index d048208a8ff..4ffe1c075b6 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -438,3 +438,16 @@ select * from t1
          left outer join t2 using (f2)
          left outer join t3 using (f3);
 drop table t1,t2,t3;
+
+create table t1 (a1 int, a2 int);
+create table t2 (b1 int not null, b2 int);
+create table t3 (c1 int, c2 int);
+
+insert into t1 values (1,2), (2,2), (3,2);
+insert into t2 values (1,3), (2,3);
+insert into t3 values (2,4),        (3,4);
+
+select * from t1 left join t2  on  b1 = a1 left join t3  on  c1 = a1  and  b1 is null;
+explain select * from t1 left join t2  on  b1 = a1 left join t3  on  c1 = a1  and  b1 is null;
+
+drop table t1, t2, t3;
diff --git a/mysql-test/t/limit.test b/mysql-test/t/limit.test
index 88a9831aa68..61c57c9b468 100644
--- a/mysql-test/t/limit.test
+++ b/mysql-test/t/limit.test
@@ -22,9 +22,7 @@ select * from t1;
 drop table t1;
 
 create table t1 (i int);
-insert into t1 (i) values(1);
-insert into t1 (i) values(1);
-insert into t1 (i) values(1);
+insert into t1 (i) values(1),(1),(1);
 delete from t1 limit 1;
 update t1 set i=2 limit 1;
 delete from t1 limit 0;
@@ -32,4 +30,22 @@ update t1 set i=3 limit 0;
 select * from t1;
 drop table t1;
 
+# LIMIT 0
+
 select 0 limit 0;
+
+#
+# Test with DELETE, ORDER BY and limit (bug #1024)
+#
+
+CREATE TABLE t1(id int auto_increment primary key, id2 int, index(id2)); 
+INSERT INTO t1 (id2) values (0),(0),(0);
+DELETE FROM t1 WHERE id=1;
+INSERT INTO t1 SET id2=0;
+SELECT * FROM t1; 
+DELETE FROM t1 WHERE id2 = 0 ORDER BY id LIMIT 1; 
+# should have deleted WHERE id=2 
+SELECT * FROM t1; 
+DELETE FROM t1 WHERE id2 = 0 ORDER BY id desc LIMIT 1; 
+SELECT * FROM t1;
+DROP TABLE t1;
diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test
index 1720ae69bbb..aa0ea0a2f55 100644
--- a/mysql-test/t/loaddata.test
+++ b/mysql-test/t/loaddata.test
@@ -20,3 +20,14 @@ create table t1 (a text, b text);
 load data infile '../../std_data/loaddata2.dat' into table t1 fields terminated by ',' enclosed by '''';
 select concat('|',a,'|'), concat('|',b,'|') from t1;
 drop table t1;
+
+create table t1 (a int, b char(10));
+load data infile '../../std_data/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines;
+select * from t1;
+truncate table t1;
+load data infile '../../std_data/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines;
+
+# The empty line last comes from the end line field in the file
+select * from t1;
+drop table t1;
+
diff --git a/mysql-test/t/lowercase_table.test b/mysql-test/t/lowercase_table.test
index 28e45bde5c5..601f1734ac6 100644
--- a/mysql-test/t/lowercase_table.test
+++ b/mysql-test/t/lowercase_table.test
@@ -3,21 +3,24 @@
 #
 
 --disable_warnings
-drop table if exists t1,t2,t3;
+drop table if exists t1,t2,t3,t4,T1;
 --enable_warnings
 
 create table T1 (id int primary key, Word varchar(40) not null, Index(Word));
+create table t4 (id int primary key, Word varchar(40) not null);
 INSERT INTO T1 VALUES (1, 'a'), (2, 'b'), (3, 'c');
+INSERT INTO T4 VALUES(1,'match');
 SELECT * FROM t1;
 SELECT T1.id from T1 LIMIT 1;
 SELECT T2.id from t1 as T2 LIMIT 1;
+SELECT * from t1 left join t4 on (test.t1.id= TEST.t4.id) where TEST.t1.id >= test.t4.id;
 # This gave an error in 4.0, but it's fixed in 4.1
 SELECT T2.id from t1 as t2 LIMIT 1;
 RENAME TABLE T1 TO T2;
 ALTER TABLE T2 ADD new_col int not null;
 ALTER TABLE T2 RENAME T3;
 show tables like 't_';
-drop table t3;
+drop table t3,t4;
 #
 # Test alias
 #
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 115007d9e63..4b69b002342 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -277,6 +277,42 @@ select t1.a, t1.b,t2.a, t2.b from t1 left join t2  on t1.a=t2.a where t1.b=1 and
 drop table t1,t2;
 
 #
+# Test reuse of same table
+#
+
+create table t1 (a int not null auto_increment primary key, b int not null);
+insert into t1 (b) values (1),(2),(3),(4);
+update t1, t1 as t2 set t1.b=t2.b+1 where t1.a=t2.a;
+select * from t1;
+drop table t1;
+
+# Test multi-update and multi-delete with impossible where
+
+drop table if exists t1, t2;
+create table t1(id1 smallint(5), field char(5));
+create table t2(id2 smallint(5), field char(5));
+
+insert into t1 values (1, 'a'), (2, 'aa');
+insert into t2 values (1, 'b'), (2, 'bb');
+
+select * from t1;
+select * from t2;
+
+update t2 inner join t1 on t1.id1=t2.id2
+  set t2.field=t1.field
+  where 0=1;
+update t2, t1 set t2.field=t1.field
+  where t1.id1=t2.id2 and 0=1;
+
+delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2
+  where 0=1;
+delete t1, t2 from t2,t1 
+  where t1.id1=t2.id2 and 0=1;
+
+drop table t1,t2;
+
+
+#
 # Test for bug #1820.
 #
 
@@ -309,4 +345,4 @@ delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 100*1000;
 select 't2 rows after big delete', count(*) from t2;
 select 't1 rows after big delete', count(*) from t1;
 
-#drop table t1,t2;
+drop table t1,t2;
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 582fae0da36..c926c8e0b0d 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -6,7 +6,29 @@ DROP TABLE IF EXISTS t1, `"t"1`;
 
 CREATE TABLE t1(a int);
 INSERT INTO t1 VALUES (1), (2);
---exec $MYSQL_DUMP --skip-all -X test t1
+--exec $MYSQL_DUMP --skip-comments -X test t1
+DROP TABLE t1;
+
+#
+# Bug #2005
+#
+
+CREATE TABLE t1 (a decimal(240, 20));
+INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"),
+("0987654321098765432109876543210987654321");
+--exec $MYSQL_DUMP --skip-comments test t1
+DROP TABLE t1;
+
+#
+# Bug #2055
+#
+
+CREATE TABLE t1 (a double);
+INSERT INTO t1 VALUES (-9e999999);
+# The following replaces is here because some systems replaces the above
+# double with '-inf' and others with MAX_DOUBLE
+--replace_result (-1.79769313486232e+308) (RES) (NULL) (RES)
+--exec $MYSQL_DUMP --skip-comments test t1
 DROP TABLE t1;
 
 CREATE TABLE t1(a int, b text, c varchar(3));
diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index c93d616b379..d7681e9c2ec 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -254,10 +254,14 @@ select * from mysqltest.t1 where i is null;
 #
 # drop db
 #
+create table t1(a int);
+select * from t1;
+show status like "Qcache_queries_in_cache";
 select * from mysqltest.t1;
 show status like "Qcache_queries_in_cache";
 drop database mysqltest;
 show status like "Qcache_queries_in_cache";
+drop table t1;
 
 #
 # Charset convertion (cp1251_koi8 always present)
diff --git a/mysql-test/t/rpl_EE_error.test b/mysql-test/t/rpl_EE_error.test
new file mode 100644
index 00000000000..de69c4897c3
--- /dev/null
+++ b/mysql-test/t/rpl_EE_error.test
@@ -0,0 +1,30 @@
+# See if an EE_ error in one event of the master's binlog stops replication
+# (it should not: in this configuration the EE_ error is probably not 
+# critical). Example: you do a DROP TABLE on a table which has no MYI file
+# check if START SLAVE, RESET SLAVE, CHANGE MASTER reset Last_slave_error and
+# Last_slave_errno in SHOW SLAVE STATUS (1st and 3rd commands did not: bug 986).
+
+source include/master-slave.inc;
+
+create table t1 (a int) type=myisam;
+flush tables;
+system rm ./var/master-data/test/t1.MYI ;
+drop table t1;
+save_master_pos;
+connection slave;
+sync_with_master;
+
+# Now a real error.
+
+connection master;
+create table t1 (a int, unique(a)) type=myisam;
+set sql_log_bin=0;
+insert into t1 values(2);
+set sql_log_bin=1;
+save_master_pos;
+--error 1062;
+insert into t1 values(1),(2);
+drop table t1;
+save_master_pos;
+connection slave;
+wait_for_slave_to_stop;
diff --git a/mysql-test/t/rpl_multi_update.test b/mysql-test/t/rpl_multi_update.test
new file mode 100644
index 00000000000..c44239594dd
--- /dev/null
+++ b/mysql-test/t/rpl_multi_update.test
@@ -0,0 +1,25 @@
+source include/master-slave.inc;
+drop table if exists t1,t2;
+
+CREATE TABLE t1 (
+ a int unsigned not null auto_increment primary key,
+ b int unsigned,
+) TYPE=MyISAM;
+
+CREATE TABLE t2 (
+ a int unsigned not null auto_increment primary key,
+ b int unsigned
+) TYPE=MyISAM;
+
+INSERT INTO t1 VALUES (NULL, 0);
+INSERT INTO t1 SELECT NULL, 0 FROM t1;
+
+INSERT INTO t2 VALUES (NULL, 0), (NULL,1);
+
+SELECT * FROM t1 ORDER BY a;
+SELECT * FROM t2 ORDER BY a;
+
+UPDATE t1, t2 SET t1.b = t2.b WHERE t1.a = t2.a;
+save_master_pos;
+connection slave;
+sync_with_master;
diff --git a/mysql-test/t/rpl_rotate_logs.test b/mysql-test/t/rpl_rotate_logs.test
index 7560d56af1c..0d447b8d1a3 100644
--- a/mysql-test/t/rpl_rotate_logs.test
+++ b/mysql-test/t/rpl_rotate_logs.test
@@ -130,6 +130,7 @@ while ($1)
  dec $1;
 }
 enable_query_log;
+select count(*) from t3 where n >= 4;
 create table t4 select * from temp_table;
 show binary logs;
 show master status;
diff --git a/mysql-test/t/select_found.test b/mysql-test/t/select_found.test
index f787cec6533..3cf736cafb0 100644
--- a/mysql-test/t/select_found.test
+++ b/mysql-test/t/select_found.test
@@ -95,3 +95,17 @@ SELECT FOUND_ROWS();
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 ORDER BY numeropost  LIMIT 0;
 SELECT FOUND_ROWS();
 drop table t1;
+
+#
+# Test problem with impossible WHERE (Bug #1468)
+#
+
+create table t1 (id int, primary key (id));
+insert into t1 values (1), (2), (3), (4), (5);
+select SQL_CALC_FOUND_ROWS * from t1 where id > 3 limit 0, 1;
+select FOUND_ROWS();
+select SQL_CALC_FOUND_ROWS * from t1 where id > 3 AND 1=2 limit 0, 1;
+select FOUND_ROWS();
+select SQL_CALC_FOUND_ROWS * from t1 where id > 6 limit 0, 1;
+select FOUND_ROWS();
+drop table t1;
diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test
index 716eed7abc0..c0a9ce1bbcf 100644
--- a/mysql-test/t/symlink.test
+++ b/mysql-test/t/symlink.test
@@ -93,3 +93,25 @@ select count(*) from mysqltest.t9;
 --replace_result $MYSQL_TEST_DIR TEST_DIR
 show create table mysqltest.t9;
 drop database mysqltest;
+
+#
+# Test changing data dir (Bug #1662)
+#
+
+create table t1 (a int not null) type=myisam;
+disable_query_log;
+eval alter table t1 data directory="$MYSQL_TEST_DIR/var/tmp";
+enable_query_log;
+--replace_result $MYSQL_TEST_DIR TEST_DIR
+show create table t1;
+alter table t1 add b int;
+disable_query_log;
+eval alter table t1 data directory="$MYSQL_TEST_DIR/var/log";
+enable_query_log;
+--replace_result $MYSQL_TEST_DIR TEST_DIR
+show create table t1;
+disable_query_log;
+eval alter table t1 index directory="$MYSQL_TEST_DIR/var/log";
+enable_query_log;
+show create table t1;
+drop table t1;
diff --git a/mysql-test/t/timezone.test b/mysql-test/t/timezone.test
index 14facc0374a..ab732c11a34 100644
--- a/mysql-test/t/timezone.test
+++ b/mysql-test/t/timezone.test
@@ -1,9 +1,9 @@
 #
-# Test of timezone handling.  This script must be run with TZ=MEST
+# Test of timezone handling.  This script must be run with TZ=MET
 
--- require r/have_mest_timezone.require
+-- require r/have_met_timezone.require
 disable_query_log;
-show variables like "timezone";
+select FROM_UNIXTIME(24*3600);
 enable_query_log;
 
 # Initialization
@@ -11,6 +11,17 @@ enable_query_log;
 DROP TABLE IF EXISTS t1;
 --enable_warnings
 
+show variables like "timezone";
+
+#
+# Test unix timestamp
+#
+select @a:=FROM_UNIXTIME(1);
+select unix_timestamp(@a);
+
+#
+# Test of some values, including some with daylight saving time
+#
 
 CREATE TABLE t1 (ts int);
 INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00'));
@@ -26,3 +37,4 @@ INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 04:00:01'));
 
 SELECT ts,from_unixtime(ts) FROM t1;
 DROP TABLE t1;
+
diff --git a/mysql-test/t/truncate.test b/mysql-test/t/truncate.test
index dd80baba269..434a1907e42 100644
--- a/mysql-test/t/truncate.test
+++ b/mysql-test/t/truncate.test
@@ -24,3 +24,15 @@ select * from t1;
 drop table t1;
 --error 1146
 truncate non_existing_table;
+
+#
+# test autoincrement with TRUNCATE
+#
+
+create table t1 (a integer auto_increment primary key);
+insert into t1 (a) values (NULL),(NULL);
+truncate table t1;
+insert into t1 (a) values (NULL),(NULL);
+SELECT * from t1;
+drop table t1;
+
diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test
index c9c3284fe0d..70fa2f9b172 100644
--- a/mysql-test/t/type_blob.test
+++ b/mysql-test/t/type_blob.test
@@ -308,3 +308,52 @@ drop table t1;
 create table t1 select load_file('../../std_data/words.dat');
 show full fields from t1;
 drop table t1;
+
+#
+# Test blob's with end space (Bug #1651)
+#
+
+create table t1 (id integer primary key auto_increment, txt text not null, unique index txt_index (txt (20)));
+insert into t1 (txt) values ('Chevy'), ('Chevy ');
+select * from t1 where txt='Chevy';
+select * from t1 where txt='Chevy ';
+select * from t1 where txt='Chevy ' or txt='Chevy';
+select * from t1 where txt='Chevy' or txt='Chevy ';
+select * from t1 where id='1' or id='2';
+insert into t1 (txt) values('Ford');
+select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford';
+select * from t1 where txt='Chevy' or txt='Chevy ';
+select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy';
+select * from t1 where txt in ('Chevy ','Chevy');
+select * from t1 where txt in ('Chevy');
+select * from t1 where txt between 'Chevy' and 'Chevy';
+select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy ';
+select * from t1 where txt between 'Chevy' and 'Chevy ';
+select * from t1 where txt < 'Chevy ';
+select * from t1 where txt <= 'Chevy';
+select * from t1 where txt > 'Chevy';
+select * from t1 where txt >= 'Chevy';
+drop table t1;
+
+create table t1 (id integer primary key auto_increment, txt text, unique index txt_index (txt (20)));
+insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL);
+select * from t1 where txt='Chevy' or txt is NULL;
+select * from t1 where txt='Chevy ';
+select * from t1 where txt='Chevy ' or txt='Chevy';
+select * from t1 where txt='Chevy' or txt='Chevy ';
+select * from t1 where id='1' or id='2';
+insert into t1 (txt) values('Ford');
+select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford';
+select * from t1 where txt='Chevy' or txt='Chevy ';
+select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy';
+select * from t1 where txt in ('Chevy ','Chevy');
+select * from t1 where txt in ('Chevy');
+select * from t1 where txt between 'Chevy' and 'Chevy';
+select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy ';
+select * from t1 where txt between 'Chevy' and 'Chevy ';
+select * from t1 where txt < 'Chevy ';
+select * from t1 where txt < 'Chevy ' or txt is NULL;
+select * from t1 where txt <= 'Chevy';
+select * from t1 where txt > 'Chevy';
+select * from t1 where txt >= 'Chevy';
+drop table t1;
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index 7cedbc087e4..cec2aa3582b 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -7,12 +7,13 @@ drop table if exists t1;
 --enable_warnings
 
 create table t1 (t datetime);
-insert into t1 values(101),(691231),(700101),(991231),(10000101),(99991231),(101000000),(691231000000),(700101000000),(991231235959),(10000101000000),(99991231235959);
+insert into t1 values(101),(691231),(700101),(991231),(10000101),(99991231),(101000000),(691231000000),(700101000000),(991231235959),(10000101000000),(99991231235959),(20030102030460),(20030102036301),(20030102240401),(20030132030401),(20031302030460);
 select * from t1;
 delete from t1 where t > 0;
 optimize table t1;
 check table t1;
-insert into t1 values("000101"),("691231"),("700101"),("991231"),("00000101"),("00010101"),("99991231"),("00101000000"),("691231000000"),("700101000000"),("991231235959"),("10000101000000"),("99991231235959");
+delete from t1;
+insert into t1 values("000101"),("691231"),("700101"),("991231"),("00000101"),("00010101"),("99991231"),("00101000000"),("691231000000"),("700101000000"),("991231235959"),("10000101000000"),("99991231235959"),("20030102030460"),("20030102036301"),("20030102240401"),("20030132030401"),("20031302030460");
 select * from t1;
 drop table t1;
 
diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test
index 36bd8eb48e4..8250f40fc99 100644
--- a/mysql-test/t/type_decimal.test
+++ b/mysql-test/t/type_decimal.test
@@ -244,3 +244,9 @@ CREATE TABLE t1 (a_dec DECIMAL(-1,0));
 CREATE TABLE t1 (a_dec DECIMAL(-2,1));
 --error 1064
 CREATE TABLE t1 (a_dec DECIMAL(-1,1));
+
+#  Zero prepend overflow bug
+create table t1(a decimal(10,4));
+insert into t1 values ("+0000100000000");
+select * from t1;
+drop table t1;
diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test
index bcebb1a5b27..f9e5e6470c6 100644
--- a/mysql-test/t/type_enum.test
+++ b/mysql-test/t/type_enum.test
@@ -26,6 +26,17 @@ show create table t1;
 drop table t1;
 
 #
+# Tests of wrong enum values (bug #2023)
+#
+
+create table t1 (a enum ('0','1'));
+insert into t1 set a='foobar';
+select * from t1;
+update t1 set a = replace(a,'x','y');
+select * from t1;
+drop table t1;
+
+#
 # Bug #2077
 #
 
diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test
index cd76dbe6ab0..fc7cd019171 100644
--- a/mysql-test/t/type_timestamp.test
+++ b/mysql-test/t/type_timestamp.test
@@ -37,8 +37,11 @@ select date_format(a,"%Y %y"),year(a),year(now()) from t1;
 drop table t1;
 
 create table t1 (ix timestamp);
-insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000);
-select ix+0 from t1; 
+insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101);
+select ix+0 from t1;
+delete from t1;
+insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101");
+select ix+0 from t1;
 drop table t1;
 
 CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp);
@@ -72,3 +75,9 @@ set new=1;
 select * from t1;
 drop table t1;
 
+#
+# Bug #1885
+#
+
+--error 1067
+create table t1 (a timestamp default 1);
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 6138bba5a31..fc986b05fa4 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -11,7 +11,7 @@ insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
 CREATE TABLE t2 (a int not null, b char (10) not null);
 insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');
 
-select a,b from t1 union select a,b from t2;
+select a,b from t1 union distinct select a,b from t2;
 select a,b from t1 union all select a,b from t2;
 select a,b from t1 union all select a,b from t2 order by b;
 select a,b from t1 union all select a,b from t2 union select 7,'g';
@@ -247,9 +247,19 @@ SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;
 --error 1234
 (SELECT * FROM t1) UNION all (SELECT SQL_CALC_FOUND_ROWS * FROM t2) LIMIT 1;
 
+create temporary table t1 select a from t1 union select a from t2;
+--error 1093
+create table t1 select a from t1 union select a from t2;
 drop table t1,t2;
 
 #
+# Problem with alias '*' (BUG #1249)
+#
+
+select length(version()) > 1 as `*` UNION select 2;
+
+
+#
 # Test for another bug with UNION and LEFT JOIN
 #
 CREATE TABLE t1 (  id int(3) unsigned default '0') ENGINE=MyISAM;