bk commit into 6.0 tree (malff:1.2551) BUG#34455

[marc.alff]

Below is the list of changes that have just been committed into a local
6.0 repository of malff.  When malff does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-02-13 11:05:36-07:00, malff@stripped. +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.

  mysql-test/r/foreign_key.result@stripped, 2008-02-13 11:05:31-07:00,
malff@stripped. +42 -0
    Bug#34455 (Ambiguous foreign keys syntax is accepted)

  mysql-test/t/foreign_key.test@stripped, 2008-02-13 11:05:31-07:00,
malff@stripped. +72 -0
    Bug#34455 (Ambiguous foreign keys syntax is accepted)

  sql/sql_lex.h@stripped, 2008-02-13 11:05:32-07:00, malff@stripped. +3 -1
    Bug#34455 (Ambiguous foreign keys syntax is accepted)

  sql/sql_yacc.yy@stripped, 2008-02-13 11:05:32-07:00, malff@stripped. +68
-31
    Bug#34455 (Ambiguous foreign keys syntax is accepted)

diff -Nrup a/mysql-test/r/foreign_key.result b/mysql-test/r/foreign_key.result
--- a/mysql-test/r/foreign_key.result	2004-03-20 03:31:12 -07:00
+++ b/mysql-test/r/foreign_key.result	2008-02-13 11:05:31 -07:00
@@ -13,3 +13,45 @@ foreign key (a,b) references t3 (c,d) on
 create index a on t1 (a);
 create unique index b on t1 (a,b);
 drop table t1;
+drop table if exists t_34455;
+create table t_34455 (
+a int not null,
+foreign key (a) references t3 (a) match full match partial);
+ERROR 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 'match partial)' at line 3
+create table t_34455 (
+a int not null,
+foreign key (a) references t3 (a) on delete set default match full);
+ERROR 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 'match full)' at line 3
+create table t_34455 (
+a int not null,
+foreign key (a) references t3 (a) on update set default match full);
+ERROR 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 'match full)' at line 3
+create table t_34455 (
+a int not null,
+foreign key (a) references t3 (a)
+on delete set default on delete set default);
+ERROR 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 'delete set default)' at line
4
+create table t_34455 (
+a int not null,
+foreign key (a) references t3 (a)
+on update set default on update set default);
+ERROR 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 'update set default)' at line
4
+create table t_34455 (a int not null);
+alter table t_34455
+add foreign key (a) references t3 (a) match full match partial);
+ERROR 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 'match partial)' at line 2
+alter table t_34455
+add foreign key (a) references t3 (a) on delete set default match full);
+ERROR 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 'match full)' at line 2
+alter table t_34455
+add foreign key (a) references t3 (a) on update set default match full);
+ERROR 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 'match full)' at line 2
+alter table t_34455
+add foreign key (a) references t3 (a)
+on delete set default on delete set default);
+ERROR 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 'delete set default)' at line
3
+alter table t_34455
+add foreign key (a) references t3 (a)
+on update set default on update set default);
+ERROR 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 'update set default)' at line
3
+drop table t_34455;
diff -Nrup a/mysql-test/t/foreign_key.test b/mysql-test/t/foreign_key.test
--- a/mysql-test/t/foreign_key.test	2005-07-27 18:21:41 -06:00
+++ b/mysql-test/t/foreign_key.test	2008-02-13 11:05:31 -07:00
@@ -23,3 +23,75 @@ create unique index b on t1 (a,b);
 drop table t1;
 
 # End of 4.1 tests
