11.2.5 Function Name Parsing and Resolution (original) (raw)

11.2.5 Function Name Parsing and Resolution

MySQL supports built-in (native) functions, loadable functions, 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 theIGNORE_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 tocount 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:

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. Thesql/lex.h source file lists the names of these special functions for which following whitespace determines their interpretation: names defined by theSYM_FN() macro in thesymbols[] array.

The following list names the functions in MySQL 8.0 that are affected by theIGNORE_SPACE setting and listed as special in the sql/lex.h source file. You may find it easiest to treat the no-whitespace requirement as applying to all function calls.

For functions not listed as special insql/lex.h, 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:_`funcname`_ () is interpreted as a built-in function if there is one with the given name; if not,_`funcname`_ () is interpreted as a loadable 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:

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);  
SELECT COUNT(*) FROM mytable;  
SELECT COUNT (*) FROM mytable;

However, enablingIGNORE_SPACE also has the side effect that the parser treats the affected function names as reserved words (seeSection 11.3, “Keywords and 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 interpretscount 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 asANSI that include it in their value:

SET sql_mode = 'ANSI';

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

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

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);

Function Name Resolution

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

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