diff options
Diffstat (limited to 'mysql-test/main')
41 files changed, 688 insertions, 679 deletions
diff --git a/mysql-test/main/alter_table.result b/mysql-test/main/alter_table.result index a27b54d4b48..a5f70d61def 100644 --- a/mysql-test/main/alter_table.result +++ b/mysql-test/main/alter_table.result @@ -126,44 +126,44 @@ key (n3, n4, n1, n2), key (n4, n1, n2, n3) ); alter table t1 disable keys; show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 n1 1 n1 A 0 NULL NULL BTREE -t1 1 n1_2 1 n1 A NULL NULL NULL BTREE disabled -t1 1 n1_2 2 n2 A NULL NULL NULL YES BTREE disabled -t1 1 n1_2 3 n3 A NULL NULL NULL YES BTREE disabled -t1 1 n1_2 4 n4 A NULL NULL NULL YES BTREE disabled -t1 1 n2 1 n2 A NULL NULL NULL YES BTREE disabled -t1 1 n2 2 n3 A NULL NULL NULL YES BTREE disabled -t1 1 n2 3 n4 A NULL NULL NULL YES BTREE disabled -t1 1 n2 4 n1 A NULL NULL NULL BTREE disabled -t1 1 n3 1 n3 A NULL NULL NULL YES BTREE disabled -t1 1 n3 2 n4 A NULL NULL NULL YES BTREE disabled -t1 1 n3 3 n1 A NULL NULL NULL BTREE disabled -t1 1 n3 4 n2 A NULL NULL NULL YES BTREE disabled -t1 1 n4 1 n4 A NULL NULL NULL YES BTREE disabled -t1 1 n4 2 n1 A NULL NULL NULL BTREE disabled -t1 1 n4 3 n2 A NULL NULL NULL YES BTREE disabled -t1 1 n4 4 n3 A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 n1 1 n1 A 0 NULL NULL BTREE NO +t1 1 n1_2 1 n1 A NULL NULL NULL BTREE disabled NO +t1 1 n1_2 2 n2 A NULL NULL NULL YES BTREE disabled NO +t1 1 n1_2 3 n3 A NULL NULL NULL YES BTREE disabled NO +t1 1 n1_2 4 n4 A NULL NULL NULL YES BTREE disabled NO +t1 1 n2 1 n2 A NULL NULL NULL YES BTREE disabled NO +t1 1 n2 2 n3 A NULL NULL NULL YES BTREE disabled NO +t1 1 n2 3 n4 A NULL NULL NULL YES BTREE disabled NO +t1 1 n2 4 n1 A NULL NULL NULL BTREE disabled NO +t1 1 n3 1 n3 A NULL NULL NULL YES BTREE disabled NO +t1 1 n3 2 n4 A NULL NULL NULL YES BTREE disabled NO +t1 1 n3 3 n1 A NULL NULL NULL BTREE disabled NO +t1 1 n3 4 n2 A NULL NULL NULL YES BTREE disabled NO +t1 1 n4 1 n4 A NULL NULL NULL YES BTREE disabled NO +t1 1 n4 2 n1 A NULL NULL NULL BTREE disabled NO +t1 1 n4 3 n2 A NULL NULL NULL YES BTREE disabled NO +t1 1 n4 4 n3 A NULL NULL NULL YES BTREE disabled NO alter table t1 enable keys; show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 n1 1 n1 A 10 NULL NULL BTREE -t1 1 n1_2 1 n1 A 10 NULL NULL BTREE -t1 1 n1_2 2 n2 A 10 NULL NULL YES BTREE -t1 1 n1_2 3 n3 A 10 NULL NULL YES BTREE -t1 1 n1_2 4 n4 A 10 NULL NULL YES BTREE -t1 1 n2 1 n2 A 10 NULL NULL YES BTREE -t1 1 n2 2 n3 A 10 NULL NULL YES BTREE -t1 1 n2 3 n4 A 10 NULL NULL YES BTREE -t1 1 n2 4 n1 A 10 NULL NULL BTREE -t1 1 n3 1 n3 A 10 NULL NULL YES BTREE -t1 1 n3 2 n4 A 10 NULL NULL YES BTREE -t1 1 n3 3 n1 A 10 NULL NULL BTREE -t1 1 n3 4 n2 A 10 NULL NULL YES BTREE -t1 1 n4 1 n4 A 10 NULL NULL YES BTREE -t1 1 n4 2 n1 A 10 NULL NULL BTREE -t1 1 n4 3 n2 A 10 NULL NULL YES BTREE -t1 1 n4 4 n3 A 10 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 n1 1 n1 A 10 NULL NULL BTREE NO +t1 1 n1_2 1 n1 A 10 NULL NULL BTREE NO +t1 1 n1_2 2 n2 A 10 NULL NULL YES BTREE NO +t1 1 n1_2 3 n3 A 10 NULL NULL YES BTREE NO +t1 1 n1_2 4 n4 A 10 NULL NULL YES BTREE NO +t1 1 n2 1 n2 A 10 NULL NULL YES BTREE NO +t1 1 n2 2 n3 A 10 NULL NULL YES BTREE NO +t1 1 n2 3 n4 A 10 NULL NULL YES BTREE NO +t1 1 n2 4 n1 A 10 NULL NULL BTREE NO +t1 1 n3 1 n3 A 10 NULL NULL YES BTREE NO +t1 1 n3 2 n4 A 10 NULL NULL YES BTREE NO +t1 1 n3 3 n1 A 10 NULL NULL BTREE NO +t1 1 n3 4 n2 A 10 NULL NULL YES BTREE NO +t1 1 n4 1 n4 A 10 NULL NULL YES BTREE NO +t1 1 n4 2 n1 A 10 NULL NULL BTREE NO +t1 1 n4 3 n2 A 10 NULL NULL YES BTREE NO +t1 1 n4 4 n3 A 10 NULL NULL YES BTREE NO drop table t1; create table t1 (i int unsigned not null auto_increment primary key); alter table t1 rename t2; @@ -176,19 +176,19 @@ drop table t1; create table t1 (a int, b int); alter table t1 add unique (a,b), add key (b); show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A 3 NULL NULL YES BTREE -t1 0 a 2 b A 300 NULL NULL YES BTREE -t1 1 b 1 b A 100 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A 3 NULL NULL YES BTREE NO +t1 0 a 2 b A 300 NULL NULL YES BTREE NO +t1 1 b 1 b A 100 NULL NULL YES BTREE NO analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A 3 NULL NULL YES BTREE -t1 0 a 2 b A 300 NULL NULL YES BTREE -t1 1 b 1 b A 100 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A 3 NULL NULL YES BTREE NO +t1 0 a 2 b A 300 NULL NULL YES BTREE NO +t1 1 b 1 b A 100 NULL NULL YES BTREE NO drop table t1; CREATE TABLE t1 (i int(10), index(i) ) ENGINE=MyISAM; ALTER TABLE t1 DISABLE KEYS; @@ -204,9 +204,9 @@ ALTER TABLE t1 DISABLE KEYS; LOCK TABLES t1 WRITE; INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty'); SHOW INDEX FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE -t1 0 PRIMARY 2 User A 0 NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE NO +t1 0 PRIMARY 2 User A 0 NULL NULL BTREE NO ALTER TABLE t1 ENABLE KEYS; UNLOCK TABLES; CHECK TABLES t1; @@ -221,23 +221,23 @@ KEY (Host) ) ENGINE=MyISAM; ALTER TABLE t1 DISABLE KEYS; SHOW INDEX FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE -t1 0 PRIMARY 2 User A 0 NULL NULL BTREE -t1 1 Host 1 Host A NULL NULL NULL BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE NO +t1 0 PRIMARY 2 User A 0 NULL NULL BTREE NO +t1 1 Host 1 Host A NULL NULL NULL BTREE disabled NO LOCK TABLES t1 WRITE; INSERT INTO t1 VALUES ('localhost','root'),('localhost',''); SHOW INDEX FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE -t1 0 PRIMARY 2 User A 0 NULL NULL BTREE -t1 1 Host 1 Host A NULL NULL NULL BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE NO +t1 0 PRIMARY 2 User A 0 NULL NULL BTREE NO +t1 1 Host 1 Host A NULL NULL NULL BTREE disabled NO ALTER TABLE t1 ENABLE KEYS; SHOW INDEX FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE -t1 0 PRIMARY 2 User A 2 NULL NULL BTREE -t1 1 Host 1 Host A 1 NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE NO +t1 0 PRIMARY 2 User A 2 NULL NULL BTREE NO +t1 1 Host 1 Host A 1 NULL NULL BTREE NO UNLOCK TABLES; CHECK TABLES t1; Table Op Msg_type Msg_text @@ -259,10 +259,10 @@ KEY (Host) LOCK TABLES t1 WRITE; ALTER TABLE t1 DISABLE KEYS; SHOW INDEX FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE -t1 0 PRIMARY 2 User A 0 NULL NULL BTREE -t1 1 Host 1 Host A NULL NULL NULL BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE NO +t1 0 PRIMARY 2 User A 0 NULL NULL BTREE NO +t1 1 Host 1 Host A NULL NULL NULL BTREE disabled NO DROP TABLE t1; create table t1 (a int); alter table t1 rename to ``; @@ -444,38 +444,38 @@ drop table t1; drop table if exists t1; create table t1 (a int, key(a)); show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE NO "this used not to disable the index" alter table t1 modify a int, disable keys; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled NO alter table t1 enable keys; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE NO alter table t1 modify a bigint, disable keys; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled NO alter table t1 enable keys; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE NO alter table t1 add b char(10), disable keys; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled NO alter table t1 add c decimal(10,2), enable keys; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE NO "this however did" alter table t1 disable keys; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled NO desc t1; Field Type Null Key Default Extra a bigint(20) YES MUL NULL @@ -484,83 +484,83 @@ c decimal(10,2) YES NULL alter table t1 add d decimal(15,5); "The key should still be disabled" show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled NO drop table t1; "Now will test with one unique index" create table t1(a int, b char(10), unique(a)); show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES BTREE NO alter table t1 disable keys; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES BTREE NO alter table t1 enable keys; "If no copy on noop change, this won't touch the data file" "Unique index, no change" alter table t1 modify a int, disable keys; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES BTREE NO "Change the type implying data copy" "Unique index, no change" alter table t1 modify a bigint, disable keys; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES BTREE NO alter table t1 modify a bigint; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES BTREE NO alter table t1 modify a int; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES BTREE NO drop table t1; "Now will test with one unique and one non-unique index" create table t1(a int, b char(10), unique(a), key(b)); show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES BTREE NO +t1 1 b 1 b A NULL NULL NULL YES BTREE NO alter table t1 disable keys; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES BTREE NO +t1 1 b 1 b A NULL NULL NULL YES BTREE disabled NO alter table t1 enable keys; "If no copy on noop change, this won't touch the data file" "The non-unique index will be disabled" alter table t1 modify a int, disable keys; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES BTREE NO +t1 1 b 1 b A NULL NULL NULL YES BTREE disabled NO alter table t1 enable keys; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES BTREE NO +t1 1 b 1 b A NULL NULL NULL YES BTREE NO "Change the type implying data copy" "The non-unique index will be disabled" alter table t1 modify a bigint, disable keys; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES BTREE NO +t1 1 b 1 b A NULL NULL NULL YES BTREE disabled NO "Change again the type, but leave the indexes as_is" alter table t1 modify a int; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES BTREE NO +t1 1 b 1 b A NULL NULL NULL YES BTREE disabled NO "Try the same. When data is no copied on similar tables, this is noop" alter table t1 modify a int; show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES BTREE NO +t1 1 b 1 b A NULL NULL NULL YES BTREE disabled NO drop table t1; create database mysqltest; create table t1 (c1 int); @@ -596,12 +596,12 @@ DROP TABLE IF EXISTS bug24219; DROP TABLE IF EXISTS bug24219_2; CREATE TABLE bug24219 (a INT, INDEX(a)); SHOW INDEX FROM bug24219; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -bug24219 1 a 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +bug24219 1 a 1 a A NULL NULL NULL YES BTREE NO ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS; SHOW INDEX FROM bug24219_2; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -bug24219_2 1 a 1 a A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +bug24219_2 1 a 1 a A NULL NULL NULL YES BTREE disabled NO DROP TABLE bug24219_2; drop table if exists table_24562; create table table_24562( @@ -1044,8 +1044,8 @@ Field Type Null Key Default Extra int_field int(10) unsigned NO MUL NULL char_field char(10) YES NULL SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 int_field 1 int_field A NULL NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 int_field 1 int_field A NULL NULL NULL BTREE NO INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet"); "Non-copy data change - new frm, but old data and index files" ALTER TABLE t1 diff --git a/mysql-test/main/analyze.result b/mysql-test/main/analyze.result index dd785abcd42..a1332abd177 100644 --- a/mysql-test/main/analyze.result +++ b/mysql-test/main/analyze.result @@ -60,8 +60,8 @@ analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A 5 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 5 NULL NULL YES BTREE NO drop table t1; End of 4.1 tests create table t1(a int); diff --git a/mysql-test/main/compress.result b/mysql-test/main/compress.result index 788eb7ab13b..24979346149 100644 --- a/mysql-test/main/compress.result +++ b/mysql-test/main/compress.result @@ -2097,10 +2097,10 @@ fld6 char(4) latin1_swedish_ci NO # show full columns from t2 from test like 's%'; Field Type Collation Null Key Default Extra Privileges Comment show keys from t2; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE -t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE -t2 1 fld3 1 fld3 A NULL NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE NO +t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE NO +t2 1 fld3 1 fld3 A NULL NULL NULL BTREE NO drop table t4, t3, t2, t1; CREATE TABLE t1 ( cont_nr int(11) NOT NULL auto_increment, diff --git a/mysql-test/main/create.result b/mysql-test/main/create.result index e6af42db1ff..2b68e9f3e9c 100644 --- a/mysql-test/main/create.result +++ b/mysql-test/main/create.result @@ -980,13 +980,13 @@ 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 Index_comment -t1 1 c1 1 c1 NULL 0 NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 c1 1 c1 NULL 0 NULL NULL YES HASH NO 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 Index_comment -t1 1 c1 1 c1 A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 c1 1 c1 A NULL NULL NULL YES BTREE NO DROP TABLE t1; create user mysqltest_1@'test@test'; ERROR HY000: Malformed hostname (illegal symbol: '@') diff --git a/mysql-test/main/ctype_mb.result b/mysql-test/main/ctype_mb.result index 015457cc1b3..5b0291aa53c 100644 --- a/mysql-test/main/ctype_mb.result +++ b/mysql-test/main/ctype_mb.result @@ -32,8 +32,8 @@ t1 CREATE TABLE `t1` ( KEY `key_a` (`a`(3)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SHOW KEYS FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 key_a 1 a A NULL 3 NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 key_a 1 a A NULL 3 NULL YES BTREE NO ALTER TABLE t1 CHANGE a a CHAR(4); SHOW CREATE TABLE t1; Table Create Table @@ -42,8 +42,8 @@ t1 CREATE TABLE `t1` ( KEY `key_a` (`a`(3)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SHOW KEYS FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 key_a 1 a A NULL 3 NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 key_a 1 a A NULL 3 NULL YES BTREE NO ALTER TABLE t1 CHANGE a a CHAR(4) CHARACTER SET utf8; SHOW CREATE TABLE t1; Table Create Table @@ -52,6 +52,6 @@ t1 CREATE TABLE `t1` ( KEY `key_a` (`a`(3)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SHOW KEYS FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 key_a 1 a A NULL 3 NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 key_a 1 a A NULL 3 NULL YES BTREE NO DROP TABLE t1; diff --git a/mysql-test/main/fulltext.result b/mysql-test/main/fulltext.result index dbc08144e30..343458c4267 100644 --- a/mysql-test/main/fulltext.result +++ b/mysql-test/main/fulltext.result @@ -6,9 +6,9 @@ INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), ('Function MATCH ... AGAINST()','is used to do a search'), ('Full-text search in MySQL', 'implements vector space model'); SHOW INDEX FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a NULL NULL NULL NULL YES FULLTEXT -t1 1 a 2 b NULL NULL NULL NULL YES FULLTEXT +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a NULL NULL NULL NULL YES FULLTEXT NO +t1 1 a 2 b NULL NULL NULL NULL YES FULLTEXT NO select * from t1 where MATCH(a,b) AGAINST ("collections"); a b Only MyISAM tables support collections @@ -242,9 +242,9 @@ match(ttxt.inhalt) against ('foobar'); id 3 show keys from t2; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t2 1 tig 1 ticket A NULL NULL NULL YES BTREE -t2 1 tix 1 inhalt NULL NULL NULL NULL YES FULLTEXT +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t2 1 tig 1 ticket A NULL NULL NULL YES BTREE NO +t2 1 tix 1 inhalt NULL NULL NULL NULL YES FULLTEXT NO show create table t2; Table Create Table t2 CREATE TABLE `t2` ( diff --git a/mysql-test/main/grant2.result b/mysql-test/main/grant2.result index 3f2b1d48c55..7bb8aeb9d23 100644 --- a/mysql-test/main/grant2.result +++ b/mysql-test/main/grant2.result @@ -708,8 +708,8 @@ a SHOW COLUMNS FROM t1; Field Type Null Key Default Extra SHOW KEYS FROM t3; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t3 0 PRIMARY 1 a A 0 NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t3 0 PRIMARY 1 a A 0 NULL NULL BTREE NO # # SHOW CREATE TABLE. # diff --git a/mysql-test/main/grant4.result b/mysql-test/main/grant4.result index 29021b608aa..981bbdeddf5 100644 --- a/mysql-test/main/grant4.result +++ b/mysql-test/main/grant4.result @@ -94,14 +94,14 @@ use mysqltest_db1; ** SELECT FROM INFORMATION_SCHEMA.STATISTICS will succeed because any privileges will do (authentication is enough). ** but will return no rows SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='t5'; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED ** SHOW INDEX FROM t5 will fail because we don't have any privileges on any column combination. SHOW INDEX FROM t5; ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't5' ** SHOW INDEX FROM t6 will succeed because there exist a privilege on a column combination on t6. SHOW INDEX FROM t6; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t6 1 i 1 s1 A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t6 1 i 1 s1 A NULL NULL NULL YES BTREE NO ** CHECK TABLE requires any privilege on any column combination and should succeed for t6: CHECK TABLE t6; Table Op Msg_type Msg_text diff --git a/mysql-test/main/ignore_indexes.result b/mysql-test/main/ignore_indexes.result index bcbfb57ba34..4318bb7dc3d 100644 --- a/mysql-test/main/ignore_indexes.result +++ b/mysql-test/main/ignore_indexes.result @@ -4,15 +4,15 @@ # Test of ALTER INDEX syntax. CREATE TABLE t1 ( a INT, KEY (a) ); SHOW KEYS FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a A NULL NULL NULL YES BTREE NO ALTER TABLE t1 ALTER INDEX a IGNORE; SHOW KEYS FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a A NULL NULL NULL YES BTREE YES ALTER TABLE t1 ALTER INDEX a NOT IGNORE; SHOW KEYS FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a A NULL NULL NULL YES BTREE NO DROP TABLE t1; # Test of CREATE INDEX syntax with IGNORE indexes. @@ -23,7 +23,7 @@ Warnings: Note 1831 Duplicate index `b_visible`. This is deprecated and will be disallowed in a future release CREATE INDEX a_b_invisible ON t1(a, b) IGNORE; SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a_invisible 1 a A NULL NULL NULL YES BTREE YES t1 1 b_visible 1 a A NULL NULL NULL YES BTREE NO t1 1 a_b_invisible 1 a A NULL NULL NULL YES BTREE YES @@ -65,19 +65,19 @@ a INT, INDEX (a), b INT, INDEX (b) IGNORE ); SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a A NULL NULL NULL YES BTREE NO t1 1 b 1 b A NULL NULL NULL YES BTREE YES ALTER TABLE t1 RENAME INDEX a TO a1; ALTER TABLE t1 RENAME INDEX b TO b1; SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a1 1 a A NULL NULL NULL YES BTREE NO t1 1 b1 1 b A NULL NULL NULL YES BTREE YES ALTER TABLE t1 ALTER INDEX a1 IGNORE; ALTER TABLE t1 ALTER INDEX b1 NOT IGNORE; SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a1 1 a A NULL NULL NULL YES BTREE YES t1 1 b1 1 b A NULL NULL NULL YES BTREE NO DROP TABLE t1; @@ -136,12 +136,12 @@ ERROR 42000: Key 'no_such_index' doesn't exist in table 't1' # ALTER TABLE t1 ALTER INDEX a IGNORE, ALTER INDEX a NOT IGNORE; SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a A NULL NULL NULL YES BTREE NO t1 1 b 1 b A NULL NULL NULL YES BTREE YES ALTER TABLE t1 ALTER INDEX a NOT IGNORE, ALTER INDEX b IGNORE; SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a A NULL NULL NULL YES BTREE NO t1 1 b 1 b A NULL NULL NULL YES BTREE YES # @@ -171,7 +171,7 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a A 3 NULL NULL YES BTREE YES t1 1 b 1 b A 3 NULL NULL YES BTREE YES ALTER TABLE t1 ALTER INDEX a NOT IGNORE, ALGORITHM = INPLACE; @@ -182,7 +182,7 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a A 3 NULL NULL YES BTREE NO t1 1 b 1 b A 3 NULL NULL YES BTREE YES ALTER TABLE t1 ALTER INDEX a IGNORE, ALGORITHM = DEFAULT; @@ -193,7 +193,7 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a A 3 NULL NULL YES BTREE YES t1 1 b 1 b A 3 NULL NULL YES BTREE YES ALTER TABLE t1 ALTER INDEX a NOT IGNORE; @@ -204,7 +204,7 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a A 3 NULL NULL YES BTREE NO t1 1 b 1 b A 3 NULL NULL YES BTREE YES ALTER TABLE t1 ADD INDEX ab(a, b), ALTER INDEX ab IGNORE; @@ -220,7 +220,7 @@ b INT NOT NULL PRIMARY KEY, UNIQUE KEY (a) IGNORE ); SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 0 PRIMARY 1 b A 0 NULL NULL BTREE NO t1 0 a 1 a A 0 NULL NULL BTREE YES DROP TABLE t1; @@ -232,7 +232,7 @@ UNIQUE KEY (a), UNIQUE KEY (b) IGNORE ); SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 0 a 1 a A 0 NULL NULL BTREE NO t1 0 b 1 b A 0 NULL NULL BTREE YES DROP TABLE t1; @@ -241,7 +241,7 @@ ERROR HY000: A primary key cannot be marked as IGNORE CREATE TEMPORARY TABLE t1 ( a INT, KEY (a) IGNORE); INSERT INTO t1 VALUES (0), (1), (2), (3); SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a A NULL NULL NULL YES BTREE YES EXPLAIN SELECT a FROM t1; id select_type table type possible_keys key key_len ref rows Extra @@ -282,7 +282,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 4 NULL 3 Using index ALTER TABLE t1 ALTER INDEX a IGNORE; SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a A 3 NULL NULL BTREE YES EXPLAIN SELECT a FROM t1; id select_type table type possible_keys key key_len ref rows Extra @@ -302,7 +302,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range key1 key1 34 NULL 8 Using where ALTER TABLE t1 ALTER INDEX key1 IGNORE; SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 0 PRIMARY 1 fid A 150 NULL NULL BTREE NO t1 1 key1 1 g A NULL 32 NULL SPATIAL YES EXPLAIN SELECT fid, AsText(g) FROM t1 @@ -313,14 +313,14 @@ DROP TABLE t1; CREATE TABLE t1 ( a INT GENERATED ALWAYS AS (1), KEY key1(a)); INSERT INTO t1 VALUES (),(),(); SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 key1 1 a A NULL NULL NULL YES BTREE NO EXPLAIN SELECT a FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL key1 5 NULL 3 Using index ALTER TABLE t1 ALTER INDEX key1 IGNORE; SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignore +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 key1 1 a A NULL NULL NULL YES BTREE YES EXPLAIN SELECT a FROM t1; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index fcaa0a921c9..f875c88182d 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -201,11 +201,11 @@ table_name t1 t4 select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest"; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT -def mysqltest t1 1 mysqltest string_data 1 b A NULL NULL NULL YES BTREE +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED +def mysqltest t1 1 mysqltest string_data 1 b A NULL NULL NULL YES BTREE NO show keys from t3 where Key_name = "a_data"; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t3 1 a_data 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t3 1 a_data 1 a A NULL NULL NULL YES BTREE NO show tables like 't%'; Tables_in_test (t%) t2 @@ -457,7 +457,7 @@ latin1_spanish_ci latin1_swedish_nopad_ci latin1_nopad_bin show keys from v4; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored select * from information_schema.views where TABLE_NAME like "v%"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM def test v0 select `information_schema`.`schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata` NONE NO root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED @@ -691,7 +691,7 @@ user drop view v1; create view vo as select 'a' union select 'a'; show index from vo; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME= "vo"; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE @@ -1249,8 +1249,8 @@ describe t1; Field Type Null Key Default Extra f1 int(11) YES MUL NULL show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 f1 1 f1 A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 f1 1 f1 A NULL NULL NULL YES BTREE NO drop table t1; create table t1(f1 binary(32), f2 varbinary(64)); select character_maximum_length, character_octet_length @@ -1632,9 +1632,9 @@ CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG U select * from information_schema.schemata where schema_name = NULL; CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH SCHEMA_COMMENT select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED select * from information_schema.tables where table_schema = NULL; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT MAX_INDEX_LENGTH TEMPORARY select * from information_schema.tables where table_catalog = NULL; diff --git a/mysql-test/main/invisible_field_debug.result b/mysql-test/main/invisible_field_debug.result index 582c935333c..4cbdb31e155 100644 --- a/mysql-test/main/invisible_field_debug.result +++ b/mysql-test/main/invisible_field_debug.result @@ -249,10 +249,10 @@ b alter table t1 add index(a); alter table t1 add index(a,b); show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE -t1 1 a_2 1 a A NULL NULL NULL YES BTREE -t1 1 a_2 2 b A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE NO +t1 1 a_2 1 a A NULL NULL NULL YES BTREE NO +t1 1 a_2 2 b A NULL NULL NULL YES BTREE NO drop table t1; set debug_dbug= "+d,test_pseudo_invisible"; Create table t1( a int default(99) invisible, b int); @@ -280,7 +280,7 @@ ERROR 42000: Key column 'invisible' doesn't exist in table alter table t1 add index(b,invisible); ERROR 42000: Key column 'invisible' doesn't exist in table show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored drop table t1; set debug_dbug= "+d,test_completely_invisible"; Create table t1( a int default(99) invisible, b int); @@ -308,17 +308,17 @@ ERROR 42000: Key column 'invisible' doesn't exist in table alter table t1 add index(b,invisible); ERROR 42000: Key column 'invisible' doesn't exist in table show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored drop table t1; set debug_dbug= "+d,test_completely_invisible,test_invisible_index"; Create table t1( a int default(99) , b int,c int, index(b)); set debug_dbug=@old_debug; Show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 b 1 b A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 b 1 b A NULL NULL NULL YES BTREE NO select * from INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA ='test' and table_name='t1'; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT -def test t1 1 test b 1 b A NULL NULL NULL YES BTREE +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED +def test t1 1 test b 1 b A NULL NULL NULL YES BTREE NO show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -359,8 +359,8 @@ invisible a b 9 7 7 set debug_dbug=@old_debug; Show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 b 1 b A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 b 1 b A NULL NULL NULL YES BTREE NO create index a1 on t1(invisible); ERROR 42000: Key column 'invisible' doesn't exist in table set debug_dbug= "+d,test_completely_invisible,test_invisible_index"; @@ -374,10 +374,10 @@ explain select * from t1 where invisible =9; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL invisible_2 NULL NULL NULL 7 Using where show indexes in t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 b 1 b A NULL NULL NULL YES BTREE -t1 1 invisible 1 c A NULL NULL NULL YES BTREE -t1 1 invisible_2 1 invisible A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 b 1 b A NULL NULL NULL YES BTREE NO +t1 1 invisible 1 c A NULL NULL NULL YES BTREE NO +t1 1 invisible_2 1 invisible A NULL NULL NULL YES BTREE NO drop table t1; set @old_debug= @@debug_dbug; CREATE TABLE t1 (i INT ); diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index d8eb9795b4e..04b541d2040 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -3337,9 +3337,9 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK set myisam_stats_method=@tmp1; show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 pk A 1010 NULL NULL BTREE -t1 0 a 1 a A 1010 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 pk A 1010 NULL NULL BTREE NO +t1 0 a 1 a A 1010 NULL NULL YES BTREE NO # t1 must use eq_ref(t1.a=t0.a) and rows must be 1 (and not 45): explain select * from t0,t1 where t0.a=t1.a; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/main/key.result b/mysql-test/main/key.result index 78a2f42c477..c35dbbc4754 100644 --- a/mysql-test/main/key.result +++ b/mysql-test/main/key.result @@ -148,12 +148,12 @@ a 2 drop table t1; create table t1 (a int not null unique, b int unique, c int, d int not null primary key, key(c), e int not null unique); show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 d A 0 NULL NULL BTREE -t1 0 a 1 a A 0 NULL NULL BTREE -t1 0 e 1 e A 0 NULL NULL BTREE -t1 0 b 1 b A NULL NULL NULL YES BTREE -t1 1 c 1 c A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 d A 0 NULL NULL BTREE NO +t1 0 a 1 a A 0 NULL NULL BTREE NO +t1 0 e 1 e A 0 NULL NULL BTREE NO +t1 0 b 1 b A NULL NULL NULL YES BTREE NO +t1 1 c 1 c A NULL NULL NULL YES BTREE NO drop table t1; CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT, UNIQUE (c,i)); diff --git a/mysql-test/main/long_unique.result b/mysql-test/main/long_unique.result index 29cb1a881ca..744e303b198 100644 --- a/mysql-test/main/long_unique.result +++ b/mysql-test/main/long_unique.result @@ -38,6 +38,7 @@ Null YES Index_type HASH Comment Index_comment +Ignored NO MyISAM file: DATADIR/test/t1 Record format: Packed @@ -52,8 +53,8 @@ select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION def test t1 a 1 NULL YES blob 65535 65535 NULL NULL NULL NULL NULL blob UNI NEVER NULL select * from information_schema.statistics where table_schema = 'test' and table_name = 't1'; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT -def test t1 0 test a 1 a A NULL NULL NULL YES HASH +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED +def test t1 0 test a 1 a A NULL NULL NULL YES HASH NO select * from information_schema.key_column_usage where table_schema= 'test' and table_name= 't1'; CONSTRAINT_CATALOG def CONSTRAINT_SCHEMA test @@ -211,8 +212,8 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE NO #add column with unique index on blob ; alter table t1 add column a blob unique; show create table t1; @@ -240,9 +241,9 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `a` (`aa`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE -t1 0 a 1 aa A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE NO +t1 0 a 1 aa A NULL NULL NULL YES HASH NO # try to change the blob unique datatype; #this will change index to b tree; alter table t1 modify column aa int ; @@ -258,9 +259,9 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `a` (`aa`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE -t1 0 a 1 aa A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE NO +t1 0 a 1 aa A NULL NULL NULL YES BTREE NO alter table t1 add column clm blob unique; #try changing the name ; alter table t1 change column clm clm_changed blob; @@ -278,10 +279,10 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `clm` (`clm_changed`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE -t1 0 a 1 aa A NULL NULL NULL YES BTREE -t1 0 clm 1 clm_changed A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE NO +t1 0 a 1 aa A NULL NULL NULL YES BTREE NO +t1 0 clm 1 clm_changed A NULL NULL NULL YES HASH NO #now drop the unique key; alter table t1 drop key clm; show create table t1; @@ -297,17 +298,17 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `a` (`aa`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE -t1 0 a 1 aa A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE NO +t1 0 a 1 aa A NULL NULL NULL YES BTREE NO drop table t1; create table t1 (a TEXT CHARSET latin1 COLLATE latin1_german2_ci unique); desc t1; Field Type Null Key Default Extra a text YES UNI NULL show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES HASH NO insert into t1 values ('ae'); insert into t1 values ('AE'); ERROR 23000: Duplicate entry 'AE' for key 'a' @@ -319,9 +320,9 @@ Field Type Null Key Default Extra a int(11) NO PRI NULL b blob YES UNI NULL show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 a A 0 NULL NULL BTREE -t1 0 b 1 b A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 a A 0 NULL NULL BTREE NO +t1 0 b 1 b A NULL NULL NULL YES HASH NO insert into t1 values(1,1),(2,2),(3,3); insert into t1 values(1,1); ERROR 23000: Duplicate entry '1' for key 'b' @@ -356,11 +357,11 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `e` (`e`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH -t1 0 c 1 c A NULL NULL NULL YES HASH -t1 0 d 1 d A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES HASH NO +t1 0 c 1 c A NULL NULL NULL YES HASH NO +t1 0 d 1 d A NULL NULL NULL YES HASH NO +t1 0 e 1 e A NULL NULL NULL YES HASH NO MyISAM file: DATADIR/test/t1 Record format: Packed @@ -382,11 +383,11 @@ def test t1 c 3 NULL YES blob 65535 65535 NULL NULL NULL NULL NULL blob UNI NE def test t1 d 4 NULL YES text 65535 65535 NULL NULL NULL latin1 latin1_swedish_ci text UNI NEVER NULL def test t1 e 5 NULL YES varchar 3000 3000 NULL NULL NULL latin1 latin1_swedish_ci varchar(3000) UNI NEVER NULL select * from information_schema.statistics where table_schema = 'test' and table_name = 't1'; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT -def test t1 0 test a 1 a A NULL NULL NULL YES HASH -def test t1 0 test c 1 c A NULL NULL NULL YES HASH -def test t1 0 test d 1 d A NULL NULL NULL YES HASH -def test t1 0 test e 1 e A NULL NULL NULL YES HASH +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED +def test t1 0 test a 1 a A NULL NULL NULL YES HASH NO +def test t1 0 test c 1 c A NULL NULL NULL YES HASH NO +def test t1 0 test d 1 d A NULL NULL NULL YES HASH NO +def test t1 0 test e 1 e A NULL NULL NULL YES HASH NO select * from information_schema.key_column_usage where table_schema= 'test' and table_name= 't1'; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME def test a def test t1 a 1 NULL NULL NULL NULL @@ -549,11 +550,11 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `e` (`e`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE -t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE -t1 0 d 1 d A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE NO +t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE NO +t1 0 d 1 d A NULL NULL NULL YES HASH NO +t1 0 e 1 e A NULL NULL NULL YES HASH NO #add column with unique index on blob; alter table t1 add column a blob unique; show create table t1; @@ -574,12 +575,12 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `a` (`a`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE -t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE -t1 0 d 1 d A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH -t1 0 a 1 a A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE NO +t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE NO +t1 0 d 1 d A NULL NULL NULL YES HASH NO +t1 0 e 1 e A NULL NULL NULL YES HASH NO +t1 0 a 1 a A NULL NULL NULL YES HASH NO #try to change the blob unique column name; #this will change index to b tree; alter table t1 modify column a int , modify column e int; @@ -601,12 +602,12 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `d` (`d`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE -t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE -t1 0 e 1 e A NULL NULL NULL YES BTREE -t1 0 a 1 a A NULL NULL NULL YES BTREE -t1 0 d 1 d A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE NO +t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE NO +t1 0 e 1 e A NULL NULL NULL YES BTREE NO +t1 0 a 1 a A NULL NULL NULL YES BTREE NO +t1 0 d 1 d A NULL NULL NULL YES HASH NO alter table t1 add column clm1 blob unique,add column clm2 blob unique; #try changing the name; alter table t1 change column clm1 clm_changed1 blob, change column clm2 clm_changed2 blob; @@ -632,14 +633,14 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `clm2` (`clm_changed2`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE -t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE -t1 0 e 1 e A NULL NULL NULL YES BTREE -t1 0 a 1 a A NULL NULL NULL YES BTREE -t1 0 d 1 d A NULL NULL NULL YES HASH -t1 0 clm1 1 clm_changed1 A NULL NULL NULL YES HASH -t1 0 clm2 1 clm_changed2 A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE NO +t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE NO +t1 0 e 1 e A NULL NULL NULL YES BTREE NO +t1 0 a 1 a A NULL NULL NULL YES BTREE NO +t1 0 d 1 d A NULL NULL NULL YES HASH NO +t1 0 clm1 1 clm_changed1 A NULL NULL NULL YES HASH NO +t1 0 clm2 1 clm_changed2 A NULL NULL NULL YES HASH NO #now drop the unique key; alter table t1 drop key clm1, drop key clm2; show create table t1; @@ -662,12 +663,12 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `d` (`d`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE -t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE -t1 0 e 1 e A NULL NULL NULL YES BTREE -t1 0 a 1 a A NULL NULL NULL YES BTREE -t1 0 d 1 d A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE NO +t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE NO +t1 0 e 1 e A NULL NULL NULL YES BTREE NO +t1 0 a 1 a A NULL NULL NULL YES BTREE NO +t1 0 d 1 d A NULL NULL NULL YES HASH NO drop table t1; #now the table with key on multiple columns; the ultimate test; create table t1(a blob, b int , c varchar(2000) , d text , e varchar(3000) , f longblob , g int , h text , @@ -703,21 +704,21 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `b` (`b`,`d`,`g`,`h`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH -t1 0 a 2 b A NULL NULL NULL YES HASH -t1 0 a 3 c A NULL NULL NULL YES HASH -t1 0 c 1 c A NULL NULL NULL YES HASH -t1 0 c 2 d A NULL NULL NULL YES HASH -t1 0 c 3 e A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH -t1 0 e 2 f A NULL NULL NULL YES HASH -t1 0 e 3 g A NULL NULL NULL YES HASH -t1 0 e 4 h A NULL NULL NULL YES HASH -t1 0 b 1 b A NULL NULL NULL YES HASH -t1 0 b 2 d A NULL NULL NULL YES HASH -t1 0 b 3 g A NULL NULL NULL YES HASH -t1 0 b 4 h A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES HASH NO +t1 0 a 2 b A NULL NULL NULL YES HASH NO +t1 0 a 3 c A NULL NULL NULL YES HASH NO +t1 0 c 1 c A NULL NULL NULL YES HASH NO +t1 0 c 2 d A NULL NULL NULL YES HASH NO +t1 0 c 3 e A NULL NULL NULL YES HASH NO +t1 0 e 1 e A NULL NULL NULL YES HASH NO +t1 0 e 2 f A NULL NULL NULL YES HASH NO +t1 0 e 3 g A NULL NULL NULL YES HASH NO +t1 0 e 4 h A NULL NULL NULL YES HASH NO +t1 0 b 1 b A NULL NULL NULL YES HASH NO +t1 0 b 2 d A NULL NULL NULL YES HASH NO +t1 0 b 3 g A NULL NULL NULL YES HASH NO +t1 0 b 4 h A NULL NULL NULL YES HASH NO MyISAM file: DATADIR/test/t1 Record format: Packed @@ -742,21 +743,21 @@ def test t1 f 6 NULL YES longblob 4294967295 4294967295 NULL NULL NULL NULL NULL def test t1 g 7 NULL YES int NULL NULL 10 0 NULL NULL NULL int(11) NEVER NULL def test t1 h 8 NULL YES text 65535 65535 NULL NULL NULL latin1 latin1_swedish_ci text NEVER NULL select * from information_schema.statistics where table_schema = 'test' and table_name = 't1'; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT -def test t1 0 test a 1 a A NULL NULL NULL YES HASH -def test t1 0 test a 2 b A NULL NULL NULL YES HASH -def test t1 0 test a 3 c A NULL NULL NULL YES HASH -def test t1 0 test c 1 c A NULL NULL NULL YES HASH -def test t1 0 test c 2 d A NULL NULL NULL YES HASH -def test t1 0 test c 3 e A NULL NULL NULL YES HASH -def test t1 0 test e 1 e A NULL NULL NULL YES HASH -def test t1 0 test e 2 f A NULL NULL NULL YES HASH -def test t1 0 test e 3 g A NULL NULL NULL YES HASH -def test t1 0 test e 4 h A NULL NULL NULL YES HASH -def test t1 0 test b 1 b A NULL NULL NULL YES HASH -def test t1 0 test b 2 d A NULL NULL NULL YES HASH -def test t1 0 test b 3 g A NULL NULL NULL YES HASH -def test t1 0 test b 4 h A NULL NULL NULL YES HASH +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED +def test t1 0 test a 1 a A NULL NULL NULL YES HASH NO +def test t1 0 test a 2 b A NULL NULL NULL YES HASH NO +def test t1 0 test a 3 c A NULL NULL NULL YES HASH NO +def test t1 0 test c 1 c A NULL NULL NULL YES HASH NO +def test t1 0 test c 2 d A NULL NULL NULL YES HASH NO +def test t1 0 test c 3 e A NULL NULL NULL YES HASH NO +def test t1 0 test e 1 e A NULL NULL NULL YES HASH NO +def test t1 0 test e 2 f A NULL NULL NULL YES HASH NO +def test t1 0 test e 3 g A NULL NULL NULL YES HASH NO +def test t1 0 test e 4 h A NULL NULL NULL YES HASH NO +def test t1 0 test b 1 b A NULL NULL NULL YES HASH NO +def test t1 0 test b 2 d A NULL NULL NULL YES HASH NO +def test t1 0 test b 3 g A NULL NULL NULL YES HASH NO +def test t1 0 test b 4 h A NULL NULL NULL YES HASH NO select * from information_schema.key_column_usage where table_schema= 'test' and table_name= 't1'; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME def test a def test t1 a 1 NULL NULL NULL NULL @@ -859,21 +860,21 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `b` (`b`,`d`,`g`,`h`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH -t1 0 a 2 b A NULL NULL NULL YES HASH -t1 0 a 3 c A NULL NULL NULL YES HASH -t1 0 c 1 c A NULL NULL NULL YES HASH -t1 0 c 2 d A NULL NULL NULL YES HASH -t1 0 c 3 e A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH -t1 0 e 2 f A NULL NULL NULL YES HASH -t1 0 e 3 g A NULL NULL NULL YES HASH -t1 0 e 4 h A NULL NULL NULL YES HASH -t1 0 b 1 b A NULL NULL NULL YES HASH -t1 0 b 2 d A NULL NULL NULL YES HASH -t1 0 b 3 g A NULL NULL NULL YES HASH -t1 0 b 4 h A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES HASH NO +t1 0 a 2 b A NULL NULL NULL YES HASH NO +t1 0 a 3 c A NULL NULL NULL YES HASH NO +t1 0 c 1 c A NULL NULL NULL YES HASH NO +t1 0 c 2 d A NULL NULL NULL YES HASH NO +t1 0 c 3 e A NULL NULL NULL YES HASH NO +t1 0 e 1 e A NULL NULL NULL YES HASH NO +t1 0 e 2 f A NULL NULL NULL YES HASH NO +t1 0 e 3 g A NULL NULL NULL YES HASH NO +t1 0 e 4 h A NULL NULL NULL YES HASH NO +t1 0 b 1 b A NULL NULL NULL YES HASH NO +t1 0 b 2 d A NULL NULL NULL YES HASH NO +t1 0 b 3 g A NULL NULL NULL YES HASH NO +t1 0 b 4 h A NULL NULL NULL YES HASH NO alter table t1 drop column db_row_hash_7 , drop column db_row_hash_5 , drop column db_row_hash_1, drop column db_row_hash_2 ; show create table t1; @@ -893,21 +894,21 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `b` (`b`,`d`,`g`,`h`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH -t1 0 a 2 b A NULL NULL NULL YES HASH -t1 0 a 3 c A NULL NULL NULL YES HASH -t1 0 c 1 c A NULL NULL NULL YES HASH -t1 0 c 2 d A NULL NULL NULL YES HASH -t1 0 c 3 e A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH -t1 0 e 2 f A NULL NULL NULL YES HASH -t1 0 e 3 g A NULL NULL NULL YES HASH -t1 0 e 4 h A NULL NULL NULL YES HASH -t1 0 b 1 b A NULL NULL NULL YES HASH -t1 0 b 2 d A NULL NULL NULL YES HASH -t1 0 b 3 g A NULL NULL NULL YES HASH -t1 0 b 4 h A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES HASH NO +t1 0 a 2 b A NULL NULL NULL YES HASH NO +t1 0 a 3 c A NULL NULL NULL YES HASH NO +t1 0 c 1 c A NULL NULL NULL YES HASH NO +t1 0 c 2 d A NULL NULL NULL YES HASH NO +t1 0 c 3 e A NULL NULL NULL YES HASH NO +t1 0 e 1 e A NULL NULL NULL YES HASH NO +t1 0 e 2 f A NULL NULL NULL YES HASH NO +t1 0 e 3 g A NULL NULL NULL YES HASH NO +t1 0 e 4 h A NULL NULL NULL YES HASH NO +t1 0 b 1 b A NULL NULL NULL YES HASH NO +t1 0 b 2 d A NULL NULL NULL YES HASH NO +t1 0 b 3 g A NULL NULL NULL YES HASH NO +t1 0 b 4 h A NULL NULL NULL YES HASH NO #try to change column names; alter table t1 change column a aa blob , change column b bb blob , change column d dd blob; show create table t1; @@ -927,21 +928,21 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `b` (`bb`,`dd`,`g`,`h`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 aa A NULL NULL NULL YES HASH -t1 0 a 2 bb A NULL NULL NULL YES HASH -t1 0 a 3 c A NULL NULL NULL YES HASH -t1 0 c 1 c A NULL NULL NULL YES HASH -t1 0 c 2 dd A NULL NULL NULL YES HASH -t1 0 c 3 e A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH -t1 0 e 2 f A NULL NULL NULL YES HASH -t1 0 e 3 g A NULL NULL NULL YES HASH -t1 0 e 4 h A NULL NULL NULL YES HASH -t1 0 b 1 bb A NULL NULL NULL YES HASH -t1 0 b 2 dd A NULL NULL NULL YES HASH -t1 0 b 3 g A NULL NULL NULL YES HASH -t1 0 b 4 h A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 aa A NULL NULL NULL YES HASH NO +t1 0 a 2 bb A NULL NULL NULL YES HASH NO +t1 0 a 3 c A NULL NULL NULL YES HASH NO +t1 0 c 1 c A NULL NULL NULL YES HASH NO +t1 0 c 2 dd A NULL NULL NULL YES HASH NO +t1 0 c 3 e A NULL NULL NULL YES HASH NO +t1 0 e 1 e A NULL NULL NULL YES HASH NO +t1 0 e 2 f A NULL NULL NULL YES HASH NO +t1 0 e 3 g A NULL NULL NULL YES HASH NO +t1 0 e 4 h A NULL NULL NULL YES HASH NO +t1 0 b 1 bb A NULL NULL NULL YES HASH NO +t1 0 b 2 dd A NULL NULL NULL YES HASH NO +t1 0 b 3 g A NULL NULL NULL YES HASH NO +t1 0 b 4 h A NULL NULL NULL YES HASH NO alter table t1 change column aa a blob , change column bb b blob , change column dd d blob; show create table t1; Table Create Table @@ -960,21 +961,21 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `b` (`b`,`d`,`g`,`h`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH -t1 0 a 2 b A NULL NULL NULL YES HASH -t1 0 a 3 c A NULL NULL NULL YES HASH -t1 0 c 1 c A NULL NULL NULL YES HASH -t1 0 c 2 d A NULL NULL NULL YES HASH -t1 0 c 3 e A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH -t1 0 e 2 f A NULL NULL NULL YES HASH -t1 0 e 3 g A NULL NULL NULL YES HASH -t1 0 e 4 h A NULL NULL NULL YES HASH -t1 0 b 1 b A NULL NULL NULL YES HASH -t1 0 b 2 d A NULL NULL NULL YES HASH -t1 0 b 3 g A NULL NULL NULL YES HASH -t1 0 b 4 h A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES HASH NO +t1 0 a 2 b A NULL NULL NULL YES HASH NO +t1 0 a 3 c A NULL NULL NULL YES HASH NO +t1 0 c 1 c A NULL NULL NULL YES HASH NO +t1 0 c 2 d A NULL NULL NULL YES HASH NO +t1 0 c 3 e A NULL NULL NULL YES HASH NO +t1 0 e 1 e A NULL NULL NULL YES HASH NO +t1 0 e 2 f A NULL NULL NULL YES HASH NO +t1 0 e 3 g A NULL NULL NULL YES HASH NO +t1 0 e 4 h A NULL NULL NULL YES HASH NO +t1 0 b 1 b A NULL NULL NULL YES HASH NO +t1 0 b 2 d A NULL NULL NULL YES HASH NO +t1 0 b 3 g A NULL NULL NULL YES HASH NO +t1 0 b 4 h A NULL NULL NULL YES HASH NO #now we will change the data type to int and varchar limit so that we no longer require hash_index; #on key a_b_c; alter table t1 modify column a varchar(20) , modify column b varchar(20) , modify column c varchar(20); @@ -995,21 +996,21 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `b` (`b`,`d`,`g`,`h`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES BTREE -t1 0 a 2 b A NULL NULL NULL YES BTREE -t1 0 a 3 c A NULL NULL NULL YES BTREE -t1 0 c 1 c A NULL NULL NULL YES HASH -t1 0 c 2 d A NULL NULL NULL YES HASH -t1 0 c 3 e A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH -t1 0 e 2 f A NULL NULL NULL YES HASH -t1 0 e 3 g A NULL NULL NULL YES HASH -t1 0 e 4 h A NULL NULL NULL YES HASH -t1 0 b 1 b A NULL NULL NULL YES HASH -t1 0 b 2 d A NULL NULL NULL YES HASH -t1 0 b 3 g A NULL NULL NULL YES HASH -t1 0 b 4 h A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES BTREE NO +t1 0 a 2 b A NULL NULL NULL YES BTREE NO +t1 0 a 3 c A NULL NULL NULL YES BTREE NO +t1 0 c 1 c A NULL NULL NULL YES HASH NO +t1 0 c 2 d A NULL NULL NULL YES HASH NO +t1 0 c 3 e A NULL NULL NULL YES HASH NO +t1 0 e 1 e A NULL NULL NULL YES HASH NO +t1 0 e 2 f A NULL NULL NULL YES HASH NO +t1 0 e 3 g A NULL NULL NULL YES HASH NO +t1 0 e 4 h A NULL NULL NULL YES HASH NO +t1 0 b 1 b A NULL NULL NULL YES HASH NO +t1 0 b 2 d A NULL NULL NULL YES HASH NO +t1 0 b 3 g A NULL NULL NULL YES HASH NO +t1 0 b 4 h A NULL NULL NULL YES HASH NO #change it back; alter table t1 modify column a blob , modify column b blob , modify column c blob; show create table t1; @@ -1029,21 +1030,21 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `b` (`b`,`d`,`g`,`h`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH -t1 0 a 2 b A NULL NULL NULL YES HASH -t1 0 a 3 c A NULL NULL NULL YES HASH -t1 0 c 1 c A NULL NULL NULL YES HASH -t1 0 c 2 d A NULL NULL NULL YES HASH -t1 0 c 3 e A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH -t1 0 e 2 f A NULL NULL NULL YES HASH -t1 0 e 3 g A NULL NULL NULL YES HASH -t1 0 e 4 h A NULL NULL NULL YES HASH -t1 0 b 1 b A NULL NULL NULL YES HASH -t1 0 b 2 d A NULL NULL NULL YES HASH -t1 0 b 3 g A NULL NULL NULL YES HASH -t1 0 b 4 h A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES HASH NO +t1 0 a 2 b A NULL NULL NULL YES HASH NO +t1 0 a 3 c A NULL NULL NULL YES HASH NO +t1 0 c 1 c A NULL NULL NULL YES HASH NO +t1 0 c 2 d A NULL NULL NULL YES HASH NO +t1 0 c 3 e A NULL NULL NULL YES HASH NO +t1 0 e 1 e A NULL NULL NULL YES HASH NO +t1 0 e 2 f A NULL NULL NULL YES HASH NO +t1 0 e 3 g A NULL NULL NULL YES HASH NO +t1 0 e 4 h A NULL NULL NULL YES HASH NO +t1 0 b 1 b A NULL NULL NULL YES HASH NO +t1 0 b 2 d A NULL NULL NULL YES HASH NO +t1 0 b 3 g A NULL NULL NULL YES HASH NO +t1 0 b 4 h A NULL NULL NULL YES HASH NO #try to delete blob column in unique; truncate table t1; #now try to delete keys; @@ -1063,14 +1064,14 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `b` (`b`,`d`,`g`,`h`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH -t1 0 a 2 b A NULL NULL NULL YES HASH -t1 0 a 3 c A NULL NULL NULL YES HASH -t1 0 b 1 b A NULL NULL NULL YES HASH -t1 0 b 2 d A NULL NULL NULL YES HASH -t1 0 b 3 g A NULL NULL NULL YES HASH -t1 0 b 4 h A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES HASH NO +t1 0 a 2 b A NULL NULL NULL YES HASH NO +t1 0 a 3 c A NULL NULL NULL YES HASH NO +t1 0 b 1 b A NULL NULL NULL YES HASH NO +t1 0 b 2 d A NULL NULL NULL YES HASH NO +t1 0 b 3 g A NULL NULL NULL YES HASH NO +t1 0 b 4 h A NULL NULL NULL YES HASH NO drop table t1; #now alter table containing some data basically some tests with ignore; create table t1 (a blob); @@ -1097,8 +1098,8 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `a` (`a`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES HASH NO drop table t1; #Now with multiple keys; create table t1(a blob , b blob, c blob , d blob , e int); @@ -1135,12 +1136,12 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `b` (`b`,`d`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 e 1 e A NULL NULL NULL YES BTREE -t1 0 a 1 a A NULL NULL NULL YES HASH -t1 0 a 2 c A NULL NULL NULL YES HASH -t1 0 b 1 b A NULL NULL NULL YES HASH -t1 0 b 2 d A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 e 1 e A NULL NULL NULL YES BTREE NO +t1 0 a 1 a A NULL NULL NULL YES HASH NO +t1 0 a 2 c A NULL NULL NULL YES HASH NO +t1 0 b 1 b A NULL NULL NULL YES HASH NO +t1 0 b 2 d A NULL NULL NULL YES HASH NO drop table t1; #visibility of db_row_hash create table t1 (a blob unique , b blob unique); @@ -1248,10 +1249,10 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `b` (`b`,`c`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH -t1 0 b 1 b A NULL NULL NULL YES HASH -t1 0 b 2 c A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES HASH NO +t1 0 b 1 b A NULL NULL NULL YES HASH NO +t1 0 b 2 c A NULL NULL NULL YES HASH NO insert into t1 values(concat(repeat('sachin',10000000),'1'),concat(repeat('sachin',10000000),'1'), concat(repeat('sachin',10000000),'1')); insert into t1 values(concat(repeat('sachin',10000000),'2'),concat(repeat('sachin',10000000),'2'), @@ -1269,8 +1270,8 @@ desc t1; Field Type Null Key Default Extra a blob YES UNI NULL show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL 3000 NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL 3000 NULL YES HASH NO show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -1301,11 +1302,11 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `c` (`c`(4500),`d`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL 3500 NULL YES HASH -t1 0 a 2 b A NULL NULL NULL YES HASH -t1 0 c 1 c A NULL 4500 NULL YES HASH -t1 0 c 2 d A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL 3500 NULL YES HASH NO +t1 0 a 2 b A NULL NULL NULL YES HASH NO +t1 0 c 1 c A NULL 4500 NULL YES HASH NO +t1 0 c 2 d A NULL NULL NULL YES HASH NO drop table t1; disconnect newcon; connection default; diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index 5f9715a14ee..d4bb64581de 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -82,6 +82,7 @@ Null YES Index_type HASH Comment Index_comment +Ignored NO insert into t1 values(1,1); ERROR 23000: Duplicate entry '1' for key 'a' DROP TABLE t1; @@ -128,6 +129,7 @@ Null Index_type BTREE Comment Index_comment +Ignored NO ALTER TABLE t1 ADD INDEX (pk); DROP TABLE t1; CREATE TABLE t1 (b int, a varchar(4000)); @@ -280,14 +282,14 @@ DROP TABLE t1, t2; SET binlog_row_image= FULL; CREATE TABLE t1 (a int, b VARCHAR(1000), UNIQUE (a,b)) ENGINE=MyISAM; show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH -t1 0 a 2 b A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES HASH NO +t1 0 a 2 b A NULL NULL NULL YES HASH NO CREATE TABLE t2 (a varchar(900), b VARCHAR(900), UNIQUE (a,b)) ENGINE=MyISAM; show index from t2; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t2 0 a 1 a A NULL NULL NULL YES HASH -t2 0 a 2 b A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t2 0 a 1 a A NULL NULL NULL YES HASH NO +t2 0 a 2 b A NULL NULL NULL YES HASH NO DROP TABLE t1,t2; create temporary table tmp ( a int, b int, c blob not null, d int, e int default 0, f int, unique key (c)) engine=innodb; create table t2 (x int); diff --git a/mysql-test/main/long_unique_update.result b/mysql-test/main/long_unique_update.result index b508583f47c..1741ec5310a 100644 --- a/mysql-test/main/long_unique_update.result +++ b/mysql-test/main/long_unique_update.result @@ -24,6 +24,7 @@ Null YES Index_type HASH Comment Index_comment +Ignored NO insert into t1 values(1),(2),(3),(4),(5); select * from t1; a @@ -69,10 +70,10 @@ drop table t1; #test 2; create table t1 (a int primary key, b blob unique , c int unique ); show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 a A 0 NULL NULL BTREE -t1 0 c 1 c A NULL NULL NULL YES BTREE -t1 0 b 1 b A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 a A 0 NULL NULL BTREE NO +t1 0 c 1 c A NULL NULL NULL YES BTREE NO +t1 0 b 1 b A NULL NULL NULL YES HASH NO insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7); select * from t1 limit 3; a b c @@ -129,10 +130,10 @@ drop table t1; #test 3; create table t1 (a blob unique, b blob unique , c blob unique); show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH -t1 0 b 1 b A NULL NULL NULL YES HASH -t1 0 c 1 c A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES HASH NO +t1 0 b 1 b A NULL NULL NULL YES HASH NO +t1 0 c 1 c A NULL NULL NULL YES HASH NO insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7); select * from t1 limit 3; a b c @@ -226,21 +227,21 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `a` (`a`,`b`,`c`,`d`,`e`,`f`,`g`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 a A 0 NULL NULL BTREE -t1 0 b_2 1 b A NULL NULL NULL YES BTREE -t1 0 b_2 2 f A NULL NULL NULL YES BTREE -t1 0 b 1 b A NULL NULL NULL YES HASH -t1 0 b 2 c A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH -t1 0 e 2 g A NULL NULL NULL YES HASH -t1 0 a 1 a A NULL NULL NULL HASH -t1 0 a 2 b A NULL NULL NULL YES HASH -t1 0 a 3 c A NULL NULL NULL YES HASH -t1 0 a 4 d A NULL NULL NULL YES HASH -t1 0 a 5 e A NULL NULL NULL YES HASH -t1 0 a 6 f A NULL NULL NULL YES HASH -t1 0 a 7 g A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 a A 0 NULL NULL BTREE NO +t1 0 b_2 1 b A NULL NULL NULL YES BTREE NO +t1 0 b_2 2 f A NULL NULL NULL YES BTREE NO +t1 0 b 1 b A NULL NULL NULL YES HASH NO +t1 0 b 2 c A NULL NULL NULL YES HASH NO +t1 0 e 1 e A NULL NULL NULL YES HASH NO +t1 0 e 2 g A NULL NULL NULL YES HASH NO +t1 0 a 1 a A NULL NULL NULL HASH NO +t1 0 a 2 b A NULL NULL NULL YES HASH NO +t1 0 a 3 c A NULL NULL NULL YES HASH NO +t1 0 a 4 d A NULL NULL NULL YES HASH NO +t1 0 a 5 e A NULL NULL NULL YES HASH NO +t1 0 a 6 f A NULL NULL NULL YES HASH NO +t1 0 a 7 g A NULL NULL NULL YES HASH NO insert into t1 values(1,1,1,1,1,1,1),(2,2,2,2,2,2,2),(3,3,3,3,3,3,3),(4,4,4,4,4,4,4), (5,5,5,5,5,5,5),(6,6,6,6,6,6,6),(7,7,7,7,7,7,7),(8,8,8,8,8,8,8),(9,9,9,9,9,9,9); select * from t1 limit 3; diff --git a/mysql-test/main/long_unique_using_hash.result b/mysql-test/main/long_unique_using_hash.result index 987e11294ec..619b0f5baa5 100644 --- a/mysql-test/main/long_unique_using_hash.result +++ b/mysql-test/main/long_unique_using_hash.result @@ -13,6 +13,7 @@ Null YES Index_type HASH Comment Index_comment +Ignored NO drop table t1; create table t1(a blob , unique(a) using btree); ERROR 42000: Specified key was too long; max key length is 1000 bytes @@ -31,23 +32,24 @@ Null YES Index_type HASH Comment Index_comment +Ignored NO drop table t1; create table t1(a int ,b int , c int, unique(a, b, c) using hash); show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH -t1 0 a 2 b A NULL NULL NULL YES HASH -t1 0 a 3 c A NULL NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a A NULL NULL NULL YES HASH NO +t1 0 a 2 b A NULL NULL NULL YES HASH NO +t1 0 a 3 c A NULL NULL NULL YES HASH NO insert into t1 values(1,1,1); insert into t1 values(1,1,1); ERROR 23000: Duplicate entry '1-1-1' for key 'a' drop table t1; create table t1(a int ,b int , c int, unique(a, b, c) using hash) engine=memory; show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a NULL NULL NULL NULL YES HASH -t1 0 a 2 b NULL NULL NULL NULL YES HASH -t1 0 a 3 c NULL 0 NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 a 1 a NULL NULL NULL NULL YES HASH NO +t1 0 a 2 b NULL NULL NULL NULL YES HASH NO +t1 0 a 3 c NULL 0 NULL NULL YES HASH NO insert into t1 values(1,1,1); insert into t1 values(1,1,1); ERROR 23000: Duplicate entry '1-1-1' for key 'a' diff --git a/mysql-test/main/merge.result b/mysql-test/main/merge.result index 0d6f387a7bc..d1950d03939 100644 --- a/mysql-test/main/merge.result +++ b/mysql-test/main/merge.result @@ -760,10 +760,10 @@ a b c 1 1 1 1 1 0 show index from t3; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t3 1 a 1 a A NULL NULL NULL YES BTREE -t3 1 a 2 b A NULL NULL NULL YES BTREE -t3 1 a 3 c A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t3 1 a 1 a A NULL NULL NULL YES BTREE NO +t3 1 a 2 b A NULL NULL NULL YES BTREE NO +t3 1 a 3 c A NULL NULL NULL YES BTREE NO drop table t1, t2, t3; CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), UNIQUE (b) ) ENGINE=MyISAM; diff --git a/mysql-test/main/mix2_myisam.result b/mysql-test/main/mix2_myisam.result index 5a228f63502..dd2af645767 100644 --- a/mysql-test/main/mix2_myisam.result +++ b/mysql-test/main/mix2_myisam.result @@ -170,10 +170,10 @@ optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 id A # NULL NULL BTREE -t1 1 parent_id 1 parent_id A # NULL NULL BTREE -t1 1 level 1 level A # NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 id A # NULL NULL BTREE NO +t1 1 parent_id 1 parent_id A # NULL NULL BTREE NO +t1 1 level 1 level A # NULL NULL BTREE NO drop table t1; CREATE TABLE t1 ( gesuchnr int(11) DEFAULT '0' NOT NULL, @@ -214,8 +214,8 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 skr 1 a A # NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 skr 1 a A # NULL NULL YES BTREE NO drop table t1; create table t1 (a int,b varchar(20),key(a)) engine=MyISAM; insert into t1 values (1,""), (2,"testing"); @@ -261,13 +261,13 @@ key(a),primary key(a,b), unique(c),key(a),unique(b)) ENGINE = MyISAM; Warnings: Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 a A # NULL NULL BTREE -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 +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 a A # NULL NULL BTREE NO +t1 0 PRIMARY 2 b A # NULL NULL BTREE NO +t1 0 c 1 c A # NULL NULL BTREE NO +t1 0 b 1 b A # NULL NULL BTREE NO +t1 1 a 1 a A # NULL NULL BTREE NO +t1 1 a_2 1 a A # NULL NULL BTREE NO drop table t1; create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = MEMORY; alter table t1 engine=MyISAM; @@ -639,8 +639,8 @@ optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 a A # NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 a A # NULL NULL BTREE NO drop table t1; create table t1 (i int, j int ) ENGINE=MyISAM; insert into t1 values (1,2); diff --git a/mysql-test/main/myisam.result b/mysql-test/main/myisam.result index a2a9d4854ee..0f68171c6c2 100644 --- a/mysql-test/main/myisam.result +++ b/mysql-test/main/myisam.result @@ -40,16 +40,16 @@ optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 a A 5 NULL NULL BTREE -t1 1 b 1 b A 1 NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 a A 5 NULL NULL BTREE NO +t1 1 b 1 b A 1 NULL NULL BTREE NO optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status Table is already up to date show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 a A 5 NULL NULL BTREE -t1 1 b 1 b A 1 NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 a A 5 NULL NULL BTREE NO +t1 1 b 1 b A 1 NULL NULL BTREE NO drop table t1; create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=myisam; insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); @@ -339,13 +339,13 @@ optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 b 1 b A 5 NULL NULL YES BTREE -t1 1 c 1 c A 5 NULL NULL YES BTREE -t1 1 a 1 a A 1 NULL NULL BTREE -t1 1 a 2 b A 5 NULL NULL YES BTREE -t1 1 c_2 1 c A 5 NULL NULL YES BTREE -t1 1 c_2 2 a A 5 NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 b 1 b A 5 NULL NULL YES BTREE NO +t1 1 c 1 c A 5 NULL NULL YES BTREE NO +t1 1 a 1 a A 1 NULL NULL BTREE NO +t1 1 a 2 b A 5 NULL NULL YES BTREE NO +t1 1 c_2 1 c A 5 NULL NULL YES BTREE NO +t1 1 c_2 2 a A 5 NULL NULL BTREE NO explain select * from t1,t2 where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL a NULL NULL NULL 2 @@ -363,13 +363,13 @@ optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 b 1 b A 10 NULL NULL YES BTREE -t1 1 c 1 c A 10 NULL NULL YES BTREE -t1 1 a 1 a A 10 NULL NULL BTREE -t1 1 a 2 b A 10 NULL NULL YES BTREE -t1 1 c_2 1 c A 10 NULL NULL YES BTREE -t1 1 c_2 2 a A 10 NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 b 1 b A 10 NULL NULL YES BTREE NO +t1 1 c 1 c A 10 NULL NULL YES BTREE NO +t1 1 a 1 a A 10 NULL NULL BTREE NO +t1 1 a 2 b A 10 NULL NULL YES BTREE NO +t1 1 c_2 1 c A 10 NULL NULL YES BTREE NO +t1 1 c_2 2 a A 10 NULL NULL BTREE NO explain select * from t1,t2 where t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL b NULL NULL NULL 2 Using where @@ -379,13 +379,13 @@ optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 b 1 b A 5 NULL NULL YES BTREE -t1 1 c 1 c A 5 NULL NULL YES BTREE -t1 1 a 1 a A 1 NULL NULL BTREE -t1 1 a 2 b A 5 NULL NULL YES BTREE -t1 1 c_2 1 c A 5 NULL NULL YES BTREE -t1 1 c_2 2 a A 5 NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 b 1 b A 5 NULL NULL YES BTREE NO +t1 1 c 1 c A 5 NULL NULL YES BTREE NO +t1 1 a 1 a A 1 NULL NULL BTREE NO +t1 1 a 2 b A 5 NULL NULL YES BTREE NO +t1 1 c_2 1 c A 5 NULL NULL YES BTREE NO +t1 1 c_2 2 a A 5 NULL NULL BTREE NO explain select * from t1,t2 force index(c) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 @@ -613,29 +613,29 @@ test.t1 2924214226 drop table t1,t2; create table t1 (a int, key (a)); show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE NO alter table t1 disable keys; show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled NO create table t2 (a int); set @@rand_seed1=31415926,@@rand_seed2=2718281828; insert t1 select * from t2; show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled NO alter table t1 enable keys; show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A 1000 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 1000 NULL NULL YES BTREE NO alter table t1 engine=heap; alter table t1 disable keys; Warnings: Note 1031 Storage engine MEMORY of the table `test`.`t1` doesn't have this option show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a NULL 500 NULL NULL YES HASH +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a NULL 500 NULL NULL YES HASH NO drop table t1,t2; create table t1 ( a tinytext, b char(1), index idx (a(1),b) ); insert into t1 values (null,''), (null,''); @@ -679,16 +679,16 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A 10 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 10 NULL NULL YES BTREE NO insert into t1 values (11); delete from t1 where a=11; check table t1; Table Op Msg_type Msg_text test.t1 check status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A 10 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 10 NULL NULL YES BTREE NO set myisam_stats_method=nulls_equal; set @save_use_stat_tables = @@use_stat_tables; set @@use_stat_tables = COMPLEMENTARY; @@ -702,16 +702,16 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A 5 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 5 NULL NULL YES BTREE NO insert into t1 values (11); delete from t1 where a=11; check table t1; Table Op Msg_type Msg_text test.t1 check status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A 5 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 5 NULL NULL YES BTREE NO set @@use_stat_tables = @save_use_stat_tables; set myisam_stats_method=DEFAULT; show variables like 'myisam_stats_method'; @@ -724,16 +724,16 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A 10 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 10 NULL NULL YES BTREE NO insert into t1 values (11); delete from t1 where a=11; check table t1; Table Op Msg_type Msg_text test.t1 check status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A 10 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 10 NULL NULL YES BTREE NO drop table t1; set myisam_stats_method=nulls_ignored; show variables like 'myisam_stats_method'; @@ -754,22 +754,22 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A 2 NULL NULL YES BTREE -t1 1 a 2 b A 4 NULL NULL YES BTREE -t1 1 a 3 c A 4 NULL NULL YES BTREE -t1 1 a 4 d A 4 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 2 NULL NULL YES BTREE NO +t1 1 a 2 b A 4 NULL NULL YES BTREE NO +t1 1 a 3 c A 4 NULL NULL YES BTREE NO +t1 1 a 4 d A 4 NULL NULL YES BTREE NO delete from t1; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A 0 NULL NULL YES BTREE -t1 1 a 2 b A 0 NULL NULL YES BTREE -t1 1 a 3 c A 0 NULL NULL YES BTREE -t1 1 a 4 d A 0 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 0 NULL NULL YES BTREE NO +t1 1 a 2 b A 0 NULL NULL YES BTREE NO +t1 1 a 3 c A 0 NULL NULL YES BTREE NO +t1 1 a 4 d A 0 NULL NULL YES BTREE NO set myisam_stats_method=DEFAULT; drop table t1; set @@use_stat_tables = @save_use_stat_tables; @@ -1815,13 +1815,13 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A 8 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 8 NULL NULL YES BTREE NO alter table t1 disable keys; alter table t1 enable keys; show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A 8 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 8 NULL NULL YES BTREE NO drop table t1; create table t1 (c1 int) engine=myisam pack_keys=0; create table t2 (c1 int) engine=myisam pack_keys=1; diff --git a/mysql-test/main/mysqldump-utf8mb4.result b/mysql-test/main/mysqldump-utf8mb4.result index 3bafd6da6c9..50c23c5efa9 100644 --- a/mysql-test/main/mysqldump-utf8mb4.result +++ b/mysql-test/main/mysqldump-utf8mb4.result @@ -18,7 +18,7 @@ Testing XML format output <field Field="point" Type="varchar(10)" Null="NO" Key="PRI" Extra="" Comment="" /> <field Field="data" Type="varchar(10)" Null="YES" Key="" Default="NULL" Extra="" Comment="" /> <field Field="comment" Type="varchar(64)" Null="YES" Key="" Default="NULL" Extra="" Comment="" /> - <key Table="t1" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="point" Collation="A" Cardinality="1" Null="" Index_type="BTREE" Comment="" Index_comment="" /> + <key Table="t1" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="point" Collation="A" Cardinality="1" Null="" Index_type="BTREE" Comment="" Index_comment="" Ignored="NO" /> </table_structure> <table_data name="t1"> <row> diff --git a/mysql-test/main/mysqldump.result b/mysql-test/main/mysqldump.result index c878a2098ee..df790546d5b 100644 --- a/mysql-test/main/mysqldump.result +++ b/mysql-test/main/mysqldump.result @@ -12,7 +12,7 @@ INSERT INTO t1 VALUES (1), (2); <database name="test"> <table_structure name="t1"> <field Field="a" Type="int(11)" Null="YES" Key="MUL" Default="NULL" Extra="" Comment="" /> - <key Table="t1" Non_unique="1" Key_name="a" Seq_in_index="1" Column_name="a" Collation="A" Null="YES" Index_type="BTREE" Comment="" Index_comment="" /> + <key Table="t1" Non_unique="1" Key_name="a" Seq_in_index="1" Column_name="a" Collation="A" Null="YES" Index_type="BTREE" Comment="" Index_comment="" Ignored="NO" /> </table_structure> <table_data name="t1"> <row> diff --git a/mysql-test/main/partition.result b/mysql-test/main/partition.result index 373522ded40..109e6b8993a 100644 --- a/mysql-test/main/partition.result +++ b/mysql-test/main/partition.result @@ -282,15 +282,15 @@ partition by list (a) partition p1 values in (2)); insert into t1 values (1,1),(2,1),(2,2),(2,3); show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE NO analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK show indexes from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A 2 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 2 NULL NULL YES BTREE NO drop table t1; create table t1 (a int) partition by hash (a); diff --git a/mysql-test/main/pool_of_threads.result b/mysql-test/main/pool_of_threads.result index d4a7d9092f4..91ad7ab098f 100644 --- a/mysql-test/main/pool_of_threads.result +++ b/mysql-test/main/pool_of_threads.result @@ -2091,10 +2091,10 @@ fld6 char(4) latin1_swedish_ci NO # show full columns from t2 from test like 's%'; Field Type Collation Null Key Default Extra Privileges Comment show keys from t2; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE -t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE -t2 1 fld3 1 fld3 A NULL NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE NO +t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE NO +t2 1 fld3 1 fld3 A NULL NULL NULL BTREE NO drop table t4, t3, t2, t1; CREATE TABLE t1 ( cont_nr int(11) NOT NULL auto_increment, diff --git a/mysql-test/main/ps_1general.result b/mysql-test/main/ps_1general.result index 46812425ebc..663aeec2e29 100644 --- a/mysql-test/main/ps_1general.result +++ b/mysql-test/main/ps_1general.result @@ -291,9 +291,9 @@ a int(11) NO PRI NULL create index t2_idx on t2(b); prepare stmt4 from ' show index from t2 from test '; execute stmt4; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t2 0 PRIMARY 1 a A 0 NULL NULL BTREE -t2 1 t2_idx 1 b A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t2 0 PRIMARY 1 a A 0 NULL NULL BTREE NO +t2 1 t2_idx 1 b A NULL NULL NULL YES BTREE NO prepare stmt4 from ' show table status from test like ''t2%'' '; execute stmt4; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary diff --git a/mysql-test/main/ps_ddl.result b/mysql-test/main/ps_ddl.result index c5c5b5a9ac0..e528252c86f 100644 --- a/mysql-test/main/ps_ddl.result +++ b/mysql-test/main/ps_ddl.result @@ -2295,11 +2295,11 @@ drop table if exists t1; create table t1 (a int); prepare stmt from "show keys from t1 where (1) in (select * from t1)"; execute stmt; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored drop table t1; create table t1 (x int); execute stmt; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored drop table t1; deallocate prepare stmt; # diff --git a/mysql-test/main/repair.result b/mysql-test/main/repair.result index 899ca1e1a32..88f5f9eb06b 100644 --- a/mysql-test/main/repair.result +++ b/mysql-test/main/repair.result @@ -14,16 +14,16 @@ create table t1(id int PRIMARY KEY, st varchar(10), KEY st_key(st)); insert into t1 values(1, "One"); alter table t1 disable keys; show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 id A 1 NULL NULL BTREE -t1 1 st_key 1 st A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 id A 1 NULL NULL BTREE NO +t1 1 st_key 1 st A NULL NULL NULL YES BTREE disabled NO repair table t1 extended; Table Op Msg_type Msg_text test.t1 repair status OK show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 id A 1 NULL NULL BTREE -t1 1 st_key 1 st A NULL NULL NULL YES BTREE disabled +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 id A 1 NULL NULL BTREE NO +t1 1 st_key 1 st A NULL NULL NULL YES BTREE disabled NO drop table t1; repair table t1 use_frm; Table Op Msg_type Msg_text @@ -47,8 +47,8 @@ REPAIR TABLE t1; Table Op Msg_type Msg_text test.t1 repair status OK SHOW INDEX FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A 5 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 5 NULL NULL YES BTREE NO SET myisam_repair_threads=@@global.myisam_repair_threads; DROP TABLE t1; CREATE TABLE t1(a INT); diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index 7547b5b1347..2f051ab33bf 100644 --- a/mysql-test/main/select.result +++ b/mysql-test/main/select.result @@ -2088,10 +2088,10 @@ fld6 char(4) latin1_swedish_ci NO # show full columns from t2 from test like 's%'; Field Type Collation Null Key Default Extra Privileges Comment show keys from t2; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE -t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE -t2 1 fld3 1 fld3 A NULL NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE NO +t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE NO +t2 1 fld3 1 fld3 A NULL NULL NULL BTREE NO drop table t4, t3, t2, t1; DO 1; DO benchmark(100,1+1),1,1; diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index 6a8174d124c..6a309d8aa69 100644 --- a/mysql-test/main/select_jcl6.result +++ b/mysql-test/main/select_jcl6.result @@ -2099,10 +2099,10 @@ fld6 char(4) latin1_swedish_ci NO # show full columns from t2 from test like 's%'; Field Type Collation Null Key Default Extra Privileges Comment show keys from t2; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE -t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE -t2 1 fld3 1 fld3 A NULL NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE NO +t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE NO +t2 1 fld3 1 fld3 A NULL NULL NULL BTREE NO drop table t4, t3, t2, t1; DO 1; DO benchmark(100,1+1),1,1; diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result index 7547b5b1347..2f051ab33bf 100644 --- a/mysql-test/main/select_pkeycache.result +++ b/mysql-test/main/select_pkeycache.result @@ -2088,10 +2088,10 @@ fld6 char(4) latin1_swedish_ci NO # show full columns from t2 from test like 's%'; Field Type Collation Null Key Default Extra Privileges Comment show keys from t2; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE -t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE -t2 1 fld3 1 fld3 A NULL NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE NO +t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE NO +t2 1 fld3 1 fld3 A NULL NULL NULL BTREE NO drop table t4, t3, t2, t1; DO 1; DO benchmark(100,1+1),1,1; diff --git a/mysql-test/main/show_check.result b/mysql-test/main/show_check.result index d031c792922..f6ef7b8ccf1 100644 --- a/mysql-test/main/show_check.result +++ b/mysql-test/main/show_check.result @@ -76,10 +76,11 @@ def information_schema STATISTICS STATISTICS NULLABLE Null 253 3 0 N 1 0 8 def information_schema STATISTICS STATISTICS INDEX_TYPE Index_type 253 16 5 N 1 0 8 def information_schema STATISTICS STATISTICS COMMENT Comment 253 16 0 Y 0 0 8 def information_schema STATISTICS STATISTICS INDEX_COMMENT Index_comment 253 1024 0 N 1 0 8 -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 a A 5 NULL NULL BTREE -t1 1 b 1 b A 1 NULL NULL BTREE -t1 1 b 2 c A 5 NULL NULL BTREE +def information_schema STATISTICS STATISTICS IGNORED Ignored 253 3 2 N 1 0 8 +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 a A 5 NULL NULL BTREE NO +t1 1 b 1 b A 1 NULL NULL BTREE NO +t1 1 b 2 c A 5 NULL NULL BTREE NO insert into t1 values (5,5,5); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' -- Here we enable metadata just to check that the collation of the @@ -169,11 +170,11 @@ Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 f1 A 1 NULL NULL BTREE -t1 0 PRIMARY 2 f2 A 3 NULL NULL BTREE -t1 0 PRIMARY 3 f3 A 9 NULL NULL BTREE -t1 0 PRIMARY 4 f4 A 18 NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 f1 A 1 NULL NULL BTREE NO +t1 0 PRIMARY 2 f2 A 3 NULL NULL BTREE NO +t1 0 PRIMARY 3 f3 A 9 NULL NULL BTREE NO +t1 0 PRIMARY 4 f4 A 18 NULL NULL BTREE NO -- Here we enable metadata just to check that the collation of the -- resultset is non-binary for string type. This should be changed -- after Bug#29394 is implemented. @@ -186,11 +187,11 @@ def Msg_text 250 393216 2 Y 0 39 8 Table Op Msg_type Msg_text test.t1 repair status OK show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 f1 A 1 NULL NULL BTREE -t1 0 PRIMARY 2 f2 A 3 NULL NULL BTREE -t1 0 PRIMARY 3 f3 A 9 NULL NULL BTREE -t1 0 PRIMARY 4 f4 A 18 NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 f1 A 1 NULL NULL BTREE NO +t1 0 PRIMARY 2 f2 A 3 NULL NULL BTREE NO +t1 0 PRIMARY 3 f3 A 9 NULL NULL BTREE NO +t1 0 PRIMARY 4 f4 A 18 NULL NULL BTREE NO drop table t1; create temporary table t1 (a int not null); show create table t1; @@ -660,8 +661,9 @@ def information_schema STATISTICS STATISTICS NULLABLE Null 253 3 0 N 1 0 63 def information_schema STATISTICS STATISTICS INDEX_TYPE Index_type 253 16 5 N 1 0 63 def information_schema STATISTICS STATISTICS COMMENT Comment 253 16 0 Y 0 0 63 def information_schema STATISTICS STATISTICS INDEX_COMMENT Index_comment 253 1024 0 N 1 0 63 -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 field1 A 0 1000 NULL BTREE +def information_schema STATISTICS STATISTICS IGNORED Ignored 253 3 2 N 1 0 63 +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 field1 A 0 1000 NULL BTREE NO drop table t1; create table t1 ( c1 int NOT NULL, @@ -930,8 +932,9 @@ def information_schema STATISTICS STATISTICS NULLABLE Null 253 9 0 N 1 0 33 def information_schema STATISTICS STATISTICS INDEX_TYPE Index_type 253 48 5 N 1 0 33 def information_schema STATISTICS STATISTICS COMMENT Comment 253 48 0 Y 0 0 33 def information_schema STATISTICS STATISTICS INDEX_COMMENT Index_comment 253 3072 0 N 1 0 33 -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 c A 0 NULL NULL BTREE +def information_schema STATISTICS STATISTICS IGNORED Ignored 253 9 2 N 1 0 33 +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 c A 0 NULL NULL BTREE NO ---------------------------------------------------------------- SELECT TABLE_CATALOG, @@ -1312,7 +1315,7 @@ show columns from `a\b` from `mysqlttest\1`; Field Type Null Key Default Extra a int(11) YES NULL show keys from `mysqlttest\1`.`a\b`; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored drop table `mysqlttest\1`.`a\b`; drop database `mysqlttest\1`; show engine foobar status; diff --git a/mysql-test/main/show_explain.result b/mysql-test/main/show_explain.result index 69dc506527b..6ad93930448 100644 --- a/mysql-test/main/show_explain.result +++ b/mysql-test/main/show_explain.result @@ -714,10 +714,10 @@ id select_type table type possible_keys key key_len ref rows Extra Warnings: Note 1003 SHOW INDEX FROM t1 connection con1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a A NULL NULL NULL YES BTREE -t1 1 b 1 b A NULL NULL NULL YES BTREE -t1 1 c 1 c A NULL NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE NO +t1 1 b 1 b A NULL NULL NULL YES BTREE NO +t1 1 c 1 c A NULL NULL NULL YES BTREE NO SET debug_dbug=@old_debug; DROP TABLE t1; # diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result index 17b0af92a40..f30283d3b1d 100644 --- a/mysql-test/main/sp.result +++ b/mysql-test/main/sp.result @@ -2414,7 +2414,7 @@ Level Code Message Field Type Null Key Default Extra id char(16) NO data int(11) NO NULL -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored Database Table In_use Name_locked Variable_name Value Tables_in_test (foo) @@ -2435,7 +2435,7 @@ Level Code Message Field Type Null Key Default Extra id char(16) NO data int(11) NO NULL -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored Database Table In_use Name_locked Variable_name Value Tables_in_test (foo) diff --git a/mysql-test/main/ssl.result b/mysql-test/main/ssl.result index 40a32fdd1dd..b2ca0cf80ad 100644 --- a/mysql-test/main/ssl.result +++ b/mysql-test/main/ssl.result @@ -2100,10 +2100,10 @@ fld6 char(4) latin1_swedish_ci NO # show full columns from t2 from test like 's%'; Field Type Collation Null Key Default Extra Privileges Comment show keys from t2; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE -t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE -t2 1 fld3 1 fld3 A NULL NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE NO +t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE NO +t2 1 fld3 1 fld3 A NULL NULL NULL BTREE NO drop table t4, t3, t2, t1; CREATE TABLE t1 ( cont_nr int(11) NOT NULL auto_increment, diff --git a/mysql-test/main/ssl_compress.result b/mysql-test/main/ssl_compress.result index 8c63c798afa..69de425cdc1 100644 --- a/mysql-test/main/ssl_compress.result +++ b/mysql-test/main/ssl_compress.result @@ -2097,10 +2097,10 @@ fld6 char(4) latin1_swedish_ci NO # show full columns from t2 from test like 's%'; Field Type Collation Null Key Default Extra Privileges Comment show keys from t2; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE -t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE -t2 1 fld3 1 fld3 A NULL NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE NO +t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE NO +t2 1 fld3 1 fld3 A NULL NULL NULL BTREE NO drop table t4, t3, t2, t1; CREATE TABLE t1 ( cont_nr int(11) NOT NULL auto_increment, diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index a5089c609d2..22f5bfafb8e 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -154,10 +154,10 @@ select count(distinct o_custkey) from orders; count(distinct o_custkey) 100 show index from orders; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -orders 0 PRIMARY 1 o_orderkey A 1500 NULL NULL BTREE -orders 1 i_o_orderdate 1 o_orderDATE A 1126 NULL NULL YES BTREE -orders 1 i_o_custkey 1 o_custkey A 100 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +orders 0 PRIMARY 1 o_orderkey A 1500 NULL NULL BTREE NO +orders 1 i_o_orderdate 1 o_orderDATE A 1126 NULL NULL YES BTREE NO +orders 1 i_o_custkey 1 o_custkey A 100 NULL NULL YES BTREE NO select index_name, column_name, cardinality from information_schema.statistics where table_name='orders'; index_name column_name cardinality @@ -656,33 +656,33 @@ dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033 dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404 dbt3_s001 lineitem i_l_commitdate 1 2.7160 SHOW INDEXES FROM lineitem; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -lineitem 0 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE -lineitem 0 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE -lineitem 1 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE -lineitem 1 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE -lineitem 1 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE -lineitem 1 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE -lineitem 1 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE -lineitem 1 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE -lineitem 1 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE -lineitem 1 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE -lineitem 1 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE -lineitem 1 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +lineitem 0 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE NO +lineitem 0 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE NO +lineitem 1 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE NO +lineitem 1 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE NO +lineitem 1 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE NO +lineitem 1 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE NO +lineitem 1 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE NO +lineitem 1 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE NO +lineitem 1 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE NO +lineitem 1 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE NO +lineitem 1 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE NO +lineitem 1 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE NO SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='lineitem'; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT -def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE -def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED +def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE NO +def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE NO SELECT COUNT(DISTINCT l_orderkey), COUNT(DISTINCT l_orderkey,l_linenumber), COUNT(DISTINCT l_shipDATE), diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result index 6fafac8776b..5b62f228b1f 100644 --- a/mysql-test/main/stat_tables_innodb.result +++ b/mysql-test/main/stat_tables_innodb.result @@ -186,10 +186,10 @@ select count(distinct o_custkey) from orders; count(distinct o_custkey) 100 show index from orders; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -orders 0 PRIMARY 1 o_orderkey A 1500 NULL NULL BTREE -orders 1 i_o_orderdate 1 o_orderDATE A 1126 NULL NULL YES BTREE -orders 1 i_o_custkey 1 o_custkey A 100 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +orders 0 PRIMARY 1 o_orderkey A 1500 NULL NULL BTREE NO +orders 1 i_o_orderdate 1 o_orderDATE A 1126 NULL NULL YES BTREE NO +orders 1 i_o_custkey 1 o_custkey A 100 NULL NULL YES BTREE NO select index_name, column_name, cardinality from information_schema.statistics where table_name='orders'; index_name column_name cardinality @@ -688,33 +688,33 @@ dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033 dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404 dbt3_s001 lineitem i_l_commitdate 1 2.7160 SHOW INDEXES FROM lineitem; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -lineitem 0 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE -lineitem 0 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE -lineitem 1 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE -lineitem 1 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE -lineitem 1 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE -lineitem 1 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE -lineitem 1 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE -lineitem 1 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE -lineitem 1 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE -lineitem 1 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE -lineitem 1 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE -lineitem 1 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +lineitem 0 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE NO +lineitem 0 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE NO +lineitem 1 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE NO +lineitem 1 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE NO +lineitem 1 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE NO +lineitem 1 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE NO +lineitem 1 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE NO +lineitem 1 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE NO +lineitem 1 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE NO +lineitem 1 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE NO +lineitem 1 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE NO +lineitem 1 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE NO SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='lineitem'; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT -def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE -def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE -def dbt3_s001 lineitem 1 dbt3_s001 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED +def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE NO +def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE NO +def dbt3_s001 lineitem 1 dbt3_s001 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE NO SELECT COUNT(DISTINCT l_orderkey), COUNT(DISTINCT l_orderkey,l_linenumber), COUNT(DISTINCT l_shipDATE), diff --git a/mysql-test/main/stat_tables_par.result b/mysql-test/main/stat_tables_par.result index 33fbe079c9e..7a95e8259b6 100644 --- a/mysql-test/main/stat_tables_par.result +++ b/mysql-test/main/stat_tables_par.result @@ -274,8 +274,8 @@ a b 2 2 SET DEBUG_SYNC= "now SIGNAL go"; connection default; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT -def test t1 1 test a 1 a A 2 NULL NULL YES BTREE +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED +def test t1 1 test a 1 a A 2 NULL NULL YES BTREE NO connection default; disconnect con1; set debug_sync='RESET'; diff --git a/mysql-test/main/stat_tables_par_innodb.result b/mysql-test/main/stat_tables_par_innodb.result index c33785a9436..2b2d091444f 100644 --- a/mysql-test/main/stat_tables_par_innodb.result +++ b/mysql-test/main/stat_tables_par_innodb.result @@ -307,8 +307,8 @@ a b 2 2 SET DEBUG_SYNC= "now SIGNAL go"; connection default; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT -def test t1 1 test a 1 a A 2 NULL NULL YES BTREE +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED +def test t1 1 test a 1 a A 2 NULL NULL YES BTREE NO connection default; disconnect con1; set debug_sync='RESET'; diff --git a/mysql-test/main/type_ranges.result b/mysql-test/main/type_ranges.result index 784a394d8b5..02b6c79bdf5 100644 --- a/mysql-test/main/type_ranges.result +++ b/mysql-test/main/type_ranges.result @@ -65,21 +65,21 @@ longblob_col longblob NULL NO '' # options enum('one','two','tree') latin1_swedish_ci NO MUL NULL # flags set('one','two','tree') latin1_swedish_ci NO # show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 PRIMARY 1 auto A 0 NULL NULL BTREE -t1 1 utiny 1 utiny A NULL NULL NULL BTREE -t1 1 tiny 1 tiny A NULL NULL NULL BTREE -t1 1 short 1 short A NULL NULL NULL BTREE -t1 1 any_name 1 medium A NULL NULL NULL BTREE -t1 1 longlong 1 longlong A NULL NULL NULL BTREE -t1 1 real_float 1 real_float A NULL NULL NULL BTREE -t1 1 ushort 1 ushort A NULL NULL NULL BTREE -t1 1 umedium 1 umedium A NULL NULL NULL BTREE -t1 1 ulong 1 ulong A NULL NULL NULL BTREE -t1 1 ulonglong 1 ulonglong A NULL NULL NULL BTREE -t1 1 ulonglong 2 ulong A NULL NULL NULL BTREE -t1 1 options 1 options A NULL NULL NULL BTREE -t1 1 options 2 flags A NULL NULL NULL BTREE +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 auto A 0 NULL NULL BTREE NO +t1 1 utiny 1 utiny A NULL NULL NULL BTREE NO +t1 1 tiny 1 tiny A NULL NULL NULL BTREE NO +t1 1 short 1 short A NULL NULL NULL BTREE NO +t1 1 any_name 1 medium A NULL NULL NULL BTREE NO +t1 1 longlong 1 longlong A NULL NULL NULL BTREE NO +t1 1 real_float 1 real_float A NULL NULL NULL BTREE NO +t1 1 ushort 1 ushort A NULL NULL NULL BTREE NO +t1 1 umedium 1 umedium A NULL NULL NULL BTREE NO +t1 1 ulong 1 ulong A NULL NULL NULL BTREE NO +t1 1 ulonglong 1 ulonglong A NULL NULL NULL BTREE NO +t1 1 ulonglong 2 ulong A NULL NULL NULL BTREE NO +t1 1 options 1 options A NULL NULL NULL BTREE NO +t1 1 options 2 flags A NULL NULL NULL BTREE NO CREATE UNIQUE INDEX test on t1 ( auto ) ; CREATE INDEX test2 on t1 ( ulonglong,ulong) ; Warnings: diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 4585f370c1d..35e607ad43c 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -3678,7 +3678,7 @@ c1 c2 2 2 CREATE VIEW v1 AS SELECT c1, c2 FROM t1; SHOW INDEX FROM v1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored SELECT * FROM v1 USE INDEX (PRIMARY) WHERE c1=2; ERROR 42000: Key 'PRIMARY' doesn't exist in table 'v1' SELECT * FROM v1 FORCE INDEX (PRIMARY) WHERE c1=2; |