From: Date: October 1 2008 11:09pm
Subject: bzr commit into mysql-5.0-bugteam branch (gshchepa:2691) Bug#39283
List-Archive: http://lists.mysql.com/commits/54991
X-Bug: 39283
Message-Id: <20081001211147.9566E40C8B6@localhost.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7BIT

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

 2691 Gleb Shchepa	2008-10-02
      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-01 21:09:40 +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-01 21:09:40 +0000
@@ -16218,6 +16218,56 @@ 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, t2");
+  myquery(rc);
+  rc= mysql_query(mysql, "CREATE TABLE t1 (f1 INT, f2 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, "CREATE TABLE t2 SELECT * FROM t1");
+  myquery(rc);
+# define CHECK_DATE_COLUMN(expr) do                                          \
+  {                                                                          \
+    rc= mysql_query(mysql, "SELECT " #expr " AS baddate FROM t1"             \
+                           "  JOIN t2 ON t2.f1 = t1.f1 ORDER BY baddate");   \
+    myquery(rc);                                                             \
+    res= mysql_store_result(mysql);                                          \
+    f= &res->fields[0];                                                      \
+    if (!opt_silent)                                                         \
+      printf("'%s AS %s' return type: %s(%d)\n", #expr, f->name,             \
+             (f->type == MYSQL_TYPE_DATE) ? "DATE" : "not DATE", f->type);   \
+    DIE_UNLESS(f->type == MYSQL_TYPE_DATE);                                  \
+    mysql_free_result(res);                                                  \
+  } while(0)
+
+  CHECK_DATE_COLUMN(COALESCE(t1.f2, t2.f2));
+  CHECK_DATE_COLUMN(IFNULL(t1.f2, t2.f2));
+  CHECK_DATE_COLUMN(IF(RAND(), t1.f2, t2.f2));
+  CHECK_DATE_COLUMN((CASE RAND() WHEN 1 THEN t1.f2 ELSE t2.f2 END));
+  CHECK_DATE_COLUMN(GREATEST(t1.f2, t2.f2));
+  CHECK_DATE_COLUMN(LEAST(t1.f2, t2.f2));
+
+# undef CHECK_DATE_COLUMN
+
+  rc= mysql_query(mysql, "DROP TABLE t1, t2");
+  myquery(rc);
+
+}
+
 
 /*
   Read and parse arguments and MySQL options from my.cnf
@@ -16514,6 +16564,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 }
 };