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

2 files changed, 63 insertions, 2 deletions

diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index b411c65faee..4321cd92826 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -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;
@@ -6176,4 +6178,33 @@ v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VI
 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 --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 2f82482bdf7..aeb85ac6012 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -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 primary key, b int) engine=innodb;
 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