diff options
Diffstat (limited to 'mysql-test/t/key.test')
| -rw-r--r-- | mysql-test/t/key.test | 131 |
1 files changed, 115 insertions, 16 deletions
diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test index f63336a9864..9a4158d8e13 100644 --- a/mysql-test/t/key.test +++ b/mysql-test/t/key.test @@ -150,7 +150,7 @@ create table t1 ); INSERT INTO t1 VALUES (1, 1, 1, 1, 'a'); INSERT INTO t1 VALUES (1, 1, 1, 1, 'b'); ---error 1062 +--error ER_DUP_ENTRY INSERT INTO t1 VALUES (1, 1, 1, 1, 'a'); drop table t1; @@ -243,13 +243,13 @@ show create table t1; insert t1 values ('cccc', 'tttt'), (0xD0B1212223D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1212223D0B1D0B1D0B1D0B1), (0xD0B1222123D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1222123D0B1D0B1D0B1D0B1); ---error 1062 +--error ER_DUP_ENTRY insert t1 (c) values ('cc22'); ---error 1062 +--error ER_DUP_ENTRY insert t1 (t) values ('ttt22'); ---error 1062 +--error ER_DUP_ENTRY insert t1 (c) values (0xD0B1212322D0B1D0B1D0B1D0B1D0B1); ---error 1062 +--error ER_DUP_ENTRY insert t1 (t) values (0xD0B1D0B1212322D0B1D0B1D0B1D0B1); select c from t1 where c='cccc'; select t from t1 where t='tttt'; @@ -386,6 +386,75 @@ drop table t1; # End of 4.1 tests # +# WL#1563 - Modify MySQL to support on-line CREATE/DROP INDEX +# To test if this really works, you need to run with --debug +# and check the trace file. +# +# Create a table with named and unnamed indexes. +create table t1 ( + c1 int, + c2 char(12), + c3 varchar(123), + c4 timestamp, + index (c1), + index i1 (c1), + index i2 (c2), + index i3 (c3), + unique i4 (c4), + index i5 (c1, c2, c3, c4), + primary key (c2, c3), + index (c2, c4)); +show create table t1; +# Some simple tests. +alter table t1 drop index c1; +alter table t1 add index (c1); +# This creates index 'c1_2'. +alter table t1 add index (c1); +alter table t1 drop index i3; +alter table t1 add index i3 (c3); +# Two indexes at the same time. +alter table t1 drop index i2, drop index i4; +alter table t1 add index i2 (c2), add index i4 (c4); +# Three indexes, one of them reversely. +alter table t1 drop index i2, drop index i4, add index i6 (c2, c4); +alter table t1 add index i2 (c2), add index i4 (c4), drop index i6; +# include an unique index. +alter table t1 drop index i2, drop index i4, add unique i4 (c4); +alter table t1 add index i2 (c2), drop index i4, add index i4 (c4); +# Modify an index by changing its definition. +alter table t1 drop index c2, add index (c2(4),c3(7)); +# Change nothing. The new key definition is the same as the old one. +alter table t1 drop index c2, add index (c2(4),c3(7)); +# Test primary key handling. +alter table t1 add primary key (c1, c2), drop primary key; +alter table t1 drop primary key; +# Drop is checked first. Primary key must exist. +--error 1091 +alter table t1 add primary key (c1, c2), drop primary key; +show create table t1; +# Insert non-unique values. +insert into t1 values(1, 'a', 'a', NULL); +insert into t1 values(1, 'b', 'b', NULL); +# Drop some indexes for new adds. +alter table t1 drop index i3, drop index i2, drop index i1; +# Add indexes, one is unique on non-unique values. +--error ER_DUP_ENTRY +alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1); +drop table t1; + + +# +# Bug #20604: Test for disabled keys with aggregate functions and FORCE INDEX. +# + +CREATE TABLE t1( a TINYINT, KEY(a) ) ENGINE=MyISAM; +INSERT INTO t1 VALUES( 1 ); +ALTER TABLE t1 DISABLE KEYS; +EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a); + +drop table t1; + +# # Bug #24778: Innodb: No result when using ORDER BY # CREATE TABLE t1 ( @@ -433,16 +502,6 @@ ORDER BY c.b, c.d DROP TABLE t1, t2; - -# -# Bug #20604: Test for disabled keys with aggregate functions and FORCE INDEX. -# -CREATE TABLE t1( a TINYINT, KEY(a) ) ENGINE=MyISAM; -INSERT INTO t1 VALUES( 1 ); -ALTER TABLE t1 DISABLE KEYS; -EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a); -DROP TABLE t1; - # # Bug #31137: Assertion failed: primary_key_no == -1 || primary_key_no == 0 # @@ -461,4 +520,44 @@ desc t1; show create table t1; drop table t1; ---echo End of 5.0 tests. +--echo End of 5.0 tests + +# +# Bug #31148: bool close_thread_table(THD*, TABLE**): Assertion +# `table->key_read == 0' failed. +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT); + +INSERT INTO t1 VALUES (), (), (); + +SELECT 1 AS c1 +FROM t1 +ORDER BY ( + SELECT 1 AS c2 + FROM t1 + GROUP BY GREATEST(LAST_INSERT_ID(), t1.a) ASC + LIMIT 1); + +DROP TABLE t1; + + +# +# Bug #31974: Wrong EXPLAIN output +# + +CREATE TABLE t1 (a INT, b INT, INDEX (a,b)); +INSERT INTO t1 (a, b) + VALUES + (1,1), (1,2), (1,3), (1,4), (1,5), + (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE + (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; +SELECT 1 as RES FROM t1 AS t1_outer WHERE + (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; + +DROP TABLE t1; |