Skip navigation links
MySQL 5.4 Reference Manual :: 8 Language Structure :: 8.2 Schema Object Names :: 8.2.4 Function Name Parsing and Resolution
8.3 Reserved Words »
Section Navigation      [Toggle]

8.2.4. Function Name Parsing and Resolution

MySQL 5.4 supports built-in (native) functions, user-defined functions (UDFs), and stored functions. This section describes how the server recognizes whether the name of a built-in function is used as a function call or as an identifier, and how the server determines which function to use in cases when functions of different types exist with a given name.

Built-In Function Name Parsing

The parser uses default rules for parsing names of built-in functions. These rules can be changed by enabling the IGNORE_SPACE SQL mode.

When the parser encounters a word that is the name of a built-in function, it must determine whether the name signifies a function call or is instead a nonexpression reference to an identifier such as a table or column name. For example, in the following statements, the first reference to count is a function call, whereas the second reference is a table name: 

SELECT COUNT(*) FROM mytable;
CREATE TABLE count (i INT);

The parser should recognize the name of a built-in function as indicating a function call only when parsing what is expected to be an expression. That is, in nonexpression context, function names are permitted as identifiers.

However, some built-in functions have special parsing or implementation considerations, so the parser uses the following rules by default to distinguish whether their names are being used as function calls or as identifiers in nonexpression context:

  • To use the name as a function call in an expression, there must be no whitespace between the name and the following “(” parenthesis character.

  • Conversely, to use the function name as an identifier, it must not be followed immediately by a parenthesis.

The requirement that function calls be written with no whitespace between the name and the parenthesis applies only to the built-in functions that have special considerations. COUNT is one such name. The exact list of function names for which following whitespace determines their interpretation are those listed in the sql_functions[] array of the sql/lex.h source file. Before MySQL 5.1, these are rather numerous (about 200), so you may find it easiest to treat the no-whitespace requirement as applying to all function calls. In MySQL 5.1 and later, parser improvements reduce to about 30 the number of affected function names.

For functions not listed in the sql_functions[]) array, whitespace does not matter. They are interpreted as function calls only when used in expression context and may be used freely as identifiers otherwise. ASCII is one such name. However, for these nonaffected function names, interpretation may vary in expression context: func_name () is interpreted as a built-in function if there is one with the given name; if not, func_name () is interpreted as a user-defined function or stored function if one exists with that name.

The IGNORE_SPACE SQL mode can be used to modify how the parser treats function names that are whitespace-sensitive:

  • With IGNORE_SPACE disabled, the parser interprets the name as a function call when there is no whitespace between the name and the following parenthesis. This occurs even when the function name is used in nonexpression context: 

    mysql> CREATE TABLE count(i INT);
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'count(i INT)'

    To eliminate the error and cause the name to be treated as an identifier, either use whitespace following the name or write it as a quoted identifier (or both): 

    CREATE TABLE count (i INT);
CREATE TABLE `count`(i INT);
CREATE TABLE `count` (i INT);

  • With IGNORE_SPACE enabled, the parser loosens the requirement that there be no whitespace between the function name and the following parenthesis. This provides more flexibility in writing function calls. For example, either of the following function calls are legal: 

    SELECT COUNT(*) FROM mytable;
SELECT COUNT (*) FROM mytable;

    However, enabling IGNORE_SPACE also has the side effect that the parser treats the affected function names as reserved words (see Section 8.3, “Reserved Words”). This means that a space following the name no longer signifies its use as an identifier. The name can be used in function calls with or without following whitespace, but causes a syntax error in nonexpression context unless it is quoted. For example, with IGNORE_SPACE enabled, both of the following statements fail with a syntax error because the parser interprets count as a reserved word: 

    CREATE TABLE count(i INT);
CREATE TABLE count (i INT);

    To use the function name in nonexpression context, write it as a quoted identifier: 

    CREATE TABLE `count`(i INT);
