Merge with 4.0.17

BitKeeper/etc/logging_ok:
  auto-union
Build-tools/Do-compile:
  Auto merged
acinclude.m4:
  Auto merged
configure.in:
  Auto merged
dbug/dbug.c:
  Auto merged
include/config-win.h:
  Auto merged
include/my_base.h:
  Auto merged
include/my_global.h:
  Auto merged
include/my_pthread.h:
  Auto merged
include/my_sys.h:
  Auto merged
include/mysql.h:
  Auto merged
include/mysql_com.h:
  Auto merged
innobase/lock/lock0lock.c:
  Auto merged
innobase/row/row0ins.c:
  Auto merged
innobase/row/row0sel.c:
  Auto merged
innobase/row/row0umod.c:
  Auto merged
innobase/row/row0upd.c:
  Auto merged
myisam/ft_boolean_search.c:
  Auto merged
myisam/mi_check.c:
  Auto merged
myisam/mi_dbug.c:
  Auto merged
myisam/mi_open.c:
  Auto merged
mysql-test/r/auto_increment.result:
  Auto merged
mysql-test/r/bdb.result:
  Auto merged
mysql-test/r/func_op.result:
  Auto merged
Build-tools/Bootstrap:
  Merge with 4.0
client/mysql.cc:
  Merge with 4.0
client/mysqldump.c:
  Merge with 4.0
client/mysqltest.c:
  Use local version
