bk commit into 4.1 tree (gkodinov:1.2539) BUG#11032

[kgeorge]

Below is the list of changes that have just been committed into a local
4.1 repository of kgeorge. When kgeorge 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@stripped, 2006-11-07 18:16:17+02:00, gkodinov@stripped +4 -0
  Bug #11032: getObject() returns a String for a sub-query of type datetime
   - When returning metadata for scalar subqueries the actual type of the
     column was calculated based on the value type, which limits the actual
     type of a scalar subselect to the set of (currently) 3 basic types : 
     integer, double precision or string. This is the reason that columns
     of types other then the basic ones (e.g. date/time) are reported as
     being of the corresponding basic type.
     Fixed by storing/returning information for the column type in addition
     to the result type.

  mysql-test/r/subselect.result@stripped, 2006-11-07 18:16:10+02:00, gkodinov@stripped +17
-0
    Bug #11032: getObject() returns a String for a sub-query of type datetime
     - test case

  mysql-test/t/subselect.test@stripped, 2006-11-07 18:16:11+02:00, gkodinov@stripped +16
-0
    Bug #11032: getObject() returns a String for a sub-query of type datetime
     - test case

  sql/item_subselect.cc@stripped, 2006-11-07 18:16:11+02:00, gkodinov@stripped +24 -10
    Bug #11032: getObject() returns a String for a sub-query of type datetime
     - store and return the field type as well in addition to result type for 
       single row subqueries 

  sql/item_subselect.h@stripped, 2006-11-07 18:16:12+02:00, gkodinov@stripped +7 -0
    Bug #11032: getObject() returns a String for a sub-query of type datetime
     - store and return the field type as well in addition to result type for 
       single row subqueries

# 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:	gkodinov
# Host:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B11302-4.1-opt

--- 1.184/mysql-test/r/subselect.result	2006-10-20 09:05:51 +03:00
+++ 1.185/mysql-test/r/subselect.result	2006-11-07 18:16:10 +02:00
@@ -2997,3 +2997,20 @@
 2	NULL
 3	1
 DROP TABLE t1,t2;
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
+CREATE TABLE t2 AS SELECT 
+(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a 
+FROM t1 WHERE a > '2000-01-01';
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `sub_a` datetime default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1
WHERE a > '2000-01-01');
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `a` datetime default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2,t3;

--- 1.161/mysql-test/t/subselect.test	2006-10-20 09:05:51 +03:00
+++ 1.162/mysql-test/t/subselect.test	2006-11-07 18:16:11 +02:00
@@ -1965,4 +1965,20 @@
 
 DROP TABLE t1,t2;
 
+#
+# Bug #11302: getObject() returns a String for a sub-query of type datetime
+#
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
+
+CREATE TABLE t2 AS SELECT 
+  (SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a 
+   FROM t1 WHERE a > '2000-01-01';
+SHOW CREATE TABLE t2;
+
+CREATE TABLE t3 AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1
WHERE a > '2000-01-01'); 
+SHOW CREATE TABLE t3;
+
+DROP TABLE t1,t2,t3;
+
 # End of 4.1 tests

--- 1.145/sql/item_subselect.cc	2006-10-20 09:05:51 +03:00
+++ 1.146/sql/item_subselect.cc	2006-11-07 18:16:11 +02:00
@@ -391,6 +391,15 @@
   return engine->type();
 }
 
+/* 
+ Don't rely on the result type to calculate field type. 
+ Ask the engine instead.
+*/
+enum_field_types Item_singlerow_subselect::field_type() const
+{
+  return engine->field_type();
+}
+
 void Item_singlerow_subselect::fix_length_and_dec()
 {
   if ((max_columns= engine->cols()) == 1)
@@ -1357,31 +1366,35 @@
   return 1;
 }
 
-static Item_result set_row(List<Item> &item_list, Item *item,
-			   Item_cache **row, bool *maybe_null)
+/* 
+ makes storage for the output values for the subquery and calcuates 
+ their data and column types and their nullability.
+*/ 
+void subselect_engine::set_row(List<Item> &item_list, Item_cache **row)
 {
-  Item_result res_type= STRING_RESULT;
   Item *sel_item;
   List_iterator_fast<Item> li(item_list);
+  res_type= STRING_RESULT;
+  res_field_type= FIELD_TYPE_VAR_STRING;
   for (uint i= 0; (sel_item= li++); i++)
   {
     item->max_length= sel_item->max_length;
     res_type= sel_item->result_type();
+    res_field_type= sel_item->field_type();
     item->decimals= sel_item->decimals;
-    *maybe_null= sel_item->maybe_null;
+    maybe_null= sel_item->maybe_null;
     if (!(row[i]= Item_cache::get_cache(res_type)))
-      return STRING_RESULT; // we should return something
+      return;
     row[i]->setup(sel_item);
   }
   if (item_list.elements > 1)
     res_type= ROW_RESULT;
-  return res_type;
 }
 
 void subselect_single_select_engine::fix_length_and_dec(Item_cache **row)
 {
   DBUG_ASSERT(row || select_lex->item_list.elements==1);
-  res_type= set_row(select_lex->item_list, item, row, &maybe_null);
+  set_row(select_lex->item_list, row);
   item->collation.set(row[0]->collation);
   if (cols() != 1)
     maybe_null= 0;
@@ -1393,13 +1406,14 @@
 
   if (unit->first_select()->item_list.elements == 1)
   {
-    res_type= set_row(unit->types, item, row, &maybe_null);
+    set_row(unit->types, row);
     item->collation.set(row[0]->collation);
   }
   else
   {
-    bool fake= 0;
-    res_type= set_row(unit->types, item, row, &fake);
+    bool maybe_null_saved= maybe_null;
+    set_row(unit->types, row);
+    maybe_null= maybe_null_saved;
   }
 }
 

--- 1.63/sql/item_subselect.h	2006-07-21 02:04:02 +03:00
+++ 1.64/sql/item_subselect.h	2006-11-07 18:16:12 +02:00
@@ -142,6 +142,7 @@
   longlong val_int ();
   String *val_str (String *);
   enum Item_result result_type() const;
+  enum_field_types field_type() const;
   void fix_length_and_dec();
 
   uint cols();
@@ -273,6 +274,7 @@
   THD *thd; /* pointer to current THD */
   Item_subselect *item; /* item, that use this engine */
   enum Item_result res_type; /* type of results */
+  enum_field_types res_field_type; /* column type of the results */
   bool maybe_null; /* may be null (first item in select) */
 public:
 
@@ -282,6 +284,7 @@
     result= res;
     item= si;
     res_type= STRING_RESULT;
+    res_field_type= FIELD_TYPE_VAR_STRING;
     maybe_null= 0;
   }
   virtual ~subselect_engine() {}; // to satisfy compiler
@@ -296,6 +299,7 @@
   virtual uint cols()= 0; /* return number of columnss in select */
   virtual uint8 uncacheable()= 0; /* query is uncacheable */
   enum Item_result type() { return res_type; }
+  enum_field_types field_type() { return res_field_type; }
   virtual void exclude()= 0;
   bool may_be_null() { return maybe_null; };
   virtual table_map upper_select_const_tables()= 0;
@@ -303,6 +307,9 @@
   virtual void print(String *str)= 0;
   virtual int change_item(Item_subselect *si, select_subselect *result)= 0;
   virtual bool no_tables()= 0;
+
+protected:
+  void set_row(List<Item> &item_list, Item_cache **row);
 };