Bug #21314 Stored functions in constant context not optimized to constants
Submitted: 27 Jul 2006 13:11 Modified: 17 Oct 2006 22:25
Reporter: Beat Vontobel (Silver Quality Contributor) (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.23 OS:Any (any)
Assigned to: Assigned Account CPU Architecture:Any
Tags: constant, Optimizer, stored function

[27 Jul 2006 13:11] Beat Vontobel 
Description:
A stored function in a WHERE clause will always be executed for every single row examined, even if it could easily be optimized away to a constant (as it is done for other constant expressions). This even happens for functions explicitly declared as NO SQL and DETERMINISTIC (which means they should have no side effects and should also return the same result everytime they are called).

How to repeat:
DROP TABLE IF EXISTS t;
DROP FUNCTION IF EXISTS f;

CREATE TABLE t (i INT(11));
INSERT INTO t (i) VALUES (1), (2), (3), (4), (5);

delimiter //

CREATE FUNCTION f(i INT)
  RETURNS INT
  NO SQL
  DETERMINISTIC
BEGIN
  DECLARE c INT DEFAULT 0;
  WHILE c < 300000 DO
    SET c = c + 1;
  END WHILE;
  RETURN i;
END //

delimiter ;

mysql> SELECT f(1);
+------+
| f(1) |
+------+
|    1 | 
+------+
1 row in set (1.13 sec)

mysql> SELECT * FROM t WHERE i = f(1);
+------+
| i    |
+------+
|    1 | 
+------+
1 row in set (5.55 sec)

The execution time (accumulated loop execution time) shows that the function will be executed for every single row, even if it is a constant expression.

Suggested fix:
Optimize constant functions to constants.
[27 Jul 2006 15:55] Hartmut Holzgraefe 
verified, see also bug #18761 for a similar problem with UDFs
[17 Oct 2006 22:25] Igor Babaev 
This problem should be considered together with the problem reported in #14669.
[10 Jul 2007 8:21] Gleb Shchepa 
See also #29338 (duplicate).