Introduce keys in child tables corresponding to FOREIGN KEYs

Remove redundant keys in CREATE TABLE and ALTER TABLE


mysql-test/r/constraints.result:
  Remove redundant keys
mysql-test/r/create.result:
  Remove redundant keys
mysql-test/r/innodb.result:
  Remove redundant keys
mysql-test/r/range.result:
  Remove redundant keys
mysql-test/t/range.test:
  Remove redundant keys
sql/sql_class.cc:
  Equality comparison of keys (ignoring name)
sql/sql_class.h:
  Equality comparison of keys (ignoring name)
sql/sql_table.cc:
  Remove redundant keys
sql/sql_yacc.yy:
  Introduce keys in child tables corresponding to FOREIGN KEYs

5 files changed, 5 insertions, 43 deletions

diff --git a/mysql-test/r/constraints.result b/mysql-test/r/constraints.result
index d4d525c8991..e2fb0607819 100644
--- a/mysql-test/r/constraints.result
+++ b/mysql-test/r/constraints.result
@@ -22,8 +22,6 @@ show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) default NULL,
-  UNIQUE KEY `constraint_1` (`a`),
-  UNIQUE KEY `key_1` (`a`),
-  UNIQUE KEY `key_2` (`a`)
+  UNIQUE KEY `constraint_1` (`a`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index ce72c353d20..e24297dc77c 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -155,37 +155,7 @@ t1	CREATE TABLE `t1` (
   `a` int(11) NOT NULL default '0',
   `b` int(11) default NULL,
   PRIMARY KEY  (`a`),
-  KEY `b` (`b`),
-  KEY `b_2` (`b`),
-  KEY `b_3` (`b`),
-  KEY `b_4` (`b`),
-  KEY `b_5` (`b`),
-  KEY `b_6` (`b`),
-  KEY `b_7` (`b`),
-  KEY `b_8` (`b`),
-  KEY `b_9` (`b`),
-  KEY `b_10` (`b`),
-  KEY `b_11` (`b`),
-  KEY `b_12` (`b`),
-  KEY `b_13` (`b`),
-  KEY `b_14` (`b`),
-  KEY `b_15` (`b`),
-  KEY `b_16` (`b`),
-  KEY `b_17` (`b`),
-  KEY `b_18` (`b`),
-  KEY `b_19` (`b`),
-  KEY `b_20` (`b`),
-  KEY `b_21` (`b`),
-  KEY `b_22` (`b`),
-  KEY `b_23` (`b`),
-  KEY `b_24` (`b`),
-  KEY `b_25` (`b`),
-  KEY `b_26` (`b`),
-  KEY `b_27` (`b`),
-  KEY `b_28` (`b`),
-  KEY `b_29` (`b`),
-  KEY `b_30` (`b`),
-  KEY `b_31` (`b`)
+  KEY `b` (`b`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 select if(1,'1','0'), month("2002-08-02");
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 5e513061821..0729fd1b547 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -371,7 +371,6 @@ t1	0	PRIMARY	2	b	A	#	NULL	NULL		BTREE
 t1	0	c	1	c	A	#	NULL	NULL		BTREE	
 t1	0	b	1	b	A	#	NULL	NULL		BTREE	
 t1	1	a	1	a	A	#	NULL	NULL		BTREE	
-t1	1	a_2	1	a	A	#	NULL	NULL		BTREE	
 drop table t1;
 create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
 alter table t1 engine=innodb;
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index d1a5dd00370..290e72d3b55 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -249,18 +249,14 @@ explain select count(*) from t1 where x in (1,2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	x	x	5	NULL	2	Using where; Using index
 drop table t1;
-CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1), KEY i2 (key1));
+CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1));
 INSERT INTO t1 VALUES (0),(0),(1),(1);
 CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya));
 INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2);
 explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	j1	j1	4	const	1	Using where; Using index
-1	SIMPLE	t1	ALL	i1,i2	NULL	NULL	NULL	4	Range checked for each record (index map: 0x3)
-explain select * from t1 force index(i2), t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ref	j1	j1	4	const	1	Using where; Using index
-1	SIMPLE	t1	ALL	i2	NULL	NULL	NULL	4	Range checked for each record (index map: 0x2)
+1	SIMPLE	t1	ALL	i1	NULL	NULL	NULL	4	Range checked for each record (index map: 0x1)
 DROP TABLE t1,t2;
 CREATE TABLE t1 (
 a int(11) default NULL,
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 51b1f34ee79..4665cd02ed8 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -197,12 +197,11 @@ drop table t1;
 #
 # bug #1172
 #
-CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1), KEY i2 (key1));
+CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1));
 INSERT INTO t1 VALUES (0),(0),(1),(1);
 CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya));
 INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2);
 explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
-explain select * from t1 force index(i2), t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
 DROP TABLE t1,t2;
 
 #