From: Date: October 10 2008 12:13pm
Subject: bzr commit into mysql-5.0-bugteam branch (gshchepa:2702) Bug#39283
List-Archive: http://lists.mysql.com/commits/56018
X-Bug: 39283
Message-Id: <20081010112900.DC42440DB3F@localhost.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7BIT

#At file:///work/bzr/mysql-5.0-bugteam/

 2702 Gleb Shchepa	2008-10-10
      Bug #39283: Date returned as VARBINARY to client for queries
                  with COALESCE and JOIN
      
      The server returned to a client the VARBINARY column type
      instead of the DATE type for a result of the COALESCE,
      IFNULL, IF, CASE, GREATEST or LEAST functions if that result
      was filesorted in an anonymous temporary table during
      the query execution.
      
      For example:
        SELECT COALESCE(t1.date1, t2.date2) AS result
          FROM t1 JOIN t2 ON t1.id = t2.id ORDER BY result;
      
      
      To create a column of various date/time types in a
      temporary table the create_tmp_field_from_item() function
      uses the Item::tmp_table_field_from_field_type() method
      call. However, fields of the MYSQL_TYPE_NEWDATE type were
      missed there, and the VARBINARY columns were created
      by default.
      Necessary condition has been added.
modified:
  mysql-test/r/metadata.result
  mysql-test/t/metadata.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/metadata.result
    Added test case for bug #39283.
  mysql-test/t/metadata.test
    Added test case for bug #39283.
  sql/sql_select.cc
    Bug #39283: Date returned as VARBINARY to client for queries
                with COALESCE and JOIN
    
    To create a column of various date/time types in a
    temporary table the create_tmp_field_from_item() function
    uses the Item::tmp_table_field_from_field_type() method
    call. However, fields of the MYSQL_TYPE_NEWDATE type were
    missed there, and the VARBINARY columns were created
    by default.
    Necessary condition has been added.
=== modified file 'mysql-test/r/metadata.result'
--- a/mysql-test/r/metadata.result	2008-08-20 09:49:28 +0000
+++ b/mysql-test/r/metadata.result	2008-10-10 10:13:12 +0000
@@ -181,4 +181,21 @@ c1	c2
 3	3
 DROP VIEW v1,v2;
 DROP TABLE t1,t2;
+CREATE TABLE t1 (i INT, d DATE);
+INSERT INTO t1 VALUES (1, '2008-01-01'), (2, '2008-01-02'), (3, '2008-01-03');
+SELECT COALESCE(d, d), IFNULL(d, d), IF(i, d, d),
+CASE i WHEN i THEN d ELSE d END, GREATEST(d, d), LEAST(d, d)
+FROM t1 ORDER BY RAND();
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+2008-01-01	2008-01-01	2008-01-01	2008-01-01	2008-01-01	2008-01-01
+2008-01-02	2008-01-02	2008-01-02	2008-01-02	2008-01-02	2008-01-02
+2008-01-03	2008-01-03	2008-01-03	2008-01-03	2008-01-03	2008-01-03
+COALESCE(d, d)	IFNULL(d, d)	IF(i, d, d)	CASE i WHEN i THEN d ELSE d END	GREATEST(d, d)	LEAST(d, d)
+def				CASE i WHEN i THEN d ELSE d END	CASE i WHEN i THEN d ELSE d END	10	10	10	Y	128	0	63
+def				COALESCE(d, d)	COALESCE(d, d)	10	10	10	Y	128	0	63
+def				GREATEST(d, d)	GREATEST(d, d)	10	10	10	Y	128	0	63
+def				IF(i, d, d)	IF(i, d, d)	10	10	10	Y	128	0	63
+def				IFNULL(d, d)	IFNULL(d, d)	10	10	10	Y	128	0	63
+def				LEAST(d, d)	LEAST(d, d)	10	10	10	Y	128	0	63
+DROP TABLE t1;
 End of 5.0 tests

=== modified file 'mysql-test/t/metadata.test'
--- a/mysql-test/t/metadata.test	2007-06-20 09:21:48 +0000
+++ b/mysql-test/t/metadata.test	2008-10-10 10:13:12 +0000
@@ -112,4 +112,21 @@ SELECT v1.c1, v2.c2 FROM v1 JOIN v2 ON c
 DROP VIEW v1,v2;
 DROP TABLE t1,t2;
 
+#
+# Bug #39283: Date returned as VARBINARY to client for queries
+#             with COALESCE and JOIN
+#
+
+CREATE TABLE t1 (i INT, d DATE);
+INSERT INTO t1 VALUES (1, '2008-01-01'), (2, '2008-01-02'), (3, '2008-01-03');
+
+--enable_metadata
+--sorted_result
+SELECT COALESCE(d, d), IFNULL(d, d), IF(i, d, d),
+       CASE i WHEN i THEN d ELSE d END, GREATEST(d, d), LEAST(d, d)
+  FROM t1 ORDER BY RAND(); # force filesort
+--disable_metadata
+
+DROP TABLE t1;
+
 --echo End of 5.0 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-08-27 13:03:17 +0000
+++ b/sql/sql_select.cc	2008-10-10 10:13:12 +0000
@@ -8964,6 +8964,7 @@ static Field *create_tmp_field_from_item
     */
     if ((type= item->field_type()) == MYSQL_TYPE_DATETIME ||
         type == MYSQL_TYPE_TIME || type == MYSQL_TYPE_DATE ||
+        type == MYSQL_TYPE_NEWDATE ||
         type == MYSQL_TYPE_TIMESTAMP || type == MYSQL_TYPE_GEOMETRY)
       new_field= item->tmp_table_field_from_field_type(table);
     /*