MySQL Identifier Question

Question

From the mysql certification guide's example questions ...

question

Will the following SQL statement succeed or result in an error? Assume that the table to be created doesn't already exist.

CREATE TABLE MD5 (id INT);

answer

The statement results in an error if the IGNORE_SPACE SQL mode is enabled. In that case, all function names become reserved words and the statement would return an error because MD5 is a function name.

If the IGNORE_SPACE SQL mode is not enabled, the statement succeeds:

mysql> CREATE TABLE MD5 (id INT);
Query OK, 0 rows affected


Note that the IGNORE_SPACE SQL mode is part of the ANSI SQL mode. 

Why do they talk about spaces? Anyone got any idea? What will be the correct answer? It fails because a function is a reserved word? Will it succeed when quoted, eg. with backtick... right?

Answer 1

IGNORE_SPACE mode allows spaces between function name and the following parenthesis. If it's enabled, all function names become reserved words because it becomes impossible for MySQL to determine whether you're calling a function or declaring (using) an identifier.

Hence, if it's enabled, your CREATE TABLE will fail and the following:

SELECT MD5 (some_column) FROM some_table

will work. If IGNORE_SPACE is disabled, the opposite will happen (CREATE TABLE will work and above SELECT will not because there is a space between function name and the parenthesis).

Answer 2

IGNORE_SPACE will ignore spaces between a potential function name and (

Mysql's manual page on function resolution explains it all, but here's the short version:

SELECT md5(colum1) FROM table;

Applies the md5 function to colum1.

 SELECT md5 (colum1) FROM table;

Applies the md5 function, if IGNORE_SPACE is enabled, to colum1.

If IGNORE_SPACE is off, "md5 (" is not treated as a function call, because there's a space in there.

Putting the backticks makes Mysql not thing the word is a function name but some database, table, column etc identifier.