bk commit into 5.0 tree (kaa:1.2593) BUG#25162

[Alexey Kopytov]

Below is the list of changes that have just been committed into a local
5.0 repository of kaa.  When kaa 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-01 13:00:40+05:00, kaa@stripped +3 -0
  Fix for bug #25162: Backing up DB from 5.1 adds 'USING BTREE' to KEYs 
                      on table creates
  
  The problem was in incompatible syntax for key definition in CREATE
  TABLE.
  
  5.0 supports only the following syntax for key definition (see "CREATE
  TABLE syntax" in the manual):
  
  {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
  
  While 5.1 parser supports the above syntax, the "preferred" syntax was
  changed to:
  
  {INDEX|KEY} [index_name] (index_col_name,...) [index_type]
  
  The above syntax is used in 5.1 for the SHOW CREATE TABLE output, which
  led to dumps generated by 5.1 being incompatible with 5.0.
  
  Fixed by changing the parser in 5.0 to support both 5.0 and 5.1 syntax
  for key definition.

  mysql-test/r/create.result@stripped, 2008-02-01 13:00:39+05:00, kaa@stripped +14 -0
    Added a test case for bug #25162.

  mysql-test/t/create.test@stripped, 2008-02-01 13:00:39+05:00, kaa@stripped +24 -0
    Added a test case for bug #25162.

  sql/sql_yacc.yy@stripped, 2008-02-01 13:00:39+05:00, kaa@stripped +3 -3
    Changed the parser to support both 5.0 and 5.1 syntax for index type
    specification in CREATE TABLE.

diff -Nrup a/mysql-test/r/create.result b/mysql-test/r/create.result
--- a/mysql-test/r/create.result	2007-10-10 20:50:45 +06:00
+++ b/mysql-test/r/create.result	2008-02-01 13:00:39 +05:00
@@ -1532,4 +1532,18 @@ Handler_read_prev	0
 Handler_read_rnd	0
 Handler_read_rnd_next	7
 drop table t1,t2;
+CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1));
+DROP TABLE t1;
+CREATE TABLE t1(c1 VARCHAR(33), KEY (c1) USING BTREE);
+DROP TABLE t1;
+CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1) USING HASH) ENGINE=MEMORY;
+SHOW INDEX FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
+t1	1	c1	1	c1	NULL	0	NULL	NULL	YES	HASH	
+DROP TABLE t1;
+CREATE TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY;
+SHOW INDEX FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
+t1	1	c1	1	c1	A	NULL	NULL	NULL	YES	BTREE	
+DROP TABLE t1;
 End of 5.0 tests
diff -Nrup a/mysql-test/t/create.test b/mysql-test/t/create.test
--- a/mysql-test/t/create.test	2007-07-26 04:28:49 +06:00
+++ b/mysql-test/t/create.test	2008-02-01 13:00:39 +05:00
@@ -1148,4 +1148,28 @@ create table t2 select sql_big_result f1
 show status like 'handler_read%';
 drop table t1,t2;
 
+#
+# Bug #25162: Backing up DB from 5.1 adds 'USING BTREE' to KEYs on table creates
+#
+
+# Show that the old syntax for index type is supported
+CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1));
+DROP TABLE t1;
+
+# Show that the new syntax for index type is supported
+CREATE TABLE t1(c1 VARCHAR(33), KEY (c1) USING BTREE);
+DROP TABLE t1;
+
+# Show that in case of multiple index type definitions, the last one takes 
+# precedence
+
+CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1) USING HASH) ENGINE=MEMORY;
+SHOW INDEX FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY;
+SHOW INDEX FROM t1;
+DROP TABLE t1;
+
+
 --echo End of 5.0 tests
diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy
--- a/sql/sql_yacc.yy	2007-12-15 16:04:59 +05:00
+++ b/sql/sql_yacc.yy	2008-02-01 13:00:39 +05:00
@@ -2982,15 +2982,15 @@ column_def:
 	;
 
 key_def:
-	key_type opt_ident key_alg '(' key_list ')'
+	key_type opt_ident key_alg '(' key_list ')' key_alg
 	  {
 	    LEX *lex=Lex;
-            Key *key= new Key($1, $2, $3, 0, lex->col_list);
+            Key *key= new Key($1, $2, $7 ? $7 : $3, 0, lex->col_list);
             lex->alter_info.key_list.push_back(key);
 
 	    lex->col_list.empty();		/* Alloced by sql_alloc */
 	  }
-	| opt_constraint constraint_key_type opt_ident key_alg '(' key_list ')'
+	| opt_constraint constraint_key_type opt_ident key_alg '(' key_list ')' key_alg
 	  {
 	    LEX *lex=Lex;
 	    const char *key_name= $3 ? $3:$1;