bk commit into 5.0 tree (evgen:1.1925) BUG#11335

[eugene]

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1925 05/07/30 05:53:35 evgen@stripped +7 -0
  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.

  sql/item_sum.cc
    1.153 05/07/30 05:52:11 evgen@stripped +16 -0
    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().

  sql/sql_select.cc
    1.332 05/07/30 05:49:53 evgen@stripped +23 -1
    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/sql_union.cc
    1.123 05/07/30 05:34:52 evgen@stripped +7 -1
    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.

  mysql-test/t/view.test
    1.86 05/07/30 05:27:44 evgen@stripped +9 -0
    Test case for bug #11335 View  redefines TinyInt(1) column definition.

  mysql-test/r/view.result
    1.91 05/07/30 05:26:49 evgen@stripped +12 -1
    Added test case for bug #11335. Fixed wrong test case result.

  mysql-test/r/view_grant.result
    1.3 05/07/30 05:25:54 evgen@stripped +4 -4
     Fixed wrong test case result. bug#11335

  mysql-test/r/subselect.result
    1.119 05/07/30 05:25:07 evgen@stripped +6 -6
    Fixed wrong test case result. bug#11335

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	evgen
# Host:	moonbone.local
# Root:	/work/mysql-5.0-bug-11335

--- 1.152/sql/item_sum.cc	2005-07-01 08:05:35 +04:00
+++ 1.153/sql/item_sum.cc	2005-07-30 05:52:11 +04:00
@@ -321,6 +321,22 @@
       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);
 }
 

--- 1.331/sql/sql_select.cc	2005-07-04 04:44:31 +04:00
+++ 1.332/sql/sql_select.cc	2005-07-30 05:49:53 +04:00
@@ -7875,7 +7875,15 @@
 				   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 @@
     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,

--- 1.122/sql/sql_union.cc	2005-07-07 03:47:54 +04:00
+++ 1.123/sql/sql_union.cc	2005-07-30 05:34:52 +04:00
@@ -235,7 +235,13 @@
 
     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

--- 1.118/mysql-test/r/subselect.result	2005-07-12 21:11:21 +04:00
+++ 1.119/mysql-test/r/subselect.result	2005-07-30 05:25:07 +04:00
@@ -1087,24 +1087,24 @@
 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;

--- 1.90/mysql-test/r/view.result	2005-07-14 00:20:37 +04:00
+++ 1.91/mysql-test/r/view.result	2005-07-30 05:26:49 +04:00
@@ -566,7 +566,7 @@
 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 @@
 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;

--- 1.85/mysql-test/t/view.test	2005-07-14 00:20:37 +04:00
+++ 1.86/mysql-test/t/view.test	2005-07-30 05:27:44 +04:00
@@ -1815,3 +1815,12 @@
 
 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;

--- 1.2/mysql-test/r/view_grant.result	2005-07-05 14:36:31 +04:00
+++ 1.3/mysql-test/r/view_grant.result	2005-07-30 05:25:54 +04:00
@@ -72,12 +72,12 @@
 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;