bk commit into 5.0 tree (gshchepa:1.2525) BUG#29338

[<gshchepa]

Below is the list of changes that have just been committed into a local
5.0 repository of uchum. When uchum 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, 2007-07-10 05:26:54+05:00, gshchepa@stripped +7 -0
  Fixed bug #29338.
  Trivial stored functions that consist of RETURN <constant expression>
  operator was evaluated ineffectively.
  This type of SF may be commonly used as named constants, so optimizations
  is desirable.
  
  Simple optimization has been added: now the optimizer treats such SF as
  constant expressions.

  mysql-test/r/sp.result@stripped, 2007-07-10 05:06:49+05:00, gshchepa@stripped +19 -0
    Updated test case for bug #29338.

  mysql-test/t/sp.test@stripped, 2007-07-10 05:06:46+05:00, gshchepa@stripped +22 -0
    Updated test case for bug #29338.

  sql/item.h@stripped, 2007-07-10 05:06:44+05:00, gshchepa@stripped +6 -0
    Fixed bug #29338.
    The Item::const_expr function has been added to distinguish
    constant items that don't read external data.

  sql/item_func.cc@stripped, 2007-07-10 05:06:26+05:00, gshchepa@stripped +19 -0
    Fixed bug #29338.

  sql/item_func.h@stripped, 2007-07-10 05:06:35+05:00, gshchepa@stripped +2 -3
    Fixed bug #29338.

  sql/sp_head.cc@stripped, 2007-07-10 05:06:40+05:00, gshchepa@stripped +26 -0
    Fixed bug #29338.

  sql/sp_head.h@stripped, 2007-07-10 05:06:42+05:00, gshchepa@stripped +10 -0
    Fixed bug #29338.
    The sp_head::const_expr and sp_instr::const_expr functions have
    been added to distinguish SPs and SP operators that neither
    change external data nor read external data other than SP arguments.

