fixed bug #910 (right type of ifnull function)

mysql-test/r/create.result:
  added test for bug #910 (right type of ifnull function)
mysql-test/t/create.test:
  added test for bug #910 (right type of ifnull function)
sql/field.h:
  added new constructors of Field_decimal, Field_tiny, Field_short, Field_float,
  Field_null, Field_year
  for using in Item::tmp_table_field_from_field_type
sql/item.cc:
  added Item::tmp_table_field_from_field_type
sql/item.h:
  added Item::tmp_table_field_from_field_type

1 files changed, 49 insertions, 0 deletions

diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index 9a0eca52b84..5567cc7c841 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -310,3 +310,52 @@ t1	CREATE TABLE `t1` (
 ) TYPE=MyISAM CHARSET=latin1
 SET SESSION table_type=default;
 drop table t1;
+create table t1(a int,b int,c int unsigned,d date,e char,f datetime,g time,h blob);
+insert into t1(a)values(1);
+insert into t1(a,b,c,d,e,f,g,h)
+values(2,-2,2,'1825-12-14','a','2003-1-1 3:2:1','4:3:2','binary data');
+select * from t1;
+a	b	c	d	e	f	g	h
+1	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+2	-2	2	1825-12-14	a	2003-01-01 03:02:01	04:03:02	binary data
+select a, 
+ifnull(b,cast(-7 as signed)) as b, 
+ifnull(c,cast(7 as unsigned)) as c, 
+ifnull(d,cast('2000-01-01' as date)) as d, 
+ifnull(e,cast('b' as char)) as e,
+ifnull(f,cast('2000-01-01' as datetime)) as f, 
+ifnull(g,cast('5:4:3' as time)) as g,
+ifnull(h,cast('yet another binary data' as binary)) as h,
+addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd 
+from t1;
+a	b	c	d	e	f	g	h	dd
+1	-7	7	2000-01-01	b	2000-01-01 00:00:00	05:04:03	yet another binary data	02:00:00
+2	-2	2	1825-12-14	a	2003-01-01 03:02:01	04:03:02	binary data	02:00:00
+create table t2
+select
+a, 
+ifnull(b,cast(-7                        as signed))   as b,
+ifnull(c,cast(7                         as unsigned)) as c,
+ifnull(d,cast('2000-01-01'              as date))     as d,
+ifnull(e,cast('b'                       as char))     as e,
+ifnull(f,cast('2000-01-01'              as datetime)) as f,
+ifnull(g,cast('5:4:3'                   as time))     as g,
+ifnull(h,cast('yet another binary data' as binary))   as h,
+addtime(cast('1:0:0' as time),cast('1:0:0' as time))  as dd
+from t1;
+explain t2;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+b	bigint(11)			0	
+c	bigint(10)			0	
+d	date			0000-00-00	
+e	char(1)				
+f	datetime			0000-00-00 00:00:00	
+g	time			00:00:00	
+h	mediumblob				
+dd	time			00:00:00	
+select * from t2;
+a	b	c	d	e	f	g	h	dd
+1	-7	7	2000-01-01	b	2000-01-01 00:00:00	05:04:03	yet another binary data	02:00:00
+2	-2	2	1825-12-14	a	2003-01-01 03:02:01	04:03:02	binary data	02:00:00
+drop table t1, t2;