bzr commit into mysql-5.0-bugteam branch (gshchepa:2691) Bug#39283

[Gleb Shchepa]

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

 2691 Gleb Shchepa	2008-10-03
      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:
  sql/sql_select.cc
  tests/mysql_client_test.c

per-file messages:
  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.
  tests/mysql_client_test.c
    Added test case for bug #39283.
=== 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-03 14:12:28 +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);
     /* 

=== modified file 'tests/mysql_client_test.c'
--- a/tests/mysql_client_test.c	2008-08-20 09:49:28 +0000
+++ b/tests/mysql_client_test.c	2008-10-03 14:12:28 +0000
@@ -16218,6 +16218,48 @@ static void test_bug38486(void)
   DBUG_VOID_RETURN;
 }
 
+/*
+  Bug#39283: Date returned as VARBINARY to client for queries
+             with COALESCE and JOIN
+*/
+
+static void test_bug39283()
+{
+  MYSQL_RES     *res;
+  MYSQL_FIELD   *f;
+  int rc;
+
+  myheader("test_bug39283");
+
+  rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
+  myquery(rc);
+  rc= mysql_query(mysql, "CREATE TABLE t1 (i INT, d DATE)");
+  myquery(rc);
+  rc= mysql_query(mysql, "INSERT INTO t1 VALUES (1, '2008-01-01'),"
+                         "  (2, '2008-01-02'), (3, '2008-01-03')");
+  myquery(rc);
+
+  rc= mysql_query(mysql, "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 */
+  myquery(rc);
+  res= mysql_store_result(mysql);
+  while ((f= mysql_fetch_field(res)))
+  {
+    if (!opt_silent)
+      printf("'%s' return type: %s(%d)\n", f->name,
+          (f->type == MYSQL_TYPE_DATE) ? "DATE" : "not DATE", f->type);
+    DIE_UNLESS(f->type == MYSQL_TYPE_DATE);
+  }
+  mysql_free_result(res);
+
+  rc= mysql_query(mysql, "DROP TABLE t1");
+  myquery(rc);
+
+}
+
 
 /*
   Read and parse arguments and MySQL options from my.cnf
@@ -16514,6 +16556,7 @@ static struct my_tests_st my_tests[]= {
   { "test_bug31669", test_bug31669 },
   { "test_bug32265", test_bug32265 },
   { "test_bug38486", test_bug38486 },
+  { "test_bug39283", test_bug39283 },
   { 0, 0 }
 };