Fixed bug#19862: Sort with filesort by function evaluates function twice

When there is no index defined filesort is used to sort the result of a
query. If there is a function in the select list and the result set should be
ordered by it's value then this function will be evaluated twice. First time to
get the value of the sort key and second time to send its value to a user.
This happens because filesort when sorts a table remembers only values of its
fields but not values of functions.
All functions are affected. But taking into account that SP and UDF functions
can be both expensive and non-deterministic a temporary table should be used 
to store their results and then sort it to avoid twice SP evaluation and to 
get a correct result.

If an expression referenced in an ORDER clause contains a SP or UDF 
function, force the use of a temporary table.

A new Item_processor function called func_type_checker_processor is added
to check whether the expression contains a function of a particular type.


mysql-test/t/udf.test:
  Added test case for bug#19862: Sort with filesort by function evaluates function twice
mysql-test/t/sp.test:
  Added test case for bug#19862: Sort with filesort by function evaluates function twice
mysql-test/r/sp.result:
  Added test case for bug#19862: Sort with filesort by function evaluates function twice
mysql-test/r/udf.result:
  Added test case for bug#19862: Sort with filesort by function evaluates function twice
sql/sql_select.cc:
  Fixed bug#19862: Sort with filesort by function evaluates function twice
  If an expression referenced in an ORDER clause contains a SP or UDF
  function, force the use of a temporary table.
sql/item_func.h:
  Fixed bug#19862: Sort with filesort by function evaluates function twice
  A new Item_processor function called func_type_checker_processor is added
  to check whether the expression contains a function of a particular type.
sql/item.h:
  Fixed bug#19862: Sort with filesort by function evaluates function twice
  A new Item_processor function called func_type_checker_processor is added
  to check whether the expression contains a function of a particular type.
sql/item_func.cc:
  Fixed bug#19862: Sort with filesort by function evaluates function twice
  A new Item_processor function called func_type_checker_processor is added
  to check whether the expression contains a function of a particular type.

8 files changed, 81 insertions, 1 deletions

diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 50913fb1b90..f04b5b2c635 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -5057,4 +5057,23 @@ concat('data was: /', var1, '/')
 data was: /1/
 drop table t3|
 drop procedure bug15217|
+drop procedure if exists bug19862|
+CREATE TABLE t11 (a INT)|
+CREATE TABLE t12 (a INT)|
+CREATE FUNCTION bug19862(x INT) RETURNS INT
+BEGIN
+INSERT INTO t11 VALUES (x);
+RETURN x+1;
+END|
+INSERT INTO t12 VALUES (1), (2)|
+SELECT bug19862(a) FROM t12 ORDER BY 1|
+bug19862(a)
+2
+3
+SELECT * FROM t11|
+a
+1
+2
+DROP TABLE t11, t12|
+DROP FUNCTION bug19862|
 drop table t1,t2;
diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result
index 484c42c41bf..b44dce14230 100644
--- a/mysql-test/r/udf.result
+++ b/mysql-test/r/udf.result
@@ -93,6 +93,12 @@ NULL
 0R
 FR
 DROP TABLE bug19904;
+create table t1(f1 int);
+insert into t1 values(1),(2);
+explain select myfunc_int(f1) from t1 order by 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+drop table t1;
 End of 5.0 tests.
 DROP FUNCTION metaphon;
 DROP FUNCTION myfunc_double;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 25c96042e6f..cb9972fb800 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -5963,6 +5963,24 @@ drop table t3|
 drop procedure bug15217|
 
 #
+# BUG#19862: Sort with filesort by function evaluates function twice
+#
+--disable_warnings
+drop procedure if exists bug19862|
+--enable_warnings
+CREATE TABLE t11 (a INT)|
+CREATE TABLE t12 (a INT)|
+CREATE FUNCTION bug19862(x INT) RETURNS INT
+  BEGIN
+    INSERT INTO t11 VALUES (x);
+    RETURN x+1;
+  END|
+INSERT INTO t12 VALUES (1), (2)|
+SELECT bug19862(a) FROM t12 ORDER BY 1|
+SELECT * FROM t11|
+DROP TABLE t11, t12|
+DROP FUNCTION bug19862|
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings
diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test
index f3be08c8537..560ec88eb10 100644
--- a/mysql-test/t/udf.test
+++ b/mysql-test/t/udf.test
@@ -109,6 +109,13 @@ SELECT myfunc_double(n) AS f FROM bug19904;
 SELECT metaphon(v) AS f FROM bug19904;
 DROP TABLE bug19904;
 
