Fix for bug #20204: "order by" changes the results returned

Item_substr's results are improperly stored in a temporary table due to       
wrongly calculated max_length value for multi-byte charsets if two            
arguments specified.                                                          


mysql-test/r/ctype_utf8.result:
  Fix for bug #20204: "order by" changes the results returned
    - test case.
mysql-test/t/ctype_utf8.test:
  Fix for bug #20204: "order by" changes the results returned
    - test result.
sql/item_strfunc.cc:
  Fix for bug #20204: "order by" changes the results returned
    - always take into account current mbmaxlen value calculating max_length.

3 files changed, 27 insertions, 1 deletions

diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 941b834a733..22b6de80a35 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -1352,3 +1352,16 @@ select database();
 database()
 имя_базы_в_кодировке_утф8_длиной_больше_чем_45
 drop database имя_базы_в_кодировке_утф8_длиной_больше_чем_45;
+use test;
+create table t1(a char(10)) default charset utf8;
+insert into t1 values ('123'), ('456');
+explain
+select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	Y	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	SIMPLE	Z	ALL	NULL	NULL	NULL	NULL	2	Using where
+select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;
+substr(Z.a,-1)	a
+3	123
+6	456
+drop table t1;
diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test
index 7272cb79089..90d7ec1b3a0 100644
--- a/mysql-test/t/ctype_utf8.test
+++ b/mysql-test/t/ctype_utf8.test
@@ -1087,5 +1087,17 @@ create database имя_базы_в_кодировке_утф8_длиной_бо�
 use имя_базы_в_кодировке_утф8_длиной_больше_чем_45;
 select database();
 drop database имя_базы_в_кодировке_утф8_длиной_больше_чем_45;
+use test;
+
+#
+# Bug #20204: "order by" changes the results returned
+#
+
+create table t1(a char(10)) default charset utf8;
+insert into t1 values ('123'), ('456');
+explain
+  select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;
+select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;
+drop table t1;
 
 # End of 4.1 tests
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 1ef11945bd5..98888226e58 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -1109,12 +1109,13 @@ void Item_func_substr::fix_length_and_dec()
   }
   if (arg_count == 3 && args[2]->const_item())
   {
-    int32 length= (int32) args[2]->val_int() * collation.collation->mbmaxlen;
+    int32 length= (int32) args[2]->val_int();
     if (length <= 0)
       max_length=0; /* purecov: inspected */
     else
       set_if_smaller(max_length,(uint) length);
   }
+  max_length*= collation.collation->mbmaxlen;
 }