Fix bug #11718 query with function, join and order by returns wrong type.

create_tmp_field_from_item() was creating tmp field without regard to
original field type of Item. This results in wrong type being reported to
client.

To create_tmp_field_from_item() added special handling for Items with 
DATE/TIME field types to preserve their type.

2 files changed, 44 insertions, 1 deletions

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 9984cb4138f..73cfe153b9b 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4899,7 +4899,15 @@ static Field* create_tmp_field_from_item(THD *thd, Item *item, TABLE *table,
 				   item->name, table, item->unsigned_flag);
     break;
   case STRING_RESULT:
-    if (item->max_length > 255)
+    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)
     {
       if (convert_blob_length)
         new_field= new Field_varstring(convert_blob_length, maybe_null,
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index 37d6d951f96..64c5e7edaf9 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -11797,6 +11797,40 @@ static void test_bug12001()
 }
 
 /*
+  Bug#11718: query with function, join and order by returns wrong type
+*/
+
+static void test_bug11718()
+{
+  MYSQL_RES	*res;
+  int rc;
+  const char *query= "select str_to_date(concat(f3),'%Y%m%d') from t1,t2 "
+                     "where f1=f2 order by f1";
+
+  myheader("test_bug11718");
+
+  rc= mysql_query(mysql, "drop table if exists t1, t2");
+  myquery(rc);
+  rc= mysql_query(mysql, "create table t1 (f1 int)");
+  myquery(rc);
+  rc= mysql_query(mysql, "create table t2 (f2 int, f3 numeric(8))");
+  myquery(rc);
+  rc= mysql_query(mysql, "insert into t1 values (1), (2)");
+  myquery(rc);
+  rc= mysql_query(mysql, "insert into t2 values (1,20050101), (2,20050202)");
+  myquery(rc);
+  rc= mysql_query(mysql, query);
+  myquery(rc);
+  res = mysql_store_result(mysql);
+
+  if (!opt_silent)
+    printf("return type: %s", (res->fields[0].type == MYSQL_TYPE_DATE)?"DATE":
+           "not DATE");
+  DIE_UNLESS(res->fields[0].type == MYSQL_TYPE_DATE);
+  rc= mysql_query(mysql, "drop table t1, t2");
+  myquery(rc);
+}
+/*
   Read and parse arguments and MySQL options from my.cnf
 */
 
@@ -12013,6 +12047,7 @@ static struct my_tests_st my_tests[]= {
   { "test_bug9735", test_bug9735 },
   { "test_bug11183", test_bug11183 },
   { "test_bug12001", test_bug12001 },
+  { "test_bug11718", test_bug11718 },
   { 0, 0 }
 };