+
+#
+# Bug#34455 (Ambiguous foreign keys syntax is accepted)
+#
+
+--disable_warnings
+drop table if exists t_34455;
+--enable_warnings
+
+# 2 match clauses, illegal
+--error ER_PARSE_ERROR
+create table t_34455 (
+  a int not null,
+  foreign key (a) references t3 (a) match full match partial);
+
+# match after on delete, illegal
+--error ER_PARSE_ERROR
+create table t_34455 (
+  a int not null,
+  foreign key (a) references t3 (a) on delete set default match full);
+
+# match after on update, illegal
+--error ER_PARSE_ERROR
+create table t_34455 (
+  a int not null,
+  foreign key (a) references t3 (a) on update set default match full);
+
+# 2 on delete clauses, illegal
+--error ER_PARSE_ERROR
+create table t_34455 (
+  a int not null,
+  foreign key (a) references t3 (a)
+  on delete set default on delete set default);
+
+# 2 on update clauses, illegal
+--error ER_PARSE_ERROR
+create table t_34455 (
+  a int not null,
+  foreign key (a) references t3 (a)
+  on update set default on update set default);
+
+create table t_34455 (a int not null);
+
+# 2 match clauses, illegal
+--error ER_PARSE_ERROR
+alter table t_34455
+  add foreign key (a) references t3 (a) match full match partial);
+
+# match after on delete, illegal
+--error ER_PARSE_ERROR
+alter table t_34455
+  add foreign key (a) references t3 (a) on delete set default match full);
+
+# match after on update, illegal
+--error ER_PARSE_ERROR
+alter table t_34455
+  add foreign key (a) references t3 (a) on update set default match full);
+
+# 2 on delete clauses, illegal
+--error ER_PARSE_ERROR
+alter table t_34455
+  add foreign key (a) references t3 (a)
+  on delete set default on delete set default);
+
+# 2 on update clauses, illegal
+--error ER_PARSE_ERROR
+alter table t_34455
+  add foreign key (a) references t3 (a)
+  on update set default on update set default);
+
+drop table t_34455;
+
diff -Nrup a/sql/sql_lex.h b/sql/sql_lex.h
--- a/sql/sql_lex.h	2007-12-19 06:35:03 -07:00
+++ b/sql/sql_lex.h	2008-02-13 11:05:32 -07:00
@@ -1612,7 +1612,9 @@ typedef struct st_lex : public Query_tab
   enum ha_storage_media storage_type;
   enum column_format_type column_format;
   uint grant, grant_tot_col, which_columns;
-  uint fk_delete_opt, fk_update_opt, fk_match_option;
+  enum Foreign_key::fk_match_opt fk_match_option;
+  enum Foreign_key::fk_option fk_update_opt;
+  enum Foreign_key::fk_option fk_delete_opt;
   uint slave_thd_opt, start_transaction_opt;
   int nest_level;
   /*
diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy
--- a/sql/sql_yacc.yy	2008-01-25 13:27:12 -07:00
+++ b/sql/sql_yacc.yy	2008-02-13 11:05:32 -07:00
@@ -504,6 +504,7 @@ Item* handle_sql2003_note184_exception(T
   enum index_hint_type index_hint;
   enum enum_filetype filetype;
   enum ha_build_method build_method;
+  enum Foreign_key::fk_option m_fk_option;
 }
 
 %{
@@ -1153,7 +1154,7 @@ bool my_yyoverflow(short **a, YYSTYPE **
         type int_type real_type order_dir
         udf_type if_exists opt_local opt_table_options table_options
         table_option opt_if_not_exists opt_no_write_to_binlog
-        delete_option opt_temporary all_or_any opt_distinct
+        opt_temporary all_or_any opt_distinct
         opt_ignore_leaves fulltext_options spatial_type union_option
         start_transaction_opts opt_chain opt_release
         union_opt select_derived_init option_type2
@@ -1163,6 +1164,9 @@ bool my_yyoverflow(short **a, YYSTYPE **
         opt_transactional_lock_timeout
         /* opt_lock_timeout_value */
 
+%type <m_fk_option>
+        delete_option
+
 %type <ulong_num>
         ulong_num real_ulong_num merge_insert_types
         ws_nweights
