Fix for bug #9855 (Inconsistend column type in create select)

5 files changed, 54 insertions, 6 deletions

diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index d7a741827d5..200b4a4d53b 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -131,9 +131,9 @@ grp	group_concat(a separator "")+0
 3	456789
 select grp, group_concat(a separator "")+0.0 from t1 group by grp;
 grp	group_concat(a separator "")+0.0
-1	1.0
-2	23.0
-3	456789.0
+1	1
+2	23
+3	456789
 select grp, ROUND(group_concat(a separator "")) from t1 group by grp;
 grp	ROUND(group_concat(a separator ""))
 1	1
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 4c795abe986..500aa4b1728 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -213,9 +213,9 @@ select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from
 a
 select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
 b	(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)
-8	7.5000
-8	4.5000
-9	7.5000
+8	7.5
+8	4.5
+9	7.5
 explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	3	
diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result
index 6e381192270..e8daeb08526 100644
--- a/mysql-test/r/type_float.result
+++ b/mysql-test/r/type_float.result
@@ -250,3 +250,25 @@ t3	CREATE TABLE `t3` (
   `d` double(22,9) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1, t2, t3;
+create table t1 select  105213674794682365.00 + 0.0 x;
+show warnings;
+Level	Code	Message
+desc  t1;
+Field	Type	Null	Key	Default	Extra
+x	double			0	
+drop table t1;
+create table t1 select 0.0 x;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+x	double(3,1)			0.0	
+create table t2 select 105213674794682365.00 y;
+desc t2;
+Field	Type	Null	Key	Default	Extra
+y	double(21,2)			0.00	
+create table t3 select x+y a from t1,t2;
+show warnings;
+Level	Code	Message
+desc t3;
+Field	Type	Null	Key	Default	Extra
+a	double			0	
+drop table t1,t2,t3;
diff --git a/mysql-test/t/type_float.test b/mysql-test/t/type_float.test
index abaf72ea2ed..75723d2a0ff 100644
--- a/mysql-test/t/type_float.test
+++ b/mysql-test/t/type_float.test
@@ -162,4 +162,21 @@ show create table t3;
 drop table t1, t2, t3;
 
 
+#
+# Bug #9855 (inconsistent column type for create select
+#
+create table t1 select  105213674794682365.00 + 0.0 x;
+show warnings;
+desc  t1;
+drop table t1;
+
+create table t1 select 0.0 x;
+desc t1;
+create table t2 select 105213674794682365.00 y;
+desc t2;
+create table t3 select x+y a from t1,t2;
+show warnings;
+desc t3;
+drop table t1,t2,t3;
+
 # End of 4.1 tests
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 288859443ff..019147e151f 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -359,10 +359,19 @@ String *Item_num_func::val_str(String *str)
 
 void Item_func::fix_num_length_and_dec()
 {
+  uint fl_length= 0;
   decimals=0;
   for (uint i=0 ; i < arg_count ; i++)
+  {
     set_if_bigger(decimals,args[i]->decimals);
+    set_if_bigger(fl_length, args[i]->max_length);
+  }
   max_length=float_length(decimals);
+  if (fl_length > max_length)
+  {
+    decimals= NOT_FIXED_DEC;
+    max_length= float_length(NOT_FIXED_DEC);
+  }
 }
 
 Item *Item_func::get_tmp_table_item(THD *thd)