Inlining user-defined functions

Simple user-defined functions are sometimes inlined when called as part of a query. That is, the query is rewritten to be equivalent to the original query but without the function definition. Temporary functions, recursive functions, and functions with the NOT DETERMINISTIC clause are never inlined. Also, a function is never inlined if it is called with a subquery as an argument, or when it is called from inside a temporary procedure.

User-defined functions can be inlined if they take one of the following forms:

A function with a single RETURN statement. For example:

CREATE FUNCTION F1( arg1 INT, arg2 INT )
RETURNS INT
BEGIN
	RETURN arg1 * arg2
END;

A function that declares a single variable, assigns the variable, and returns a single value. For example:

CREATE FUNCTION F2( arg1 INT )
RETURNS INT
BEGIN
	DECLARE result INT;
	SET result = ( SELECT ManagerID FROM Employees WHERE EmployeeID=arg1 );
	RETURN result;
END;

A function that declares a single variable, selects into that variable, and returns a single value. For example:

CREATE FUNCTION F3( arg1 INT )
RETURNS INT
BEGIN
	DECLARE result INT;
	SELECT ManagerID INTO result FROM Employees e1 WHERE EmployeeID=arg1;
	RETURN result;
END;

A user-defined function is inlined by copying the body of the user-defined function, inserting the arguments from the call, and inserting appropriate CAST functions to ensure that the rewritten form of the query is equivalent to the original. For example, suppose you created a function similar to the function F1 defined previously, and then you call the procedure in a FROM clause of a query as follows:

SELECT F1( e.EmployeeID, 2.5 ) FROM Employees e;

The database server may rewrite the query as follows:

SELECT CAST( e.EmployeeID AS INT ) * CAST( 2.5 AS INT ) FROM Employees e;

Inlining user-defined functions

See also