Fixes for binary protocol (complement to last push)

Changed timestamp to return string in YYYY-MM-DD HH:MM:SS format.
DATE_ADD() and related functions now returns correct DATE/DATETIME type depending on argument types.
Now all tests passes, still some work left to remove warnings in log files from mysql-test-run


mysql-test/r/cast.result:
  New result for time
mysql-test/r/delayed.result:
  Timestamp update
mysql-test/r/derived.result:
  Fix after bulk insert change
mysql-test/r/explain.result:
  Fix after bulk insert change
mysql-test/r/func_date_add.result:
  Timestamp change
mysql-test/r/func_str.result:
  Timestamp change
mysql-test/r/func_time.result:
  Timestamp change
mysql-test/r/innodb.result:
  Timestamp change
mysql-test/r/join_outer.result:
  Fix after bulk insert change
mysql-test/r/key_primary.result:
  Fix after bulk insert change
mysql-test/r/keywords.result:
  Timestamp change
mysql-test/r/merge.result:
  Removed warning
mysql-test/r/odbc.result:
  Fix after bulk insert change
mysql-test/r/range.result:
  Fix after bulk insert change
mysql-test/r/select.result:
  Fix after bulk insert change
mysql-test/r/subselect.result:
  Fixed EXPLAIN output
mysql-test/r/type_datetime.result:
  Timestamp update
mysql-test/r/type_ranges.result:
  Timestamp update
mysql-test/r/type_timestamp.result:
  Timestamp update
mysql-test/r/union.result:
  EXPLAIN UPDATE
mysql-test/t/func_str.test:
  Timestamp update
mysql-test/t/func_time.test:
  New test for interval type result
mysql-test/t/merge.test:
  Remove warnings of wrong drop table
mysql-test/t/type_datetime.test:
  Timestamp change
mysql-test/t/type_timestamp.test:
  Timestamp change
sql/field.cc:
  Changed timestamp to return string in YYYY-MM-DD HH:MM:SS format
sql/field.h:
  Changed timestamp to return string in YYYY-MM-DD HH:MM:SS format
sql/item.cc:
  Binary protocol update
sql/item.h:
  Binary protocol update
sql/item_func.cc:
  Added comment
sql/item_func.h:
  @variables are always returned to the client as strings
sql/item_timefunc.cc:
  Changed INTERVAL to return correct type
sql/item_timefunc.h:
  Changed INTERVAL to return correct type
sql/mysqld.cc:
  Changed default pthread_attr_setstacksize to 129K
sql/protocol.cc:
  More type checking
sql/set_var.cc:
  Fixed that @convert works ok with new protocol
sql/sql_analyse.cc:
  Fixed bug in analyze
sql/sql_class.cc:
  Fixed bug from last push in LIMIT
sql/sql_error.cc:
  More optimal types
sql/sql_repl.cc:
  Binary protocol changes
sql/sql_select.cc:
  Fixed bug in multi-table-update
  Changed EXPLAIN to return NULL instead of empty strings
sql/sql_show.cc:
  Binary protocol

20 files changed, 150 insertions, 90 deletions

diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index 2309de2354b..db28038526b 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -27,7 +27,7 @@ cast("2001-1-1" as DATE)	cast("2001-1-1" as DATETIME)
 2001-01-01	2001-01-01 00:00:00
 select cast("1:2:3" as TIME);
 cast("1:2:3" as TIME)
-1:02:03
+01:02:03
 select cast("2001-1-1" as date) = "2001-01-01";
 cast("2001-1-1" as date) = "2001-01-01"
 0
diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result
index 9e375203e49..8e4b52a8366 100644
--- a/mysql-test/r/delayed.result
+++ b/mysql-test/r/delayed.result
@@ -12,9 +12,9 @@ select * from t1 where tmsp=0;
 a	tmsp
 select * from t1 where tmsp=19711006010203;
 a	tmsp
