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.


sql/sql_select.cc:
  Fix bug #11718 query with function, join and order by returns wrong type.
tests/mysql_client_test.c:
  Test case for bug #11718: query with function, join and order by returns wrong type

1 files changed, 35 insertions, 0 deletions

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 }
 };