Fixed bug #19714.

DESCRIBE returned the type BIGINT for a column of a view if the column
was specified by an expression over values of the type INT.
    
E.g. for the view defined as follows:
  CREATE VIEW v1 SELECT COALESCE(f1,f2) FROM t1
DESCRIBE returned type BIGINT for the only column of the view if f1,f2 are
columns of the INT type.
At the same time DESCRIBE returned type INT for the only column of the table
defined by the statement:
  CREATE TABLE t2 SELECT COALESCE(f1,f2) FROM t1.
    
This inconsistency was removed by the patch.

Now the code chooses between INT/BIGINT depending on the
precision of the aggregated column type.
 
Thus both DESCRIBE commands above returns type INT for v1 and t2.
 


mysql-test/r/analyse.result:
  Adjusted the results after having fixed bug #19714.
mysql-test/r/bigint.result:
  Adjusted the results after having fixed bug #19714.
mysql-test/r/create.result:
  Adjusted the results after having fixed bug #19714.
mysql-test/r/olap.result:
  Adjusted the results after having fixed bug #19714.
mysql-test/r/ps_2myisam.result:
  Adjusted the results after having fixed bug #19714.
mysql-test/r/ps_3innodb.result:
  Adjusted the results after having fixed bug #19714.
mysql-test/r/ps_4heap.result:
  Adjusted the results after having fixed bug #19714.
mysql-test/r/ps_5merge.result:
  Adjusted the results after having fixed bug #19714.
mysql-test/r/ps_6bdb.result:
  Adjusted the results after having fixed bug #19714.
mysql-test/r/ps_7ndb.result:
  Adjusted the results after having fixed bug #19714.
mysql-test/r/sp.result:
  Adjusted the results after having fixed bug #19714.
mysql-test/r/subselect.result:
  Adjusted the results after having fixed bug #19714.
mysql-test/r/type_ranges.result:
  Adjusted the results after having fixed bug #19714.
mysql-test/r/view.result:
  Added a test case for bug #19714.
mysql-test/t/view.test:
  Added a test case for bug #19714.

16 files changed, 71 insertions, 42 deletions