-5	19711006010203
-6	19711006010203
-8	19711006010203
+5	1971-10-06 01:02:03
+6	1971-10-06 01:02:03
+8	1971-10-06 01:02:03
 drop table t1;
 create table t1 (a int not null auto_increment primary key, b char(10));
 insert delayed into t1 values (1,"b");
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 95640e69d8b..49b9122ef46 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -89,7 +89,7 @@ a	t
 20	20
 explain select count(*) from t1 as tt1, (select * from t1) as tt2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY								Select tables optimized away
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 2	DERIVED	tt1	index	NULL	a	4	NULL	10000	Using index
 drop table if exists  t1;
 SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index dafed9a7d41..2892c2ea587 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -27,4 +27,4 @@ Key column 'foo' doesn't exist in table
 drop table t1;
 explain select 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE								No tables used
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
diff --git a/mysql-test/r/func_date_add.result b/mysql-test/r/func_date_add.result
index acdf2c5d7be..234fded9a68 100644
--- a/mysql-test/r/func_date_add.result
+++ b/mysql-test/r/func_date_add.result
@@ -31,17 +31,17 @@ INSERT INTO t1 VALUES (357917728,7,2,2,20000319145027);
 select visitor_id,max(ts) as mts from t1 group by visitor_id
 having mts < DATE_SUB(NOW(),INTERVAL 3 MONTH);
 visitor_id	mts
-48985536	20000319013932
-173865424	20000318233615
-357917728	20000319145027
-465931136	20000318160953
-1092858576	20000319013445
+48985536	2000-03-19 01:39:32
+173865424	2000-03-18 23:36:15
+357917728	2000-03-19 14:50:27
+465931136	2000-03-18 16:09:53
+1092858576	2000-03-19 01:34:45
 select visitor_id,max(ts) as mts from t1 group by visitor_id
 having DATE_ADD(mts,INTERVAL 3 MONTH) < NOW();
 visitor_id	mts
-48985536	20000319013932
-173865424	20000318233615
-357917728	20000319145027
-465931136	20000318160953
-1092858576	20000319013445
+48985536	2000-03-19 01:39:32
+173865424	2000-03-18 23:36:15
+357917728	2000-03-19 14:50:27
+465931136	2000-03-18 16:09:53
+1092858576	2000-03-19 01:34:45
 drop table t1;
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 69e37d7b911..cc4be85988e 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -213,14 +213,14 @@ bugstatus int(10) unsigned default NULL,
 submitter int(10) unsigned default NULL
 ) TYPE=MyISAM;
 INSERT INTO t1 VALUES (1,'Link',1,1,1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','2001-02-28 08:40:16',20010228084016,0,4);
-SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugstatus,submitter), '"') FROM t1;
-CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugstatus,submitter), '"')
+SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter), '"') FROM t1;
+CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter), '"')
 "Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4"
 SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"') FROM t1;
 CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"')
 "Link";"1";"1";"1";"0";"4"
-SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugstatus,submitter) FROM t1;
-CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugstatus,submitter)
+SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter) FROM t1;
+CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter)
 Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4
 drop table t1;
 CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) TYPE=MyISAM;
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 2941352c776..d8491035f0b 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -392,11 +392,71 @@ CREATE TABLE t3 (ctime1 char(19) NOT NULL, ctime2 char(19) NOT NULL);
 INSERT INTO t3 VALUES ("2002-10-29 16:51:06","2002-11-05 16:47:31");
 select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2;
 start	ctime1	ctime2
-2002-11-04 00:00:00	20021029165106	20021105164731
+2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31
 select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2;
 start	ctime1	ctime2
-2002-11-04 00:00:00	20021029165106	20021105164731
+2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31
 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;
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 467436a2f85..82e678416ad 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -959,29 +959,29 @@ INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,0000000000000
 INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
 select * from t1;
 number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
