Below is the list of changes that have just been committed into a local
6.1 repository of dlenev. When dlenev 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-26 12:33:20+03:00, dlenev@stripped +3 -0
Tentative patch for bug #34772 "Foreign Keys: UNIQUE can't follow REFERENCES
in column definition".
According to specification (LLD) in WL148 "Foreign keys" REFERENCES clause
should be allowed at any place in column definition after the data type.
This is not the fact for current server. Particularly it is not allowed
to have column definition in which REFERENCES clause will precede UNIQUE
clause.
SQL-standard also allows specifying column constraints in any order.
This patch addresses this problem by adjusting our grammar to support
column constraints in any order.
mysql-test/r/foreign_key.result@stripped, 2008-02-26 12:33:15+03:00, dlenev@stripped +14 -0
Added test case for bug #34772 "Foreign Keys: UNIQUE can't follow REFERENCES
in column definition"
mysql-test/t/foreign_key.test@stripped, 2008-02-26 12:33:15+03:00, dlenev@stripped +22 -0
Added test case for bug #34772 "Foreign Keys: UNIQUE can't follow REFERENCES
in column definition"
sql/sql_yacc.yy@stripped, 2008-02-26 12:33:15+03:00, dlenev@stripped +76 -47
Adjusted grammar to support any order for specifying column constraints
by moving foreign key and check constraint specification to the attribute
rule.
Unfortunately this adds 3 new shift/reduce conflicts to our grammar
(between ON UPDATE/DELETE clauses in constraint specification and
ON UPDATE NOW() alternative in "attribute" rule). Since these
conflicts are resolved by preferring shift to reduce (i.e. by
following foreign key constraint specification) we have to use
auxiliary rule to avoid syntax errors when we ON UPDATE NOW() clause
follows foreign key constraint definition.
QQ: is there any ways to solve this problems without introducing new
conflicts or opening way to introducing new conflicts in future?
diff -Nrup a/mysql-test/r/foreign_key.result b/mysql-test/r/foreign_key.result
--- a/mysql-test/r/foreign_key.result 2008-02-22 10:54:05 +03:00
+++ b/mysql-test/r/foreign_key.result 2008-02-26 12:33:15 +03:00
@@ -90,3 +90,17 @@ alter table t1 change column d2 d3 int r
alter table t1 change column e2 e3 int references t2 (a) match full on delete set null;
alter table t1 change column f2 f3 int references t2 (a) references t3 (a);
drop table t1;
+drop table if exists t1;
+create table t1 (a int references t2 (a) not null,
+b int references t2 (a) unique,
+c int references t2 (a) on update set null,
+d int references t2 (a) unique references t2 (c),
+e timestamp references t2 (b) on update now());
+drop table t1;
+create table t1 (a timestamp references t2 (b) on update set default
+on update now());
+drop table t1;
+create table t1 (a timestamp references t2 (b) on delete set default
+on update set default
+on update now());
+drop table t1;
diff -Nrup a/mysql-test/t/foreign_key.test b/mysql-test/t/foreign_key.test
--- a/mysql-test/t/foreign_key.test 2008-02-22 10:54:05 +03:00
+++ b/mysql-test/t/foreign_key.test 2008-02-26 12:33:15 +03:00
@@ -153,3 +153,25 @@ alter table t1 change column e2 e3 int r
alter table t1 change column f2 f3 int references t2 (a) references t3 (a);
drop table t1;
+
+
+#
+# Bug #34772 "Foreign Keys: UNIQUE can't follow REFERENCES in column definition"
+#
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+create table t1 (a int references t2 (a) not null,
+ b int references t2 (a) unique,
+ c int references t2 (a) on update set null,
+ d int references t2 (a) unique references t2 (c),
+ e timestamp references t2 (b) on update now());
+drop table t1;
+create table t1 (a timestamp references t2 (b) on update set default
+ on update now());
+drop table t1;
+create table t1 (a timestamp references t2 (b) on delete set default
+ on update set default
+ on update now());
+drop table t1;
diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy
--- a/sql/sql_yacc.yy 2008-02-22 10:54:05 +03:00
+++ b/sql/sql_yacc.yy 2008-02-26 12:33:15 +03:00
@@ -513,10 +513,10 @@ bool my_yyoverflow(short **a, YYSTYPE **
%pure_parser /* We have threads */
/*
- Currently there are 168 shift/reduce conflicts.
+ Currently there are 171 shift/reduce conflicts.
We should not introduce new conflicts any more.
*/
-%expect 168
+%expect 171
/*
Comments for TOKENS.
@@ -1136,13 +1136,12 @@ bool my_yyoverflow(short **a, YYSTYPE **
IDENT_sys TEXT_STRING_sys TEXT_STRING_literal
NCHAR_STRING opt_component key_cache_name
sp_opt_label BIN_NUM label_ident TEXT_STRING_filesystem ident_or_empty
- field_spec
%type <lex_str_ptr>
opt_table_alias
%type <table>
- table_ident table_ident_nodb references xid
+ table_ident table_ident_nodb references column_references xid
%type <simple_string>
remember_name remember_end opt_ident opt_db text_or_password
@@ -1320,6 +1319,7 @@ bool my_yyoverflow(short **a, YYSTYPE **
init_key_options key_options key_opts key_opt key_using_alg
server_def server_options_list server_option
definer_opt no_definer definer
+ check_constraint
END_OF_INPUT
%type <NONE> call sp_proc_stmts sp_proc_stmts1 sp_proc_stmt
@@ -4562,39 +4562,6 @@ field_list_item:
| key_def
;
-column_def:
- field_spec check_constraint { }
- | field_spec
- { Lex->ident= $1; }
- opt_column_ref_constraint_list
- { }
- ;
-
-opt_column_ref_constraint_list:
- /* empty */ { }
- | opt_column_ref_constraint_list column_ref_constraint_def
- ;
-
-column_ref_constraint_def:
- opt_constraint references
- {
- THD *thd= YYTHD;
- LEX *lex= thd->lex;
- Key *key;
- lex->col_list.push_back(new (thd->mem_root) Key_part_spec(lex->ident.str));
- key= new (thd->mem_root) Foreign_key($1, FALSE, FALSE,
- lex->col_list, $2,
- lex->ref_list,
- lex->fk_delete_opt,
- lex->fk_update_opt,
- lex->fk_match_option);
- lex->alter_info.key_list.push_back(key);
- /* Only used for ALTER TABLE. Ignored otherwise. */
- lex->alter_info.flags|= ALTER_FOREIGN_KEY;
- lex->col_list.empty(); /* Alloced by sql_alloc */
- }
- ;
-
key_def:
key_type opt_ident key_alg '(' key_list ')' key_options
{
@@ -4651,7 +4618,7 @@ opt_check_constraint:
;
check_constraint:
- CHECK_SYM expr
+ CHECK_SYM expr { }
;
opt_constraint:
@@ -4663,7 +4630,7 @@ constraint:
CONSTRAINT opt_ident { $$=$2; }
;
-field_spec:
+column_def:
field_ident
{
LEX *lex=Lex;
@@ -4674,6 +4641,8 @@ field_spec:
lex->charset=NULL;
lex->storage_type= HA_SM_DEFAULT;
lex->column_format= COLUMN_FORMAT_TYPE_DEFAULT;
+ /* We need field name in column referential constraints. */
+ lex->ident= $1;
}
type opt_attribute
{
@@ -4686,7 +4655,6 @@ field_spec:
lex->change,&lex->interval_list,lex->charset,
lex->uint_geom_type))
MYSQL_YYABORT;
- $$= $1;
}
;
@@ -5035,6 +5003,24 @@ attribute:
Lex->charset=$2;
}
}
+ | opt_constraint column_references
+ {
+ THD *thd= YYTHD;
+ LEX *lex= thd->lex;
+ Key *key;
+ lex->col_list.push_back(new (thd->mem_root) Key_part_spec(lex->ident.str));
+ key= new (thd->mem_root) Foreign_key($1, FALSE, FALSE,
+ lex->col_list, $2,
+ lex->ref_list,
+ lex->fk_delete_opt,
+ lex->fk_update_opt,
+ lex->fk_match_option);
+ lex->alter_info.key_list.push_back(key);
+ /* Only used for ALTER TABLE. Ignored otherwise. */
+ lex->alter_info.flags|= ALTER_FOREIGN_KEY;
+ lex->col_list.empty(); /* Alloced by sql_alloc */
+ }
+ | check_constraint
;
now_or_signed_literal:
@@ -5236,6 +5222,17 @@ references:
}
;
+column_references:
+ REFERENCES
+ table_ident
+ opt_ref_list
+ opt_match_clause
+ opt_column_on_update_delete
+ {
+ $$=$2;
+ }
+ ;
+
opt_ref_list:
/* empty */
{ Lex->ref_list.empty(); }
@@ -5299,6 +5296,41 @@ opt_on_update_delete:
}
;
+/*
+ We need this auxiliary rule to avoid syntax errors in situations when
+ REFERENCES clause is followed by ON UPDATE NOW clause. Our grammar has
+ a shift/reduce conflict between opt_on_update_delete rule and
+ ON UPDATE NOW() part of attribute rule which is solved by preferring
+ shift to reduce i.e. by preferring continuing following
+ opt_on_update_delete rule. So by using version of opt_on_update_delete
+ which is extended by addition of ON UPDATE_SYM NOW_SYM () clause we
+ avoid syntax error when istead of shift reduce should have been
+ preferred.
+*/
+
+opt_column_on_update_delete:
+ ON UPDATE_SYM NOW_SYM optional_braces
+ { Lex->on_update_value= new Item_func_now_local(); }
+ | ON UPDATE_SYM delete_option
+ ON UPDATE_SYM NOW_SYM optional_braces
+ {
+ LEX *lex= Lex;
+ lex->fk_update_opt= $3;
+ lex->fk_delete_opt= Foreign_key::FK_OPTION_UNDEF;
+ lex->on_update_value= new Item_func_now_local();
+ }
+ | ON DELETE_SYM delete_option
+ ON UPDATE_SYM NOW_SYM optional_braces
+ {
+ LEX *lex= Lex;
+ lex->fk_update_opt= Foreign_key::FK_OPTION_UNDEF;
+ lex->fk_delete_opt= $3;
+ lex->on_update_value= new Item_func_now_local();
+ }
+ |
+ opt_on_update_delete
+ ;
+
delete_option:
RESTRICT { $$= Foreign_key::FK_OPTION_RESTRICT; }
| CASCADE { $$= Foreign_key::FK_OPTION_CASCADE; }
@@ -5900,11 +5932,7 @@ alter_list_item:
lex->change= $3.str;
lex->alter_info.flags|= ALTER_CHANGE_COLUMN;
}
- field_spec
- {
- Lex->ident= $5;
- }
- opt_column_ref_constraint_list opt_place
+ column_def opt_place
| MODIFY_SYM opt_column field_ident
{
LEX *lex=Lex;
@@ -5915,6 +5943,8 @@ alter_list_item:
lex->alter_info.flags|= ALTER_CHANGE_COLUMN;
lex->storage_type= HA_SM_DEFAULT;
lex->column_format= COLUMN_FORMAT_TYPE_DEFAULT;
+ /* We need field name in column referential constraints. */
+ lex->ident= $3;
}
type opt_attribute
{
@@ -5928,9 +5958,8 @@ alter_list_item:
$3.str, &lex->interval_list, lex->charset,
lex->uint_geom_type))
MYSQL_YYABORT;
- lex->ident= $3;
}
- opt_column_ref_constraint_list opt_place
+ opt_place
| DROP opt_column field_ident opt_restrict
{
LEX *lex=Lex;
| Thread |
|---|
| • bk commit into 6.1 tree (dlenev:1.2552) BUG#34772 | dlenev | 26 Feb |
