bk commit into 5.0 tree (gshchepa:1.2527) 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-19 18:39:01+05:00, gshchepa@stripped +4 -0
  Fixed bug #29338.
  Optimization of queries with DETERMINISTIC functions in the
  WHERE clause was not effective: sequential scan was always
  used.
  Now a SF with the DETERMINISTIC flags is treated as constant
  when it's arguments are constants (or a SF doesn't has arguments).

  mysql-test/r/sp.result@stripped, 2007-07-19 18:38:52+05:00, gshchepa@stripped +32 -1
    Updated test case for bug #29338.

  mysql-test/t/sp.test@stripped, 2007-07-19 18:38:52+05:00, gshchepa@stripped +31 -1
    Updated test case for bug #29338.

  sql/item_func.cc@stripped, 2007-07-19 18:38:50+05:00, gshchepa@stripped +10 -0
    Fixed bug #29338.
    The Item_func_sp::update_used_tables and the
    Item_func_sp::fix_field functions have been modified
    to take into account the DETERMINISTIC flag of SF definition.

  sql/item_func.h@stripped, 2007-07-19 18:38:47+05:00, gshchepa@stripped +1 -1
    Fixed bug #29338.
    The Item_func_sp::used_tables has been removed
    (virtual Item_func::used_tables function is enough).
    The virtual Item_func_sp::update_used_tables function
    has been added.

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-19 18:38:52 +05:00
@@ -1,5 +1,7 @@
 use test;
 drop table if exists t1,t2,t3,t4;
+drop function if exists f1;
+drop function if exists f2;
 create table t1 (
 id   char(16) not null default '',
 data int not null
@@ -6144,7 +6146,7 @@ drop table t1,t2;
 CREATE TABLE t1 (a int auto_increment primary key) engine=MyISAM;
 CREATE TABLE t2 (a int auto_increment primary key, b int) engine=innodb;
 set @a=0;
-CREATE function bug27354() RETURNS int deterministic
+CREATE function bug27354() RETURNS int not deterministic
 begin
 insert into t1 values (null);
 set @a=@a+1;
@@ -6175,5 +6177,34 @@ View	Create View
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1`
AS select `test`.`metered`(`t1`.`a`) AS `metered` from `t1`
 DROP VIEW v1;
 DROP FUNCTION metered;
+DROP TABLE t1;
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 2;
+CREATE FUNCTION f2(I INT) RETURNS INT DETERMINISTIC RETURN 3;
+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
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(10);
+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=f2(c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	c1	5	NULL	5	Using where; Using index
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(rand());
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	c1	5	NULL	5	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-29 13:27:19 +05:00
+++ b/mysql-test/t/sp.test	2007-07-19 18:38:52 +05:00
@@ -23,6 +23,8 @@ use test;
 #
 --disable_warnings
 drop table if exists t1,t2,t3,t4;
+drop function if exists f1;
+drop function if exists f2;
 --enable_warnings
 create table t1 (
 	id   char(16) not null default '',
@@ -7098,7 +7100,7 @@ CREATE TABLE t2 (a int auto_increment pr
 set @a=0;
 
 delimiter |;
-CREATE function bug27354() RETURNS int deterministic
+CREATE function bug27354() RETURNS int not deterministic
 begin
 insert into t1 values (null);
 set @a=@a+1;
@@ -7134,5 +7136,33 @@ 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 DETERMINISTIC RETURN 2;
+CREATE FUNCTION f2(I INT) RETURNS INT DETERMINISTIC RETURN 3;
+
+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();
+
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(10);
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1);
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(rand());
+
+
+DROP VIEW v1;
+DROP FUNCTION f1;
+DROP TABLE t1;
 
 --echo End of 5.0 tests
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-19 18:38:50 +05:00
@@ -5591,5 +5591,15 @@ Item_func_sp::fix_fields(THD *thd, Item 
     
 #endif /* ! NO_EMBEDDED_ACCESS_CHECKS */
   }
+  if (!m_sp->m_chistics->detistic)
+   used_tables_cache |= RAND_TABLE_BIT;
   DBUG_RETURN(res);
+}
+
+
+void Item_func_sp::update_used_tables()
+{
+  Item_func::update_used_tables();
+  if (!m_sp->m_chistics->detistic)
+   used_tables_cache |= RAND_TABLE_BIT;
 }
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-19 18:38:47 +05:00
@@ -1467,7 +1467,7 @@ public:
   virtual ~Item_func_sp()
   {}
 
-  table_map used_tables() const { return RAND_TABLE_BIT; }
+  void update_used_tables();
 
   void cleanup();