-4077711111	SeanWheeler	90	2	20020111112846	500	00000000000000	-1	2	3	1
-9197722223	berry	90	3	20020111112809	500	20020102114532	501	4	10	0
-650	San Francisco	0	0	20011227111336	342	00000000000000	-1	1	24	1
-302467	Sue's Subshop	90	3	20020109113241	500	20020102115111	501	7	24	0
-6014911113	SudzCarwash	520	1	20020102115234	500	20020102115259	501	33	32768	0
-333	tubs	99	2	20020109113440	501	20020109113440	500	3	10	0
+4077711111	SeanWheeler	90	2	2002-01-11 11:28:46	500	0000-00-00 00:00:00	-1	2	3	1
+9197722223	berry	90	3	2002-01-11 11:28:09	500	2002-01-02 11:45:32	501	4	10	0
+650	San Francisco	0	0	2001-12-27 11:13:36	342	0000-00-00 00:00:00	-1	1	24	1
+302467	Sue's Subshop	90	3	2002-01-09 11:32:41	500	2002-01-02 11:51:11	501	7	24	0
+6014911113	SudzCarwash	520	1	2002-01-02 11:52:34	500	2002-01-02 11:52:59	501	33	32768	0
+333	tubs	99	2	2002-01-09 11:34:40	501	2002-01-09 11:34:40	500	3	10	0
 select * from t2;
 number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
-4077711111	SeanWheeler	0	2	20020111112853	500	00000000000000	-1	2	3	1
-9197722223	berry	90	3	20020111112818	500	20020102114532	501	4	10	0
-650	San Francisco	90	0	20020109113158	342	00000000000000	-1	1	24	1
-333	tubs	99	2	20020109113453	501	20020109113453	500	3	10	0
+4077711111	SeanWheeler	0	2	2002-01-11 11:28:53	500	0000-00-00 00:00:00	-1	2	3	1
+9197722223	berry	90	3	2002-01-11 11:28:18	500	2002-01-02 11:45:32	501	4	10	0
+650	San Francisco	90	0	2002-01-09 11:31:58	342	0000-00-00 00:00:00	-1	1	24	1
+333	tubs	99	2	2002-01-09 11:34:53	501	2002-01-09 11:34:53	500	3	10	0
 delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or  (t1.carrier_id=90 and t2.number is null);
 select * from t1;
 number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
-6014911113	SudzCarwash	520	1	20020102115234	500	20020102115259	501	33	32768	0
-333	tubs	99	2	20020109113440	501	20020109113440	500	3	10	0
+6014911113	SudzCarwash	520	1	2002-01-02 11:52:34	500	2002-01-02 11:52:59	501	33	32768	0
+333	tubs	99	2	2002-01-09 11:34:40	501	2002-01-09 11:34:40	500	3	10	0
 select * from t2;
 number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
-333	tubs	99	2	20020109113453	501	20020109113453	500	3	10	0
+333	tubs	99	2	2002-01-09 11:34:53	501	2002-01-09 11:34:53	500	3	10	0
 select * from t2;
 number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
-333	tubs	99	2	20020109113453	501	20020109113453	500	3	10	0
+333	tubs	99	2	2002-01-09 11:34:53	501	2002-01-09 11:34:53	500	3	10	0
 drop table t1,t2;
 create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) type=innodb;
 BEGIN;
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 700b73d3eca..b89a6696727 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -91,7 +91,7 @@ grp	a	c	id	a	c	d
 NULL	NULL		NULL	NULL	NULL	NULL
 explain select t1.*,t2.* from t1,t2 where t1.a=t2.a and isnull(t2.a)=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE								Impossible WHERE noticed after reading const tables
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	
diff --git a/mysql-test/r/key_primary.result b/mysql-test/r/key_primary.result
index e148548b721..14ca90b3dd2 100644
--- a/mysql-test/r/key_primary.result
+++ b/mysql-test/r/key_primary.result
@@ -16,5 +16,5 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	3	const	1	
 describe select * from t1 where t1="ABCD";
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE								Impossible WHERE noticed after reading const tables
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 drop table t1;
diff --git a/mysql-test/r/keywords.result b/mysql-test/r/keywords.result
index 2ca36425841..c218379110f 100644
--- a/mysql-test/r/keywords.result
+++ b/mysql-test/r/keywords.result
@@ -3,7 +3,7 @@ create table t1 (time time, date date, timestamp timestamp);
 insert into t1 values ("12:22:22","97:02:03","1997-01-02");
 select * from t1;
 time	date	timestamp
-12:22:22	1997-02-03	19970102000000
+12:22:22	1997-02-03	1997-01-02 00:00:00
 select t1.time+0,t1.date+0,t1.timestamp+0,concat(date," ",time) from t1;
 t1.time+0	t1.date+0	t1.timestamp+0	concat(date," ",time)
 122222	19970203	19970102000000	1997-02-03 12:22:22
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index cd78ac791c4..d0e595d6551 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -560,7 +560,7 @@ select * from t6;
 a
 1
 2
-drop table if exists t1, t2, t3, t4, t5, t6;
+drop table if exists t6, t3, t1, t2, t4, t5;
 DROP TABLE IF EXISTS t1, t2;
 CREATE TABLE t1 (
 fileset_id tinyint(3) unsigned NOT NULL default '0',
@@ -595,4 +595,4 @@ EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
 AND file_code = '0000000115' LIMIT 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	const	PRIMARY,files	PRIMARY	33	const,const	1	
-DROP TABLE IF EXISTS t1, t2;
+DROP TABLE IF EXISTS t2, t1;
diff --git a/mysql-test/r/odbc.result b/mysql-test/r/odbc.result
index 498147704a3..30366762cd0 100644
--- a/mysql-test/r/odbc.result
+++ b/mysql-test/r/odbc.result
@@ -12,5 +12,5 @@ select * from t1 where a is null;
 a	b
 explain select * from t1 where b is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE								Impossible WHERE noticed after reading const tables
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 drop table t1;
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index a3663416bc8..86a93398a9f 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -16,7 +16,7 @@ event_date	type	event_id
 1999-07-14	100600	10
 explain select event_date,type,event_id from t1 WHERE type = 100601 and event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE								Impossible WHERE
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 select event_date,type,event_id from t1 WHERE event_date >= "1999-07-01" AND event_date <= "1999-07-15" AND (type=100600 OR type=100100) or event_date >= "1999-07-01" AND event_date <= "1999-07-15" AND type=100099;
 event_date	type	event_id
 1999-07-10	100100	24
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 46343eb8248..1914edf799e 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3097,7 +3097,7 @@ fld1	sum(price)
 038008	234298
 explain select fld3 from t2 where 1>2 or 2>3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE								Impossible WHERE
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 explain select fld3 from t2 where fld1=fld1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	
@@ -3152,7 +3152,7 @@ count(*)
 4181
 explain select min(fld1),max(fld1),count(*) from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE								Select tables optimized away
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 select min(fld1),max(fld1),count(*) from t2;
 min(fld1)	max(fld1)	count(*)
 0	1232609	1199
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index d442e4d97ce..c1aa21e6798 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -20,8 +20,8 @@ Reference 'a' not supported (forward reference in item list)
 EXPLAIN SELECT 1 FROM (SELECT 1 as a) as b  HAVING (SELECT a)=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
-3	DEPENDENT SUBSELECT								No tables used
-2	DERIVED								No tables used
+3	DEPENDENT SUBSELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
 1
 1
@@ -280,7 +280,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	41	NULL	2	Using where; Using index
 EXPLAIN SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY								No tables used
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	SUBSELECT	t1	index	NULL	PRIMARY	41	NULL	2	Using where; Using index
 SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
 date
@@ -298,8 +298,8 @@ Subselect returns more than 1 record
 EXPLAIN SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	topic	3	NULL	2	Using index
-2	SUBSELECT								No tables used
-3	UNION								No tables used
+2	SUBSELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 drop table t1;
 CREATE TABLE `t1` (
 `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -394,11 +394,11 @@ EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1
 Subselect returns more than 1 record
 EXPLAIN SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE								Select tables optimized away
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 EXPLAIN SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	
-2	SUBSELECT								Select tables optimized away
+2	SUBSELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
@@ -574,14 +574,14 @@ id
 EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1+(select 1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	ref	id	id	5	const	1	Using where; Using index
-3	SUBSELECT								No tables used
+3	SUBSELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1247	Select 2 was reduced during optimisation
 EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	index	NULL	id	5	NULL	2	Using where; Using index
-2	DEPENDENT SUBSELECT								No tables used
-3	UNION								No tables used
+2	DEPENDENT SUBSELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 3);
 id
 SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 2);
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 1e58b8da42e..cac8cd3d71e 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -41,8 +41,8 @@ t
 drop table t1;
 CREATE TABLE t1 (a timestamp, b date, c time, d datetime);
 insert into t1 (b,c,d) values(now(),curtime(),now());
-select date_format(a,"%Y-%m-%d")=b,right(a,6)=c+0,a=d+0 from t1;
-date_format(a,"%Y-%m-%d")=b	right(a,6)=c+0	a=d+0
+select date_format(a,"%Y-%m-%d")=b,right(a+0,6)=c+0,a=d+0 from t1;
+date_format(a,"%Y-%m-%d")=b	right(a+0,6)=c+0	a=d+0
 1	1	1
 drop table t1;
 CREATE TABLE t1 (a datetime not null);
diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
index d0f964e4641..ea2863aefce 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -53,7 +53,7 @@ ushort	smallint(5) unsigned zerofill		MUL	00000		select,insert,update,references
 umedium	mediumint(8) unsigned		MUL	0		select,insert,update,references	
 ulong	int(11) unsigned		MUL	0		select,insert,update,references	
 ulonglong	bigint(13) unsigned		MUL	0		select,insert,update,references	
-time_stamp	timestamp(14)	YES		NULL		select,insert,update,references	
+time_stamp	timestamp	YES		NULL		select,insert,update,references	
 date_field	date	YES		NULL		select,insert,update,references	
 time_field	time	YES		NULL		select,insert,update,references	
 date_time	datetime	YES		NULL		select,insert,update,references	
@@ -183,7 +183,7 @@ ushort	smallint(5) unsigned zerofill			00000		select,insert,update,references
 umedium	mediumint(8) unsigned		MUL	0		select,insert,update,references	
 ulong	int(11) unsigned		MUL	0		select,insert,update,references	
 ulonglong	bigint(13) unsigned		MUL	0		select,insert,update,references	
-time_stamp	timestamp(14)	YES		NULL		select,insert,update,references	
+time_stamp	timestamp	YES		NULL		select,insert,update,references	
 date_field	varchar(10) character set latin1	YES		NULL		select,insert,update,references	
 time_field	time	YES		NULL		select,insert,update,references	
 date_time	datetime	YES		NULL		select,insert,update,references	
@@ -209,7 +209,7 @@ ushort	smallint(5) unsigned zerofill			00000		select,insert,update,references
 umedium	mediumint(8) unsigned			0		select,insert,update,references	
 ulong	int(11) unsigned			0		select,insert,update,references	
 ulonglong	bigint(13) unsigned			0		select,insert,update,references	
-time_stamp	timestamp(14)	YES		NULL		select,insert,update,references	
+time_stamp	timestamp	YES		NULL		select,insert,update,references	
 date_field	varchar(10) character set latin1	YES		NULL		select,insert,update,references	
 time_field	time	YES		NULL		select,insert,update,references	
 date_time	datetime	YES		NULL		select,insert,update,references	
diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result
index 088f3b205b9..26dedf544c4 100644
--- a/mysql-test/r/type_timestamp.result
+++ b/mysql-test/r/type_timestamp.result
@@ -15,26 +15,26 @@ SET TIMESTAMP=1238;
 insert into t1 (a) select a+1 from t2 where a=8;
 select * from t1;
 a	t
-1	19700101032034
-2	20020303000000
-3	19700101032035
-4	19700101032036
-5	20020304000000
-6	19700101032037
-7	20020305000000
-8	00000000000000
-9	19700101032038
+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));
 INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00");
 SELECT stamp FROM t1 WHERE id="myKey";
 stamp
-19990402000000
+1999-04-02 00:00:00
 UPDATE t1 SET value="my value" WHERE id="myKey";
 SELECT stamp FROM t1 WHERE id="myKey";
 stamp
-19990402000000
+1999-04-02 00:00:00
 drop table t1;
 create table t1 (a timestamp);
 insert into t1 values (now());
@@ -44,8 +44,8 @@ date_format(a,"%Y %y")	year(a)	year(now())
 drop table t1;
 create table t1 (ix timestamp);
 insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000);
-select * from t1;
-ix
+select ix+0 from t1;
+ix+0
 19991101000000
 19990102030405
 19990630232922
@@ -71,16 +71,16 @@ INSERT INTO t1 VALUES ("2005-01-01","2005-01-01 00:00:00",20050101000000);
 INSERT INTO t1 VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000);
 SELECT * FROM t1;
 date	date_time	time_stamp
-1998-12-31	1998-12-31 23:59:59	19981231235959
-1999-01-01	1999-01-01 00:00:00	19990101000000
-1999-09-09	1999-09-09 23:59:59	19990909235959
-2000-01-01	2000-01-01 00:00:00	20000101000000
-2000-02-28	2000-02-28 00:00:00	20000228000000
-2000-02-29	2000-02-29 00:00:00	20000229000000
-2000-03-01	2000-03-01 00:00:00	20000301000000
-2000-12-31	2000-12-31 23:59:59	20001231235959
-2001-01-01	2001-01-01 00:00:00	20010101000000
-2004-12-31	2004-12-31 23:59:59	20041231235959
-2005-01-01	2005-01-01 00:00:00	20050101000000
-2030-01-01	2030-01-01 00:00:00	20300101000000
+1998-12-31	1998-12-31 23:59:59	1998-12-31 23:59:59
+1999-01-01	1999-01-01 00:00:00	1999-01-01 00:00:00
+1999-09-09	1999-09-09 23:59:59	1999-09-09 23:59:59
+2000-01-01	2000-01-01 00:00:00	2000-01-01 00:00:00
+2000-02-28	2000-02-28 00:00:00	2000-02-28 00:00:00
+2000-02-29	2000-02-29 00:00:00	2000-02-29 00:00:00
+2000-03-01	2000-03-01 00:00:00	2000-03-01 00:00:00
+2000-12-31	2000-12-31 23:59:59	2000-12-31 23:59:59
+2001-01-01	2001-01-01 00:00:00	2001-01-01 00:00:00
+2004-12-31	2004-12-31 23:59:59	2004-12-31 23:59:59
+2005-01-01	2005-01-01 00:00:00	2005-01-01 00:00:00
+2030-01-01	2030-01-01 00:00:00	2030-01-01 00:00:00
 drop table t1;
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 02dda58d48f..9b16b8c63a2 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -102,16 +102,16 @@ Unknown column 'xx' in 'field list'
 explain select a,b from t1 union select 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	UNION								No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 explain select 1 union select a,b from t1 union select 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY								No tables used
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	UNION	t1	ALL	NULL	NULL	NULL	NULL	4	
-3	UNION								No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 explain select a,b from t1 union select 1 limit 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY								Impossible WHERE
-2	UNION								Impossible WHERE
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 select a,b from t1 into outfile 'skr' union select a,b from t2;
 Wrong usage of UNION and INTO
 select a,b from t1 order by a union select a,b from t2;