Fixed BUG#12589: Assert when creating temp. table from decimal stored

                 procedure variable
  Second version, after review.
  Keep the unsigned_flag in Item_decimal updated. Note that this also changed
  the result of several old test results - creating tables from decimal
  templates now gives unsigned columns and different sizes. (Several tests
  had Length > Max_length before.)

12 files changed, 131 insertions, 28 deletions

diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result
index b02f85132aa..ea80695ad7b 100644
--- a/mysql-test/r/case.result
+++ b/mysql-test/r/case.result
@@ -103,8 +103,8 @@ t1	CREATE TABLE `t1` (
   `c2` varchar(1) character set latin1 collate latin1_danish_ci NOT NULL default '',
   `c3` varbinary(1) NOT NULL default '',
   `c4` varbinary(1) NOT NULL default '',
-  `c5` varbinary(4) NOT NULL default '',
-  `c6` varbinary(4) NOT NULL default '',
+  `c5` varbinary(3) NOT NULL default '',
+  `c6` varbinary(3) NOT NULL default '',
   `c7` decimal(2,1) NOT NULL default '0.0',
   `c8` decimal(2,1) NOT NULL default '0.0',
   `c9` decimal(2,1) default NULL,
@@ -152,11 +152,11 @@ SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `COALESCE(1)` int(1) NOT NULL default '0',
-  `COALESCE(1.0)` decimal(2,1) NOT NULL default '0.0',
+  `COALESCE(1.0)` decimal(2,1) unsigned NOT NULL default '0.0',
   `COALESCE('a')` varchar(1) NOT NULL default '',
   `COALESCE(1,1.0)` decimal(2,1) NOT NULL default '0.0',
   `COALESCE(1,'1')` varbinary(1) NOT NULL default '',
-  `COALESCE(1.1,'1')` varbinary(4) NOT NULL default '',
+  `COALESCE(1.1,'1')` varbinary(3) NOT NULL default '',
   `COALESCE('a' COLLATE latin1_bin,'b')` varchar(1) character set latin1 collate latin1_bin NOT NULL default ''
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1;
diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result
index 50b0b6ae294..0a170e16188 100644
--- a/mysql-test/r/metadata.result
+++ b/mysql-test/r/metadata.result
@@ -2,7 +2,7 @@ drop table if exists t1,t2;
 select 1, 1.0, -1, "hello", NULL;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def					1	8	1	1	N	32897	0	63
-def					1.0	246	4	3	N	129	1	63
+def					1.0	246	3	3	N	161	1	63
 def					-1	8	2	2	N	32897	0	63
 def					hello	253	5	5	N	1	31	8
 def					NULL	6	0	0	Y	32896	0	63
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index 16ead200933..c839c8a65b9 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -1775,7 +1775,7 @@ Table	Create Table
 t5	CREATE TABLE `t5` (
   `const01` bigint(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
-  `const02` decimal(2,1) NOT NULL default '0.0',
+  `const02` decimal(2,1) unsigned NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
   `const03` double NOT NULL default '0',
   `param03` double default NULL,
@@ -1805,7 +1805,7 @@ select * from t5 ;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def	test	t5	t5	const01	const01	8	1	1	N	32769	0	63
 def	test	t5	t5	param01	param01	8	20	1	Y	32768	0	63
-def	test	t5	t5	const02	const02	246	4	3	N	1	1	63
+def	test	t5	t5	const02	const02	246	3	3	N	33	1	63
 def	test	t5	t5	param02	param02	246	67	32	Y	0	30	63
 def	test	t5	t5	const03	const03	5	17	1	N	32769	31	63
 def	test	t5	t5	param03	param03	5	23	1	Y	32768	31	63
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index 9ab5a79f755..81d6180e41f 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -1758,7 +1758,7 @@ Table	Create Table
 t5	CREATE TABLE `t5` (
   `const01` bigint(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
-  `const02` decimal(2,1) NOT NULL default '0.0',
+  `const02` decimal(2,1) unsigned NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
   `const03` double NOT NULL default '0',
   `param03` double default NULL,
@@ -1788,7 +1788,7 @@ select * from t5 ;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def	test	t5	t5	const01	const01	8	1	1	N	32769	0	63
 def	test	t5	t5	param01	param01	8	20	1	Y	32768	0	63
-def	test	t5	t5	const02	const02	246	4	3	N	1	1	63
+def	test	t5	t5	const02	const02	246	3	3	N	33	1	63
 def	test	t5	t5	param02	param02	246	67	32	Y	0	30	63
 def	test	t5	t5	const03	const03	5	17	1	N	32769	31	63
 def	test	t5	t5	param03	param03	5	23	1	Y	32768	31	63
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index 8336a5bf99b..931e6b7c86c 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -1759,7 +1759,7 @@ Table	Create Table
 t5	CREATE TABLE `t5` (
   `const01` bigint(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
-  `const02` decimal(2,1) NOT NULL default '0.0',
+  `const02` decimal(2,1) unsigned NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
   `const03` double NOT NULL default '0',
   `param03` double default NULL,
@@ -1789,7 +1789,7 @@ select * from t5 ;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def	test	t5	t5	const01	const01	8	1	1	N	32769	0	63
 def	test	t5	t5	param01	param01	8	20	1	Y	32768	0	63
-def	test	t5	t5	const02	const02	246	4	3	N	1	1	63
+def	test	t5	t5	const02	const02	246	3	3	N	33	1	63
 def	test	t5	t5	param02	param02	246	67	32	Y	0	30	63
 def	test	t5	t5	const03	const03	5	17	1	N	32769	31	63
 def	test	t5	t5	param03	param03	5	23	1	Y	32768	31	63
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index f341247a417..3b9244c251f 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -1695,7 +1695,7 @@ Table	Create Table
 t5	CREATE TABLE `t5` (
   `const01` bigint(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
-  `const02` decimal(2,1) NOT NULL default '0.0',
+  `const02` decimal(2,1) unsigned NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
   `const03` double NOT NULL default '0',
   `param03` double default NULL,
@@ -1725,7 +1725,7 @@ select * from t5 ;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def	test	t5	t5	const01	const01	8	1	1	N	32769	0	63
 def	test	t5	t5	param01	param01	8	20	1	Y	32768	0	63
-def	test	t5	t5	const02	const02	246	4	3	N	1	1	63
+def	test	t5	t5	const02	const02	246	3	3	N	33	1	63
 def	test	t5	t5	param02	param02	246	67	32	Y	0	30	63
 def	test	t5	t5	const03	const03	5	17	1	N	32769	31	63
 def	test	t5	t5	param03	param03	5	23	1	Y	32768	31	63
@@ -4707,7 +4707,7 @@ Table	Create Table
 t5	CREATE TABLE `t5` (
   `const01` bigint(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
-  `const02` decimal(2,1) NOT NULL default '0.0',
+  `const02` decimal(2,1) unsigned NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
   `const03` double NOT NULL default '0',
   `param03` double default NULL,
@@ -4737,7 +4737,7 @@ select * from t5 ;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def	test	t5	t5	const01	const01	8	1	1	N	32769	0	63
 def	test	t5	t5	param01	param01	8	20	1	Y	32768	0	63
-def	test	t5	t5	const02	const02	246	4	3	N	1	1	63
+def	test	t5	t5	const02	const02	246	3	3	N	33	1	63
 def	test	t5	t5	param02	param02	246	67	32	Y	0	30	63
 def	test	t5	t5	const03	const03	5	17	1	N	32769	31	63
 def	test	t5	t5	param03	param03	5	23	1	Y	32768	31	63
diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result
index fe4536827e6..643e12f7e2d 100644
--- a/mysql-test/r/ps_6bdb.result
+++ b/mysql-test/r/ps_6bdb.result
@@ -1758,7 +1758,7 @@ Table	Create Table
 t5	CREATE TABLE `t5` (
   `const01` bigint(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
-  `const02` decimal(2,1) NOT NULL default '0.0',
+  `const02` decimal(2,1) unsigned NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
   `const03` double NOT NULL default '0',
   `param03` double default NULL,
@@ -1788,7 +1788,7 @@ select * from t5 ;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def	test	t5	t5	const01	const01	8	1	1	N	32769	0	63
 def	test	t5	t5	param01	param01	8	20	1	Y	32768	0	63
-def	test	t5	t5	const02	const02	246	4	3	N	1	1	63
+def	test	t5	t5	const02	const02	246	3	3	N	33	1	63
 def	test	t5	t5	param02	param02	246	67	32	Y	0	30	63
 def	test	t5	t5	const03	const03	5	17	1	N	32769	31	63
 def	test	t5	t5	param03	param03	5	23	1	Y	32768	31	63
diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result
index c4cb92bdc02..9fbe67f581b 100644
--- a/mysql-test/r/ps_7ndb.result
+++ b/mysql-test/r/ps_7ndb.result
@@ -1758,7 +1758,7 @@ Table	Create Table
 t5	CREATE TABLE `t5` (
   `const01` bigint(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
-  `const02` decimal(2,1) NOT NULL default '0.0',
+  `const02` decimal(2,1) unsigned NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
   `const03` double NOT NULL default '0',
   `param03` double default NULL,
@@ -1788,7 +1788,7 @@ select * from t5 ;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def	test	t5	t5	const01	const01	8	1	1	N	32769	0	63
 def	test	t5	t5	param01	param01	8	20	1	Y	32768	0	63
-def	test	t5	t5	const02	const02	246	4	3	N	1	1	63
+def	test	t5	t5	const02	const02	246	3	3	N	33	1	63
 def	test	t5	t5	param02	param02	246	67	32	Y	0	30	63
 def	test	t5	t5	const03	const03	5	17	1	N	32769	31	63
 def	test	t5	t5	param03	param03	5	23	1	Y	32768	31	63
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index f9e6eae7e57..1b8cde6d3db 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -3390,4 +3390,49 @@ s1
 set sql_mode=@sm|
 drop table t3|
 drop procedure bug6127|
+drop procedure if exists bug12589_1|
+drop procedure if exists bug12589_2|
+drop procedure if exists bug12589_3|
+create procedure bug12589_1()
+begin
+declare spv1 decimal(3,3);
+set spv1= 123.456;
+set spv1 = 'test';
+create temporary table tm1 as select spv1;
+show create table tm1;
+drop temporary table tm1;
+end|
+create procedure bug12589_2()
+begin
+declare spv1 decimal(6,3);
+set spv1= 123.456;
+create temporary table tm1 as select spv1;
+show create table tm1;
+drop temporary table tm1;
+end|
+create procedure bug12589_3()
+begin
+declare spv1 decimal(6,3);
+set spv1= -123.456;
+create temporary table tm1 as select spv1;
+show create table tm1;
+drop temporary table tm1;
+end|
+call bug12589_1()|
+Table	Create Table
+tm1	CREATE TEMPORARY TABLE `tm1` (
+  `spv1` decimal(1,0) unsigned default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'test'
+call bug12589_2()|
+Table	Create Table
+tm1	CREATE TEMPORARY TABLE `tm1` (
+  `spv1` decimal(6,3) unsigned default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+call bug12589_3()|
+Table	Create Table
+tm1	CREATE TEMPORARY TABLE `tm1` (
+  `spv1` decimal(6,3) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1,t2;
diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index d821339a229..17ebab1c6e7 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -68,10 +68,10 @@ NULL	1.1	NULL	NULL	NULL	1
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `nullif(1.1, 1.1)` decimal(2,1) default NULL,
-  `nullif(1.1, 1.2)` decimal(2,1) default NULL,
-  `nullif(1.1, 0.11e1)` decimal(2,1) default NULL,
-  `nullif(1.0, 1)` decimal(2,1) default NULL,
+  `nullif(1.1, 1.1)` decimal(2,1) unsigned default NULL,
+  `nullif(1.1, 1.2)` decimal(2,1) unsigned default NULL,
+  `nullif(1.1, 0.11e1)` decimal(2,1) unsigned default NULL,
+  `nullif(1.0, 1)` decimal(2,1) unsigned default NULL,
   `nullif(1, 1.0)` int(1) default NULL,
   `nullif(1, 1.1)` int(1) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
@@ -174,9 +174,9 @@ create table t1 select round(15.4,-1), truncate(-5678.123451,-3), abs(-1.1), -(-
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `round(15.4,-1)` decimal(3,0) NOT NULL default '0',
+  `round(15.4,-1)` decimal(3,0) unsigned NOT NULL default '0',
   `truncate(-5678.123451,-3)` decimal(4,0) NOT NULL default '0',
-  `abs(-1.1)` decimal(3,1) NOT NULL default '0.0',
+  `abs(-1.1)` decimal(2,1) NOT NULL default '0.0',
   `-(-1.1)` decimal(2,1) NOT NULL default '0.0'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
@@ -781,7 +781,7 @@ create table t1 as select 0.5;
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `0.5` decimal(2,1) NOT NULL default '0.0'
+  `0.5` decimal(2,1) unsigned NOT NULL default '0.0'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 select round(1.5),round(2.5);
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 65d4f89e2bb..e16e7456056 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -4265,6 +4265,57 @@ drop procedure bug6127|
 
 
 #
+# BUG#12589: Assert when creating temp. table from decimal stored procedure
+#            variable
+#
+--disable_warnings
+drop procedure if exists bug12589_1|
+drop procedure if exists bug12589_2|
+drop procedure if exists bug12589_3|
+--enable_warnings
+create procedure bug12589_1()
+begin
+  declare spv1 decimal(3,3);
+  set spv1= 123.456;
+
+  set spv1 = 'test';
+  create temporary table tm1 as select spv1;
+  show create table tm1;
+  drop temporary table tm1;
+end|
+
+create procedure bug12589_2()
+begin
+  declare spv1 decimal(6,3);
+  set spv1= 123.456;
+
+  create temporary table tm1 as select spv1;
+  show create table tm1;
+  drop temporary table tm1;
+end|
+
+create procedure bug12589_3()
+begin
+  declare spv1 decimal(6,3);
+  set spv1= -123.456;
+
+  create temporary table tm1 as select spv1;
+  show create table tm1;
+  drop temporary table tm1;
+end|
+
+# Note: The type of the field will match the value, not the declared
+#       type of the variable. (This is a type checking issue which
+#       might be changed later.)
+
+# Warning expected from "set spv1 = 'test'", the value is set to decimal "0".
+call bug12589_1()|
+# No warnings here
+call bug12589_2()|
+call bug12589_3()|
+
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings
diff --git a/sql/item.cc b/sql/item.cc
index 45c12d3840f..9b2406f16ff 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -904,6 +904,7 @@ bool Item_splocal::fix_fields(THD *, Item **)
   DBUG_ASSERT(it->fixed);
   max_length= it->max_length;
   decimals= it->decimals;
+  unsigned_flag= it->unsigned_flag;
   fixed= 1;
   return FALSE;
 }
@@ -1804,6 +1805,7 @@ Item_decimal::Item_decimal(const char *str_arg, uint length,
   name= (char*) str_arg;
   decimals= (uint8) decimal_value.frac;
   fixed= 1;
+  unsigned_flag= !decimal_value.sign();
   max_length= my_decimal_precision_to_length(decimal_value.intg + decimals,
                                              decimals, unsigned_flag);
 }
@@ -1813,6 +1815,7 @@ Item_decimal::Item_decimal(longlong val, bool unsig)
   int2my_decimal(E_DEC_FATAL_ERROR, val, unsig, &decimal_value);
   decimals= (uint8) decimal_value.frac;
   fixed= 1;
+  unsigned_flag= !decimal_value.sign();
   max_length= my_decimal_precision_to_length(decimal_value.intg + decimals,
                                              decimals, unsigned_flag);
 }
@@ -1823,6 +1826,7 @@ Item_decimal::Item_decimal(double val, int precision, int scale)
   double2my_decimal(E_DEC_FATAL_ERROR, val, &decimal_value);
   decimals= (uint8) decimal_value.frac;
   fixed= 1;
+  unsigned_flag= !decimal_value.sign();
   max_length= my_decimal_precision_to_length(decimal_value.intg + decimals,
                                              decimals, unsigned_flag);
 }
@@ -1835,6 +1839,7 @@ Item_decimal::Item_decimal(const char *str, const my_decimal *val_arg,
   name= (char*) str;
   decimals= (uint8) decimal_par;
   max_length= length;
+  unsigned_flag= !decimal_value.sign();
   fixed= 1;
 }
 
@@ -1844,8 +1849,9 @@ Item_decimal::Item_decimal(my_decimal *value_par)
   my_decimal2decimal(value_par, &decimal_value);
   decimals= (uint8) decimal_value.frac;
   fixed= 1;
+  unsigned_flag= !decimal_value.sign();
   max_length= my_decimal_precision_to_length(decimal_value.intg + decimals,
-                                             decimals, !decimal_value.sign());
+                                             decimals, unsigned_flag);
 }
 
 
@@ -1855,8 +1861,9 @@ Item_decimal::Item_decimal(const char *bin, int precision, int scale)
                     &decimal_value, precision, scale);
   decimals= (uint8) decimal_value.frac;
   fixed= 1;
+  unsigned_flag= !decimal_value.sign();
   max_length= my_decimal_precision_to_length(precision, decimals,
-                                             !decimal_value.sign());
+                                             unsigned_flag);
 }