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 ;
© 2025, Oracle Corporation and/or its affiliates
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 ;