+#
+# Bug#19862: Sort with filesort by function evaluates function twice
+#
+create table t1(f1 int);
+insert into t1 values(1),(2);
+explain select myfunc_int(f1) from t1 order by 1;
+drop table t1;
 --echo End of 5.0 tests.
 
 #
diff --git a/sql/item.h b/sql/item.h
index 0f49145082f..c59d84aaeaa 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -752,6 +752,7 @@ public:
   virtual bool find_item_in_field_list_processor(byte *arg) { return 0; }
   virtual bool change_context_processor(byte *context) { return 0; }
   virtual bool reset_query_id_processor(byte *query_id) { return 0; }
+  virtual bool func_type_checker_processor(byte *arg) { return 0; }
 
   virtual Item *equal_fields_propagator(byte * arg) { return this; }
   virtual Item *set_no_const_sub(byte *arg) { return this; }
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 1d906b300b6..c31ac2bf047 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -398,6 +398,13 @@ Field *Item_func::tmp_table_field(TABLE *t_arg)
   return res;
 }
 
+
+bool Item_func::func_type_checker_processor(byte *arg)
+{
+  return *((Functype*)arg) == functype();
+}
+
+
 my_decimal *Item_func::val_decimal(my_decimal *decimal_value)
 {
   DBUG_ASSERT(fixed);
diff --git a/sql/item_func.h b/sql/item_func.h
index 2ca4be9f3f2..88a52d7f46e 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -55,7 +55,7 @@ public:
                   NOT_FUNC, NOT_ALL_FUNC,
                   NOW_FUNC, TRIG_COND_FUNC,
                   GUSERVAR_FUNC, COLLATE_FUNC,
-                  EXTRACT_FUNC, CHAR_TYPECAST_FUNC, FUNC_SP };
+                  EXTRACT_FUNC, CHAR_TYPECAST_FUNC, FUNC_SP, UDF_FUNC };
   enum optimize_type { OPTIMIZE_NONE,OPTIMIZE_KEY,OPTIMIZE_OP, OPTIMIZE_NULL,
                        OPTIMIZE_EQUAL };
   enum Type type() const { return FUNC_ITEM; }
@@ -186,6 +186,7 @@ public:
   Item *transform(Item_transformer transformer, byte *arg);
   void traverse_cond(Cond_traverser traverser,
                      void * arg, traverse_order order);
+  bool func_type_checker_processor(byte *arg);
 };
 
 
@@ -930,6 +931,7 @@ public:
   Item_udf_func(udf_func *udf_arg, List<Item> &list)
     :Item_func(list), udf(udf_arg) {}
   const char *func_name() const { return udf.name(); }
+  enum Functype functype() const   { return UDF_FUNC; }
   bool fix_fields(THD *thd, Item **ref)
   {
     DBUG_ASSERT(fixed == 0);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index a7158960ed0..14389a57465 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1064,6 +1064,26 @@ JOIN::optimize()
     {
       need_tmp=1; simple_order=simple_group=0;	// Force tmp table without sort
     }
+    if (order)
+    {
+      /*
+        Force using of tmp table if sorting by a SP or UDF function due to
+        their expensive and probably non-deterministic nature.
+      */
+      for (ORDER *tmp_order= order; tmp_order ; tmp_order=tmp_order->next)
+      {
+        Item *item= *tmp_order->item;
+        Item_func::Functype type=Item_func::FUNC_SP;
+        Item_func::Functype type1=Item_func::UDF_FUNC;
+        if (item->walk(&Item::func_type_checker_processor,(byte*)&type) ||
+            item->walk(&Item::func_type_checker_processor,(byte*)&type1))
+        {
+          /* Force tmp table without sort */
+          need_tmp=1; simple_order=simple_group=0;
+          break;
+        }
+      }
+    }
   }
 
   tmp_having= having;