Bug #56360 Constant functions
Submitted: 30 Aug 2010 10:40 Modified: 30 Aug 2010 10:52
Reporter: Winfried Trümper Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[30 Aug 2010 10:40] Winfried Trümper
Description:
Good day,

I'm re-factoring a medium size SQL-only application. The application is working well courtesy to the very reasonable extensions MySQL provided in the past. After editing a few thousand lines of SQL code I'd to suggest another extension MySQL could provide.

Constants functions could make statements (stored programs) more readable and faster. Yes, a good approximization of constants can be achieved with deterministic functions. In theory. In practice of MySQL, deterministic is an optimization which might get applied or not. Your deterministic function can be called any number of times any time. See the code at the end. That is different from a constant.

I will call the feature Inline Constants in the following. Two ways to implement the feature in MySQL come to my mind:

mysql.proc.type enum('FUNCTION','PROCEDURE','INLINE_CONSTANT')
- or -
mysql.proc.is_inline_constant enum('YES','NO')

In other words, either to add a CREATE INLINE_CONSTANT syntax or to expand function semantics. In both cases, the constant value would have to be stored in mysql.proc.constant_rv (longblob).

In theory the advantage of CREATE INLINE_CONSTANT is the isolation of changes, i.e. less likely to break stored programs in general. However, once you say CREATE, you also need ALTER, DROP and three times SHOW. Maybe a little bit much for such a feature. Beside that, the code assumes "either it is a procedure or a function", which keeps some constructs simple (bool is_proc, checks to TYPE_ENUM_*). Maintaining another option (in order to avoid code duplication) would require enum_sp_type and sp_type_name to be introduced. The change is almost trivial, but not really isolated.

Expanding function semantics appears to be the smallest change to the language. As an alternative to DETERMINISTIC there could be INLINE_CONSTANT for functions. An empty set of paranthesis would be required to refer to the constant, because it is still a function. Example under Suggested Fix.

Inline constants would save the overhead for checking security and calling parameters. An early step of statement processing would be to inline the constant values. The body of an inline constant would be used to calculate mysql.proc.constant_rv initially. It will only be re-executed upon ALTER FUNCTION. Minimum body would be BEGIN RETURN(some_value); END

Thanks for your time.

-Winfried

How to repeat:
-- Server version: 5.1.36
CREATE TABLE call_cnt (cnt int NOT NULL default 0);

DROP FUNCTION IF EXISTS pi_approx;
delimiter //
CREATE FUNCTION pi_approx()
RETURNS double
DETERMINISTIC
MODIFIES SQL
BEGIN
        DECLARE pi_approx double;
        SET pi_approx = 355/113;
	INSERT INTO call_cnt SET cnt = 1;
        RETURN(pi_approx);
END //
DELIMITER ;

SELECT BENCHMARK(100000, pi_approx());
SELECT COUNT(*) FROM call_cnt;

Suggested fix:
DROP FUNCTION IF EXISTS pi_approx;
delimiter //
CREATE FUNCTION pi_approx()
RETURNS double
INLINE_CONSTANT
BEGIN
        DECLARE pi_approx double;
        SET pi_approx = 355/113;
        RETURN(pi_approx);
END //
DELIMITER ;
[30 Aug 2010 10:52] Valeriy Kravchuk
Thank you for the feature request.