innobase/btr/btr0cur.c:
  Merge with 4.0 (Heikki should check if we should remove btr_cur_update_sec_rec_in_place()
libmysql/libmysql.c:
  Merge with 4.0
libmysqld/lib_sql.cc:
  Merge with 4.0
myisam/mi_key.c:
  Merge with 4.0
myisam/mi_search.c:
  Merge with 4.0
mysql-test/r/binary.result:
  Merge with 4.0
mysql-test/r/func_group.result:
  Merge with 4.0
mysql-test/r/func_str.result:
  Merge with 4.0
mysql-test/r/func_time.result:
  Merge with 4.0
mysql-test/r/group_by.result:
  Merge with 4.0
mysql-test/r/handler.result:
  Merge with 4.0
mysql-test/r/innodb.result:
  Merge with 4.0
mysql-test/r/insert.result:
  Merge with 4.0
mysql-test/r/join_outer.result:
  Merge with 4.0
mysql-test/r/loaddata.result:
  Merge with 4.0
mysql-test/r/lowercase_table.result:
  Merge with 4.0
mysql-test/r/multi_update.result:
  Merge with 4.0
mysql-test/r/mysqldump.result:
  Merge with 4.0
mysql-test/r/query_cache.result:
  Merge with 4.0
mysql-test/r/rpl_max_relay_size.result:
  Merge with 4.0
mysql-test/r/rpl_rotate_logs.result:
  Merge with 4.0
mysql-test/r/rpl_trunc_binlog.result:
  Merge with 4.0
mysql-test/r/select_found.result:
  Merge with 4.0
mysql-test/r/symlink.result:
  Merge with 4.0
mysql-test/r/truncate.result:
  Merge with 4.0
mysql-test/r/type_blob.result:
  Merge with 4.0
mysql-test/r/type_datetime.result:
  Merge with 4.0
mysql-test/r/type_decimal.result:
  Merge with 4.0
mysql-test/r/type_enum.result:
  Merge with 4.0
mysql-test/r/type_timestamp.result:
  Merge with 4.0
mysql-test/r/union.result:
  Merge with 4.0
mysql-test/t/auto_increment.test:
  Merge with 4.0
mysql-test/t/bdb.test:
  Merge with 4.0
mysql-test/t/func_group.test:
  Merge with 4.0
mysql-test/t/func_op.test:
  Merge with 4.0
mysql-test/t/func_str.test:
  Merge with 4.0
mysql-test/t/func_time.test:
  Merge with 4.0
mysql-test/t/group_by.test:
  Merge with 4.0
mysql-test/t/handler.test:
  Merge with 4.0
mysql-test/t/innodb.test:
  Merge with 4.0
mysql-test/t/insert.test:
  Merge with 4.0
mysql-test/t/join_outer.test:
  Merge with 4.0
mysql-test/t/limit.test:
  Merge with 4.0
mysql-test/t/loaddata.test:
  Merge with 4.0
mysql-test/t/lowercase_table.test:
  Merge with 4.0
mysql-test/t/multi_update.test:
  Merge with 4.0
mysql-test/t/mysqldump.test:
  Merge with 4.0
mysql-test/t/query_cache.test:
  Merge with 4.0
mysql-test/t/rpl_log_pos.test:
  Merge with 4.0
mysql-test/t/rpl_max_relay_size.test:
  Merge with 4.0
mysql-test/t/rpl_rotate_logs.test:
  Merge with 4.0
mysql-test/t/rpl_trunc_binlog.test:
  Merge with 4.0
mysql-test/t/select_found.test:
  Merge with 4.0
mysql-test/t/symlink.test:
  Merge with 4.0
mysql-test/t/truncate.test:
  Merge with 4.0
mysql-test/t/type_blob.test:
  Merge with 4.0
mysql-test/t/type_datetime.test:
  Merge with 4.0
mysql-test/t/type_decimal.test:
  Merge with 4.0
mysql-test/t/type_enum.test:
  Merge with 4.0
mysql-test/t/type_timestamp.test:
  Merge with 4.0
mysql-test/t/union.test:
  Merge with 4.0
mysys/charset.c:
  Merge with 4.0
mysys/my_init.c:
  Merge with 4.0
mysys/my_symlink.c:
  Merge with 4.0
mysys/my_thr_init.c:
  Merge with 4.0
regex/reginit.c:
  Merge with 4.0
sql/field.cc:
  Change fix_datetime() to print errors
sql/field.h:
  Merge with 4.0
sql/ha_innodb.cc:
  Merge with 4.0
sql/item.cc:
  Merge with 4.0
sql/item.h:
  Merge with 4.0
sql/item_cmpfunc.cc:
  Merge with 4.0
sql/item_func.cc:
  Merge with 4.0
sql/item_func.h:
  Merge with 4.0
sql/item_strfunc.cc:
  Merge with 4.0
sql/item_strfunc.h:
  Merge with 4.0
sql/item_sum.cc:
  Merge with 4.0
sql/item_sum.h:
  Merge with 4.0
sql/item_timefunc.cc:
  Merge with 4.0
sql/lex.h:
  Merge with 4.0
sql/log.cc:
  Merge with 4.0
sql/log_event.cc:
  Merge with 4.0
sql/log_event.h:
  Merge with 4.0
sql/mysql_priv.h:
  Merge with 4.0
sql/mysqld.cc:
  Merge with 4.0
sql/nt_servc.cc:
  Merge with 4.0
sql/opt_range.cc:
  Merge with 4.0
sql/records.cc:
  Merge with 4.0
sql/repl_failsafe.cc:
  Merge with 4.0
sql/slave.cc:
  Merge with 4.0
sql/sql_acl.cc:
  Merge with 4.0
sql/sql_analyse.cc:
  Merge with 4.0
sql/sql_base.cc:
  Merge with 4.0
sql/sql_cache.cc:
  Merge with 4.0
sql/sql_class.h:
  Merge with 4.0
sql/sql_db.cc:
  Merge with 4.0
sql/sql_delete.cc:
  Merge with 4.0
sql/sql_insert.cc:
  Merge with 4.0
sql/sql_load.cc:
  Merge with 4.0
sql/sql_parse.cc:
  Merge with 4.0
sql/sql_rename.cc:
  Merge with 4.0
sql/sql_select.cc:
  Merge with 4.0
sql/sql_show.cc:
  Merge with 4.0
sql/sql_table.cc:
  Merge with 4.0
sql/sql_update.cc:
  Merge with 4.0
sql/sql_yacc.yy:
  Merge with 4.0
sql/table.cc:
  Merge with 4.0
sql/table.h:
  Merge with 4.0
sql/time.cc:
  Merge with 4.0
sql/uniques.cc:
  Merge with 4.0
strings/ctype-tis620.c:
  Merge with 4.0
strings/strto.c:
  Merge with 4.0
support-files/mysql.server.sh:
  Merge with 4.0
support-files/mysql.spec.sh:
  Merge with 4.0

32 files changed, 606 insertions, 461 deletions

diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result
index 05317c0f845..1390141df51 100644
--- a/mysql-test/r/auto_increment.result
+++ b/mysql-test/r/auto_increment.result
@@ -244,3 +244,29 @@ a	b
 0	13
 500	14
 drop table t1;
+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();
+last_insert_id()
+1
+insert into t1 values (NULL);
+select * from t1;
+a
+-1
+1
+2
+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();
+last_insert_id()
+1
+insert into t1 values (NULL);
+select * from t1;
+a
+-1
+1
+2
+drop table t1;
diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result
index 6021f02f2a6..dd5633af90d 100644
--- a/mysql-test/r/bdb.result
+++ b/mysql-test/r/bdb.result
@@ -1145,3 +1145,14 @@ x
 7
 6
 drop table t1;
+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/r/binary.result b/mysql-test/r/binary.result
index 5da627a30b9..d4576791aed 100644
--- a/mysql-test/r/binary.result
+++ b/mysql-test/r/binary.result
@@ -66,6 +66,7 @@ concat("-",a,"-",b,"-")
 alter table t1 modify b tinytext not null, drop key b, add key (b(100));
 select concat("-",a,"-",b,"-") from t1 where b="hello ";
 concat("-",a,"-",b,"-")
+-hello-hello-
 select concat("-",a,"-",b,"-") from t1 ignore index (b) where b="hello ";
 concat("-",a,"-",b,"-")
 -hello-hello-
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index bdac40d3378..ec00c850197 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -47,7 +47,7 @@ sum(all a)	count(all a)	avg(all a)	std(all a)	variance(all a)	bit_or(all a)	bit_
 21	6	3.5000	1.7078	2.9167	7	0	1	6		E
 select grp, sum(a),count(a),avg(a),std(a),variance(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp;
 grp	sum(a)	count(a)	avg(a)	std(a)	variance(a)	bit_or(a)	bit_and(a)	min(a)	max(a)	min(c)	max(c)
-NULL	NULL	0	NULL	NULL	NULL	0	0	NULL	NULL		
+NULL	NULL	0	NULL	NULL	NULL	0	1844674407379551615	NULL	NULL		
 1	1	1	1.0000	0.0000	0.0000	1	1	1	1	a	a
 2	5	2	2.5000	0.5000	0.2500	3	2	2	3	b	c
 3	15	3	5.0000	0.8165	0.6667	7	4	4	6	C	E
@@ -632,3 +632,15 @@ select a from t1 having a=1;
 a
 1
 drop table t1;
+create table t1 (col int);
+insert into t1 values (-1), (-2), (-3);
+select bit_and(col), bit_or(col) from t1;
+bit_and(col)	bit_or(col)
+18446744073709551612	18446744073709551615
+select SQL_BIG_RESULT bit_and(col), bit_or(col) from t1 group by col;
+bit_and(col)	bit_or(col)
+18446744073709551613	18446744073709551613
+18446744073709551614	18446744073709551614
+18446744073709551615	18446744073709551615
+drop table t1;
+
diff --git a/mysql-test/r/func_op.result b/mysql-test/r/func_op.result
index 25a7ac20b66..fb1e715ac3b 100644
--- a/mysql-test/r/func_op.result
+++ b/mysql-test/r/func_op.result
@@ -17,3 +17,21 @@ Note	1003	select high_priority (1 | (1 + 1)) AS `1 | (1+1)`,(5 & 3) AS `5 & 3`,b
 select 1 << 32,1 << 63, 1 << 64, 4 >> 2, 4 >> 63, 1<< 63 >> 60;
 1 << 32	1 << 63	1 << 64	4 >> 2	4 >> 63	1<< 63 >> 60
 4294967296	9223372036854775808	0	1	0	8
+select -1 | 0, -1 ^ 0, -1 & 0;
+-1 | 0	-1 ^ 0	-1 & 0
+18446744073709551615	18446744073709551615	0
+select -1 | 1, -1 ^ 1, -1 & 1;
+-1 | 1	-1 ^ 1	-1 & 1
+18446744073709551615	18446744073709551614	1
+select  1 | -1,  1 ^ -1,  1 & -1;
+1 | -1	1 ^ -1	1 & -1
+18446744073709551615	18446744073709551614	1
+select  0 | -1,  0 ^ -1,  0 & -1;
+0 | -1	0 ^ -1	0 & -1
+18446744073709551615	18446744073709551615	0
+select -1 >> 0, -1 << 0;
+-1 >> 0	-1 << 0
+18446744073709551615	18446744073709551615
+select -1 >> 1, -1 << 1;
+-1 >> 1	-1 << 1
+9223372036854775807	18446744073709551614
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index ac075e129ee..aaebd33d010 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -144,12 +144,12 @@ montymontymontymontymonty	*     *
 select reverse('abc'),reverse('abcd');
 reverse('abc')	reverse('abcd')
 cba	dcba
-select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12');
-rpad('a',4,'1')	rpad('a',4,'12')	rpad('abcd',3,'12')
-a111	a121	abc
-select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12');
-lpad('a',4,'1')	lpad('a',4,'12')	lpad('abcd',3,'12')
-111a	121a	abc
+select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'), rpad(11, 10 , 22), rpad("ab", 10, 22);
+rpad('a',4,'1')	rpad('a',4,'12')	rpad('abcd',3,'12')	rpad(11, 10 , 22)	rpad("ab", 10, 22)
+a111	a121	abc	1122222222	ab22222222
+select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'), lpad(11, 10 , 22);
+lpad('a',4,'1')	lpad('a',4,'12')	lpad('abcd',3,'12')	lpad(11, 10 , 22)
+111a	121a	abc	2222222211
 select rpad(741653838,17,'0'),lpad(741653838,17,'0');
 rpad(741653838,17,'0')	lpad(741653838,17,'0')
 74165383800000000	00000000741653838
@@ -159,6 +159,12 @@ abcdaba	abaabcd
 select rpad('abcd',1,'ab'),lpad('abcd',1,'ab');
 rpad('abcd',1,'ab')	lpad('abcd',1,'ab')
 a	a
+select rpad('STRING', 20, CONCAT('p','a','d') );
+rpad('STRING', 20, CONCAT('p','a','d') )
+STRINGpadpadpadpadpa
+select lpad('STRING', 20, CONCAT('p','a','d') );
+lpad('STRING', 20, CONCAT('p','a','d') )
+padpadpadpadpaSTRING
 select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD');
 LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD')	GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD')
 HAROLD	HARRY
@@ -183,6 +189,9 @@ NULL	'\0\Z'
 select length(quote(concat(char(0),"test")));
 length(quote(concat(char(0),"test")))
 8
+select hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))));
+hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))))
+27E0E3E6E7E8EAEB27
 select reverse("");
 reverse("")
 
@@ -259,340 +268,3 @@ INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf');
 SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password");
 1
 DROP TABLE t1;