@@ -1285,7 +1289,7 @@ bool my_yyoverflow(short **a, YYSTYPE **
         opt_precision opt_ignore opt_column opt_restrict
         grant revoke set lock unlock string_list field_options field_option
         field_opt_list opt_binary table_lock_list table_lock
-        ref_list opt_on_delete opt_on_delete_list opt_on_delete_item use
+        ref_list opt_match_clause opt_on_update_delete use
         opt_delete_options opt_delete_option varchar nchar nvarchar
         opt_outer table_list table_name table_alias_ref_list table_alias_ref
         opt_option opt_place
@@ -5199,52 +5203,85 @@ opt_primary:
         ;
 
 references:
-          REFERENCES table_ident
-          {
-            LEX *lex=Lex;
-            lex->fk_delete_opt= lex->fk_update_opt= lex->fk_match_option= 0;
-            lex->ref_list.empty();
-          }
+          REFERENCES
+          table_ident
           opt_ref_list
+          opt_match_clause
+          opt_on_update_delete
           {
             $$=$2;
           }
         ;
 
 opt_ref_list:
-          /* empty */ opt_on_delete {}
-        | '(' ref_list ')' opt_on_delete {}
+          /* empty */
+          { Lex->ref_list.empty(); }
+        | '(' ref_list ')'
         ;
 
 ref_list:
-          ref_list ',' ident { Lex->ref_list.push_back(new Key_part_spec($3.str)); }
-        | ident { Lex->ref_list.push_back(new Key_part_spec($1.str)); }
-        ;
-
-opt_on_delete:
-          /* empty */ {}
-        | opt_on_delete_list {}
+          ref_list ',' ident
+          { Lex->ref_list.push_back(new Key_part_spec($3.str)); }
+        | ident
+          {
+            LEX *lex= Lex;
+            lex->ref_list.empty();
+            lex->ref_list.push_back(new Key_part_spec($1.str));
+          }
         ;
 
-opt_on_delete_list:
-          opt_on_delete_list opt_on_delete_item {}
-        | opt_on_delete_item {}
+opt_match_clause:
+          /* empty */
+          { Lex->fk_match_option= Foreign_key::FK_MATCH_UNDEF; }
+        | MATCH FULL
+          { Lex->fk_match_option= Foreign_key::FK_MATCH_FULL; }
+        | MATCH PARTIAL
+          { Lex->fk_match_option= Foreign_key::FK_MATCH_PARTIAL; }
+        | MATCH SIMPLE_SYM
+          { Lex->fk_match_option= Foreign_key::FK_MATCH_SIMPLE; }
         ;
 
-opt_on_delete_item:
-          ON DELETE_SYM delete_option { Lex->fk_delete_opt= $3; }
-        | ON UPDATE_SYM delete_option { Lex->fk_update_opt= $3; }
-        | MATCH FULL       { Lex->fk_match_option= Foreign_key::FK_MATCH_FULL; }
-        | MATCH PARTIAL    { Lex->fk_match_option= Foreign_key::FK_MATCH_PARTIAL; }
-        | MATCH SIMPLE_SYM { Lex->fk_match_option= Foreign_key::FK_MATCH_SIMPLE; }
+opt_on_update_delete:
+          /* empty */
+          {
+            LEX *lex= Lex;
+            lex->fk_update_opt= Foreign_key::FK_OPTION_UNDEF;
+            lex->fk_delete_opt= Foreign_key::FK_OPTION_UNDEF;
+          }
+        | ON UPDATE_SYM delete_option
+          {
+            LEX *lex= Lex;
+            lex->fk_update_opt= $3;
+            lex->fk_delete_opt= Foreign_key::FK_OPTION_UNDEF;
+          }
+        | ON DELETE_SYM delete_option
+          {
+            LEX *lex= Lex;
+            lex->fk_update_opt= Foreign_key::FK_OPTION_UNDEF;
+            lex->fk_delete_opt= $3;
+          }
+        | ON UPDATE_SYM delete_option
+          ON DELETE_SYM delete_option
+          {
+            LEX *lex= Lex;
+            lex->fk_update_opt= $3;
+            lex->fk_delete_opt= $6;
+          }
+        | ON DELETE_SYM delete_option
+          ON UPDATE_SYM delete_option
+          {
+            LEX *lex= Lex;
+            lex->fk_update_opt= $6;
+            lex->fk_delete_opt= $3;
+          }
         ;
 
 delete_option:
-          RESTRICT      { $$= (int) Foreign_key::FK_OPTION_RESTRICT; }
-        | CASCADE       { $$= (int) Foreign_key::FK_OPTION_CASCADE; }
-        | SET NULL_SYM  { $$= (int) Foreign_key::FK_OPTION_SET_NULL; }
-        | NO_SYM ACTION { $$= (int) Foreign_key::FK_OPTION_NO_ACTION; }
-        | SET DEFAULT   { $$= (int) Foreign_key::FK_OPTION_DEFAULT;  }
+          RESTRICT      { $$= Foreign_key::FK_OPTION_RESTRICT; }
+        | CASCADE       { $$= Foreign_key::FK_OPTION_CASCADE; }
+        | SET NULL_SYM  { $$= Foreign_key::FK_OPTION_SET_NULL; }
+        | NO_SYM ACTION { $$= Foreign_key::FK_OPTION_NO_ACTION; }
+        | SET DEFAULT   { $$= Foreign_key::FK_OPTION_DEFAULT;  }
         ;
 
 key_type: