| Bug #21114 | Foreign key creation fails when referring to table named 'format' | ||
|---|---|---|---|
| Submitted: | 18 Jul 2006 13:02 | Modified: | 9 Jan 2007 19:21 |
| Reporter: | Andre Timmer | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S3 (Non-critical) |
| Version: | 5.0.18 / 5.0.22/4.1BK/5.0BK/5.1BK | OS: | Linux (Suse Linux 10) |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
[18 Jul 2006 13:02]
Andre Timmer
[18 Jul 2006 13:10]
Andre Timmer
When prefixing references table with '<schema>.' table creation does succeed. Example: - constraint format_options_format_fk FOREIGN KEY (format_code) references sbrquery.format(code)
[18 Jul 2006 15:56]
MySQL Verification Team
Thank you for the bug report. Verified as described.
[20 Jul 2006 13:15]
Andre Timmer
Refering to a table with name 'field' gives the same result.
[21 Jul 2006 7:06]
Heikki Tuuri
Hi! This is a shortcoming in the MySQL parser, not in InnoDB. Probably format() is a function name in the MySQL SQL syntax, and that is why the parser gets confused. Regards, Heikki
[23 Aug 2006 23:37]
Marc ALFF
Documenting the current findings.
As Heikki Tuuri noted, this is indeed an issue caused by the parser.
'format' is a function name, and the syntax for functions is sensitive to spaces
placed between the function name and a following '('
The CREATE TABLE statement succeeded for a table named format, because a space
was added before the '('. The same statement would have failed with no space.
As for the foreign key part in the second create table,
it failed because 'format' was followed by '(' with no space in between.
With a space, the statement is parsed properly.
Please note:
This behavior found is a bug, and the bug is still opened.
This comment just offers a possible work around less intrusive than
renaming the table to formatx.
Another possibility is to use the `format` syntax, to avoid collisions
with any keywords.
[28 Sep 2006 1:16]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/12672 ChangeSet@1.2307, 2006-09-27 19:16:24-07:00, malff@weblab.(none) +18 -0 Bug#21114 (Foreign key creation fails to table with name format) Before the change, A) MySQL native functions Depending on the exact name of the function, 1 or more of the following could happen for a given name: - "foo" "(" was always interpretted by the parser as a function call - "foo" <space> "(", depending on the sql_mode (IGNORE_SPACE), was: - sometime a native function call, - sometime a UDF, - sometime a stored function in the current <use> database. For example, see connection_id() - "foo" "(" or "foo" <space> "(", regardless of the sql_mode, was interpretted as a function call. For example, see database() B) Pollution of the DDL (Data Definition Language) Each identifier name recognized at the lexical level (sql_lex.h) would be returned to the parser as a function (FUNC_ARG0, ...), regardless of the parsing context. This in particular caused incompatibilities when a valid identifier followed by a '(' would collide with a MySQL *non* reserved function name, in a context where no functions calls are expected. In particular, CREATE TABLE foo ( ... REFERENCES FOREIGN KEY foo (... would not parse for valid table names. C) Pollution of the grammar Some functions, using *non* reserved names, were implemented as a token in the grammar. Doing so in effect makes the name reserved, and can break user code. See for example benchmark() D) Risk of regressions and incompatibilities Each time a new function is implemented for a feature, a risk exist that implementing the function following the existing design pattern will introduce new incompatibilities, and break otherwise valid user code. After the change, A) MySQL native functions - spaces are not significant, so that "foo" "(" and "foo" <space> "(" always refer to the same object, regardless of the current sql_mode. B) Non pollution of the DDL (Data Definition Language) - an idenfier followed by '(' is a function call *only* if it's found when parsing what is expected to be an expression. C) Pollution of the grammar - some tokens have simply been removed, preventing any pollution. D) Risk of regressions and incompatibilities This risk has been reduced. The current design enforces that new function names are introduced in item_create.cc, which does not affect the overall grammar or the lexical parsing. The change consist of 1) removing any CREATE_FUNC builder from sql/lex.h sql_functions[] 2) documenting reserved / non reserved keywords in sql/sql_yacc.yy 3) reorganizing the grammar to better classify and document rules 4) change the CREATE_FUNC factoy/builder pattern from a C-style function pointer call back to a C++ virtual function call. This is critical since the pattern has been generalized to take into account functions with a variable number or arguments, which in turn is critical to avoid even naming a function like EXPORT_SET in the bison parser (see point C). Incompatible change Previous user code that would: - run only in IGNORE_SPACE=false sql_mode - define stored functions with a name that collide with a MySQL builtin function - call the stored function using "foo" <space> "(" will now parse differently, interpreting the call as a call to a native function. Note that this historical behavior is not standard (see below), and that the same user code would execute differently or fail with sql_mode=ANSI. Last note The SQL standard defines a "PATH" syntax to help clarify how "foo" "(" should be interpreted, and in particular define the order of databases to search for a function named <db>.foo(). See WL#2128 for details. This patch, by removing name resolution from the lex ad yacc parser (a single grammar rule, function_call_generic, is used in the grammar, so name resolution is technically separated from the grammar now) makes the implementation of WL#2128 possible.
[14 Oct 2006 1:46]
Marc ALFF
Got some comments from konstantin (verbal while in Denver, email), changing back to "in progress" to fix them.
[17 Oct 2006 20:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/13829 ChangeSet@1.2307, 2006-10-17 14:54:44-07:00, malff@weblab.(none) +20 -0 Bug#21114 (Foreign key creation fails to table with name format) Before the change, A) MySQL native functions Depending on the exact name of the function, 1 or more of the following could happen for a given name: - "foo" "(" was always interpreted by the parser as a function call - "foo" <space> "(", depending on the sql_mode (IGNORE_SPACE), was: - sometime a native function call, - sometime a UDF, - sometime a stored function in the current <use> database. For example, see connection_id() - "foo" "(" or "foo" <space> "(", regardless of the sql_mode, was interpreted as a function call. For example, see database() B) Pollution of the DDL (Data Definition Language) Each identifier name recognized at the lexical level (sql_lex.h) would be returned to the parser as a function (FUNC_ARG0, ...), regardless of the parsing context. This in particular caused incompatibilities when a valid identifier followed by a '(' would collide with a MySQL *non* reserved function name, in a context where no functions calls are expected. In particular, CREATE TABLE foo ( ... REFERENCES FOREIGN KEY foo (... would not parse for valid table names. C) Pollution of the grammar Some functions, using *non* reserved names, were implemented as a token in the grammar. Doing so in effect makes the name reserved, and can break user code. See for example benchmark() D) Risk of regressions and incompatibilities Each time a new function is implemented for a feature, a risk exist that implementing the function following the existing design pattern will introduce new incompatibilities, and break otherwise valid user code. After the change, A) MySQL native functions - spaces are not significant, so that "foo" "(" and "foo" <space> "(" always refer to the same object, regardless of the current sql_mode. B) Non pollution of the DDL (Data Definition Language) - an identifier followed by '(' is a function call *only* if it's found when parsing what is expected to be an expression. C) Pollution of the grammar - some tokens have simply been removed, preventing any pollution. D) Risk of regressions and incompatibilities This risk has been reduced. The current design enforces that new function names are introduced in item_create.cc, which does not affect the overall grammar or the lexical parsing. The change consist of 1) removing any CREATE_FUNC builder from sql/lex.h sql_functions[] 2) documenting reserved / non reserved keywords in sql/sql_yacc.yy 3) reorganizing the grammar to better classify and document rules 4) change the CREATE_FUNC factory/builder pattern from a C-style function pointer call back to a C++ virtual function call. This is critical since the pattern has been generalized to take into account functions with a variable number or arguments, which in turn is critical to avoid even naming a function like EXPORT_SET in the bison parser (see point C). The builder pattern works with the following class structure: Create_func - Create_func_arg0 - Create_func_connection_id, ... - Create_func_arg1 - Create_func_cos, ... - Create_func_arg2 - Create_func_addtime, ... - Create_func_arg3 - Create_func_maketime, ... - Create_func_export_set, ... Impact on the bison parser The complexity of the generated code has decreased significantly. The net change (in BEFORE --> AFTER (DELTA) format) is: /* YYLAST -- Last index in YYTABLE. */ 47613 --> 42003 (-5610) /* YYNTOKENS -- Number of terminals. */ 612 --> 570 (-42) /* YYNNTS -- Number of nonterminals. */ 814 --> 818 (+4) /* YYNRULES -- Number of rules. */ 2344 --> 2284 (-60) /* YYNRULES -- Number of states. */ 4183 --> 3918 (-265) Incompatible change Previous user code that would: - run only in IGNORE_SPACE=false sql_mode - define stored functions with a name that collide with a MySQL built in function - call the stored function using "foo" <space> "(" will now parse differently, interpreting the call as a call to a native function. Note that this historical behavior is not standard (see below), and that the same user code would execute differently or fail with sql_mode=ANSI. Last note The SQL standard defines a "PATH" syntax to help clarify how "foo" "(" should be interpreted, and in particular define the order of databases to search for a function named <db>.foo(). See WL 2128 for details. This patch, by removing name resolution from the lex ad yacc parser (a single grammar rule, function_call_generic, is used in the grammar, so name resolution is technically separated from the grammar now) makes the implementation of WL 2128 possible.
[18 Oct 2006 15:55]
Marc ALFF
See WL#3565
[25 Oct 2006 12:11]
Konstantin Osipov
Sent out a review by email.
[27 Oct 2006 20:46]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/14504 ChangeSet@1.2328, 2006-10-27 14:44:57-07:00, malff@weblab.(none) +19 -0 Bug#21114 (Foreign key creation fails to table with name format) Due to the complexity of this change, everything is documented in WL#3565 This patch is the second iteration, it takes into account the comments received to date.
[1 Nov 2006 21:20]
Konstantin Osipov
Approved over email after an IRC and phone discussion.
[2 Nov 2006 18:02]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/14757 ChangeSet@1.2328, 2006-11-02 11:01:53-07:00, malff@weblab.(none) +19 -0 Bug#21114 (Foreign key creation fails to table with name format) Due to the complexity of this change, everything is documented in WL#3565 This patch is the third iteration, it takes into account the comments received to date.
[2 Nov 2006 21:05]
Marc ALFF
Pushed to mysql-5.1-runtime
[3 Nov 2006 17:31]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/14818 ChangeSet@1.2338, 2006-11-03 09:56:44-07:00, malff@weblab.(none) +2 -0 Bug#21114 (Foreign key creation fails to table with name format) Fixed the tests for case insensitive platforms
[9 Nov 2006 21:23]
Konstantin Osipov
Merged into 5.1.13 tree. See also WL#3565 for documentation information.
[9 Jan 2007 19:21]
Paul DuBois
Noted in 5.1.13 changelog. Incompatible change: The number of function names affected by IGNORE_SPACE was reduced significantly in MySQL 5.1.13, from about 200 to about 30. This change improves the consistency of parser operation. Also updated http://dev.mysql.com/doc/refman/5.1/en/function-resolution.html with a more detailed description of IGNORE_SPACE and how it affects parsing of function names.
