diff options
Diffstat (limited to 'mysql-test/main/key.test')
| -rw-r--r-- | mysql-test/main/key.test | 583 |
1 files changed, 583 insertions, 0 deletions
diff --git a/mysql-test/main/key.test b/mysql-test/main/key.test new file mode 100644 index 00000000000..0695dc2eecb --- /dev/null +++ b/mysql-test/main/key.test @@ -0,0 +1,583 @@ +--disable_warnings +drop table if exists t1,t2,t3; +--enable_warnings +SET SQL_WARNINGS=1; + +# +# This failed for Elizabeth Mattijsen +# + +CREATE TABLE t1 ( + ID CHAR(32) NOT NULL, + name CHAR(32) NOT NULL, + value CHAR(255), + INDEX indexIDname (ID(8),name(8)) +) ; + +INSERT INTO t1 VALUES +('keyword','indexdir','/export/home/local/www/database/indexes/keyword'); +INSERT INTO t1 VALUES ('keyword','urlprefix','text/ /text'); +INSERT INTO t1 VALUES ('keyword','urlmap','/text/ /'); +INSERT INTO t1 VALUES ('keyword','attr','personal employee company'); +INSERT INTO t1 VALUES +('emailgids','indexdir','/export/home/local/www/database/indexes/emailgids'); +INSERT INTO t1 VALUES ('emailgids','urlprefix','text/ /text'); +INSERT INTO t1 VALUES ('emailgids','urlmap','/text/ /'); +INSERT INTO t1 VALUES ('emailgids','attr','personal employee company'); + +SELECT value FROM t1 WHERE ID='emailgids' AND name='attr'; + +drop table t1; + +# +# Problem with many key parts and many or +# + +CREATE TABLE t1 ( + price int(5) DEFAULT '0' NOT NULL, + area varchar(40) DEFAULT '' NOT NULL, + type varchar(40) DEFAULT '' NOT NULL, + transityes enum('Y','N') DEFAULT 'Y' NOT NULL, + shopsyes enum('Y','N') DEFAULT 'Y' NOT NULL, + schoolsyes enum('Y','N') DEFAULT 'Y' NOT NULL, + petsyes enum('Y','N') DEFAULT 'Y' NOT NULL, + KEY price (price,area,type,transityes,shopsyes,schoolsyes,petsyes) +); + +INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N'); +INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N'); +INSERT IGNORE INTO t1 VALUES (900,'Vancouver','Shared/Roomate','','','',''); +INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); +INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); +INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); +INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); +INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); + + SELECT * FROM t1 WHERE area='Vancouver' and transityes='y' and schoolsyes='y' and ( ((type='1 Bedroom' or type='Studio/Bach') and (price<=500)) or ((type='2 Bedroom') and (price<=550)) or ((type='Shared/Roomate') and (price<=300)) or ((type='Room and Board') and (price<=500)) ) and price <= 400; + +drop table t1; + +# +# No longer a problem with primary key +# + +CREATE TABLE t1 (program enum('signup','unique','sliding') not null, type enum('basic','sliding','signup'), sites set('mt'), PRIMARY KEY (program)); +# This no longer give an error for wrong primary key +ALTER TABLE t1 modify program enum('signup','unique','sliding'); +drop table t1; + +# +# Test of compressed decimal index. +# + +CREATE TABLE t1 ( + name varchar(50) DEFAULT '' NOT NULL, + author varchar(50) DEFAULT '' NOT NULL, + category decimal(10,0) DEFAULT '0' NOT NULL, + email varchar(50), + password varchar(50), + proxy varchar(50), + bitmap varchar(20), + msg varchar(255), + urlscol varchar(127), + urlhttp varchar(127), + timeout decimal(10,0), + nbcnx decimal(10,0), + creation decimal(10,0), + livinguntil decimal(10,0), + lang decimal(10,0), + type decimal(10,0), + subcat decimal(10,0), + subtype decimal(10,0), + reg char(1), + scs varchar(255), + capacity decimal(10,0), + userISP varchar(50), + CCident varchar(50) DEFAULT '' NOT NULL, + PRIMARY KEY (name,author,category) +); +INSERT INTO t1 VALUES +('patnom','patauteur',0,'p.favre@cryo-networks.fr',NULL,NULL,'#p2sndnq6ae5g1u6t','essai salut','scol://195.242.78.119:patauteur.patnom',NULL,NULL,NULL,950036174,-882087474,NULL,3,0,3,'1','Pub/patnom/futur_divers.scs',NULL,'pat','CC1'); +INSERT INTO t1 VALUES +('LeNomDeMonSite','Marc',0,'m.barilley@cryo-networks.fr',NULL,NULL,NULL,NULL,'scol://195.242.78.119:Marc.LeNomDeMonSite',NULL,NULL,NULL,950560434,-881563214,NULL,3,0,3,'1','Pub/LeNomDeMonSite/domus_hibere.scs',NULL,'Marq','CC1'); +select * from t1 where name='patnom' and author='patauteur' and category=0; +drop table t1; + +# +# Problem with search on partial index +# + +create table t1 +( + name_id int not null auto_increment, + name blob, + INDEX name_idx (name(5)), + primary key (name_id) +); + +INSERT t1 VALUES(NULL,'/'); +INSERT t1 VALUES(NULL,'[T,U]_axpby'); +SELECT * FROM t1 WHERE name='[T,U]_axpy'; +SELECT * FROM t1 WHERE name='[T,U]_axpby'; +create table t2 +( + name_id int not null auto_increment, + name char(255) binary, + INDEX name_idx (name(5)), + primary key (name_id) +); +INSERT t2 select * from t1; +SELECT * FROM t2 WHERE name='[T,U]_axpy'; +SELECT * FROM t2 WHERE name='[T,U]_axpby'; +# Test possible problems with warnings in CREATE ... SELECT +CREATE TABLE t3 SELECT * FROM t2 WHERE name='[T,U]_axpby'; +SELECT * FROM t2 WHERE name='[T,U]_axpby'; + +drop table t1,t2,t3; + +# +# Test bug with long primary key +# + +create table t1 +( + SEQNO numeric(12 ) not null, + MOTYPEID numeric(12 ) not null, + MOINSTANCEID numeric(12 ) not null, + ATTRID numeric(12 ) not null, + VALUE varchar(120) not null, + primary key (SEQNO, MOTYPEID, MOINSTANCEID, ATTRID, VALUE ) +); +INSERT INTO t1 VALUES (1, 1, 1, 1, 'a'); +INSERT INTO t1 VALUES (1, 1, 1, 1, 'b'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES (1, 1, 1, 1, 'a'); +drop table t1; + +# +# Test with blob + tinyint key +# (Failed for Greg Valure) +# + +CREATE TABLE t1 ( + a tinytext NOT NULL, + b tinyint(3) unsigned NOT NULL default '0', + PRIMARY KEY (a(32),b) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('a',1),('a',2); +SELECT * FROM t1 WHERE a='a' AND b=2; +SELECT * FROM t1 WHERE a='a' AND b in (2); +SELECT * FROM t1 WHERE a='a' AND b in (1,2); +drop table t1; + +# +# Test of create key order +# + +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; +drop table t1; + +# +# Problem with UNIQUE() with NULL parts and auto increment +# + +CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT, +UNIQUE (c,i)); +INSERT IGNORE INTO t1 (c) VALUES (NULL),(NULL); +SELECT * FROM t1; +INSERT INTO t1 (c) VALUES ('a'),('a'); +SELECT * FROM t1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c CHAR(10) NULL, i INT NOT NULL AUTO_INCREMENT, +UNIQUE (c,i)); +INSERT INTO t1 (c) VALUES (NULL),(NULL); +SELECT * FROM t1; +INSERT INTO t1 (c) VALUES ('a'),('a'); +SELECT * FROM t1; +drop table t1; + +# +# longer keys +# +create table t1 (i int, a char(200), b text, unique (a), unique (b(300))) charset utf8; +insert ignore t1 values (1, repeat('a',210), repeat('b', 310)); +insert ignore t1 values (2, repeat(0xD0B1,215), repeat(0xD0B1, 310)); +select i, length(a), length(b), char_length(a), char_length(b) from t1; +select i from t1 where a=repeat(_utf8 'a',200); +select i from t1 where a=repeat(_utf8 0xD0B1,200); +select i from t1 where b=repeat(_utf8 'b',310); +drop table t1; + +# +# Test of key read with primary key (Bug #3497) +# + +CREATE TABLE t1 (id int unsigned auto_increment, name char(50), primary key (id)) engine=myisam; +insert into t1 (name) values ('a'), ('b'),('c'),('d'),('e'),('f'),('g'); +explain select 1 from t1 where id =2; +explain select 1 from t1 where id =2 or id=3; +explain select name from t1 where id =2; +ALTER TABLE t1 DROP PRIMARY KEY, ADD INDEX (id); +explain select 1 from t1 where id =2; +drop table t1; + +# +# Test of problem with key read (Bug #3666) +# + +CREATE TABLE t1 (numeropost mediumint(8) unsigned NOT NULL default '0', numreponse int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (numeropost,numreponse), UNIQUE KEY numreponse (numreponse)); +INSERT INTO t1 (numeropost,numreponse) VALUES ('1','1'),('1','2'),('2','3'),('2','4'); +SELECT numeropost FROM t1 WHERE numreponse='1'; +EXPLAIN SELECT numeropost FROM t1 WHERE numreponse='1'; +FLUSH TABLES; +SELECT numeropost FROM t1 WHERE numreponse='1'; +drop table t1; + +# +# UNIQUE prefix keys and multi-byte charsets +# + +create table t1 (c varchar(30) character set utf8, t text character set utf8, unique (c(2)), unique (t(3))) engine=myisam; +show create table t1; +insert t1 values ('cccc', 'tttt'), + (0xD0B1212223D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1212223D0B1D0B1D0B1D0B1), + (0xD0B1222123D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1222123D0B1D0B1D0B1D0B1); +--error ER_DUP_ENTRY +insert t1 (c) values ('cc22'); +--error ER_DUP_ENTRY +insert t1 (t) values ('ttt22'); +--error ER_DUP_ENTRY +insert t1 (c) values (0xD0B1212322D0B1D0B1D0B1D0B1D0B1); +--error ER_DUP_ENTRY +insert t1 (t) values (0xD0B1D0B1212322D0B1D0B1D0B1D0B1); +select c from t1 where c='cccc'; +select t from t1 where t='tttt'; +select c from t1 where c=0xD0B1212223D0B1D0B1D0B1D0B1D0B1; +select t from t1 where t=0xD0B1D0B1212223D0B1D0B1D0B1D0B1; +drop table t1; + +# +# BUG#6151 - myisam index corruption +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( + c1 int, + c2 varbinary(240), + UNIQUE KEY (c1), + KEY (c2) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'\Z\Z\Z\Z'); +INSERT INTO t1 VALUES (2,'\Z\Z\Z\Z\Z\Z'); +INSERT INTO t1 VALUES (3,'\Z\Z\Z\Z'); +select c1 from t1 where c2='\Z\Z\Z\Z'; +DELETE FROM t1 WHERE (c1 = 1); +check table t1; +select c1 from t1 where c2='\Z\Z\Z\Z'; +DELETE FROM t1 WHERE (c1 = 3); +check table t1; +select c1 from t1 where c2='\Z\Z\Z\Z'; + +# +# test delete of keys in a different order +# +truncate table t1; +insert into t1 values(1,"aaaa"),(2,"aaab"),(3,"aaac"),(4,"aaccc"); +delete from t1 where c1=3; +delete from t1 where c1=1; +delete from t1 where c1=4; +check table t1; + +drop table t1; + +# +# Bug 6166: index prefix length of 0 not rejected +# +# this test should fail in 5.0 +# to fix it, remove #ifdef in +# file sql_yacc.yy(key_part) +# create dedicated error code for this and +# and change my_printf_error() to my_error + +--error 1391 +create table t1 (c char(10), index (c(0))); + +# +# Bug #6126: Duplicate columns in keys should fail +# Bug #6252: (dup) +# +--error 1060 +create table t1 (c char(10), index (c,c)); +--error 1060 +create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)); +--error 1060 +create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)); +--error 1060 +create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)); +create table t1 (c1 char(10), c2 char(10)); +--error 1060 +alter table t1 add key (c1,c1); +--error 1060 +alter table t1 add key (c2,c1,c1); +--error 1060 +alter table t1 add key (c1,c2,c1); +--error 1060 +alter table t1 add key (c1,c1,c2); +drop table t1; + +# +# Bug#11228: DESC shows arbitrary column as "PRI" +# +create table t1 ( + i1 INT NOT NULL, + i2 INT NOT NULL, + UNIQUE i1idx (i1), + UNIQUE i2idx (i2)); +desc t1; +show create table t1; +drop table t1; + +# +# Bug#12565 - ERROR 1034 when running simple UPDATE or DELETE +# on large MyISAM table +# +create table t1 ( + c1 int, + c2 varchar(20) not null, + primary key (c1), + key (c2(10)) +) engine=myisam; +insert into t1 values (1,''); +insert into t1 values (2,' \t\tTest String'); +insert into t1 values (3,' \n\tTest String'); +update t1 set c2 = 'New Test String' where c1 = 1; +select * from t1; +drop table t1; + +# +# If we use a partial field for a key that is actually the length of the +# field, and we extend the field, we end up with a key that includes the +# whole new length of the field. +# +create table t1 (a varchar(10), b varchar(10), key(a(10),b(10))); +show create table t1; +alter table t1 modify b varchar(20); +show create table t1; +alter table t1 modify a varchar(20); +show create table t1; +drop table t1; + +# +# Bug #11227: Incorrectly reporting 'MUL' vs. 'UNI' on varchar +# +create table t1 (a int not null primary key, b varchar(20) not null unique); +desc t1; +drop table t1; +create table t1 (a int not null primary key, b int not null unique); +desc t1; +drop table t1; +create table t1 (a int not null primary key, b varchar(20) not null, unique (b(10))); +desc t1; +drop table t1; +create table t1 (a int not null primary key, b varchar(20) not null, c varchar(20) not null, unique(b(10),c(10))); +desc t1; +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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_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 ( + a INTEGER auto_increment PRIMARY KEY, + b INTEGER NOT NULL, + c INTEGER NOT NULL, + d CHAR(64) +); + +CREATE TABLE t2 ( + a INTEGER auto_increment PRIMARY KEY, + b INTEGER NOT NULL, + c SMALLINT NOT NULL, + d DATETIME NOT NULL, + e SMALLINT NOT NULL, + f INTEGER NOT NULL, + g INTEGER NOT NULL, + h SMALLINT NOT NULL, + i INTEGER NOT NULL, + j INTEGER NOT NULL, + UNIQUE INDEX (b), + INDEX (b, d, e, f, g, h, i, j, c), + INDEX (c) +); + +INSERT INTO t2 VALUES + (NULL, 1, 254, '1000-01-01 00:00:00', 257, 0, 0, 0, 0, 0), + (NULL, 2, 1, '2004-11-30 12:00:00', 1, 0, 0, 0, 0, 0), + (NULL, 3, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -21600, 0), + (NULL, 4, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -10800, 0), + (NULL, 5, 1, '2004-11-30 12:00:00', 1, 0, 0, 5, -10800, 0), + (NULL, 6, 1, '2004-11-30 12:00:00', 102, 0, 0, 0, 0, 0), + (NULL, 7, 1, '2004-11-30 12:00:00', 105, 2, 0, 0, 0, 0), + (NULL, 8, 1, '2004-11-30 12:00:00', 105, 10, 0, 0, 0, 0); + +INSERT INTO t1 (b, c, d) VALUES + (3388000, -553000, NULL), + (3388000, -553000, NULL); + +SELECT * +FROM t2 c JOIN t1 pa ON c.b = pa.a +WHERE c.c = 1 +ORDER BY c.b, c.d +; + +DROP TABLE t1, t2; + +# +# Bug #31137: Assertion failed: primary_key_no == -1 || primary_key_no == 0 +# +create table t1(a int not null, key aa(a), + b char(10) not null, unique key bb(b(1)), + c char(4) not null, unique key cc(c)); +desc t1; +show create table t1; +drop table t1; +create table t1(a int not null, key aa(a), + b char(10) not null, unique key bb(b(1)), + c char(4) not null); +desc t1; +alter table t1 add unique key cc(c); +desc t1; +show create table t1; +drop table t1; + +--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; + +--echo # +--echo # Bug#18144: Cost with FORCE/USE index seems incorrect in some cases. +--echo # +--echo # We are interested in showing that the cost for the last plan is higher +--echo # than for the preceding two plans. +--echo # +CREATE TABLE t1( a INT, b INT, KEY( a ) ); +INSERT INTO t1 values (1, 2), (1, 3), (2, 3), (2, 4), (3, 4), (3, 5); + +EXPLAIN SELECT a, SUM( b ) FROM t1 GROUP BY a; +SHOW STATUS LIKE 'Last_query_cost'; + +EXPLAIN SELECT a, SUM( b ) FROM t1 USE INDEX( a ) GROUP BY a; +SHOW STATUS LIKE 'Last_query_cost'; + +EXPLAIN SELECT a, SUM( b ) FROM t1 FORCE INDEX( a ) GROUP BY a; +SHOW STATUS LIKE 'Last_query_cost'; + +DROP TABLE t1; |