| List: | Commits | « Previous MessageNext Message » | |
| From: | paul | Date: | January 9 2007 8:25pm |
| Subject: | svn commit - mysqldoc@docsrva: r4415 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | ||
| View as plain text | |||
Author: paul Date: 2007-01-09 20:25:04 +0100 (Tue, 09 Jan 2007) New Revision: 4415 Log: r17875@polar: paul | 2007-01-09 13:22:01 -0600 More detailed description of what IGNORE_SPACE actually does. (Bug#21114, WL#3565) Modified: trunk/refman-4.1/database-administration.xml trunk/refman-4.1/language-structure.xml trunk/refman-5.0/database-administration.xml trunk/refman-5.0/language-structure.xml trunk/refman-5.1/database-administration.xml trunk/refman-5.1/installing.xml trunk/refman-5.1/language-structure.xml trunk/refman-5.1/news-5.1.xml Property changes on: trunk ___________________________________________________________________ Name: svk:merge - 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:17755 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:14282 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:13015 + 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:17875 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:14282 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:13015 Modified: trunk/refman-4.1/database-administration.xml =================================================================== --- trunk/refman-4.1/database-administration.xml 2007-01-09 19:17:52 UTC (rev 4414) +++ trunk/refman-4.1/database-administration.xml 2007-01-09 19:25:04 UTC (rev 4415) Changed blocks: 2, Lines Added: 29, Lines Deleted: 15; 3054 bytes @@ -1744,6 +1744,10 @@ </para> <para> + See <xref linkend="myisam-start"/>. + </para> + + <para> This option is available as of MySQL 3.23.25. </para> </listitem> @@ -8945,35 +8949,45 @@ <para> Allow spaces between a function name and the - ‘<literal>(</literal>’ character. This forces - all function names to be treated as reserved words. As a - result, if you want to access any database, table, or - column name that is a reserved word, you must quote it. - For example, because there is a <literal>USER()</literal> - function, the name of the <literal>user</literal> table in - the <literal>mysql</literal> database and the - <literal>User</literal> column in that table become - reserved, so you must quote them: + ‘<literal>(</literal>’ character. This causes + built-in function names to be treated as reserved words. + As a result, identifiers that are the same as function + names must be quoted as described in + <xref linkend="identifiers"/>. For example, because there + is a <literal>COUNT()</literal> function, the use of + <literal>count</literal> as a table name in the following + statement causes an error: </para> <programlisting> -SELECT "User" FROM mysql."user"; +mysql> <userinput>CREATE TABLE count (i INT);</userinput> +ERROR 1064 (42000): You have an error in your SQL syntax </programlisting> <para> + The table name should be quoted: + </para> + +<programlisting> +mysql> <userinput>CREATE TABLE `count` (i INT);</userinput> +Query OK, 0 rows affected (0.00 sec) +</programlisting> + + <para> The <literal>IGNORE_SPACE</literal> SQL mode applies to built-in functions, not to user-defined functions. It is - always allowable to have spaces after a user-defined - function name, regardless of whether - <literal>IGNORE_SPACE</literal> is enabled. + always allowable to have spaces after a UDF name, + regardless of whether <literal>IGNORE_SPACE</literal> is + enabled. </para> <para> - (Added in MySQL 4.0.0) + For further discussion of <literal>IGNORE_SPACE</literal>, + see <xref linkend="function-resolution"/>. </para> <para> - See <xref linkend="myisam-start"/>. + (Added in MySQL 4.0.0) </para> </listitem> Modified: trunk/refman-4.1/language-structure.xml =================================================================== --- trunk/refman-4.1/language-structure.xml 2007-01-09 19:17:52 UTC (rev 4414) +++ trunk/refman-4.1/language-structure.xml 2007-01-09 19:25:04 UTC (rev 4415) Changed blocks: 4, Lines Added: 292, Lines Deleted: 55; 14834 bytes @@ -1343,21 +1343,284 @@ <section id="function-resolution"> - <title>Function Name Resolution</title> + <title>Function Name Parsing and Resolution</title> + <indexterm> + <primary>function names</primary> + <secondary>parsing</secondary> + </indexterm> + + <indexterm> + <primary>function names</primary> + <secondary>resolving ambiguity</secondary> + </indexterm> + <para> MySQL ¤t-series; supports built-in (native) functions and - user-defined functions (UDFs). The server resolves references to - function names for function creation and invocation as follows: - A UDF can be created with the same name as a built-in function - but the UDF cannot be invoked because the parser resolves - invocations of the function to refer to the built-in function. - For example, if you create a UDF named <literal>ABS</literal>, - references to <literal>ABS()</literal> invoke the built-in - function. + user-defined functions (UDFs). 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. </para> <para> + <emphasis role="bold">Built-In Function Name Parsing</emphasis> + </para> + + <para> + The parser uses default rules for parsing names of built-in + functions. These rules can be changed by enabling the + <literal>IGNORE_SPACE</literal> SQL mode. + </para> + + <para> + 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 non-expression reference to an + identifier such as a table or column name. For example, in the + following statements, the first reference to + <literal>count</literal> is a function call, whereas the second + reference is a table name: + </para> + +<programlisting> +SELECT COUNT(*) FROM mytable; +CREATE TABLE count (i INT); +</programlisting> + + <para> + 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 non-expression context, function + names are permitted as identifiers. + </para> + + <para> + 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 non-expression + context: + </para> + + <itemizedlist> + + <listitem> + <para> + To use the name as a function call in an expression, there + must be no whitespace between the name and the following + ‘<literal>(</literal>’ parenthesis character. + </para> + </listitem> + + <listitem> + <para> + Conversely, to use the function name as an identifier, it + must not be followed immediately by a parenthesis. + </para> + </listitem> + + </itemizedlist> + + <para> + 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. + <literal>COUNT</literal> is one such name. The exact list of + function names for which following whitespace determines their + interpretation are those listed in the + <literal>sql_functions[]</literal> array of the + <filename>sql/lex.h</filename> source file. Before MySQL 5.1, + they 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, parser improvements reduce to + about 30 the number of affected function names. + </para> + + <para> + For functions not listed in the 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. <literal>ASCII</literal> is one such name. + </para> + + <para> + The <literal>IGNORE_SPACE</literal> SQL mode can be used to + modify how the parser treats function names that are + whitespace-sensitive: + </para> + + <itemizedlist> + + <listitem> + <para> + With <literal>IGNORE_SPACE</literal> 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 + non-expression context: + </para> + +<programlisting> +mysql> <userinput>CREATE TABLE count(i INT);</userinput> +ERROR 1064 (42000): You have an error in your SQL syntax ... +near 'count(i INT)' +</programlisting> + + <para> + 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): + </para> + +<programlisting> +CREATE TABLE count (i INT); +CREATE TABLE `count`(i INT); +CREATE TABLE `count` (i INT); +</programlisting> + + <para> + For non-affected function names (those not listed in + <literal>sql_functions[]</literal>), interpretation may vary + when there is whitespace following the function name in + expression context: + <literal><replaceable>func_name</replaceable> ()</literal> + is interpreted as a built-in function if there is one; if + not, <literal><replaceable>func_name</replaceable> + ()</literal> is interpreted as a user-defined function or + stored function if one exists with that name. + </para> + </listitem> + + <listitem> + <para> + With <literal>IGNORE_SPACE</literal> 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: + </para> + +<programlisting> +SELECT COUNT(*) FROM mytable; +SELECT COUNT (*) FROM mytable; +</programlisting> + + <para> + However, enabling <literal>IGNORE_SPACE</literal> also has + the side effect that the parser treats the affected function + names as reserved words (see + <xref linkend="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 + non-expression context unless it is quoted. For example, + with <literal>IGNORE_SPACE</literal> enabled, both of the + following statements fail with a syntax error because the + parser interprets <literal>count</literal> as a reserved + word: + </para> + +<programlisting> +CREATE TABLE count(i INT); +CREATE TABLE count (i INT); +</programlisting> + + <para> + To use the function name in non-expression context, write it + as a quoted identifier: + </para> + +<programlisting> +CREATE TABLE `count`(i INT); +CREATE TABLE `count` (i INT); +</programlisting> + </listitem> + + </itemizedlist> + + <para> + To enable the <literal>IGNORE_SPACE</literal> SQL mode, use this + statement: + </para> + +<programlisting> +SET sql_mode = 'IGNORE_SPACE'; +</programlisting> + + <para> + <literal>IGNORE_SPACE</literal> is also enabled by certain other + composite modes such as <literal>ANSI</literal> that include it + in their value: + </para> + +<programlisting> +SET sql_mode = 'ANSI'; +</programlisting> + + <para> + Check <xref linkend="server-sql-mode"/>, to see which composite + modes enable <literal>IGNORE_SPACE</literal>. + </para> + + <para> + To minimize the dependency of SQL code on the + <literal>IGNORE_SPACE</literal> setting, use these guidelines: + </para> + + <itemizedlist> + + <listitem> + <para> + Avoid creating UDFs or stored functions that have the same + name as a built-in function. + </para> + </listitem> + + <listitem> + <para> + Avoid using function names in non-expression context. For + example, these statements use <literal>count</literal> (one + of the affected function names affected by + <literal>IGNORE_SPACE</literal>), so they fail with or + without whitespace following the name if + <literal>IGNORE_SPACE</literal> is enabled: + </para> + +<programlisting> +CREATE TABLE count(i INT); +CREATE TABLE count (i INT); +</programlisting> + + <para> + If you must use a function name in non-expression context, + write it as a quoted identifier: + </para> + +<programlisting> +CREATE TABLE `count`(i INT); +CREATE TABLE `count` (i INT); +</programlisting> + </listitem> + + </itemizedlist> + + <para> + <emphasis role="bold">Function Name Resolution</emphasis> + </para> + + <para> + The server resolves references to function names for function + creation and invocation as follows: A UDF can be created with + the same name as a built-in function but the UDF cannot be + invoked because the parser resolves invocations of the function + to refer to the built-in function. For example, if you create a + UDF named <literal>ABS</literal>, references to + <literal>ABS()</literal> invoke the built-in function. + </para> + + <para> 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 @@ -1386,11 +1649,11 @@ </indexterm> <para> - A common problem stems from trying to use an identifier such as a - table or column name that is a reserved word such as - <literal>SELECT</literal> or the name of a built-in MySQL data - type or function such as <literal>TIMESTAMP</literal> or - <literal>GROUP</literal>. + Certain words such as <literal>SELECT</literal>, + <literal>DELETE</literal>, or <literal>BIGINT</literal> are + reserved and require special treatment for use as identifiers such + as table and column names. This may also be true for the names of + built-in functions. </para> <para> @@ -1400,9 +1663,8 @@ <programlisting> mysql> <userinput>CREATE TABLE interval (begin INT, end INT);</userinput> -ERROR 1064 (42000): You have an error in your SQL syntax; check -the manual that corresponds to your MySQL server version for -the right syntax to use near 'interval (begin INT, end INT)' +ERROR 1064 (42000): You have an error in your SQL syntax ... +near 'interval (begin INT, end INT)' mysql> <userinput>CREATE TABLE `interval` (begin INT, end INT);</userinput> Query OK, 0 rows affected (0.01 sec) @@ -1410,53 +1672,28 @@ <para> Exception: A word that follows a period in a qualified name must - be an identifier, so it need not be quoted even if it is reserved. + be an identifier, so it need not be quoted even if it is reserved: </para> - <para> - Function names are permitted as identifiers. For example, - <literal>ABS</literal> is acceptable as a column name. However, by - default, no whitespace is allowed in function invocations between - the function name and the following - ‘<literal>(</literal>’ character. This requirement - enables the parser to distinguish whether the name is used in a - function call or in non-function context. - </para> - - <para> - A side effect of this requirement is that omitting a space in some - contexts causes the parser to interpret an identifier as a - function name. For example, this statement is legal: - </para> - <programlisting> -mysql> <userinput>CREATE TABLE abs (val INT);</userinput> +mysql> <userinput>CREATE TABLE mydb.interval (begin INT, end INT);</userinput> +Query OK, 0 rows affected (0.01 sec) </programlisting> <para> - But omitting the space after <literal>abs</literal> results in a - syntax error because the statement then appears to invoke the - <literal>ABS()</literal> function: + Names of built-in functions are permitted as identifiers but may + require care to be used as such. For example, + <literal>COUNT</literal> is acceptable as a column name. However, + by default, no whitespace is allowed in function invocations + between the function name and the following + ‘<literal>(</literal>’ character. This requirement + enables the parser to distinguish whether the name is used in a + function call or in non-function context. For further detail on + recognition of function names, see + <xref linkend="function-resolution"/>. </para> -<programlisting> -mysql> <userinput>CREATE TABLE abs(val INT);</userinput> -ERROR 1064 (42000) at line 2: You have an error in your SQL -syntax ... near 'abs(val INT)' -</programlisting> - <para> - If the <literal>IGNORE_SPACE</literal> SQL mode is enabled, the - server allows function invocations to have whitespace between a - function name and the following ‘<literal>(</literal>’ - character. This causes function names to be treated as reserved - words. As a result, identifiers that are the same as function - names must be quoted as described in - <xref linkend="identifiers"/>. The server SQL mode is controlled - as described in <xref linkend="server-sql-mode"/>. - </para> - - <para> The words in the following table are explicitly reserved in MySQL ¤t-series;. At some point, you might upgrade to a higher version, so it's a good idea to have a look at future reserved Modified: trunk/refman-5.0/database-administration.xml =================================================================== --- trunk/refman-5.0/database-administration.xml 2007-01-09 19:17:52 UTC (rev 4414) +++ trunk/refman-5.0/database-administration.xml 2007-01-09 19:25:04 UTC (rev 4415) Changed blocks: 2, Lines Added: 14, Lines Deleted: 9; 2412 bytes @@ -10406,17 +10406,17 @@ <para> Allow spaces between a function name and the ‘<literal>(</literal>’ character. This causes - function names to be treated as reserved words. As a - result, identifiers that are the same as function names - must be quoted as described in + built-in function names to be treated as reserved words. + As a result, identifiers that are the same as function + names must be quoted as described in <xref linkend="identifiers"/>. For example, because there - is a <literal>ABS()</literal> function, the use of - <literal>abs</literal> as a table name in the following + is a <literal>COUNT()</literal> function, the use of + <literal>count</literal> as a table name in the following statement causes an error: </para> <programlisting> -mysql> <userinput>CREATE TABLE abs (i INT);</userinput> +mysql> <userinput>CREATE TABLE count (i INT);</userinput> ERROR 1064 (42000): You have an error in your SQL syntax </programlisting> @@ -10425,17 +10425,22 @@ </para> <programlisting> -mysql> <userinput>CREATE TABLE `abs` (i INT);</userinput> +mysql> <userinput>CREATE TABLE `count` (i INT);</userinput> Query OK, 0 rows affected (0.00 sec) </programlisting> <para> The <literal>IGNORE_SPACE</literal> SQL mode applies to built-in functions, not to user-defined functions or - stored routines. It is always allowable to have spaces - after a UDF or routine name, regardless of whether + stored functions. It is always allowable to have spaces + after a UDF or stored function name, regardless of whether <literal>IGNORE_SPACE</literal> is enabled. </para> + + <para> + For further discussion of <literal>IGNORE_SPACE</literal>, + see <xref linkend="function-resolution"/>. + </para> </listitem> <listitem> Modified: trunk/refman-5.0/language-structure.xml =================================================================== --- trunk/refman-5.0/language-structure.xml 2007-01-09 19:17:52 UTC (rev 4414) +++ trunk/refman-5.0/language-structure.xml 2007-01-09 19:25:04 UTC (rev 4415) Changed blocks: 5, Lines Added: 317, Lines Deleted: 63; 16335 bytes @@ -1333,19 +1333,283 @@ <section id="function-resolution"> - <title>Function Name Resolution</title> + <title>Function Name Parsing and Resolution</title> + <indexterm> + <primary>function names</primary> + <secondary>parsing</secondary> + </indexterm> + + <indexterm> + <primary>function names</primary> + <secondary>resolving ambiguity</secondary> + </indexterm> + <para> MySQL ¤t-series; supports built-in (native) functions, - user-defined functions (UDFs), and stored functions. The - following rules describe how the server resolves references to - function names for function creation and invocation: + 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. </para> + <para> + <emphasis role="bold">Built-In Function Name Parsing</emphasis> + </para> + + <para> + The parser uses default rules for parsing names of built-in + functions. These rules can be changed by enabling the + <literal>IGNORE_SPACE</literal> SQL mode. + </para> + + <para> + 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 non-expression reference to an + identifier such as a table or column name. For example, in the + following statements, the first reference to + <literal>count</literal> is a function call, whereas the second + reference is a table name: + </para> + +<programlisting> +SELECT COUNT(*) FROM mytable; +CREATE TABLE count (i INT); +</programlisting> + + <para> + 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 non-expression context, function + names are permitted as identifiers. + </para> + + <para> + 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 non-expression + context: + </para> + <itemizedlist> <listitem> <para> + To use the name as a function call in an expression, there + must be no whitespace between the name and the following + ‘<literal>(</literal>’ parenthesis character. + </para> + </listitem> + + <listitem> + <para> + Conversely, to use the function name as an identifier, it + must not be followed immediately by a parenthesis. + </para> + </listitem> + + </itemizedlist> + + <para> + 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. + <literal>COUNT</literal> is one such name. The exact list of + function names for which following whitespace determines their + interpretation are those listed in the + <literal>sql_functions[]</literal> array of the + <filename>sql/lex.h</filename> source file. Before MySQL 5.1, + they 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, parser improvements reduce to + about 30 the number of affected function names. + </para> + + <para> + For functions not listed in the 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. <literal>ASCII</literal> is one such name. + </para> + + <para> + The <literal>IGNORE_SPACE</literal> SQL mode can be used to + modify how the parser treats function names that are + whitespace-sensitive: + </para> + + <itemizedlist> + + <listitem> + <para> + With <literal>IGNORE_SPACE</literal> 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 + non-expression context: + </para> + +<programlisting> +mysql> <userinput>CREATE TABLE count(i INT);</userinput> +ERROR 1064 (42000): You have an error in your SQL syntax ... +near 'count(i INT)' +</programlisting> + + <para> + 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): + </para> + +<programlisting> +CREATE TABLE count (i INT); +CREATE TABLE `count`(i INT); +CREATE TABLE `count` (i INT); +</programlisting> + + <para> + For non-affected function names (those not listed in + <literal>sql_functions[]</literal>), interpretation may vary + when there is whitespace following the function name in + expression context: + <literal><replaceable>func_name</replaceable> ()</literal> + is interpreted as a built-in function if there is one; if + not, <literal><replaceable>func_name</replaceable> + ()</literal> is interpreted as a user-defined function or + stored function if one exists with that name. + </para> + </listitem> + + <listitem> + <para> + With <literal>IGNORE_SPACE</literal> 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: + </para> + +<programlisting> +SELECT COUNT(*) FROM mytable; +SELECT COUNT (*) FROM mytable; +</programlisting> + + <para> + However, enabling <literal>IGNORE_SPACE</literal> also has + the side effect that the parser treats the affected function + names as reserved words (see + <xref linkend="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 + non-expression context unless it is quoted. For example, + with <literal>IGNORE_SPACE</literal> enabled, both of the + following statements fail with a syntax error because the + parser interprets <literal>count</literal> as a reserved + word: + </para> + +<programlisting> +CREATE TABLE count(i INT); +CREATE TABLE count (i INT); +</programlisting> + + <para> + To use the function name in non-expression context, write it + as a quoted identifier: + </para> + +<programlisting> +CREATE TABLE `count`(i INT); +CREATE TABLE `count` (i INT); +</programlisting> + </listitem> + + </itemizedlist> + + <para> + To enable the <literal>IGNORE_SPACE</literal> SQL mode, use this + statement: + </para> + +<programlisting> +SET sql_mode = 'IGNORE_SPACE'; +</programlisting> + + <para> + <literal>IGNORE_SPACE</literal> is also enabled by certain other + composite modes such as <literal>ANSI</literal> that include it + in their value: + </para> + +<programlisting> +SET sql_mode = 'ANSI'; +</programlisting> + + <para> + Check <xref linkend="server-sql-mode"/>, to see which composite + modes enable <literal>IGNORE_SPACE</literal>. + </para> + + <para> + To minimize the dependency of SQL code on the + <literal>IGNORE_SPACE</literal> setting, use these guidelines: + </para> + + <itemizedlist> + + <listitem> + <para> + Avoid creating UDFs or stored functions that have the same + name as a built-in function. + </para> + </listitem> + + <listitem> + <para> + Avoid using function names in non-expression context. For + example, these statements use <literal>count</literal> (one + of the affected function names affected by + <literal>IGNORE_SPACE</literal>), so they fail with or + without whitespace following the name if + <literal>IGNORE_SPACE</literal> is enabled: + </para> + +<programlisting> +CREATE TABLE count(i INT); +CREATE TABLE count (i INT); +</programlisting> + + <para> + If you must use a function name in non-expression context, + write it as a quoted identifier: + </para> + +<programlisting> +CREATE TABLE `count`(i INT); +CREATE TABLE `count` (i INT); +</programlisting> + </listitem> + + </itemizedlist> + + <para> + <emphasis role="bold">Function Name Resolution</emphasis> + </para> + + <para> + The following rules describe how the server resolves references + to function names for function creation and invocation: + </para> + + <itemizedlist> + + <listitem> + <para> Built-in functions and user-defined functions </para> @@ -1393,21 +1657,37 @@ <para> 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 <literal>DROP - FUNCTION</literal> to drop the UDF, and then use <literal>CREATE - FUNCTION</literal> to re-create the UDF with a different - non-conflicting 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 non-conflicting name, or change calls to the function so - that they use a schema qualifier (that is, use - <literal><replaceable>schema_name</replaceable>.<replaceable>func_name</replaceable>()</literal> - syntax). + functions: </para> + <itemizedlist> + + <listitem> + <para> + 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 <literal>DROP + FUNCTION</literal> to drop the UDF, and then use + <literal>CREATE FUNCTION</literal> to re-create the UDF with + a different non-conflicting name. + </para> + </listitem> + + <listitem> + <para> + 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 + non-conflicting name, or change calls to the function so + that they use a schema qualifier (that is, use + <literal><replaceable>schema_name</replaceable>.<replaceable>func_name</replaceable>()</literal> + syntax). + </para> + </listitem> + + </itemizedlist> + </section> </section> @@ -1425,11 +1705,11 @@ </indexterm> <para> - A common problem stems from trying to use an identifier such as a - table or column name that is a reserved word such as - <literal>SELECT</literal> or the name of a built-in MySQL data - type or function such as <literal>TIMESTAMP</literal> or - <literal>GROUP</literal>. + Certain words such as <literal>SELECT</literal>, + <literal>DELETE</literal>, or <literal>BIGINT</literal> are + reserved and require special treatment for use as identifiers such + as table and column names. This may also be true for the names of + built-in functions. </para> <para> @@ -1439,9 +1719,8 @@ <programlisting> mysql> <userinput>CREATE TABLE interval (begin INT, end INT);</userinput> -ERROR 1064 (42000): You have an error in your SQL syntax; check -the manual that corresponds to your MySQL server version for -the right syntax to use near 'interval (begin INT, end INT)' +ERROR 1064 (42000): You have an error in your SQL syntax ... +near 'interval (begin INT, end INT)' mysql> <userinput>CREATE TABLE `interval` (begin INT, end INT);</userinput> Query OK, 0 rows affected (0.01 sec) @@ -1449,53 +1728,28 @@ <para> Exception: A word that follows a period in a qualified name must - be an identifier, so it need not be quoted even if it is reserved. + be an identifier, so it need not be quoted even if it is reserved: </para> - <para> - Function names are permitted as identifiers. For example, - <literal>ABS</literal> is acceptable as a column name. However, by - default, no whitespace is allowed in function invocations between - the function name and the following - ‘<literal>(</literal>’ character. This requirement - enables the parser to distinguish whether the name is used in a - function call or in non-function context. - </para> - - <para> - A side effect of this requirement is that omitting a space in some - contexts causes the parser to interpret an identifier as a - function name. For example, this statement is legal: - </para> - <programlisting> -mysql> <userinput>CREATE TABLE abs (val INT);</userinput> +mysql> <userinput>CREATE TABLE mydb.interval (begin INT, end INT);</userinput> +Query OK, 0 rows affected (0.01 sec) </programlisting> <para> - But omitting the space after <literal>abs</literal> results in a - syntax error because the statement then appears to invoke the - <literal>ABS()</literal> function: + Names of built-in functions are permitted as identifiers but may + require care to be used as such. For example, + <literal>COUNT</literal> is acceptable as a column name. However, + by default, no whitespace is allowed in function invocations + between the function name and the following + ‘<literal>(</literal>’ character. This requirement + enables the parser to distinguish whether the name is used in a + function call or in non-function context. For further detail on + recognition of function names, see + <xref linkend="function-resolution"/>. </para> -<programlisting> -mysql> <userinput>CREATE TABLE abs(val INT);</userinput> -ERROR 1064 (42000) at line 2: You have an error in your SQL -syntax ... near 'abs(val INT)' -</programlisting> - <para> - If the <literal>IGNORE_SPACE</literal> SQL mode is enabled, the - server allows function invocations to have whitespace between a - function name and the following ‘<literal>(</literal>’ - character. This causes function names to be treated as reserved - words. As a result, identifiers that are the same as function - names must be quoted as described in - <xref linkend="identifiers"/>. The server SQL mode is controlled - as described in <xref linkend="server-sql-mode"/>. - </para> - - <para> The words in the following table are explicitly reserved in MySQL ¤t-series;. At some point, you might upgrade to a higher version, so it's a good idea to have a look at future reserved Modified: trunk/refman-5.1/database-administration.xml =================================================================== --- trunk/refman-5.1/database-administration.xml 2007-01-09 19:17:52 UTC (rev 4414) +++ trunk/refman-5.1/database-administration.xml 2007-01-09 19:25:04 UTC (rev 4415) Changed blocks: 2, Lines Added: 14, Lines Deleted: 9; 2412 bytes @@ -10569,17 +10569,17 @@ <para> Allow spaces between a function name and the ‘<literal>(</literal>’ character. This causes - function names to be treated as reserved words. As a - result, identifiers that are the same as function names - must be quoted as described in + built-in function names to be treated as reserved words. + As a result, identifiers that are the same as function + names must be quoted as described in <xref linkend="identifiers"/>. For example, because there - is a <literal>ABS()</literal> function, the use of - <literal>abs</literal> as a table name in the following + is a <literal>COUNT()</literal> function, the use of + <literal>count</literal> as a table name in the following statement causes an error: </para> <programlisting> -mysql> <userinput>CREATE TABLE abs (i INT);</userinput> +mysql> <userinput>CREATE TABLE count (i INT);</userinput> ERROR 1064 (42000): You have an error in your SQL syntax </programlisting> @@ -10588,17 +10588,22 @@ </para> <programlisting> -mysql> <userinput>CREATE TABLE `abs` (i INT);</userinput> +mysql> <userinput>CREATE TABLE `count` (i INT);</userinput> Query OK, 0 rows affected (0.00 sec) </programlisting> <para> The <literal>IGNORE_SPACE</literal> SQL mode applies to built-in functions, not to user-defined functions or - stored routines. It is always allowable to have spaces - after a UDF or routine name, regardless of whether + stored functions. It is always allowable to have spaces + after a UDF or stored function name, regardless of whether <literal>IGNORE_SPACE</literal> is enabled. </para> + + <para> + For further discussion of <literal>IGNORE_SPACE</literal>, + see <xref linkend="function-resolution"/>. + </para> </listitem> <listitem> Modified: trunk/refman-5.1/installing.xml =================================================================== --- trunk/refman-5.1/installing.xml 2007-01-09 19:17:52 UTC (rev 4414) +++ trunk/refman-5.1/installing.xml 2007-01-09 19:25:04 UTC (rev 4415) Changed blocks: 1, Lines Added: 66, Lines Deleted: 0; 2968 bytes @@ -12102,6 +12102,72 @@ <listitem> <para> + <emphasis role="bold">Incompatible change</emphasis>: The + number of function names affected by + <literal>IGNORE_SPACE</literal> was reduced significantly in + MySQL 5.1.13, from about 200 to about 30. (For details about + <literal>IGNORE_SPACE</literal>, see + <xref linkend="function-resolution"/>.) This change improves + the consistency of parser operation. However, it also + introduces the possibility of incompatibility for old SQL + code that relies on the following conditions: + </para> + + <itemizedlist> + + <listitem> + <para> + <literal>IGNORE_SPACE</literal> is disabled. + </para> + </listitem> + + <listitem> + <para> + 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 + (for example, <literal>PI()</literal> versus <literal>PI + ()</literal>). + </para> + </listitem> + + </itemizedlist> + + <para> + For functions that are no longer affected by + <literal>IGNORE_SPACE</literal> 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: + </para> + + <itemizedlist> + + <listitem> + <para> + 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 + <literal><replaceable>schema_name</replaceable>.PI()</literal> + or <literal><replaceable>schema_name</replaceable>.PI + ()</literal>. + </para> + </listitem> + + <listitem> + <para> + Alternatively, rename the stored function to use a + non-conflicting name and change invocations of the + function to use the new name. + </para> + </listitem> + + </itemizedlist> + </listitem> + + <listitem> + <para> <emphasis role="bold">Incompatible change</emphasis>: For <literal>utf8</literal> columns, the full-text parser incorrectly considered several non-word punctuation and Modified: trunk/refman-5.1/language-structure.xml =================================================================== --- trunk/refman-5.1/language-structure.xml 2007-01-09 19:17:52 UTC (rev 4414) +++ trunk/refman-5.1/language-structure.xml 2007-01-09 19:25:04 UTC (rev 4415) Changed blocks: 5, Lines Added: 457, Lines Deleted: 63; 21422 bytes @@ -1528,19 +1528,423 @@ <section id="function-resolution"> - <title>Function Name Resolution</title> + <title>Function Name Parsing and Resolution</title> + <indexterm> + <primary>function names</primary> + <secondary>parsing</secondary> + </indexterm> + + <indexterm> + <primary>function names</primary> + <secondary>resolving ambiguity</secondary> + </indexterm> + <para> MySQL ¤t-series; supports built-in (native) functions, - user-defined functions (UDFs), and stored functions. The - following rules describe how the server resolves references to - function names for function creation and invocation: + 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. </para> + <para> + <emphasis role="bold">Built-In Function Name Parsing</emphasis> + </para> + + <para> + The parser uses default rules for parsing names of built-in + functions. These rules can be changed by enabling the + <literal>IGNORE_SPACE</literal> SQL mode. + </para> + + <para> + 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 non-expression reference to an + identifier such as a table or column name. For example, in the + following statements, the first reference to + <literal>count</literal> is a function call, whereas the second + reference is a table name: + </para> + +<programlisting> +SELECT COUNT(*) FROM mytable; +CREATE TABLE count (i INT); +</programlisting> + + <para> + 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 non-expression context, function + names are permitted as identifiers. + </para> + + <para> + 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 non-expression + context: + </para> + <itemizedlist> <listitem> <para> + To use the name as a function call in an expression, there + must be no whitespace between the name and the following + ‘<literal>(</literal>’ parenthesis character. + </para> + </listitem> + + <listitem> + <para> + Conversely, to use the function name as an identifier, it + must not be followed immediately by a parenthesis. + </para> + </listitem> + + </itemizedlist> + + <para> + 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. + <literal>COUNT</literal> is one such name. The exact list of + function names for which following whitespace determines their + interpretation are those listed in the + <literal>sql_functions[]</literal> array of the + <filename>sql/lex.h</filename> source file. Before MySQL 5.1, + they 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, parser improvements reduce to + about 30 the number of affected function names. + </para> + + <para> + For functions not listed in the 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. <literal>ASCII</literal> is one such name. + </para> + + <para> + The <literal>IGNORE_SPACE</literal> SQL mode can be used to + modify how the parser treats function names that are + whitespace-sensitive: + </para> + + <itemizedlist> + + <listitem> + <para> + With <literal>IGNORE_SPACE</literal> 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 + non-expression context: + </para> + +<programlisting> +mysql> <userinput>CREATE TABLE count(i INT);</userinput> +ERROR 1064 (42000): You have an error in your SQL syntax ... +near 'count(i INT)' +</programlisting> + + <para> + 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): + </para> + +<programlisting> +CREATE TABLE count (i INT); +CREATE TABLE `count`(i INT); +CREATE TABLE `count` (i INT); +</programlisting> + + <para> + For non-affected function names (those not listed in + <literal>sql_functions[]</literal>), interpretation may vary + when there is whitespace following the function name in + expression context: + <literal><replaceable>func_name</replaceable> ()</literal> + is interpreted as a built-in function if there is one; if + not, <literal><replaceable>func_name</replaceable> + ()</literal> is interpreted as a user-defined function or + stored function if one exists with that name. + </para> + </listitem> + + <listitem> + <para> + With <literal>IGNORE_SPACE</literal> 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: + </para> + +<programlisting> +SELECT COUNT(*) FROM mytable; +SELECT COUNT (*) FROM mytable; +</programlisting> + + <para> + However, enabling <literal>IGNORE_SPACE</literal> also has + the side effect that the parser treats the affected function + names as reserved words (see + <xref linkend="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 + non-expression context unless it is quoted. For example, + with <literal>IGNORE_SPACE</literal> enabled, both of the + following statements fail with a syntax error because the + parser interprets <literal>count</literal> as a reserved + word: + </para> + +<programlisting> +CREATE TABLE count(i INT); +CREATE TABLE count (i INT); +</programlisting> + + <para> + To use the function name in non-expression context, write it + as a quoted identifier: + </para> + +<programlisting> +CREATE TABLE `count`(i INT); +CREATE TABLE `count` (i INT); +</programlisting> + </listitem> + + </itemizedlist> + + <para> + To enable the <literal>IGNORE_SPACE</literal> SQL mode, use this + statement: + </para> + +<programlisting> +SET sql_mode = 'IGNORE_SPACE'; +</programlisting> + + <para> + <literal>IGNORE_SPACE</literal> is also enabled by certain other + composite modes such as <literal>ANSI</literal> that include it + in their value: + </para> + +<programlisting> +SET sql_mode = 'ANSI'; +</programlisting> + + <para> + Check <xref linkend="server-sql-mode"/>, to see which composite + modes enable <literal>IGNORE_SPACE</literal>. + </para> + + <para> + To minimize the dependency of SQL code on the + <literal>IGNORE_SPACE</literal> setting, use these guidelines: + </para> + + <itemizedlist> + + <listitem> + <para> + Avoid creating UDFs or stored functions that have the same + name as a built-in function. + </para> + </listitem> + + <listitem> + <para> + Avoid using function names in non-expression context. For + example, these statements use <literal>count</literal> (one + of the affected function names affected by + <literal>IGNORE_SPACE</literal>), so they fail with or + without whitespace following the name if + <literal>IGNORE_SPACE</literal> is enabled: + </para> + +<programlisting> +CREATE TABLE count(i INT); +CREATE TABLE count (i INT); +</programlisting> + + <para> + If you must use a function name in non-expression context, + write it as a quoted identifier: + </para> + +<programlisting> +CREATE TABLE `count`(i INT); +CREATE TABLE `count` (i INT); +</programlisting> + </listitem> + + </itemizedlist> + + <para> + The number of function names affected by + <literal>IGNORE_SPACE</literal> 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 + <literal>IGNORE_SPACE</literal> setting: + </para> + + <informaltable> + <tgroup cols="3"> + <colspec colwidth="33*"/> + <colspec colwidth="33*"/> + <colspec colwidth="33*"/> + <tbody> + <row> + <entry><literal>ADDDATE</literal></entry> + <entry><literal>BIT_AND</literal></entry> + <entry><literal>BIT_OR</literal></entry> + </row> + <row> + <entry><literal>BIT_XOR</literal></entry> + <entry><literal>CAST</literal></entry> + <entry><literal>COUNT</literal></entry> + </row> + <row> + <entry><literal>CURDATE</literal></entry> + <entry><literal>CURTIME</literal></entry> + <entry><literal>DATE_ADD</literal></entry> + </row> + <row> + <entry><literal>DATE_SUB</literal></entry> + <entry><literal>EXTRACT</literal></entry> + <entry><literal>GROUP_CONCAT</literal></entry> + </row> + <row> + <entry><literal>MAX</literal></entry> + <entry><literal>MID</literal></entry> + <entry><literal>MIN</literal></entry> + </row> + <row> + <entry><literal>NOW</literal></entry> + <entry><literal>POSITION</literal></entry> + <entry><literal>SESSION_USER</literal></entry> + </row> + <row> + <entry><literal>STD</literal></entry> + <entry><literal>STDDEV</literal></entry> + <entry><literal>STDDEV_POP</literal></entry> + </row> + <row> + <entry><literal>STDDEV_SAMP</literal></entry> + <entry><literal>SUBDATE</literal></entry> + <entry><literal>SUBSTR</literal></entry> + </row> + <row> + <entry><literal>SUBSTRING</literal></entry> + <entry><literal>SUM</literal></entry> + <entry><literal>SYSDATE</literal></entry> + </row> + <row> + <entry><literal>SYSTEM_USER</literal></entry> + <entry><literal>TRIM</literal></entry> + <entry><literal>VARIANCE</literal></entry> + </row> + <row> + <entry><literal>VAR_POP</literal></entry> + <entry><literal>VAR_SAMP</literal></entry> + <entry/> + </row> + </tbody> + </tgroup> + </informaltable> + + <para> + For earlier versions of MySQL, check the contents of the + <literal>sql_functions[]</literal> array in the + <filename>sql/lex.h</filename> source file to see which + functions are affected by <literal>IGNORE_SPACE</literal>. + </para> + + <para> + <emphasis role="bold">Incompatibility warning</emphasis>: The + change in MySQL 5.1.13 that reduces the number of function names + affected by <literal>IGNORE_SPACE</literal> improves the + consistency of parser operation. However, it also introduces the + possibility of incompatibility for old SQL code that relies on + the following conditions: + </para> + + <itemizedlist> + + <listitem> + <para> + <literal>IGNORE_SPACE</literal> is disabled. + </para> + </listitem> + + <listitem> + <para> + 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 (for example, + <literal>PI()</literal> versus <literal>PI ()</literal>). + </para> + </listitem> + + </itemizedlist> + + <para> + For functions that are no longer affected by + <literal>IGNORE_SPACE</literal> 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: + </para> + + <itemizedlist> + + <listitem> + <para> + 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 + <literal><replaceable>schema_name</replaceable>.PI()</literal> + or <literal><replaceable>schema_name</replaceable>.PI + ()</literal>. + </para> + </listitem> + + <listitem> + <para> + Alternatively, rename the stored function to use a + non-conflicting name and change invocations of the function + to use the new name. + </para> + </listitem> + + </itemizedlist> + + <para> + <emphasis role="bold">Function Name Resolution</emphasis> + </para> + + <para> + The following rules describe how the server resolves references + to function names for function creation and invocation: + </para> + + <itemizedlist> + + <listitem> + <para> Built-in functions and user-defined functions </para> @@ -1593,21 +1997,37 @@ <para> 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 <literal>DROP - FUNCTION</literal> to drop the UDF, and then use <literal>CREATE - FUNCTION</literal> to re-create the UDF with a different - non-conflicting 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 non-conflicting name, or change calls to the function so - that they use a schema qualifier (that is, use - <literal><replaceable>schema_name</replaceable>.<replaceable>func_name</replaceable>()</literal> - syntax). + functions: </para> + <itemizedlist> + + <listitem> + <para> + 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 <literal>DROP + FUNCTION</literal> to drop the UDF, and then use + <literal>CREATE FUNCTION</literal> to re-create the UDF with + a different non-conflicting name. + </para> + </listitem> + + <listitem> + <para> + 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 + non-conflicting name, or change calls to the function so + that they use a schema qualifier (that is, use + <literal><replaceable>schema_name</replaceable>.<replaceable>func_name</replaceable>()</literal> + syntax). + </para> + </listitem> + + </itemizedlist> + </section> </section> @@ -1625,11 +2045,11 @@ </indexterm> <para> - A common problem stems from trying to use an identifier such as a - table or column name that is a reserved word such as - <literal>SELECT</literal> or the name of a built-in MySQL data - type or function such as <literal>TIMESTAMP</literal> or - <literal>GROUP</literal>. + Certain words such as <literal>SELECT</literal>, + <literal>DELETE</literal>, or <literal>BIGINT</literal> are + reserved and require special treatment for use as identifiers such + as table and column names. This may also be true for the names of + built-in functions. </para> <para> @@ -1639,9 +2059,8 @@ <programlisting> mysql> <userinput>CREATE TABLE interval (begin INT, end INT);</userinput> -ERROR 1064 (42000): You have an error in your SQL syntax; check -the manual that corresponds to your MySQL server version for -the right syntax to use near 'interval (begin INT, end INT)' +ERROR 1064 (42000): You have an error in your SQL syntax ... +near 'interval (begin INT, end INT)' mysql> <userinput>CREATE TABLE `interval` (begin INT, end INT);</userinput> Query OK, 0 rows affected (0.01 sec) @@ -1649,53 +2068,28 @@ <para> Exception: A word that follows a period in a qualified name must - be an identifier, so it need not be quoted even if it is reserved. + be an identifier, so it need not be quoted even if it is reserved: </para> - <para> - Function names are permitted as identifiers. For example, - <literal>ABS</literal> is acceptable as a column name. However, by - default, no whitespace is allowed in function invocations between - the function name and the following - ‘<literal>(</literal>’ character. This requirement - enables the parser to distinguish whether the name is used in a - function call or in non-function context. - </para> - - <para> - A side effect of this requirement is that omitting a space in some - contexts causes the parser to interpret an identifier as a - function name. For example, this statement is legal: - </para> - <programlisting> -mysql> <userinput>CREATE TABLE abs (val INT);</userinput> +mysql> <userinput>CREATE TABLE mydb.interval (begin INT, end INT);</userinput> +Query OK, 0 rows affected (0.01 sec) </programlisting> <para> - But omitting the space after <literal>abs</literal> results in a - syntax error because the statement then appears to invoke the - <literal>ABS()</literal> function: + Names of built-in functions are permitted as identifiers but may + require care to be used as such. For example, + <literal>COUNT</literal> is acceptable as a column name. However, + by default, no whitespace is allowed in function invocations + between the function name and the following + ‘<literal>(</literal>’ character. This requirement + enables the parser to distinguish whether the name is used in a + function call or in non-function context. For further detail on + recognition of function names, see + <xref linkend="function-resolution"/>. </para> -<programlisting> -mysql> <userinput>CREATE TABLE abs(val INT);</userinput> -ERROR 1064 (42000) at line 2: You have an error in your SQL -syntax ... near 'abs(val INT)' -</programlisting> - <para> - If the <literal>IGNORE_SPACE</literal> SQL mode is enabled, the - server allows function invocations to have whitespace between a - function name and the following ‘<literal>(</literal>’ - character. This causes function names to be treated as reserved - words. As a result, identifiers that are the same as function - names must be quoted as described in - <xref linkend="identifiers"/>. The server SQL mode is controlled - as described in <xref linkend="server-sql-mode"/>. - </para> - - <para> The words in the following table are explicitly reserved in MySQL ¤t-series;. At some point, you might upgrade to a higher version, so it's a good idea to have a look at future reserved Modified: trunk/refman-5.1/news-5.1.xml =================================================================== --- trunk/refman-5.1/news-5.1.xml 2007-01-09 19:17:52 UTC (rev 4414) +++ trunk/refman-5.1/news-5.1.xml 2007-01-09 19:25:04 UTC (rev 4415) Changed blocks: 4, Lines Added: 83, Lines Deleted: 12; 4899 bytes @@ -55,9 +55,10 @@ installing any new version of software. Although MySQL has worked very hard to ensure a high level of quality, protect your data by making a backup as you would for any software beta release. Please - refer to our bug database at <ulink - url="http://bugs.mysql.com/"/> - for more details about the individual bugs fixed in this version. + refer to our bug database at + <ulink + url="http://bugs.mysql.com/"/> for more details + about the individual bugs fixed in this version. </para> <para role="release-special-announcement"> @@ -1582,6 +1583,75 @@ <listitem> <para> + <emphasis role="bold">Incompatible change</emphasis>: The + number of function names affected by + <literal>IGNORE_SPACE</literal> was reduced significantly in + MySQL 5.1.13, from about 200 to about 30. (For details about + <literal>IGNORE_SPACE</literal>, see + <xref linkend="function-resolution"/>.) This change improves + the consistency of parser operation. However, it also + introduces the possibility of incompatibility for old SQL code + that relies on the following conditions: + </para> + + <itemizedlist> + + <listitem> + <para> + <literal>IGNORE_SPACE</literal> is disabled. + </para> + </listitem> + + <listitem> + <para> + 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 (for example, + <literal>PI()</literal> versus <literal>PI ()</literal>). + </para> + </listitem> + + </itemizedlist> + + <para> + For functions that are no longer affected by + <literal>IGNORE_SPACE</literal> 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: + </para> + + <itemizedlist> + + <listitem> + <para> + 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 + <literal><replaceable>schema_name</replaceable>.PI()</literal> + or <literal><replaceable>schema_name</replaceable>.PI + ()</literal>. + </para> + </listitem> + + <listitem> + <para> + Alternatively, rename the stored function to use a + non-conflicting name and change invocations of the + function to use the new name. + </para> + </listitem> + + </itemizedlist> + + <para> + (Bug #21114) + </para> + </listitem> + + <listitem> + <para> If the user specified the server options <option>--max-connections=<replaceable>N</replaceable></option> or @@ -2447,7 +2517,7 @@ </para> <para> - See + See <xref linkend="mysql-cluster-upgrade-downgrade-compatibility"/>, and related sections of the Manual before upgrading a MySQL Cluster to version 5.1.12 or later. @@ -12146,14 +12216,15 @@ <literal>SUPER</literal> privilege has changed to a requirement for the <literal>TRIGGER</literal> privilege. After upgrading, be sure to update your grant tables as - described in <xref - linkend="mysql-fix-privilege-tables"/>. - This process assigns the <literal>TRIGGER</literal> privilege - to all accounts that had the <literal>SUPER</literal> - privilege. (After updating, you might also consider whether - any of those accounts no longer need - <literal>SUPER</literal>.) If you fail to update the grant - tables, triggers may fail when activated. (Bug #9142) + described in + <xref + linkend="mysql-fix-privilege-tables"/>. This + process assigns the <literal>TRIGGER</literal> privilege to + all accounts that had the <literal>SUPER</literal> privilege. + (After updating, you might also consider whether any of those + accounts no longer need <literal>SUPER</literal>.) If you fail + to update the grant tables, triggers may fail when activated. + (Bug #9142) </para> </listitem>
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r4415 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 9 Jan |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 1.551 sec. using MySQL 5.1.14-beta-log