From: Date: July 10 2007 2:26am Subject: bk commit into 5.0 tree (gshchepa:1.2525) BUG#29338 List-Archive: http://lists.mysql.com/commits/30580 X-Bug: 29338 Message-Id: <20070710002700.DC7F83D40F1@localhost.localdomain> 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 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 &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 only. + + RETURN VALUES + TRUE if SP consists of RETURN instruction[s]. +*/ + +bool sp_head::const_expr() const +{ + const sp_instr *instr; + for (uint i= 0; (instr= const_cast(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: