summaryrefslogtreecommitdiff
path: root/mysql-test/t/key.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t/key.test')
-rw-r--r--mysql-test/t/key.test583
1 files changed, 0 insertions, 583 deletions
diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test
deleted file mode 100644
index 0695dc2eecb..00000000000
--- a/mysql-test/t/key.test
+++ /dev/null
@@ -1,583 +0,0 @@
---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;
View Lorry Controller Status