MySQL Bugs: #34455: Ambiguous foreign keys syntax is accepted

Bug #34455	Ambiguous foreign keys syntax is accepted
Submitted:	11 Feb 2008 12:51	Modified:	20 Mar 2008 19:48
Reporter:	Dmitri Lenev
Status:	Closed
Category:	Server: Parser	Severity:	S3 (Non-critical)
Version:	4.1, 5.0, 5.1	OS:	Any
Assigned to:	Marc Alff	Target Version:
Triage:	D2 (Serious) / R3 (Medium) / E2 (Low)

Description:
For non-InnoDB tables MySQL accepts CREATE TABLE statements with FOREIGN KEY clauses
which has non-standard and ambiguous syntax. These clauses, for example, can contain
duplicate ON UPDATE or ON DELETE subclauses with different referential actions. Also
non-standard order between MATCH and referential actions clauses is accepted. This syntax
is not documented as supported in manual.

Although currently this does not cause much problems since we ignore FK definitions for
non-InnoDB tables anyway, this might cause problems in future, especially when we will
implement FK support for many engines.

How to repeat:
create table parent (pk int primary key );
create table child (fk int, foreign key ttt (fk) references parent (pk) on delete cascade
on delete set null match simple);

Suggested fix:
Fix parser to accept only standard syntax.

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/42234

ChangeSet@1.2551, 2008-02-13 11:05:36-07:00, malff@lambda.hsd1.co.comcast.net. +4 -0
  Bug#34455 (Ambiguous foreign keys syntax is accepted)
  
  Before this fix, the parser would accept illegal syntaxes in a FOREIGN KEY
  clause.
  In particular, the following was accepted:
  - multiple MATCH clauses
  - multiple ON DELETE clauses
  - multiple ON UPDATE clauses
  - MATCH clauses specified after ON UPDATE or ON DELETE
  In case of multiple redundant clauses, this leads to confusion,
  and implementation dependent results (last one wins).
  
  With this fix, these illegal syntaxes are now properly rejected.

Pushed into 6.0.5-alpha

Noted in 6.0.5 changelog.