-select 1=_latin1'1';
-1=_latin1'1'
-1
-select _latin1'1'=1;
-_latin1'1'=1
-1
-select _latin2'1'=1;
-_latin2'1'=1
-1
-select 1=_latin2'1';
-1=_latin2'1'
-1
-select _latin1'1'=_latin2'1';
-ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '='
-select row('a','b','c') = row('a','b','c');
-row('a','b','c') = row('a','b','c')
-1
-select row('A','b','c') = row('a','b','c');
-row('A','b','c') = row('a','b','c')
-1
-select row('A' COLLATE latin1_bin,'b','c') = row('a','b','c');
-row('A' COLLATE latin1_bin,'b','c') = row('a','b','c')
-0
-select row('A','b','c') = row('a' COLLATE latin1_bin,'b','c');
-row('A','b','c') = row('a' COLLATE latin1_bin,'b','c')
-0
-select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c');
-ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation '='
-select concat(_latin1'a',_latin2'a');
-ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat'
-select concat(_latin1'a',_latin2'a',_latin5'a');
-ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin5_turkish_ci,COERCIBLE) for operation 'concat'
-select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a');
-ERROR HY000: Illegal mix of collations for operation 'concat'
-select FIELD('b','A','B');
-FIELD('b','A','B')
-2
-select FIELD('B','A','B');
-FIELD('B','A','B')
-2
-select FIELD('b' COLLATE latin1_bin,'A','B');
-FIELD('b' COLLATE latin1_bin,'A','B')
-0
-select FIELD('b','A' COLLATE latin1_bin,'B');
-FIELD('b','A' COLLATE latin1_bin,'B')
-0
-select FIELD(_latin2'b','A','B');
-ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'
-select FIELD('b',_latin2'A','B');
-ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'
-select FIELD('b',_latin2'A','B',1);
-FIELD('b',_latin2'A','B',1)
-1
-select POSITION(_latin1'B' IN _latin1'abcd');
-POSITION(_latin1'B' IN _latin1'abcd')
-2
-select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin);
-POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin)
-0
-select POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd');
-POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd')
-0
-select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin);
-ERROR HY000: Illegal mix of collations (latin1_bin,EXPLICIT) and (latin1_general_ci,EXPLICIT) for operation 'locate'
-select POSITION(_latin1'B' IN _latin2'abcd');
-ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'locate'
-select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d');
-FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')
-2
-select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin);
-ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'find_in_set'
-select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d');
-ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'find_in_set'
-select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2);
-SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2)
-abcdabc
-select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2);
-ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'substr_index'
-select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2);
-ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'substr_index'
-select _latin1'B' between _latin1'a' and _latin1'c';
-_latin1'B' between _latin1'a' and _latin1'c'
-1
-select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c';
-_latin1'B' collate latin1_bin between _latin1'a' and _latin1'c'
-0
-select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c';
-_latin1'B' between _latin1'a' collate latin1_bin and _latin1'c'
-0
-select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin;
-_latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin
-0
-select _latin2'B' between _latin1'a' and _latin1'b';
-ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'
-select _latin1'B' between _latin2'a' and _latin1'b';
-ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'
-select _latin1'B' between _latin1'a' and _latin2'b';
-ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation 'between'
-select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b';
-ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation 'between'
-select _latin1'B' in (_latin1'a',_latin1'b');
-_latin1'B' in (_latin1'a',_latin1'b')
-1
-select _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b');
-_latin1'B' collate latin1_bin in (_latin1'a',_latin1'b')
-0
-select _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b');
-_latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b')
-0
-select _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin);
-_latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin)
-0
-select _latin2'B' in (_latin1'a',_latin1'b');
-ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN '
-select _latin1'B' in (_latin2'a',_latin1'b');
-ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN '
-select _latin1'B' in (_latin1'a',_latin2'b');
-ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation ' IN '
-select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b');
-ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation ' IN '
-select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin);
-ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_swedish_ci,COERCIBLE), (latin1_bin,EXPLICIT) for operation ' IN '
-select collation(bin(130)), coercibility(bin(130));
-collation(bin(130))	coercibility(bin(130))
-latin1_swedish_ci	3
-select collation(oct(130)), coercibility(oct(130));
-collation(oct(130))	coercibility(oct(130))
-latin1_swedish_ci	3
-select collation(conv(130,16,10)), coercibility(conv(130,16,10));
-collation(conv(130,16,10))	coercibility(conv(130,16,10))
-latin1_swedish_ci	3
-select collation(hex(130)), coercibility(hex(130));
-collation(hex(130))	coercibility(hex(130))
-latin1_swedish_ci	3
-select collation(char(130)), coercibility(hex(130));
-collation(char(130))	coercibility(hex(130))
-binary	3
-select collation(format(130,10)), coercibility(format(130,10));
-collation(format(130,10))	coercibility(format(130,10))
-latin1_swedish_ci	3
-select collation(lcase(_latin2'a')), coercibility(lcase(_latin2'a'));
-collation(lcase(_latin2'a'))	coercibility(lcase(_latin2'a'))
-latin2_general_ci	3
-select collation(ucase(_latin2'a')), coercibility(ucase(_latin2'a'));
-collation(ucase(_latin2'a'))	coercibility(ucase(_latin2'a'))
-latin2_general_ci	3
-select collation(left(_latin2'a',1)), coercibility(left(_latin2'a',1));
-collation(left(_latin2'a',1))	coercibility(left(_latin2'a',1))
-latin2_general_ci	3
-select collation(right(_latin2'a',1)), coercibility(right(_latin2'a',1));
-collation(right(_latin2'a',1))	coercibility(right(_latin2'a',1))
-latin2_general_ci	3
-select collation(substring(_latin2'a',1,1)), coercibility(substring(_latin2'a',1,1));
-collation(substring(_latin2'a',1,1))	coercibility(substring(_latin2'a',1,1))
-latin2_general_ci	3
-select collation(concat(_latin2'a',_latin2'b')), coercibility(concat(_latin2'a',_latin2'b'));
-collation(concat(_latin2'a',_latin2'b'))	coercibility(concat(_latin2'a',_latin2'b'))
-latin2_general_ci	3
-select collation(lpad(_latin2'a',4,_latin2'b')), coercibility(lpad(_latin2'a',4,_latin2'b'));
-collation(lpad(_latin2'a',4,_latin2'b'))	coercibility(lpad(_latin2'a',4,_latin2'b'))
-latin2_general_ci	3
-select collation(rpad(_latin2'a',4,_latin2'b')), coercibility(rpad(_latin2'a',4,_latin2'b'));
-collation(rpad(_latin2'a',4,_latin2'b'))	coercibility(rpad(_latin2'a',4,_latin2'b'))
-latin2_general_ci	3
-select collation(concat_ws(_latin2'a',_latin2'b')), coercibility(concat_ws(_latin2'a',_latin2'b'));
-collation(concat_ws(_latin2'a',_latin2'b'))	coercibility(concat_ws(_latin2'a',_latin2'b'))
-latin2_general_ci	3
-select collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')), coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c'));
-collation(make_set(255,_latin2'a',_latin2'b',_latin2'c'))	coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c'))
-latin2_general_ci	3
-select collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')), coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' '));
-collation(export_set(255,_latin2'y',_latin2'n',_latin2' '))	coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' '))
-binary	3
-select collation(trim(_latin2' a ')), coercibility(trim(_latin2' a '));
-collation(trim(_latin2' a '))	coercibility(trim(_latin2' a '))
-latin2_general_ci	3
-select collation(ltrim(_latin2' a ')), coercibility(ltrim(_latin2' a '));
-collation(ltrim(_latin2' a '))	coercibility(ltrim(_latin2' a '))
-latin2_general_ci	3
-select collation(rtrim(_latin2' a ')), coercibility(rtrim(_latin2' a '));
-collation(rtrim(_latin2' a '))	coercibility(rtrim(_latin2' a '))
-latin2_general_ci	3
-select collation(trim(LEADING _latin2' ' FROM _latin2'a')), coercibility(trim(LEADING _latin2'a' FROM _latin2'a'));
-collation(trim(LEADING _latin2' ' FROM _latin2'a'))	coercibility(trim(LEADING _latin2'a' FROM _latin2'a'))
-latin2_general_ci	3
-select collation(trim(TRAILING _latin2' ' FROM _latin2'a')), coercibility(trim(TRAILING _latin2'a' FROM _latin2'a'));
-collation(trim(TRAILING _latin2' ' FROM _latin2'a'))	coercibility(trim(TRAILING _latin2'a' FROM _latin2'a'))
-latin2_general_ci	3
-select collation(trim(BOTH _latin2' ' FROM _latin2'a')), coercibility(trim(BOTH _latin2'a' FROM _latin2'a'));
-collation(trim(BOTH _latin2' ' FROM _latin2'a'))	coercibility(trim(BOTH _latin2'a' FROM _latin2'a'))
-latin2_general_ci	3
-select collation(repeat(_latin2'a',10)), coercibility(repeat(_latin2'a',10));
-collation(repeat(_latin2'a',10))	coercibility(repeat(_latin2'a',10))
-latin2_general_ci	3
-select collation(reverse(_latin2'ab')), coercibility(reverse(_latin2'ab'));
-collation(reverse(_latin2'ab'))	coercibility(reverse(_latin2'ab'))
-latin2_general_ci	3
-select collation(quote(_latin2'ab')), coercibility(quote(_latin2'ab'));
-collation(quote(_latin2'ab'))	coercibility(quote(_latin2'ab'))
-latin2_general_ci	3
-select collation(soundex(_latin2'ab')), coercibility(soundex(_latin2'ab'));
-collation(soundex(_latin2'ab'))	coercibility(soundex(_latin2'ab'))
-latin2_general_ci	3
-select collation(substring(_latin2'ab',1)), coercibility(substring(_latin2'ab',1));
-collation(substring(_latin2'ab',1))	coercibility(substring(_latin2'ab',1))
-latin2_general_ci	3
-select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef'));
-collation(insert(_latin2'abcd',2,3,_latin2'ef'))	coercibility(insert(_latin2'abcd',2,3,_latin2'ef'))
-latin2_general_ci	3
-select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B'));
-collation(replace(_latin2'abcd',_latin2'b',_latin2'B'))	coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B'))
-latin2_general_ci	3
-create table t1 
-select
-bin(130),
-oct(130),
-conv(130,16,10),
-hex(130),
-char(130),
-format(130,10),
-left(_latin2'a',1),
-right(_latin2'a',1), 
-lcase(_latin2'a'), 
-ucase(_latin2'a'),
-substring(_latin2'a',1,1),
-concat(_latin2'a',_latin2'b'),
-lpad(_latin2'a',4,_latin2'b'),
-rpad(_latin2'a',4,_latin2'b'),
-concat_ws(_latin2'a',_latin2'b'),
-make_set(255,_latin2'a',_latin2'b',_latin2'c'),
-export_set(255,_latin2'y',_latin2'n',_latin2' '),
-trim(_latin2' a '),
-ltrim(_latin2' a '),
-rtrim(_latin2' a '),
-trim(LEADING _latin2' ' FROM _latin2' a '),
-trim(TRAILING _latin2' ' FROM _latin2' a '),
-trim(BOTH _latin2' ' FROM _latin2' a '),
-repeat(_latin2'a',10),
-reverse(_latin2'ab'),
-quote(_latin2'ab'),
-soundex(_latin2'ab'),
-substring(_latin2'ab',1),
-insert(_latin2'abcd',2,3,_latin2'ef'),
-replace(_latin2'abcd',_latin2'b',_latin2'B')
-;
-Warnings:
-Warning	1264	Data truncated for column 'format(130,10)' at row 1
-show create table t1;
-Table	Create Table
-t1	CREATE TABLE `t1` (
-  `bin(130)` char(64) NOT NULL default '',
-  `oct(130)` char(64) NOT NULL default '',
-  `conv(130,16,10)` char(64) NOT NULL default '',
-  `hex(130)` char(6) NOT NULL default '',
-  `char(130)` char(1) NOT NULL default '',
-  `format(130,10)` char(4) NOT NULL default '',
-  `left(_latin2'a',1)` char(1) character set latin2 NOT NULL default '',
-  `right(_latin2'a',1)` char(1) character set latin2 NOT NULL default '',
-  `lcase(_latin2'a')` char(1) character set latin2 NOT NULL default '',
-  `ucase(_latin2'a')` char(1) character set latin2 NOT NULL default '',
-  `substring(_latin2'a',1,1)` char(1) character set latin2 NOT NULL default '',
-  `concat(_latin2'a',_latin2'b')` char(2) character set latin2 NOT NULL default '',
-  `lpad(_latin2'a',4,_latin2'b')` char(4) character set latin2 NOT NULL default '',
-  `rpad(_latin2'a',4,_latin2'b')` char(4) character set latin2 NOT NULL default '',
-  `concat_ws(_latin2'a',_latin2'b')` char(1) character set latin2 NOT NULL default '',
-  `make_set(255,_latin2'a',_latin2'b',_latin2'c')` char(5) character set latin2 NOT NULL default '',
-  `export_set(255,_latin2'y',_latin2'n',_latin2' ')` char(127) character set latin2 NOT NULL default '',
-  `trim(_latin2' a ')` char(3) character set latin2 NOT NULL default '',
-  `ltrim(_latin2' a ')` char(3) character set latin2 NOT NULL default '',
-  `rtrim(_latin2' a ')` char(3) character set latin2 NOT NULL default '',
-  `trim(LEADING _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '',
-  `trim(TRAILING _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '',
-  `trim(BOTH _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '',
-  `repeat(_latin2'a',10)` char(10) character set latin2 NOT NULL default '',
-  `reverse(_latin2'ab')` char(2) character set latin2 NOT NULL default '',
-  `quote(_latin2'ab')` char(6) character set latin2 NOT NULL default '',
-  `soundex(_latin2'ab')` char(4) character set latin2 NOT NULL default '',
-  `substring(_latin2'ab',1)` char(2) character set latin2 NOT NULL default '',
-  `insert(_latin2'abcd',2,3,_latin2'ef')` char(6) character set latin2 NOT NULL default '',
-  `replace(_latin2'abcd',_latin2'b',_latin2'B')` char(4) character set latin2 NOT NULL default ''
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-drop table t1;
-select SUBSTR('abcdefg',3,2);
-SUBSTR('abcdefg',3,2)
-cd
-select SUBSTRING('abcdefg',3,2);
-SUBSTRING('abcdefg',3,2)
-cd
-select SUBSTR('abcdefg',-3,2) FROM DUAL;
-SUBSTR('abcdefg',-3,2)
-ef
-select SUBSTR('abcdefg',-1,5) FROM DUAL;
-SUBSTR('abcdefg',-1,5)
-g
-select SUBSTR('abcdefg',0,0) FROM DUAL;
-SUBSTR('abcdefg',0,0)
-
-select SUBSTR('abcdefg',-1,-1) FROM DUAL;
-SUBSTR('abcdefg',-1,-1)
-
-select SUBSTR('abcdefg',1,-1) FROM DUAL;
-SUBSTR('abcdefg',1,-1)
-
-create table t7 (s1 char);
-select * from t7
-where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA';
-ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'concat'
-drop table t7;
-CREATE TABLE t1 (
-wid int(10) unsigned NOT NULL auto_increment,
-data_podp date default NULL,
-status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy',
-PRIMARY KEY(wid),
-);
-INSERT INTO t1 VALUES (8,NULL,'real');
-INSERT INTO t1 VALUES (9,NULL,'nowy');
-SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid;
-elt(status_wnio,data_podp)
-NULL
-NULL
-DROP TABLE t1;
-CREATE TABLE t1 (title text) ENGINE=MyISAM;
-INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education');
-INSERT INTO t1 VALUES ('House passes the CAREERS bill');
-SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1;
-CONCAT("</a>",RPAD("",(55 - LENGTH(title)),"."))
-NULL
-</a>..........................
-DROP TABLE t1;
-select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2);
-substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)	substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2)
-1abcd;2abcd	3abcd;4abcd
-explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'mood' sounds like 'mud', aes_decrypt(aes_encrypt('abc','1'),'1'),concat('*',space(5),'*'), reverse('abc'), rpad('a',4,'1'), lpad('a',4,'1'),  concat_ws(',','',NULL,'a'),make_set(255,_latin2'a',_latin2'b',_latin2'c'),elt(2,1),locate("a","b",2),format(130,10),char(0),conv(130,16,10),hex(130),binary 'HE', export_set(255,_latin2'y',_latin2'n',_latin2' '),FIELD('b' COLLATE latin1_bin,'A','B'),FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'),collation(conv(130,16,10)), coercibility(conv(130,16,10)),length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h'),quote(1/0),crc32("123"),replace('aaaa','a','b'),insert('txs',2,1,'hi'),left(_latin2'a',1),right(_latin2'a',1),lcase(_latin2'a'),ucase(_latin2'a'),SUBSTR('abcdefg',3,2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),trim(_latin2' a '),ltrim(_latin2' a '),rtrim(_latin2' a '), decode(encode(repeat("a",100000),"monty"),"monty");
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-Warnings:
-Note	1003	select high_priority md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,(_latin1'HE' collate _latin1'BINARY') AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate _latin1'latin1_bin'),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n	\r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n	\r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n	\r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substr_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000))) AS `decode(encode(repeat("a",100000),"monty"),"monty")`
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index b3c3d8dff5c..c3f4b9475f9 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -95,11 +95,34 @@ week(19981231,2)	week(19981231,3)	week(20000101,2)	week(20000101,3)
 52	53	52	52
 select week(20001231,2),week(20001231,3);
 week(20001231,2)	week(20001231,3)
-1	52
+53	52
+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';
+0	1	2	3	4	5	6	7
+52	53	52	53	52	52	52	52
+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';
+0	1	2	3	4	5	6	7
+0	0	52	52	0	0	52	52
+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';
+0	1	2	3	4	5	6	7
+1	1	1	1	1	1	1	1
+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';
+0	1	2	3	4	5	6	7
+53	52	53	52	53	52	1	52
+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';
+0	1	2	3	4	5	6	7
+0	1	53	1	1	1	1	1
+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);
+yearweek(20001231,0)	yearweek(20001231,1)	yearweek(20001231,2)	yearweek(20001231,3)	yearweek(20001231,4)	yearweek(20001231,5)	yearweek(20001231,6)	yearweek(20001231,7)
+200053	200052	200053	200052	200101	200052	200101	200052
+set default_week_format = 6;
+select week(20001231), week(20001231,6);
+week(20001231)	week(20001231,6)
+1	1
+set default_week_format = 0;
 set default_week_format = 2;
 select week(20001231),week(20001231,2),week(20001231,0);
 week(20001231)	week(20001231,2)	week(20001231,0)
-1	1	53
+53	53	53
 set default_week_format = 0;
 select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v');
 date_format('1998-12-31','%x-%v')	date_format('1999-01-01','%x-%v')
@@ -408,103 +431,3 @@ select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2;
 start	ctime1	ctime2
 2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31
 drop table t1,t2,t3;
-CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date, time time);
-INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02", "06:07:08");
-SELECT * from t1;
-datetime	timestamp	date	time
-2001-01-02 03:04:05	2002-01-02 03:04:05	2003-01-02	06:07:08
-select date_add("1997-12-31",INTERVAL 1 SECOND);
-date_add("1997-12-31",INTERVAL 1 SECOND)
-1997-12-31 00:00:01
-select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH);
-date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH)
-1999-01-31
-select date_add(datetime, INTERVAL 1 SECOND) from t1;
-date_add(datetime, INTERVAL 1 SECOND)
-2001-01-02 03:04:06
-select date_add(datetime, INTERVAL 1 YEAR) from t1;
-date_add(datetime, INTERVAL 1 YEAR)
-2002-01-02 03:04:05
-select date_add(date,INTERVAL 1 SECOND) from t1;
-date_add(date,INTERVAL 1 SECOND)
-2003-01-02 00:00:01
-select date_add(date,INTERVAL 1 MINUTE) from t1;
-date_add(date,INTERVAL 1 MINUTE)
-2003-01-02 00:01:00
-select date_add(date,INTERVAL 1 HOUR) from t1;
-date_add(date,INTERVAL 1 HOUR)
-2003-01-02 01:00:00
-select date_add(date,INTERVAL 1 DAY) from t1;
-date_add(date,INTERVAL 1 DAY)
-2003-01-03
-select date_add(date,INTERVAL 1 MONTH) from t1;
-date_add(date,INTERVAL 1 MONTH)
-2003-02-02
-select date_add(date,INTERVAL 1 YEAR) from t1;
-date_add(date,INTERVAL 1 YEAR)
-2004-01-02
-select date_add(date,INTERVAL "1:1" MINUTE_SECOND) from t1;
-date_add(date,INTERVAL "1:1" MINUTE_SECOND)
-2003-01-02 00:01:01
-select date_add(date,INTERVAL "1:1" HOUR_MINUTE) from t1;
-date_add(date,INTERVAL "1:1" HOUR_MINUTE)
-2003-01-02 01:01:00
-select date_add(date,INTERVAL "1:1" DAY_HOUR) from t1;
-date_add(date,INTERVAL "1:1" DAY_HOUR)
-2003-01-03 01:00:00
-select date_add(date,INTERVAL "1 1" YEAR_MONTH) from t1;
-date_add(date,INTERVAL "1 1" YEAR_MONTH)
-2004-02-02
-select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1;
-date_add(date,INTERVAL "1:1:1" HOUR_SECOND)
-2003-01-02 01:01:01
-select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1;
-date_add(date,INTERVAL "1 1:1" DAY_MINUTE)
-2003-01-03 01:01:00
-select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1;
-date_add(date,INTERVAL "1 1:1:1" DAY_SECOND)
-2003-01-03 01:01:01
-select date_add(time,INTERVAL 1 SECOND) from t1;
-date_add(time,INTERVAL 1 SECOND)
-2006-07-08 00:00:01
-drop table t1;
-select last_day('2000-02-05') as f1, last_day('2002-12-31') as f2,
-last_day('2003-03-32') as f3, last_day('2003-04-01') as f4,
-last_day('2001-01-01 01:01:01') as f5, last_day(NULL),
-last_day('2001-02-12');
-f1	f2	f3	f4	f5	last_day(NULL)	last_day('2001-02-12')
-2000-02-29	2002-12-31	NULL	2003-04-30	2001-01-31	NULL	2001-02-28
-create table t1 select last_day('2000-02-05') as a;
-describe t1;
-Field	Type	Null	Key	Default	Extra
-a	date			0000-00-00	
-select * from t1;
-a
-2000-02-29
-drop table t1;
-select last_day('2000-02-05');
-last_day('2000-02-05')
-2000-02-29
-select strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0;
-strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0
-1
-select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0;
-strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0
-1
-select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0;
-strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0
-1
-select strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0;
-strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0
-1
-select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0;
-strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0
-1
-select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0;
-strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0
-1
-explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_days(to_days("960101")),dayofmonth("1997-01-02"), month("1997-01-02"), monthname("1972-03-04"),dayofyear("0000-00-00"),HOUR("1997-03-03 23:03:22"),MINUTE("23:03:22"),SECOND(230322),QUARTER(980303),WEEK("1998-03-03"),yearweek("2000-01-01",1),week(19950101,1),year("98-02-03"),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),sec_to_time(time_to_sec("0:30:47")/6.21),curtime(),utc_time(),curdate(),utc_date(),utc_timestamp(),date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"),from_unixtime(unix_timestamp("1994-03-02 10:11:12")),"1997-12-31 23:59:59" + INTERVAL 1 SECOND,"1998-01-01 00:00:00" - INTERVAL 1 SECOND,INTERVAL 1 DAY + "1997-12-31", extract(YEAR FROM "1999-01-02 10:11:12"),date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-Warnings:
-Note	1003	select high_priority no_cache period_add(_latin1'9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,_latin1'9404') AS `period_diff(199505,"9404")`,from_days(to_days(_latin1'960101')) AS `from_days(to_days("960101"))`,dayofmonth(_latin1'1997-01-02') AS `dayofmonth("1997-01-02")`,month(_latin1'1997-01-02') AS `month("1997-01-02")`,monthname(_latin1'1972-03-04') AS `monthname("1972-03-04")`,dayofyear(_latin1'0000-00-00') AS `dayofyear("0000-00-00")`,hour(_latin1'1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute(_latin1'23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,week(_latin1'1998-03-03',0) AS `WEEK("1998-03-03")`,yearweek(_latin1'2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year(_latin1'98-02-03') AS `year("98-02-03")`,(weekday(to_days(curdate())) - weekday(to_days(now()))) AS `weekday(curdate())-weekday(now())`,dayname(to_days(_latin1'1962-03-03')) AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time((time_to_sec(_latin1'0:30:47') / 6.21)) AS `sec_to_time(time_to_sec("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format(_latin1'1997-01-02 03:04:05',_latin1'%M %W %D %Y %y %m %d %h %i %s %w') AS `date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")`,from_unixtime(unix_timestamp(_latin1'1994-03-02 10:11:12')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,(_latin1'1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,(_latin1'1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,(_latin1'1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from _latin1'1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,(_latin1'1997-12-31 23:59:59' + interval 1 second) AS `date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)`
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 853260a0164..98a4f9a390d 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -284,7 +284,6 @@ select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
 spid	count(*)
 2	2
 1	1
-explain extended select sql_big_result spid,sum(userid) from t1 group by spid desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using filesort
 Warnings:
@@ -600,3 +599,32 @@ count(*)	category
 1	3
 1	4
 drop table t1;
+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);
+SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
+userid	count(*)
+3	5
+2	1
+1	2
+EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary; Using filesort
+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;
+i	COUNT(DISTINCT(i))
+1	1
+2	1
+4	4
+explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
+DROP TABLE t1;
diff --git a/mysql-test/r/have_mest_timezone.require b/mysql-test/r/have_mest_timezone.require
deleted file mode 100644
index 2a219f39b7e..00000000000
--- a/mysql-test/r/have_mest_timezone.require
+++ /dev/null
@@ -1,2 +0,0 @@
-Variable_name	Value
-timezone	MEST
diff --git a/mysql-test/r/have_met_timezone.require b/mysql-test/r/have_met_timezone.require
new file mode 100644
index 00000000000..b3fde075ebd
--- /dev/null
+++ b/mysql-test/r/have_met_timezone.require
@@ -0,0 +1,2 @@
+FROM_UNIXTIME(24*3600)
+1970-01-02 01:00:00
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 58c0042e122..d7bcea22dbf 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1352,6 +1352,7 @@ id	label
 1822	Test 3
 3524	Societe Test
 3525	Fournisseur Test
+drop table t1;
 drop table t1,t2;
 create table t1 (c1 char(5) unique not null, c2 int, stamp timestamp) engine=innodb;
 select * from t1;
@@ -1390,3 +1391,24 @@ test.t2	968604391
 test.t3	968604391
 test.t4	NULL
 drop table t1,t2,t3;
+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;
+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;
+a
+3
+4
+drop table t1;
diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result
index 23eef0b3c59..5af46e7db04 100644
--- a/mysql-test/r/insert.result
+++ b/mysql-test/r/insert.result
@@ -61,6 +61,7 @@ drop table t1;
 create table t1 (id int NOT NULL DEFAULT 8);
 insert into t1 values(NULL);
 ERROR 23000: Column 'id' cannot be null
+ERROR 23000: Column 'id' cannot be null
 insert into t1 values (1), (NULL), (2);
 Warnings:
 Warning	1262	Data truncated, NULL supplied to NOT NULL column 'id' at row 2
@@ -73,7 +74,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;
 id	t2
 1	mysql.com
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 76c1b06b638..19368ace977 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -4,6 +4,7 @@ grp int(11) default NULL,
 a bigint(20) unsigned default NULL,
 c char(10) NOT NULL default ''
 ) ENGINE=MyISAM;
+) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (1,1,'a'),(2,2,'b'),(2,3,'c'),(3,4,'E'),(3,5,'C'),(3,6,'D'),(NULL,NULL,'');
 create table t2 (id int, a bigint unsigned not null, c char(10), d int, primary key (a));
 insert into t2 values (1,1,"a",1),(3,4,"A",4),(3,5,"B",5),(3,6,"C",6),(4,7,"D",7);
@@ -665,3 +666,20 @@ left outer join t2 using (f2)
 left outer join t3 using (f3);
 ERROR 42S22: Unknown column 'test.t2.f3' in 'on clause'
 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;
+a1	a2	b1	b2	c1	c2
+1	2	1	3	NULL	NULL
+2	2	2	3	NULL	NULL
+3	2	NULL	NULL	3	4
+explain select * from t1 left join t2  on  b1 = a1 left join t3  on  c1 = a1  and  b1 is null;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	NULL	NULL	NULL	NULL	3	
+t2	ALL	NULL	NULL	NULL	NULL	2	
+t3	ALL	NULL	NULL	NULL	NULL	2	
+drop table t1, t2, t3;
diff --git a/mysql-test/r/limit.result b/mysql-test/r/limit.result
index 5a8edc99c12..c82105e6a49 100644
--- a/mysql-test/r/limit.result
+++ b/mysql-test/r/limit.result
@@ -36,9 +36,7 @@ a	b
 3	4
 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;
@@ -50,3 +48,22 @@ i
 drop table t1;
 select 0 limit 0;
 0
+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;
+id	id2
+4	0
+2	0
+3	0
+DELETE FROM t1 WHERE id2 = 0 ORDER BY id LIMIT 1;
+SELECT * FROM t1;
+id	id2
+4	0
+3	0
+DELETE FROM t1 WHERE id2 = 0 ORDER BY id desc LIMIT 1;
+SELECT * FROM t1;
+id	id2
+3	0
+DROP TABLE t1;
diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result
index d738431e016..d90ebb4cd79 100644
--- a/mysql-test/r/loaddata.result
+++ b/mysql-test/r/loaddata.result
@@ -40,3 +40,21 @@ Field 3,'Field 4|
 |Field 5' ,'Field 6|	NULL
 |Field 6|	| 'Field 7'|
 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;
+a	b
+1	row 1
+2	row 2
+0	1234567890
+3	row 3
+0	1234567890
+truncate table t1;
+load data infile '../../std_data/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines;
+select * from t1;
+a	b
+1	row 1
+2	row 2
+3	row 3
+0	
+drop table t1;
diff --git a/mysql-test/r/lowercase_table.result b/mysql-test/r/lowercase_table.result
index 9c6b212a4b6..0ba4a4be945 100644
--- a/mysql-test/r/lowercase_table.result
+++ b/mysql-test/r/lowercase_table.result
@@ -1,6 +1,8 @@
-drop table if exists t1,t2,t3;
+drop table if exists t1,t2,t3,t4,T1;
 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;
 id	Word
 1	a
@@ -12,6 +14,9 @@ id
 SELECT T2.id from t1 as T2 LIMIT 1;
 id
 1
+SELECT * from t1 left join t4 on (test.t1.id= TEST.t4.id) where TEST.t1.id >= test.t4.id;
+id	Word	id	Word
+1	a	1	match
 SELECT T2.id from t1 as t2 LIMIT 1;
 id
 1
@@ -21,7 +26,8 @@ ALTER TABLE T2 RENAME T3;
 show tables like 't_';
 Tables_in_test (t_)
 t3
-drop table t3;
+t4
+drop table t3,t4;
 create table t1 (a int);
 select count(*) from T1;
 count(*)
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index 38d60cd21d5..2e561452b8b 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -341,6 +341,39 @@ select t1.a, t1.b,t2.a, t2.b from t1 left join t2  on t1.a=t2.a where t1.b=1 and
 a	b	a	b
 2	2	NULL	NULL
 drop table t1,t2;
+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;
+a	b
+1	2
+2	3
+3	4
+4	5
+drop table t1;
+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;
+id1	field
+1	a
+2	aa
+select * from t2;
+id2	field
+1	b
+2	bb
+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;
 create table t1 ( a int not null, b int not null) ;
 alter table t1 add index i1(a);
 delete from t1 where a > 2000000;
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index b49f8012776..706a4d11241 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -18,6 +18,26 @@ INSERT INTO t1 VALUES (1), (2);
 </database>
 </mysqldump>
 DROP TABLE t1;
+CREATE TABLE t1 (a decimal(240, 20));
+INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"),
+("0987654321098765432109876543210987654321");
+CREATE TABLE t1 (
+  a decimal(240,20) default NULL
+) TYPE=MyISAM;
+
+INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890.00000000000000000000");
+INSERT INTO t1 VALUES ("0987654321098765432109876543210987654321.00000000000000000000");
+
+DROP TABLE t1;
+CREATE TABLE t1 (a double);
+INSERT INTO t1 VALUES (-9e999999);
+CREATE TABLE t1 (
+  a double default NULL
+) TYPE=MyISAM;
+
+INSERT INTO t1 VALUES (RES);
+
+DROP TABLE t1;
 CREATE TABLE t1(a int, b text, c varchar(3));
 INSERT INTO t1 VALUES (1, "test", "tes"), (2, "TEST", "TES");
 <?xml version="1.0"?>
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index bf658bae89e..fe2ded691d4 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -370,16 +370,23 @@ insert into mysqltest.t1 (a) values (1);
 select * from mysqltest.t1 where i is null;
 i	a
 1	1
+create table t1(a int);
+select * from t1;
+a
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
 select * from mysqltest.t1;
 i	a
 1	1
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	1
+Qcache_queries_in_cache	2
 drop database mysqltest;
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	1
+drop table t1;
 create table t1 (a char(1) not null collate koi8r_general_ci);
 insert into t1 values(_koi8r"á");
 set CHARACTER SET koi8r;
diff --git a/mysql-test/r/rpl_EE_error.result b/mysql-test/r/rpl_EE_error.result
new file mode 100644
index 00000000000..a5043250df6
--- /dev/null
+++ b/mysql-test/r/rpl_EE_error.result
@@ -0,0 +1,16 @@
+slave stop;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+slave start;
+create table t1 (a int) type=myisam;
+flush tables;
+drop table t1;
+create table t1 (a int, unique(a)) type=myisam;
+set sql_log_bin=0;
+insert into t1 values(2);
+set sql_log_bin=1;
+insert into t1 values(1),(2);
+Duplicate entry '2' for key 1
+drop table t1;
diff --git a/mysql-test/r/rpl_multi_update.result b/mysql-test/r/rpl_multi_update.result
new file mode 100644
index 00000000000..1fa1dd104d2
--- /dev/null
+++ b/mysql-test/r/rpl_multi_update.result
@@ -0,0 +1,27 @@
+slave stop;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+slave start;
+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;
+a	b
+1	0
+2	0
+SELECT * FROM t2 ORDER BY a;
+a	b
+1	0
+2	1
+UPDATE t1, t2 SET t1.b = t2.b WHERE t1.a = t2.a;
diff --git a/mysql-test/r/rpl_rotate_logs.result b/mysql-test/r/rpl_rotate_logs.result
index 87ebf870f38..2264272051a 100644
--- a/mysql-test/r/rpl_rotate_logs.result
+++ b/mysql-test/r/rpl_rotate_logs.result
@@ -68,6 +68,9 @@ m
 create temporary table temp_table (a char(80) not null);
 insert into temp_table values ("testing temporary tables part 2");
 create table t3 (n int);
+select count(*) from t3 where n >= 4;
+count(*)
+100
 create table t4 select * from temp_table;
 show binary logs;
 Log_name
diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result
index 42f7257dbba..e302a0cefeb 100644
--- a/mysql-test/r/select_found.result
+++ b/mysql-test/r/select_found.result
@@ -189,3 +189,22 @@ SELECT FOUND_ROWS();
 FOUND_ROWS()
 3
 drop table t1;
+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;
+id
+4
+select FOUND_ROWS();
+FOUND_ROWS()
+2
+select SQL_CALC_FOUND_ROWS * from t1 where id > 3 AND 1=2 limit 0, 1;
+id
+select FOUND_ROWS();
+FOUND_ROWS()
+0
+select SQL_CALC_FOUND_ROWS * from t1 where id > 6 limit 0, 1;
+id
+select FOUND_ROWS();
+FOUND_ROWS()
+0
+drop table t1;
diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result
index 48a44fc2b44..5f726476d96 100644
--- a/mysql-test/r/symlink.result
+++ b/mysql-test/r/symlink.result
@@ -64,3 +64,23 @@ t9	CREATE TABLE `t9` (
   PRIMARY KEY  (`a`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DATA DIRECTORY='TEST_DIR/var/tmp/' INDEX DIRECTORY='TEST_DIR/var/run/'
 drop database mysqltest;
+create table t1 (a int not null) type=myisam;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL default '0'
+) TYPE=MyISAM
+alter table t1 add b int;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL default '0',
+  `b` int(11) default NULL
+) TYPE=MyISAM
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL default '0',
+  `b` int(11) default NULL
+) TYPE=MyISAM
+drop table t1;
diff --git a/mysql-test/r/timezone.result b/mysql-test/r/timezone.result
index b82b39da262..20706408075 100644
--- a/mysql-test/r/timezone.result
+++ b/mysql-test/r/timezone.result
@@ -1,4 +1,13 @@
 DROP TABLE IF EXISTS t1;
+show variables like "timezone";
+Variable_name	Value
+timezone	MET
+select @a:=FROM_UNIXTIME(1);
+@a:=FROM_UNIXTIME(1)
+1970-01-01 01:00:01
+select unix_timestamp(@a);
+unix_timestamp(@a)
+1
 CREATE TABLE t1 (ts int);
 INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00'));
 INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
diff --git a/mysql-test/r/truncate.result b/mysql-test/r/truncate.result
index 0e256c1bf8e..d777bd184b2 100644
--- a/mysql-test/r/truncate.result
+++ b/mysql-test/r/truncate.result
@@ -23,3 +23,12 @@ n
 drop table t1;
 truncate non_existing_table;
 ERROR 42S02: Table 'test.non_existing_table' doesn't exist
+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;
+a
+1
+2
+drop table t1;
diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result
index a69e3308f0a..f069114f752 100644
--- a/mysql-test/r/type_blob.result
+++ b/mysql-test/r/type_blob.result
@@ -510,3 +510,149 @@ show full fields from t1;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 load_file('../../std_data/words.dat')	longblob	NULL	YES		NULL		select,insert,update,references	
 drop table t1;
+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';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt='Chevy ';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt='Chevy ' or txt='Chevy';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt='Chevy' or txt='Chevy ';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where id='1' or id='2';
+id	txt
+1	Chevy
+2	Chevy 
+insert into t1 (txt) values('Ford');
+select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford';
+id	txt
+1	Chevy
+2	Chevy 
+3	Ford
+select * from t1 where txt='Chevy' or txt='Chevy ';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt in ('Chevy ','Chevy');
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt in ('Chevy');
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt between 'Chevy' and 'Chevy';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy ';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt between 'Chevy' and 'Chevy ';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt < 'Chevy ';
+id	txt
+select * from t1 where txt <= 'Chevy';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt > 'Chevy';
+id	txt
+3	Ford
+select * from t1 where txt >= 'Chevy';
+id	txt
+1	Chevy
+2	Chevy 
+3	Ford
+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;
+id	txt
+3	NULL
+1	Chevy
+2	Chevy 
+select * from t1 where txt='Chevy ';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt='Chevy ' or txt='Chevy';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt='Chevy' or txt='Chevy ';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where id='1' or id='2';
+id	txt
+1	Chevy
+2	Chevy 
+insert into t1 (txt) values('Ford');
+select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford';
+id	txt
+1	Chevy
+2	Chevy 
+4	Ford
+select * from t1 where txt='Chevy' or txt='Chevy ';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt in ('Chevy ','Chevy');
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt in ('Chevy');
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt between 'Chevy' and 'Chevy';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy ';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt between 'Chevy' and 'Chevy ';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt < 'Chevy ';
+id	txt
+select * from t1 where txt < 'Chevy ' or txt is NULL;
+id	txt
+3	NULL
+select * from t1 where txt <= 'Chevy';
+id	txt
+1	Chevy
+2	Chevy 
+select * from t1 where txt > 'Chevy';
+id	txt
+4	Ford
+select * from t1 where txt >= 'Chevy';
+id	txt
+1	Chevy
+2	Chevy 
+4	Ford
+drop table t1;
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 204266d6ec2..a32c97f1d49 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -1,6 +1,6 @@
 drop table if exists t1;
 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;
 t
 2000-01-01 00:00:00
@@ -15,6 +15,11 @@ t
 1999-12-31 23:59:59
 1000-01-01 00:00:00
 9999-12-31 23:59:59
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+0000-00-00 00:00:00
 delete from t1 where t > 0;
 optimize table t1;
 Table	Op	Msg_type	Msg_text
@@ -22,7 +27,8 @@ test.t1	optimize	status	OK
 check table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
-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;
 t
 2000-01-01 00:00:00
@@ -38,6 +44,11 @@ t
 1999-12-31 23:59:59
 1000-01-01 00:00:00
 9999-12-31 23:59:59
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+0000-00-00 00:00:00
 drop table t1;
 CREATE TABLE t1 (a timestamp, b date, c time, d datetime);
 insert into t1 (b,c,d) values(now(),curtime(),now());
diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result
index da25394eb08..ee7d6583d64 100644
--- a/mysql-test/r/type_decimal.result
+++ b/mysql-test/r/type_decimal.result
@@ -444,3 +444,9 @@ CREATE TABLE t1 (a_dec DECIMAL(-2,1));
 ERROR 42000: 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 '-2,1))' at line 1
 CREATE TABLE t1 (a_dec DECIMAL(-1,1));
 ERROR 42000: 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,1))' at line 1
+create table t1(a decimal(10,4));
+insert into t1 values ("+0000100000000");
+select * from t1;
+a
+9999999.9999
+drop table t1;
diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result
index 1903037480a..e13a93a4156 100644
--- a/mysql-test/r/type_enum.result
+++ b/mysql-test/r/type_enum.result
@@ -1636,6 +1636,16 @@ t1	CREATE TABLE `t1` (
   `a` enum('','a','b') NOT NULL default 'b'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
+create table t1 (a enum ('0','1'));
+insert into t1 set a='foobar';
+select * from t1;
+a
+
+update t1 set a = replace(a,'x','y');
+select * from t1;
+a
+
+drop table t1;
 create table t1 (a enum(0xE4, '1', '2') not null default 0xE4);
 show columns from t1;
 Field	Type	Null	Key	Default	Extra
diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result
index 0c75155146d..a5d3abcdefc 100644
--- a/mysql-test/r/type_timestamp.result
+++ b/mysql-test/r/type_timestamp.result
@@ -24,6 +24,15 @@ a	t
 7	2002-03-05 00:00:00
 8	0000-00-00 00:00:00
 9	1970-01-01 03:20:38
+1	1970-01-01 03:20:34
+2	2002-03-03 00:00:00
+3	1970-01-01 03:20:35
+4	1970-01-01 03:20:36
+5	2002-03-04 00:00:00
+6	1970-01-01 03:20:37
+7	2002-03-05 00:00:00
+8	0000-00-00 00:00:00
+9	1970-01-01 03:20:38
 drop table t1,t2;
 SET TIMESTAMP=1234;
 CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, PRIMARY KEY (id));
@@ -43,7 +52,7 @@ date_format(a,"%Y %y")	year(a)	year(now())
 1970 70	1970	1970
 drop table t1;
 create table t1 (ix timestamp);
-insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000);
+insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101);
 select ix+0 from t1;
 ix+0
 19991101000000
@@ -55,6 +64,24 @@ ix+0
 19990501000000
 19991101000000
 19990501000000
+00000000000000
+00000000000000
+00000000000000
+00000000000000
+00000000000000
+delete from t1;
+insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101");
+select * from t1;
+ix
+19991101000000
+19990102030405
+19990630232922
+19990601000000
+00000000000000
+00000000000000
+00000000000000
+00000000000000
+00000000000000
 drop table t1;
 CREATE TABLE t1 (date date, date_time datetime, time_stamp timestamp);
 INSERT INTO t1 VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959);
@@ -104,3 +131,5 @@ t2	t4	t6	t8	t10	t12	t14
 0000-00-00 00:00:00	0000-00-00 00:00:00	0000-00-00 00:00:00	0000-00-00 00:00:00	0000-00-00 00:00:00	0000-00-00 00:00:00	0000-00-00 00:00:00
 1997-12-31 23:47:59	1997-12-31 23:47:59	1997-12-31 23:47:59	1997-12-31 23:47:59	1997-12-31 23:47:59	1997-12-31 23:47:59	1997-12-31 23:47:59
 drop table t1;
+create table t1 (a timestamp default 1);
+Invalid default value for 'a'
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index b3bf95bdf3d..1cfc6eedbaf 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -3,7 +3,7 @@ CREATE TABLE t1 (a int not null, b char (10) not null);
 insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
 CREATE TABLE t2 (a int not null, b char (10) not null);
 insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');
-select a,b from t1 union select a,b from t2;
+select a,b from t1 union distinct select a,b from t2;
 a	b
 1	a
 2	b
@@ -434,7 +434,14 @@ a
 3
 (SELECT * FROM t1) UNION all (SELECT SQL_CALC_FOUND_ROWS * FROM t2) LIMIT 1;
 ERROR 42000: Wrong usage/placement of 'SQL_CALC_FOUND_ROWS'
+create temporary table t1 select a from t1 union select a from t2;
+create table t1 select a from t1 union select a from t2;
+INSERT TABLE 't1' isn't allowed in FROM table list
 drop table t1,t2;
+select length(version()) > 1 as `*` UNION select 2;
+*
+1
+2
 CREATE TABLE t1 (  id int(3) unsigned default '0') ENGINE=MyISAM;
 INSERT INTO t1 (id) VALUES("1");
 CREATE TABLE t2 ( id int(3) unsigned default '0',  id_master int(5) default '0',  text1 varchar(5) default NULL,  text2 varchar(5) default NULL) ENGINE=MyISAM;