CREATE TABLE `count` (i INT);

To enable the IGNORE_SPACE SQL mode, use this statement: 

SET sql_mode = 'IGNORE_SPACE';

IGNORE_SPACE is also enabled by certain other composite modes such as ANSI that include it in their value: 

SET sql_mode = 'ANSI';

Check Section 5.1.8, “Server SQL Modes”, to see which composite modes enable IGNORE_SPACE.

To minimize the dependency of SQL code on the IGNORE_SPACE setting, use these guidelines:

  • Avoid creating UDFs or stored functions that have the same name as a built-in function.

  • Avoid using function names in nonexpression context. For example, these statements use count (one of the affected function names affected by IGNORE_SPACE), so they fail with or without whitespace following the name if IGNORE_SPACE is enabled: 

    CREATE TABLE count(i INT);
CREATE TABLE count (i INT);

    If you must use a function name in nonexpression context, write it as a quoted identifier: 

    CREATE TABLE `count`(i INT);
CREATE TABLE `count` (i INT);

The number of function names affected by IGNORE_SPACE was reduced significantly in MySQL 5.1.13, from about 200 to about 30. As of MySQL 5.1.13, only the following functions are still affected by the IGNORE_SPACE setting.

ADDDATE BIT_AND BIT_OR BIT_XOR
CAST COUNT CURDATE CURTIME
DATE_ADD DATE_SUB EXTRACT GROUP_CONCAT
MAX MID MIN NOW
POSITION SESSION_USER STD STDDEV
STDDEV_POP STDDEV_SAMP SUBDATE SUBSTR
SUBSTRING SUM SYSDATE SYSTEM_USER
TRIM VARIANCE VAR_POP VAR_SAMP

For earlier versions of MySQL, check the contents of the sql_functions[] array in the sql/lex.h source file to see which functions are affected by IGNORE_SPACE.

Incompatibility warning: The change in MySQL 5.1.13 that reduces the number of function names affected by IGNORE_SPACE improves the consistency of parser operation. However, it also introduces the possibility of incompatibility for old SQL code that relies on the following conditions:

  • IGNORE_SPACE is disabled.

  • The presence or absence of whitespace following a function name is used to distinguish between a built-in function and stored function that have the same name, such as PI() versus PI ().

For functions that are no longer affected by IGNORE_SPACE as of MySQL 5.1.13, that strategy no longer works. Either of the following approaches can be used if you have code that is subject to the preceding incompatibility:

  • If a stored function has a name that conflicts with a built-in function, refer to the stored function with a schema name qualifier, regardless of whether whitespace is present. For example, write schema_name.PI() or schema_name.PI ().

  • Alternatively, rename the stored function to use a nonconflicting name and change invocations of the function to use the new name.

Function Name Resolution

The following rules describe how the server resolves references to function names for function creation and invocation:

  • Built-in functions and user-defined functions

    An error occurs if you try to create a UDF with the same name as a built-in function.

  • Built-in functions and stored functions

    It is possible to create a stored function with the same name as a built-in function, but to invoke the stored function it is necessary to qualify it with a schema name. For example, if you create a stored function named PI in the test schema, you invoke it as test.PI() because the server resolves PI() as a reference to the built-in function. The server creates a warning if the stored function name collides with a built-in function name. The warning can be displayed with SHOW WARNINGS.

  • User-defined functions and stored functions

    User-defined functions and stored functions share the same namespace, so you cannot create a UDF and a stored function with the same name.

The preceding function name resolution rules have implications for upgrading to versions of MySQL that implement new built-in functions:

  • If you have already created a user-defined function with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF, and then use CREATE FUNCTION to re-create the UDF with a different nonconflicting name.

  • If a new version of MySQL implements a built-in function with the same name as an existing stored function, you have two choices: Rename the stored function to use a nonconflicting name, or change calls to the function so that they use a schema qualifier (that is, use schema_name.func_name() syntax).