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.

1 files changed, 3 insertions, 1 deletions

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