Fix bug #11335 View redefines TinyInt(1) column definition

Item_type_holder doesn't store information about length and exact type of
original item which results in redefining length to max_length and geometry 
type to longtext.

Changed the way derived tables except unions are built. Now they are created
from original field list instead of list of Item_type_holder.


mysql-test/r/subselect.result:
  Fixed wrong test case result. bug#11335
mysql-test/r/view_grant.result:
   Fixed wrong test case result. bug#11335
mysql-test/r/view.result:
  Added test case for bug #11335. Fixed wrong test case result.
mysql-test/t/view.test:
  Test case for bug #11335 View  redefines TinyInt(1) column definition.
sql/sql_union.cc:
  Fix bug #11335 View redefines TinyInt(1) column definition.
  Changed the way derived tables except unions are built. Now they are created from original field list instead of list of Item_type_holders.
sql/sql_select.cc:
  Fix bug #11335 View redefines TinyInt(1) column definition.
  Added special handling of DATE/TIME fields to preserve field's type in tmp field creation.
  In create_tmp_field() for Item_field added special handling of case when item have to be able to store NULLs but underlaid field is NOT NULL.
sql/item_sum.cc:
  Fix bug #11335 View redefines TinyInt(1) column definition.
  Added special handling of DATE/TIME fields to preserve field's type while tmp
  field created in Item_sum_hybrid::create_tmp_field().

7 files changed, 77 insertions, 13 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 693146c869e..bbca9c905df 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(20) NOT NULL default '0',
-  `(SELECT 1)` bigint(20) NOT NULL default '0'
+  `a` bigint(1) NOT NULL default '0',
+  `(SELECT 1)` bigint(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(20) NOT NULL default '0',
-  `(SELECT a)` bigint(20) NOT NULL default '0'
+  `a` bigint(1) NOT NULL default '0',
+  `(SELECT a)` bigint(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(20) NOT NULL default '0',
-  `(SELECT a+0)` bigint(20) NOT NULL default '0'
+  `a` bigint(1) NOT NULL default '0',
+  `(SELECT a+0)` bigint(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/view.result b/mysql-test/r/view.result
index ef40a408932..d3270661b52 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -566,7 +566,7 @@ select * from v1;
 col1
 describe v1;
 Field	Type	Null	Key	Default	Extra
-col1	varchar(2)	YES		NULL	
+col1	char(2)	YES		NULL	
 drop view v1;
 drop table `t1a``b`;
 create table t1 (col1 char(5),col2 char(5));
@@ -1977,3 +1977,14 @@ A
 B
 DROP VIEW v1;
 DROP TABLE t1;
+create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime);
+create view v1 as select * from t1;
+desc v1;
+Field	Type	Null	Key	Default	Extra
+f1	tinyint(1)	YES		NULL	
+f2	char(1)	YES		NULL	
+f3	varchar(1)	YES		NULL	
+f4	geometry	YES		NULL	
+f5	datetime	YES		NULL	
+drop view v1;
+drop table t1;
diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result
index b77ee59b3ff..71f0f28e59f 100644
--- a/mysql-test/r/view_grant.result
+++ b/mysql-test/r/view_grant.result
@@ -72,12 +72,12 @@ select c from mysqltest.v4;
 c
 show columns from mysqltest.v1;
 Field	Type	Null	Key	Default	Extra
-c	bigint(20)	YES		NULL	
-d	bigint(20)	YES		NULL	
+c	bigint(12)	YES		NULL	
+d	bigint(12)	YES		NULL	
 show columns from mysqltest.v2;
 Field	Type	Null	Key	Default	Extra
-c	bigint(20)	YES		NULL	
-d	bigint(20)	YES		NULL	
+c	bigint(12)	YES		NULL	
+d	bigint(12)	YES		NULL	
 explain select c from mysqltest.v1;
 ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
 show create view mysqltest.v1;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index d296d5ebee5..50a19627246 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -1815,3 +1815,12 @@ SELECT * FROM t1;
 
 DROP VIEW v1;
 DROP TABLE t1;
+
+#
+# Bug #11335 View redefines column types
+#
+create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime);
+create view v1 as select * from t1;
+desc v1;
+drop view v1;
+drop table t1;
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index d2f1016891b..2dedc6b6bc5 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -321,6 +321,22 @@ Field *Item_sum_hybrid::create_tmp_field(bool group, TABLE *table,
       field->flags&= ~NOT_NULL_FLAG;
     return field;
   }
+  /*
+    DATE/TIME fields have STRING_RESULT result types.
+    In order to preserve field type, it's needed to handle DATE/TIME
+    fields creations separately.
+  */
+  switch (args[0]->field_type()) {
+  case MYSQL_TYPE_DATE:
+    return new Field_date(maybe_null, name, table, collation.collation);
+  case MYSQL_TYPE_TIME:
+    return new Field_time(maybe_null, name, table, collation.collation);
+  case MYSQL_TYPE_TIMESTAMP:
+  case MYSQL_TYPE_DATETIME:
+    return new Field_datetime(maybe_null, name, table, collation.collation);
+  default:
+    break;
+  }
   return Item_sum::create_tmp_field(group, table, convert_blob_length);
 }
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 66e783a2103..f7e487ec40f 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7875,7 +7875,15 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table,
 				   item->name, table, item->unsigned_flag);
     break;
   case STRING_RESULT:
-    if (item->max_length > 255 && convert_blob_length)
+    enum enum_field_types type;
+    /*
+      DATE/TIME fields have STRING_RESULT result type. To preserve
+      type they needed to be handled separately.
+    */
+    if ((type= item->field_type()) == MYSQL_TYPE_DATETIME ||
+        type == MYSQL_TYPE_TIME || type == MYSQL_TYPE_DATE)
+      new_field= item->tmp_table_field_from_field_type(table);
+    else if (item->max_length > 255 && convert_blob_length)
       new_field= new Field_varstring(convert_blob_length, maybe_null,
                                      item->name, table,
                                      item->collation.collation);
@@ -7981,6 +7989,20 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type,
     if (table_cant_handle_bit_fields && field->field->type() == FIELD_TYPE_BIT)
       return create_tmp_field_from_item(thd, item, table, copy_func,
                                         modify_item, convert_blob_length);
+    /*
+      If item have to be able to store NULLs but underlaid field can't do it,
+      create_tmp_field_from_field() can't be used for tmp field creation.
+    */
+    if (field->maybe_null && !field->field->maybe_null())
+    {
+      Field *res= create_tmp_field_from_item(thd, item, table, NULL,
+                                       modify_item, convert_blob_length);
+      *from_field= field->field;
+      if (res && modify_item)
+        ((Item_field*)item)->result_field= res;
+      return res;
+    }
+
     return create_tmp_field_from_field(thd, (*from_field= field->field),
                                        item->name, table,
                                        modify_item ? (Item_field*) item : NULL,
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index f2b637dc5f4..c414f5e9e72 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -235,7 +235,13 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
 
     if ((res= (res || thd_arg->is_fatal_error)))
       goto err;
-    if (sl == first_select)
+    /*
+      Use items list of underlaid select for derived tables to preserve
+      information about fields lengths and exact types
+    */
+    if (!is_union)
+      types= first_select_in_union()->item_list;
+    else if (sl == first_select)
     {
       /*
         We need to create an empty table object. It is used