MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:eugene Date:July 24 2006 12:27pm
Subject:bk commit into 5.0 tree (evgen:1.2208) BUG#19862
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-07-24 16:27:16+04:00, evgen@stripped +6 -0
  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 slow nature of SP
  it worth to force creating a tmp table to store their results and then sort
  it to avoid twice SP evaluation.
  
  Now using of a tmp table is forced if an expression referenced by order
  list contains a SP function.
  
  A new Item_processor function called func_type_checker_processor is added
  to check presence of an SP function in an expression. 

  mysql-test/r/sp.result@stripped, 2006-07-24 15:15:52+04:00, evgen@stripped +19 -0
    Added test case for bug#19862: Sort with filesort by function evaluates function twice

  mysql-test/t/sp.test@stripped, 2006-07-24 15:15:38+04:00, evgen@stripped +18 -0
    Added test case for bug#19862: Sort with filesort by function evaluates function twice

  sql/item.h@stripped, 2006-07-24 16:26:35+04:00, evgen@stripped +1 -0
    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 presence of an SP function in an expression.

  sql/item_func.cc@stripped, 2006-07-24 16:26:15+04:00, evgen@stripped +7 -0
    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 presence of an SP function in an expression.

  sql/item_func.h@stripped, 2006-07-24 16:24:45+04:00, evgen@stripped +1 -0
    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 presence of an SP function in an expression. 

  sql/sql_select.cc@stripped, 2006-07-24 16:23:25+04:00, evgen@stripped +18 -0
    Fixed bug#19862: Sort with filesort by function evaluates function twice
    Now using of a tmp table is forced if an expression referenced by order
    list contains a SP function.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	evgen
# Host:	moonbone.local
# Root:	/work/19862-bug-5.0-opt-mysql

--- 1.203/sql/item.h	2006-07-24 16:27:20 +04:00
+++ 1.204/sql/item.h	2006-07-24 16:27:20 +04:00
@@ -752,6 +752,7 @@
   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; }

--- 1.293/sql/item_func.cc	2006-07-24 16:27:20 +04:00
+++ 1.294/sql/item_func.cc	2006-07-24 16:27:20 +04:00
@@ -398,6 +398,13 @@
   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);

--- 1.141/sql/item_func.h	2006-07-24 16:27:20 +04:00
+++ 1.142/sql/item_func.h	2006-07-24 16:27:20 +04:00
@@ -186,6 +186,7 @@
   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);
 };
 
 

--- 1.434/sql/sql_select.cc	2006-07-24 16:27:20 +04:00
+++ 1.435/sql/sql_select.cc	2006-07-24 16:27:20 +04:00
@@ -1064,6 +1064,24 @@
     {
       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 function due to slow
+        nature of SP.
+      */
+      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;
+        if (item->walk(&Item::func_type_checker_processor,(byte*)&type))
+        {
+          /* Force tmp table without sort */
+          need_tmp=1; simple_order=simple_group=0;
+          break;
+        }
+      }
+    }
   }
 
   tmp_having= having;

--- 1.203/mysql-test/r/sp.result	2006-07-24 16:27:20 +04:00
+++ 1.204/mysql-test/r/sp.result	2006-07-24 16:27:20 +04:00
@@ -5057,4 +5057,23 @@
 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;

--- 1.190/mysql-test/t/sp.test	2006-07-24 16:27:20 +04:00
+++ 1.191/mysql-test/t/sp.test	2006-07-24 16:27:20 +04:00
@@ -5963,6 +5963,24 @@
 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
Thread
bk commit into 5.0 tree (evgen:1.2208) BUG#19862eugene24 Jul