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:
None 
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
Description:
Foreign key creation fails when referring to table with name format.

How to repeat:
=============================================
= Fails
=============================================

create table format (
  code             varchar(10)  NOT null
, label            varchar(80)  NOT null
--
, constraint format_pk  PRIMARY KEY (code)
, constraint format_uk  UNIQUE KEY (label)
)
engine innodb;

create table format_options (
  format_code     varchar(10) NOT null
, code            varchar(10) NOT null   
, label           varchar(80)  
--
, constraint format_options_pk        PRIMARY KEY (format_code, code)
, constraint format_options_uk        UNIQUE KEY  (format_code, label)
, constraint format_options_format_fk FOREIGN KEY (format_code) references format(code) 
)
engine innodb;

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 'format(code)
)
engine innodb' at line 7

=============================================
= Works (different name is used)
=============================================

create table formatx (
  code             varchar(10)  NOT null
, label            varchar(80)  NOT null
--
, constraint formatx_pk  PRIMARY KEY (code)
, constraint formatx_uk  UNIQUE KEY (label)
)
engine innodb;

create table formatx_options (
  format_code     varchar(10) NOT null
, code            varchar(10) NOT null   
, label           varchar(80)  
--
, constraint formatx_options_pk        PRIMARY KEY (format_code, code)
, constraint formatx_options_uk        UNIQUE KEY  (format_code, label)
, constraint formatx_options_format_fk FOREIGN KEY (format_code) references formatx(code) 
)
engine innodb;

Suggested fix:
At first it looked like a reserved word problem, 'format' is not in this list.
Creating a table with name format succeeds so it's not a reserved word.
[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.