diff -Nrup a/mysql-test/r/sp.result b/mysql-test/r/sp.result
--- a/mysql-test/r/sp.result	2007-06-07 12:07:38 +05:00
+++ b/mysql-test/r/sp.result	2007-07-10 05:06:49 +05:00
@@ -6176,4 +6176,23 @@ v1	CREATE ALGORITHM=UNDEFINED DEFINER=`r
 DROP VIEW v1;
 DROP FUNCTION metered;
 DROP TABLE t1;
+CREATE FUNCTION f1() RETURNS INT RETURN 1 + 2;
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+CREATE VIEW v1 AS SELECT c1 FROM t1;
+EXPLAIN SELECT * FROM t1 WHERE c1=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	c1	c1	5	const	1	Using where; Using index
+EXPLAIN SELECT * FROM t1 WHERE c1=f1();
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	c1	c1	5	const	1	Using where; Using index
+EXPLAIN SELECT * FROM v1 WHERE c1=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	c1	c1	5	const	1	Using where; Using index
+EXPLAIN SELECT * FROM v1 WHERE c1=f1();
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	c1	c1	5	const	1	Using where; Using index
+DROP VIEW v1;
+DROP FUNCTION f1;
+DROP TABLE t1;
 End of 5.0 tests
diff -Nrup a/mysql-test/t/sp.test b/mysql-test/t/sp.test
--- a/mysql-test/t/sp.test	2007-06-02 03:42:03 +05:00
+++ b/mysql-test/t/sp.test	2007-07-10 05:06:46 +05:00
@@ -7130,5 +7130,27 @@ DROP VIEW v1;
 DROP FUNCTION metered;
 DROP TABLE t1;
 
+#
+# Bug #29338: no optimization for stored functions with a trivial body
+# always returning constant.
+#
+
+CREATE FUNCTION f1() RETURNS INT RETURN 1 + 2;
+
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+
+CREATE VIEW v1 AS SELECT c1 FROM t1;
+
+EXPLAIN SELECT * FROM t1 WHERE c1=1;
+EXPLAIN SELECT * FROM t1 WHERE c1=f1();
+
+EXPLAIN SELECT * FROM v1 WHERE c1=1;
+EXPLAIN SELECT * FROM v1 WHERE c1=f1();
+
+DROP VIEW v1;
+DROP FUNCTION f1;
+DROP TABLE t1;
 
 --echo End of 5.0 tests
diff -Nrup a/sql/item.h b/sql/item.h
--- a/sql/item.h	2007-07-06 05:39:23 +05:00
+++ b/sql/item.h	2007-07-10 05:06:44 +05:00
@@ -670,6 +670,12 @@ public:
   virtual bool const_during_execution() const 
   { return (used_tables() & ~PARAM_TABLE_BIT) == 0; }
   /*
+    Returns true if this is constant (during query execution, i.e. its value
+    will not change until next fix_fields), and its value is known, and
+    this value depends on neither table fields nor session/global variables.
+  */
+  virtual bool const_expr() const { return basic_const_item(); }
+  /*
     This is an essential method for correct functioning of VIEWS.
     To save a view in an .frm file we need its unequivocal
     definition in SQL that takes into account sql_mode and
diff -Nrup a/sql/item_func.cc b/sql/item_func.cc
--- a/sql/item_func.cc	2007-06-18 18:45:52 +05:00
+++ b/sql/item_func.cc	2007-07-10 05:06:26 +05:00
@@ -207,6 +207,22 @@ bool Item_func::walk (Item_processor pro
   return (this->*processor)(argument);
 }
 
+
+bool Item_func::const_expr() const
+{
+  if (arg_count)
+  {
+    Item **arg,**arg_end;
+    for (arg= args, arg_end= args+arg_count; arg != arg_end; arg++)
+    {
+      if (!(*arg)->const_expr())
+        return FALSE;
+    }
+  }
+  return TRUE;
+}
+
+
 void Item_func::traverse_cond(Cond_traverser traverser,
                               void *argument, traverse_order order)
 {
@@ -5566,6 +5582,9 @@ Item_func_sp::fix_fields(THD *thd, Item 
 
   if (res)
     DBUG_RETURN(res);
+
+  if (!m_sp->const_expr())
+    used_tables_cache|= RAND_TABLE_BIT; 
 
   if (thd->lex->view_prepare_mode)
   {
diff -Nrup a/sql/item_func.h b/sql/item_func.h
--- a/sql/item_func.h	2007-06-03 16:21:32 +05:00
+++ b/sql/item_func.h	2007-07-10 05:06:35 +05:00
@@ -135,6 +135,7 @@ public:
   */
   virtual const char *func_name() const= 0;
   virtual bool const_item() const { return const_item_cache; }
+  virtual bool const_expr() const;
   inline Item **arguments() const { return args; }
   void set_arguments(List<Item> &list);
   inline uint argument_count() const { return arg_count; }
@@ -1242,7 +1243,7 @@ class Item_func_get_user_var :public Ite
 public:
   LEX_STRING name; // keep it public
   Item_func_get_user_var(LEX_STRING a):
-    Item_func(), name(a) {}
+    Item_func(), var_entry(NULL), name(a) {}
   enum Functype functype() const { return GUSERVAR_FUNC; }
   LEX_STRING get_name() { return name; }
   double val_real();
@@ -1466,8 +1467,6 @@ public:
 
   virtual ~Item_func_sp()
   {}
-
-  table_map used_tables() const { return RAND_TABLE_BIT; }
 
   void cleanup();
 
diff -Nrup a/sql/sp_head.cc b/sql/sp_head.cc
--- a/sql/sp_head.cc	2007-06-19 02:54:33 +05:00
+++ b/sql/sp_head.cc	2007-07-10 05:06:40 +05:00
@@ -3633,6 +3633,32 @@ sp_head::add_used_tables_to_table_list(T
 
 
 /*
+  Check if a function body neither does side effects nor reads external data.
+
+  SYNOPSIS
+    sp_head::const_expr()
+
+  DESCRIPTION
+    Currently this function checks a stored function body for instructions
+    other than RETURN <constant expression> only.
+
+  RETURN VALUES
+    TRUE    if SP consists of RETURN <constant expression> instruction[s].
+*/
+
+bool sp_head::const_expr() const
+{
+  const sp_instr *instr;
+  for (uint i= 0; (instr= const_cast<sp_head *>(this)->get_instr(i)); i++)
+  {
+    if (!instr->const_expr())
+      return FALSE;
+  }
+  return TRUE;
+}
+
+
+/*
   Simple function for adding an explicetly named (systems) table to
   the global table list, e.g. "mysql", "proc".
 */
diff -Nrup a/sql/sp_head.h b/sql/sp_head.h
--- a/sql/sp_head.h	2007-05-07 13:23:09 +05:00
+++ b/sql/sp_head.h	2007-07-10 05:06:42 +05:00
@@ -361,6 +361,8 @@ public:
                  HAS_COMMIT_OR_ROLLBACK|HAS_SQLCOM_RESET|HAS_SQLCOM_FLUSH));
   }
 
+  bool const_expr() const;
+
 #ifndef DBUG_OFF
   int show_routine_code(THD *thd);
 #endif
@@ -528,6 +530,12 @@ public:
     m_ip= dst;
   }
 
+  /*
+    Return false if this instruction either makes side-effects or
+    reads external data.
+  */
+  virtual bool const_expr() const { return false; }
+
 }; // class sp_instr : public Sql_alloc
 
 
@@ -876,6 +884,8 @@ public:
     marked= 1;
     return UINT_MAX;
   }
+
+  virtual bool const_expr() const { return m_value->const_expr(); }
 
 protected: