2
drop table if exists t1,t2,t3;
7
# This failed for Elizabeth Mattijsen
12
name CHAR(32) NOT NULL,
14
INDEX indexIDname (ID(8),name(8))
18
('keyword','indexdir','/export/home/local/www/database/indexes/keyword');
19
INSERT INTO t1 VALUES ('keyword','urlprefix','text/ /text');
20
INSERT INTO t1 VALUES ('keyword','urlmap','/text/ /');
21
INSERT INTO t1 VALUES ('keyword','attr','personal employee company');
23
('emailgids','indexdir','/export/home/local/www/database/indexes/emailgids');
24
INSERT INTO t1 VALUES ('emailgids','urlprefix','text/ /text');
25
INSERT INTO t1 VALUES ('emailgids','urlmap','/text/ /');
26
INSERT INTO t1 VALUES ('emailgids','attr','personal employee company');
28
SELECT value FROM t1 WHERE ID='emailgids' AND name='attr';
33
# Problem with many key parts and many or
37
price int(5) DEFAULT '0' NOT NULL,
38
area varchar(40) DEFAULT '' NOT NULL,
39
type varchar(40) DEFAULT '' NOT NULL,
40
transityes enum('Y','N') DEFAULT 'Y' NOT NULL,
41
shopsyes enum('Y','N') DEFAULT 'Y' NOT NULL,
42
schoolsyes enum('Y','N') DEFAULT 'Y' NOT NULL,
43
petsyes enum('Y','N') DEFAULT 'Y' NOT NULL,
44
KEY price (price,area,type,transityes,shopsyes,schoolsyes,petsyes)
47
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N');
48
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N');
49
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','','','','');
50
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
51
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
52
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
53
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
54
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
56
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;
61
# No longer a problem with primary key
64
CREATE TABLE t1 (program enum('signup','unique','sliding') not null, type enum('basic','sliding','signup'), sites set('mt'), PRIMARY KEY (program));
65
# This no longer give an error for wrong primary key
66
ALTER TABLE t1 modify program enum('signup','unique','sliding');
70
# Test of compressed decimal index.
74
name varchar(50) DEFAULT '' NOT NULL,
75
author varchar(50) DEFAULT '' NOT NULL,
76
category decimal(10,0) DEFAULT '0' NOT NULL,
84
timeout decimal(10,0),
86
creation decimal(10,0),
87
livinguntil decimal(10,0),
91
subtype decimal(10,0),
94
capacity decimal(10,0),
96
CCident varchar(50) DEFAULT '' NOT NULL,
97
PRIMARY KEY (name,author,category)
100
('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');
101
INSERT INTO t1 VALUES
102
('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');
103
select * from t1 where name='patnom' and author='patauteur' and category=0;
107
# Problem with search on partial index
112
name_id int not null auto_increment,
114
INDEX name_idx (name(5)),
115
primary key (name_id)
118
INSERT t1 VALUES(NULL,'/');
119
INSERT t1 VALUES(NULL,'[T,U]_axpby');
120
SELECT * FROM t1 WHERE name='[T,U]_axpy';
121
SELECT * FROM t1 WHERE name='[T,U]_axpby';
124
name_id int not null auto_increment,
125
name char(255) binary,
126
INDEX name_idx (name(5)),
127
primary key (name_id)
129
INSERT t2 select * from t1;
130
SELECT * FROM t2 WHERE name='[T,U]_axpy';
131
SELECT * FROM t2 WHERE name='[T,U]_axpby';
132
# Test possible problems with warnings in CREATE ... SELECT
133
CREATE TABLE t3 SELECT * FROM t2 WHERE name='[T,U]_axpby';
134
SELECT * FROM t2 WHERE name='[T,U]_axpby';
139
# Test bug with long primary key
144
SEQNO numeric(12 ) not null,
145
MOTYPEID numeric(12 ) not null,
146
MOINSTANCEID numeric(12 ) not null,
147
ATTRID numeric(12 ) not null,
148
VALUE varchar(120) not null,
149
primary key (SEQNO, MOTYPEID, MOINSTANCEID, ATTRID, VALUE )
151
INSERT INTO t1 VALUES (1, 1, 1, 1, 'a');
152
INSERT INTO t1 VALUES (1, 1, 1, 1, 'b');
154
INSERT INTO t1 VALUES (1, 1, 1, 1, 'a');
158
# Test with blob + int key
159
# (Failed for Greg Valure)
164
b int(3) NOT NULL default '0',
165
PRIMARY KEY (a(32),b)
167
INSERT INTO t1 VALUES ('a',1),('a',2);
168
SELECT * FROM t1 WHERE a='a' AND b=2;
169
SELECT * FROM t1 WHERE a='a' AND b in (2);
170
SELECT * FROM t1 WHERE a='a' AND b in (1,2);
174
# Test of create key order
177
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);
182
# Problem with UNIQUE() with NULL parts and auto increment
185
CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT,
187
INSERT INTO t1 (c) VALUES (NULL),(NULL);
189
INSERT INTO t1 (c) VALUES ('a'),('a');
191
DROP TABLE IF EXISTS t1;
192
CREATE TABLE t1 (c CHAR(10) NULL, i INT NOT NULL AUTO_INCREMENT,
194
INSERT INTO t1 (c) VALUES (NULL),(NULL);
196
INSERT INTO t1 (c) VALUES ('a'),('a');
203
create table t1 (i int, a char(200), b text, unique (a), unique (b(300))) charset utf8;
204
insert t1 values (1, repeat('a',210), repeat('b', 310));
205
insert t1 values (2, repeat(0xD0B1,215), repeat(0xD0B1, 310));
206
select i, length(a), length(b), char_length(a), char_length(b) from t1;
207
select i from t1 where a=repeat(_utf8 'a',200);
208
select i from t1 where a=repeat(_utf8 0xD0B1,200);
209
select i from t1 where b=repeat(_utf8 'b',310);
213
# Test of key read with primary key (Bug #3497)
216
CREATE TABLE t1 (id int auto_increment, name char(50), primary key (id)) engine=myisam;
217
insert into t1 (name) values ('a'), ('b'),('c'),('d'),('e'),('f'),('g');
218
explain select 1 from t1 where id =2;
219
explain select 1 from t1 where id =2 or id=3;
220
explain select name from t1 where id =2;
221
ALTER TABLE t1 DROP PRIMARY KEY, ADD INDEX (id);
222
explain select 1 from t1 where id =2;
226
# Test of problem with key read (Bug #3666)
229
CREATE TABLE t1 (numeropost mediumint(8) NOT NULL default '0', numreponse int(10) NOT NULL auto_increment, PRIMARY KEY (numeropost,numreponse), UNIQUE KEY numreponse (numreponse));
230
INSERT INTO t1 (numeropost,numreponse) VALUES ('1','1'),('1','2'),('2','3'),('2','4');
231
SELECT numeropost FROM t1 WHERE numreponse='1';
232
EXPLAIN SELECT numeropost FROM t1 WHERE numreponse='1';
234
SELECT numeropost FROM t1 WHERE numreponse='1';
238
# UNIQUE prefix keys and multi-byte charsets
241
create table t1 (c varchar(30) character set utf8, t text character set utf8, unique (c(2)), unique (t(3))) engine=myisam;
242
show create table t1;
243
insert t1 values ('cccc', 'tttt'),
244
(0xD0B1212223D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1212223D0B1D0B1D0B1D0B1),
245
(0xD0B1222123D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1222123D0B1D0B1D0B1D0B1);
247
insert t1 (c) values ('cc22');
249
insert t1 (t) values ('ttt22');
251
insert t1 (c) values (0xD0B1212322D0B1D0B1D0B1D0B1D0B1);
253
insert t1 (t) values (0xD0B1D0B1212322D0B1D0B1D0B1D0B1);
254
select c from t1 where c='cccc';
255
select t from t1 where t='tttt';
256
select c from t1 where c=0xD0B1212223D0B1D0B1D0B1D0B1D0B1;
257
select t from t1 where t=0xD0B1D0B1212223D0B1D0B1D0B1D0B1;
261
# BUG#6151 - myisam index corruption
263
DROP TABLE IF EXISTS t1;
270
INSERT INTO t1 VALUES (1,'\Z\Z\Z\Z');
271
INSERT INTO t1 VALUES (2,'\Z\Z\Z\Z\Z\Z');
272
INSERT INTO t1 VALUES (3,'\Z\Z\Z\Z');
273
select c1 from t1 where c2='\Z\Z\Z\Z';
274
DELETE FROM t1 WHERE (c1 = 1);
276
select c1 from t1 where c2='\Z\Z\Z\Z';
277
DELETE FROM t1 WHERE (c1 = 3);
279
select c1 from t1 where c2='\Z\Z\Z\Z';
282
# test delete of keys in a different order
285
insert into t1 values(1,"aaaa"),(2,"aaab"),(3,"aaac"),(4,"aaccc");
286
delete from t1 where c1=3;
287
delete from t1 where c1=1;
288
delete from t1 where c1=4;
294
# Bug 6166: index prefix length of 0 not rejected
296
# this test should fail in 5.0
297
# to fix it, remove #ifdef in
298
# file sql_yacc.yy(key_part)
299
# create dedicated error code for this and
300
# and change my_printf_error() to my_error
303
create table t1 (c char(10), index (c(0)));
306
# Bug #6126: Duplicate columns in keys should fail
310
create table t1 (c char(10), index (c,c));
312
create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1));
314
create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2));
316
create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1));
317
create table t1 (c1 char(10), c2 char(10));
319
alter table t1 add key (c1,c1);
321
alter table t1 add key (c2,c1,c1);
323
alter table t1 add key (c1,c2,c1);
325
alter table t1 add key (c1,c1,c2);
329
# Bug#11228: DESC shows arbitrary column as "PRI"
337
show create table t1;
341
# Bug#12565 - ERROR 1034 when running simple UPDATE or DELETE
342
# on large MyISAM table
346
c2 varchar(20) not null,
350
insert into t1 values (1,'');
351
insert into t1 values (2,' \t\tTest String');
352
insert into t1 values (3,' \n\tTest String');
353
update t1 set c2 = 'New Test String' where c1 = 1;
358
# If we use a partial field for a key that is actually the length of the
359
# field, and we extend the field, we end up with a key that includes the
360
# whole new length of the field.
362
create table t1 (a varchar(10), b varchar(10), key(a(10),b(10)));
363
show create table t1;
364
alter table t1 modify b varchar(20);
365
show create table t1;
366
alter table t1 modify a varchar(20);
367
show create table t1;
371
# Bug #11227: Incorrectly reporting 'MUL' vs. 'UNI' on varchar
373
create table t1 (a int not null primary key, b varchar(20) not null unique);
376
create table t1 (a int not null primary key, b int not null unique);
379
create table t1 (a int not null primary key, b varchar(20) not null, unique (b(10)));
382
create table t1 (a int not null primary key, b varchar(20) not null, c varchar(20) not null, unique(b(10),c(10)));
389
# WL#1563 - Modify MySQL to support on-line CREATE/DROP INDEX
390
# To test if this really works, you need to run with --debug
391
# and check the trace file.
393
# Create a table with named and unnamed indexes.
404
index i5 (c1, c2, c3, c4),
405
primary key (c2, c3),
407
show create table t1;
409
alter table t1 drop index c1;
410
alter table t1 add index (c1);
411
# This creates index 'c1_2'.
412
alter table t1 add index (c1);
413
alter table t1 drop index i3;
414
alter table t1 add index i3 (c3);
415
# Two indexes at the same time.
416
alter table t1 drop index i2, drop index i4;
417
alter table t1 add index i2 (c2), add index i4 (c4);
418
# Three indexes, one of them reversely.
419
alter table t1 drop index i2, drop index i4, add index i6 (c2, c4);
420
alter table t1 add index i2 (c2), add index i4 (c4), drop index i6;
421
# include an unique index.
422
alter table t1 drop index i2, drop index i4, add unique i4 (c4);
423
alter table t1 add index i2 (c2), drop index i4, add index i4 (c4);
424
# Modify an index by changing its definition.
425
alter table t1 drop index c2, add index (c2(4),c3(7));
426
# Change nothing. The new key definition is the same as the old one.
427
alter table t1 drop index c2, add index (c2(4),c3(7));
428
# Test primary key handling.
429
alter table t1 add primary key (c1, c2), drop primary key;
430
alter table t1 drop primary key;
431
# Drop is checked first. Primary key must exist.
433
alter table t1 add primary key (c1, c2), drop primary key;
434
show create table t1;
435
# Insert non-unique values.
436
insert into t1 values(1, 'a', 'a', NULL);
437
insert into t1 values(1, 'b', 'b', NULL);
438
# Drop some indexes for new adds.
439
alter table t1 drop index i3, drop index i2, drop index i1;
440
# Add indexes, one is unique on non-unique values.
442
alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1);
447
# Bug #20604: Test for disabled keys with aggregate functions and FORCE INDEX.
450
CREATE TABLE t1( a int, KEY(a) ) ENGINE=MyISAM;
451
INSERT INTO t1 VALUES( 1 );
452
ALTER TABLE t1 DISABLE KEYS;
453
EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);
458
# Bug #24778: Innodb: No result when using ORDER BY
461
a INTEGER auto_increment PRIMARY KEY,
468
a INTEGER auto_increment PRIMARY KEY,
479
INDEX (b, d, e, f, g, h, i, j, c),
483
INSERT INTO t2 VALUES
484
(NULL, 1, 254, '1000-01-01 00:00:00', 257, 0, 0, 0, 0, 0),
485
(NULL, 2, 1, '2004-11-30 12:00:00', 1, 0, 0, 0, 0, 0),
486
(NULL, 3, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -21600, 0),
487
(NULL, 4, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -10800, 0),
488
(NULL, 5, 1, '2004-11-30 12:00:00', 1, 0, 0, 5, -10800, 0),
489
(NULL, 6, 1, '2004-11-30 12:00:00', 102, 0, 0, 0, 0, 0),
490
(NULL, 7, 1, '2004-11-30 12:00:00', 105, 2, 0, 0, 0, 0),
491
(NULL, 8, 1, '2004-11-30 12:00:00', 105, 10, 0, 0, 0, 0);
493
INSERT INTO t1 (b, c, d) VALUES
494
(3388000, -553000, NULL),
495
(3388000, -553000, NULL);
497
# psergey/sergefp: This crashes for a mysterious reason with MRR + Semijoin
498
# opts. TODO: fix it.
500
#FROM t2 c JOIN t1 pa ON c.b = pa.a
508
# Bug #31137: Assertion failed: primary_key_no == -1 || primary_key_no == 0
510
create table t1(a int not null, key aa(a),
511
b char(10) not null, unique key bb(b(1)),
512
c char(4) not null, unique key cc(c));
514
show create table t1;
516
create table t1(a int not null, key aa(a),
517
b char(10) not null, unique key bb(b(1)),
520
alter table t1 add unique key cc(c);
522
show create table t1;
525
--echo End of 5.0 tests
528
# Bug #31148: bool close_thread_table(THD*, TABLE**): Assertion
529
# `table->key_read == 0' failed.
533
DROP TABLE IF EXISTS t1;
536
CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT);
538
INSERT INTO t1 VALUES (), (), ();
545
GROUP BY GREATEST(LAST_INSERT_ID(), t1.a) ASC
552
# Bug #31974: Wrong EXPLAIN output
555
CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
556
INSERT INTO t1 (a, b)
558
(1,1), (1,2), (1,3), (1,4), (1,5),
559
(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
560
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
561
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
562
SELECT 1 as RES FROM t1 AS t1_outer WHERE
563
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
added
removed
tests/t/key.test