diff --git a/mysql-test/r/analyse.result b/mysql-test/r/analyse.result
index f4e547dbc66..56f67cce4d6 100644
--- a/mysql-test/r/analyse.result
+++ b/mysql-test/r/analyse.result
@@ -39,10 +39,10 @@ t2	CREATE TABLE `t2` (
   `Field_name` varbinary(255) NOT NULL default '',
   `Min_value` varbinary(255) default NULL,
   `Max_value` varbinary(255) default NULL,
-  `Min_length` bigint(11) NOT NULL default '0',
-  `Max_length` bigint(11) NOT NULL default '0',
-  `Empties_or_zeros` bigint(11) NOT NULL default '0',
-  `Nulls` bigint(11) NOT NULL default '0',
+  `Min_length` int(11) NOT NULL default '0',
+  `Max_length` int(11) NOT NULL default '0',
+  `Empties_or_zeros` int(11) NOT NULL default '0',
+  `Nulls` int(11) NOT NULL default '0',
   `Avg_value_or_avg_length` varbinary(255) NOT NULL default '',
   `Std` varbinary(255) default NULL,
   `Optimal_fieldtype` varbinary(64) NOT NULL default ''
@@ -58,10 +58,10 @@ t2	CREATE TABLE `t2` (
   `Field_name` varbinary(255) NOT NULL default '',
   `Min_value` varbinary(255) default NULL,
   `Max_value` varbinary(255) default NULL,
-  `Min_length` bigint(11) NOT NULL default '0',
-  `Max_length` bigint(11) NOT NULL default '0',
-  `Empties_or_zeros` bigint(11) NOT NULL default '0',
-  `Nulls` bigint(11) NOT NULL default '0',
+  `Min_length` int(11) NOT NULL default '0',
+  `Max_length` int(11) NOT NULL default '0',
+  `Empties_or_zeros` int(11) NOT NULL default '0',
+  `Nulls` int(11) NOT NULL default '0',
   `Avg_value_or_avg_length` varbinary(255) NOT NULL default '',
   `Std` varbinary(255) default NULL,
   `Optimal_fieldtype` varbinary(64) NOT NULL default ''
@@ -81,10 +81,10 @@ t2	CREATE TABLE `t2` (
   `Field_name` varbinary(255) NOT NULL default '',
   `Min_value` varbinary(255) default NULL,
   `Max_value` varbinary(255) default NULL,
-  `Min_length` bigint(11) NOT NULL default '0',
-  `Max_length` bigint(11) NOT NULL default '0',
-  `Empties_or_zeros` bigint(11) NOT NULL default '0',
-  `Nulls` bigint(11) NOT NULL default '0',
+  `Min_length` int(11) NOT NULL default '0',
+  `Max_length` int(11) NOT NULL default '0',
+  `Empties_or_zeros` int(11) NOT NULL default '0',
+  `Nulls` int(11) NOT NULL default '0',
   `Avg_value_or_avg_length` varbinary(255) NOT NULL default '',
   `Std` varbinary(255) default NULL,
   `Optimal_fieldtype` varbinary(64) NOT NULL default ''
diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result
index 3cdf4b17027..edc18319603 100644
--- a/mysql-test/r/bigint.result
+++ b/mysql-test/r/bigint.result
@@ -174,7 +174,7 @@ create table t1 select 1 as 'a';
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` bigint(1) NOT NULL default '0'
+  `a` int(1) NOT NULL default '0'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 select 9223372036854775809 as 'a';
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index c5b77ea4925..aa8c6d3d277 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -668,7 +668,7 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `b` int(11) NOT NULL,
   `a` varchar(12) character set utf8 collate utf8_bin NOT NULL,
-  `c` bigint(1) NOT NULL default '0',
+  `c` int(1) NOT NULL default '0',
   PRIMARY KEY  (`a`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
@@ -681,7 +681,7 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `b` int(11) default NULL,
   `a` varchar(12) character set utf8 collate utf8_bin NOT NULL,
-  `c` bigint(1) NOT NULL default '0',
+  `c` int(1) NOT NULL default '0',
   PRIMARY KEY  (`a`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result
index 225e306b3cf..28c1dc59540 100644
--- a/mysql-test/r/olap.result
+++ b/mysql-test/r/olap.result
@@ -620,8 +620,8 @@ CREATE VIEW v1 AS
 SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
 DESC v1;
 Field	Type	Null	Key	Default	Extra
-a	bigint(11)	YES		NULL	
-LENGTH(a)	bigint(10)	YES		NULL	
+a	int(11)	YES		0	
+LENGTH(a)	int(10)	YES		NULL	
 COUNT(*)	bigint(21)	NO		0	
 SELECT * FROM v1;
 a	LENGTH(a)	COUNT(*)
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index 207d9ea7475..aa355067d1f 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -1775,7 +1775,7 @@ NULL                        as const12, @arg12 as param12,
 show create table t5 ;
 Table	Create Table
 t5	CREATE TABLE `t5` (
-  `const01` bigint(1) NOT NULL default '0',
+  `const01` int(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
   `const02` decimal(2,1) NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
@@ -1805,7 +1805,7 @@ t5	CREATE TABLE `t5` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 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	const01	const01	3	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	param02	param02	246	67	32	Y	0	30	63
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index 13aa549949c..ea7ed9371e8 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -1758,7 +1758,7 @@ NULL                        as const12, @arg12 as param12,
 show create table t5 ;
 Table	Create Table
 t5	CREATE TABLE `t5` (
-  `const01` bigint(1) NOT NULL default '0',
+  `const01` int(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
   `const02` decimal(2,1) NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
@@ -1788,7 +1788,7 @@ t5	CREATE TABLE `t5` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 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	const01	const01	3	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	param02	param02	246	67	32	Y	0	30	63
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index a08dae945bd..fe4de89d6c7 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -1759,7 +1759,7 @@ NULL                        as const12, @arg12 as param12,
 show create table t5 ;
 Table	Create Table
 t5	CREATE TABLE `t5` (
-  `const01` bigint(1) NOT NULL default '0',
+  `const01` int(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
   `const02` decimal(2,1) NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
@@ -1789,7 +1789,7 @@ t5	CREATE TABLE `t5` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 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	const01	const01	3	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	param02	param02	246	67	32	Y	0	30	63
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index 6682b085097..bee7b2400b8 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -1695,7 +1695,7 @@ NULL                        as const12, @arg12 as param12,
 show create table t5 ;
 Table	Create Table
 t5	CREATE TABLE `t5` (
-  `const01` bigint(1) NOT NULL default '0',
+  `const01` int(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
   `const02` decimal(2,1) NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
@@ -1725,7 +1725,7 @@ t5	CREATE TABLE `t5` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 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	const01	const01	3	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	param02	param02	246	67	32	Y	0	30	63
@@ -4709,7 +4709,7 @@ NULL                        as const12, @arg12 as param12,
 show create table t5 ;
 Table	Create Table
 t5	CREATE TABLE `t5` (
-  `const01` bigint(1) NOT NULL default '0',
+  `const01` int(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
   `const02` decimal(2,1) NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
@@ -4739,7 +4739,7 @@ t5	CREATE TABLE `t5` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 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	const01	const01	3	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	param02	param02	246	67	32	Y	0	30	63
diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result
index dc3b984949d..d352ec9f9e2 100644
--- a/mysql-test/r/ps_6bdb.result
+++ b/mysql-test/r/ps_6bdb.result
@@ -1758,7 +1758,7 @@ NULL                        as const12, @arg12 as param12,
 show create table t5 ;
 Table	Create Table
 t5	CREATE TABLE `t5` (
-  `const01` bigint(1) NOT NULL default '0',
+  `const01` int(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
   `const02` decimal(2,1) NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
@@ -1788,7 +1788,7 @@ t5	CREATE TABLE `t5` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 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	const01	const01	3	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	param02	param02	246	67	32	Y	0	30	63
diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result
index 000a20da655..a72e6fb476a 100644
--- a/mysql-test/r/ps_7ndb.result
+++ b/mysql-test/r/ps_7ndb.result
@@ -1758,7 +1758,7 @@ NULL                        as const12, @arg12 as param12,
 show create table t5 ;
 Table	Create Table
 t5	CREATE TABLE `t5` (
-  `const01` bigint(1) NOT NULL default '0',
+  `const01` int(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
   `const02` decimal(2,1) NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
@@ -1789,7 +1789,7 @@ t5	CREATE TABLE `t5` (
 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	param01	param01	3	20	1	Y	32768	0	63
 def	test	t5	t5	const02	const02	246	4	3	N	1	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
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 96bf2f01f86..50913fb1b90 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -4921,7 +4921,7 @@ create table t3 as select * from v1|
 show create table t3|
 Table	Create Table
 t3	CREATE TABLE `t3` (
-  `j` bigint(11) default NULL
+  `j` int(11) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select * from t3|
 j
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 981072139e0..11dc68fe99b 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1087,24 +1087,24 @@ CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` bigint(1) NOT NULL default '0',
-  `(SELECT 1)` bigint(1) NOT NULL default '0'
+  `a` int(1) NOT NULL default '0',
+  `(SELECT 1)` int(1) NOT NULL default '0'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` bigint(1) NOT NULL default '0',
-  `(SELECT a)` bigint(1) NOT NULL default '0'
+  `a` int(1) NOT NULL default '0',
+  `(SELECT a)` int(1) NOT NULL default '0'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` bigint(1) NOT NULL default '0',
-  `(SELECT a+0)` bigint(3) NOT NULL default '0'
+  `a` int(1) NOT NULL default '0',
+  `(SELECT a+0)` int(3) NOT NULL default '0'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
index bd89c09e94d..1310a5b71dd 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -273,7 +273,7 @@ create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, 'a' as t2, r
 show full columns from t2;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 auto	bigint(12) unsigned	NULL	NO	PRI	0		#	
-t1	bigint(1)	NULL	NO		0		#	
+t1	int(1)	NULL	NO		0		#	
 t2	varchar(1)	latin1_swedish_ci	NO				#	
 t3	varchar(256)	latin1_swedish_ci	NO				#	
 t4	varbinary(256)	NULL	NO				#	
@@ -301,7 +301,7 @@ show full columns from t3;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 c1	int(11)	NULL	YES		NULL		#	
 c2	int(11)	NULL	YES		NULL		#	
-const	bigint(1)	NULL	NO		0		#	
+const	int(1)	NULL	NO		0		#	
 drop table t1,t2,t3;
 create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield));
 drop table t1;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 295f9442f13..7d2ab63ca77 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2763,3 +2763,14 @@ Milhouse vanMilhouse van
 MontgomeryMontgomery
 DROP VIEW v1;
 DROP TABLE t1;
+CREATE TABLE t1 (i int, j int);
+CREATE VIEW v1 AS SELECT COALESCE(i,j) FROM t1;
+DESCRIBE v1;
+Field	Type	Null	Key	Default	Extra
+COALESCE(i,j)	int(11)	YES		NULL	
+CREATE TABLE t2 SELECT COALESCE(i,j) FROM t1;
+DESCRIBE t2;
+Field	Type	Null	Key	Default	Extra
+COALESCE(i,j)	int(11)	YES		NULL	
+DROP VIEW v1;
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 623195dd527..88a4d489039 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2630,3 +2630,16 @@ SELECT CONCAT(LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")),
 
 DROP VIEW v1;
 DROP TABLE t1;
+
+#
+# Bug #19714: wrong type of a view column specified by an expressions over ints
+#
+
+CREATE TABLE t1 (i int, j int);
+CREATE VIEW v1 AS SELECT COALESCE(i,j) FROM t1;
+DESCRIBE v1;
+CREATE TABLE t2 SELECT COALESCE(i,j) FROM t1;  
+DESCRIBE t2;
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 35a4f63aad1..2db62595a21 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8074,8 +8074,13 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table,
 			       item->name, table, item->decimals);
     break;
   case INT_RESULT:
-    new_field=new Field_longlong(item->max_length, maybe_null,
-				   item->name, table, item->unsigned_flag);
+    /* Select an integer type with the minimal fit precision */
+    if (item->max_length > 11)
+      new_field=new Field_longlong(item->max_length, maybe_null,
+                                   item->name, table, item->unsigned_flag);
+    else
+      new_field=new Field_long(item->max_length, maybe_null,
+                               item->name, table, item->unsigned_flag);
     break;
   case STRING_RESULT:
     DBUG_ASSERT(item->collation.collation);