140
127
# test of myisam with huge number of packed fields
143
create table t1 (i1 int, i2 int, i3 int, i4 int, i5 int, i6 int, i7 int, i8
130
create temporary table t1 (i1 int, i2 int, i3 int, i4 int, i5 int, i6 int, i7 int, i8
144
131
int, i9 int, i10 int, i11 int, i12 int, i13 int, i14 int, i15 int, i16 int, i17
145
132
int, i18 int, i19 int, i20 int, i21 int, i22 int, i23 int, i24 int, i25 int,
146
133
i26 int, i27 int, i28 int, i29 int, i30 int, i31 int, i32 int, i33 int, i34
350
CREATE TABLE t1 (a varchar(300), b varchar(300), c varchar(300), d varchar(300), e varchar(300), KEY t1 (a, b, c, d, e)) ENGINE=MyISAM;
351
CREATE TABLE t1 (a varchar(300), b varchar(300), c varchar(300), d varchar(300), e varchar(300)) ENGINE=MyISAM;
333
CREATE TEMPORARY TABLE t1 (a varchar(300), b varchar(300), c varchar(300), d varchar(300), e varchar(300), KEY t1 (a, b, c, d, e)) ENGINE=MyISAM;
334
CREATE TEMPORARY TABLE t1 (a varchar(300), b varchar(300), c varchar(300), d varchar(300), e varchar(300)) ENGINE=MyISAM;
353
336
ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e);
357
340
# Test of cardinality of keys with NULL
360
CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a)) ENGINE=MyISAM;
343
CREATE TEMPORARY TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a)) ENGINE=MyISAM;
361
344
INSERT into t1 values (0, null, 0), (0, null, 1), (0, null, 2), (0, null,3), (1,1,4);
362
345
create table t2 (a int not null, b int, c int, key(b), key(c), key(a));
363
346
INSERT into t2 values (1,1,1), (2,2,2);
510
492
select c1 from t1 order by c1 limit 1;
514
# Bug #14400 Join could miss concurrently inserted row
516
# @TODO The below test hangs drizzle. Commenting out for now so I can continue with this test. - JRP
519
#create table t1 (a int not null, primary key(a));
520
#create table t2 (a int not null, b int not null, primary key(a,b));
521
#insert into t1 values (1),(2),(3),(4),(5),(6);
522
#insert into t2 values (1,1),(2,1);
523
#lock tables t1 read local, t2 read local;
524
#select straight_join * from t1,t2 force index (primary) where t1.a=t2.a;
525
#connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
526
#insert into t2 values(2,0);
529
#select straight_join * from t1,t2 force index (primary) where t1.a=t2.a;
534
#CREATE TABLE t1 (c1 varchar(250) NOT NULL);
535
#CREATE TABLE t2 (c1 varchar(250) NOT NULL, PRIMARY KEY (c1));
536
#INSERT INTO t1 VALUES ('test000001'), ('test000002'), ('test000003');
537
#INSERT INTO t2 VALUES ('test000002'), ('test000003'), ('test000004');
538
#LOCK TABLES t1 READ LOCAL, t2 READ LOCAL;
539
#SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2
540
# WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1;
541
#connect (con1,localhost,root,,);
543
#INSERT INTO t2 VALUES ('test000001'), ('test000005');
546
#SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2
547
# WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1;
551
495
# End of 4.0 tests
553
create table t1 (a int, b varchar(200), c text not null) checksum=1;
554
create table t2 (a int, b varchar(200), c text not null) checksum=0;
497
create table t1 (a int, b varchar(200), c text not null);
498
create table t2 (a int, b varchar(200), c text not null);
555
499
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
556
500
insert t2 select * from t1;
557
501
checksum table t1, t2, t3 quick;
621
565
checksum table t2;
622
566
drop table t1, t2;
625
# BUG#12232: New myisam_stats_method variable.
627
# @TODO The following segfaults. Disabling for now - JRP
629
#show variables like 'myisam_stats_method';
631
#create table t1 (a int, key(a));
632
#insert into t1 values (0),(1),(2),(3),(4);
633
#insert into t1 select NULL from t1;
635
# default: NULLs considered inequal
638
#insert into t1 values (11);
639
#delete from t1 where a=11;
643
# Set nulls to be equal:
644
#set myisam_stats_method=nulls_equal;
645
#show variables like 'myisam_stats_method';
646
#insert into t1 values (11);
647
#delete from t1 where a=11;
652
#insert into t1 values (11);
653
#delete from t1 where a=11;
658
# Set nulls back to be equal
659
#set myisam_stats_method=DEFAULT;
660
#show variables like 'myisam_stats_method';
661
#insert into t1 values (11);
662
#delete from t1 where a=11;
667
#insert into t1 values (11);
668
#delete from t1 where a=11;
675
# WL#2609, CSC#XXXX: MyISAM
676
#set myisam_stats_method=nulls_ignored;
677
#show variables like 'myisam_stats_method';
680
# a char(3), b char(4), c char(5), d char(6),
683
#insert into t1 values ('bcd','def1', NULL, 'zz');
684
#insert into t1 values ('bcd','def2', NULL, 'zz');
685
#insert into t1 values ('bce','def1', 'yuu', NULL);
686
#insert into t1 values ('bce','def2', NULL, 'quux');
693
#set myisam_stats_method=DEFAULT;
696
568
# BUG#13814 - key value packed incorrectly for TINYBLOBs
796
659
SELECT _id FROM t1;
799
# Test REPAIR QUICK. This retains the old data file.
801
`_id` int NOT NULL default '0',
805
`loverlap` int default NULL,
806
`roverlap` int default NULL,
807
`lneighbor_id` int default NULL,
808
`rneighbor_id` int default NULL,
809
`length_` int default NULL,
812
`_obj_class` text NOT NULL,
814
UNIQUE KEY `sequence_name_index` (`name`(50)),
818
INSERT INTO t1 VALUES
819
(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''),
820
(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''),
821
(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''),
822
(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''),
823
(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''),
824
(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''),
825
(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''),
826
(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''),
827
(9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9','');
830
DELETE FROM t1 WHERE _id < 8;
831
--replace_column 6 # 7 # 8 # 9 # 11 # 12 # 13 # 14 # 15 # 16 #
832
SHOW TABLE STATUS LIKE 't1';
833
CHECK TABLE t1 EXTENDED;
834
REPAIR TABLE t1 QUICK;
835
CHECK TABLE t1 EXTENDED;
836
--replace_column 6 # 7 # 8 # 9 # 11 # 12 # 13 # 14 # 15 # 16 #
837
SHOW TABLE STATUS LIKE 't1';
841
662
SET GLOBAL myisam_repair_threads=1;
842
663
SHOW VARIABLES LIKE 'myisam_repair%';
845
# BUG#21310 - Trees in SQL causing a "crashed" table with MyISAM storage
849
# A simplified test case that reflect crashed table issue.
850
CREATE TABLE t1(a VARCHAR(16)) ENGINE=MyISAM;
851
INSERT INTO t1 VALUES('aaaaaaaa'),(NULL);
852
UPDATE t1 AS ta1, t1 AS ta2 SET ta1.a='aaaaaaaaaaaaaaaa';
856
# A test case that reflect wrong result set.
857
CREATE TABLE t1(a INT) ENGINE=MyISAM;
858
INSERT INTO t1 VALUES(1),(2);
859
UPDATE t1,t1 AS t2 SET t1.a=t1.a+2 WHERE t1.a=t2.a-1;
860
SELECT * FROM t1 ORDER BY a;
864
666
# Bug#24607 - MyISAM pointer size determined incorrectly
866
CREATE TABLE t1 (c1 TEXT) ENGINE=MyISAM AVG_ROW_LENGTH=70100 MAX_ROWS=4100100100;
668
CREATE TEMPORARY TABLE t1 (c1 TEXT) ENGINE=MyISAM;
867
669
--replace_column 5 X 6 X 7 X 9 X 10 X 11 X 12 X 13 X 14 X 16 X
868
670
SHOW TABLE STATUS LIKE 't1';
1030
create table t1 (v varchar(65530), key(v));
1032
create table t1 (v varchar(65536));
1034
create table t1 (v varchar(65530));
833
create temporary table t1 (v varchar(65530), key(v));
835
create temporary table t1 (v varchar(65536));
837
create temporary table t1 (v varchar(65530));
1036
839
# MyISAM specific varchar tests
1038
create table t1 (v varchar(65535));
841
create temporary table t1 (v varchar(65535));
1040
843
eval set storage_engine=$default;
1043
# Test concurrent insert
1044
# First with static record length
1046
#@TODO The below test fails with unknown system variable
1049
#set @save_concurrent_insert=@@concurrent_insert;
1050
#set global concurrent_insert=1;
1051
#create table t1 (a int);
1052
#insert into t1 values (1),(2),(3),(4),(5);
1053
#lock table t1 read local;
1054
#connect (con1,localhost,root,,);
1056
# Insert in table without hole
1057
#insert into t1 values(6),(7);
1058
#connection default;
1060
#delete from t1 where a>=3 and a<=4;
1061
#lock table t1 read local;
1063
#set global concurrent_insert=2;
1064
# Insert in table with hole -> Should insert at end
1065
#insert into t1 values (8),(9);
1066
#connection default;
1069
#insert into t1 values (10),(11),(12);
1075
# Same test with dynamic record length
1076
#create table t1 (a int, b varchar(30) default "hello");
1077
#insert into t1 (a) values (1),(2),(3),(4),(5);
1078
#lock table t1 read local;
1079
#connect (con1,localhost,root,,);
1081
# Insert in table without hole
1082
#insert into t1 (a) values(6),(7);
1083
#connection default;
1085
#delete from t1 where a>=3 and a<=4;
1086
#lock table t1 read local;
1088
#set global concurrent_insert=2;
1089
## Insert in table with hole -> Should insert at end
1090
#insert into t1 (a) values (8),(9);
1091
#connection default;
1094
#insert into t1 (a) values (10),(11),(12);
1099
#set global concurrent_insert=@save_concurrent_insert;
1102
846
# BUG#9622 - ANALYZE TABLE and ALTER TABLE .. ENABLE INDEX produce
1103
847
# different statistics on the same table with NULL values.
1118
# Bug#10056 - PACK_KEYS option take values greater than 1 while creating table
1120
create table t1 (c1 int) engine=myisam pack_keys=0;
1121
create table t2 (c1 int) engine=myisam pack_keys=1;
1122
create table t3 (c1 int) engine=myisam pack_keys=default;
1124
create table t4 (c1 int) engine=myisam pack_keys=2;
1125
drop table t1, t2, t3;
1129
862
# Bug#28476: force index on a disabled myisam index gives error 124
1131
CREATE TABLE t1(a INT, b INT, KEY inx (a), UNIQUE KEY uinx (b)) ENGINE=MyISAM;
864
CREATE TEMPORARY TABLE t1(a INT, b INT, KEY inx (a), UNIQUE KEY uinx (b)) ENGINE=MyISAM;
1132
865
INSERT INTO t1(a,b) VALUES (1,1),(2,2),(3,3),(4,4),(5,5);
1133
866
SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1;
1134
867
ALTER TABLE t1 DISABLE KEYS;
1197
928
# Test of key_block_size
1200
create table t1 (a int not null, key `a` (a) key_block_size=1024) ENGINE=MyISAM;
1201
show create table t1;
1204
create table t1 (a int not null, key `a` (a) key_block_size=2048) ENGINE=MyISAM;
1205
show create table t1;
1208
create table t1 (a varchar(2048), key `a` (a)) ENGINE=MyISAM;
1209
show create table t1;
1212
create table t1 (a varchar(2048), key `a` (a) key_block_size=1024) ENGINE=MyISAM;
1213
show create table t1;
1216
create table t1 (a int not null, b varchar(2048), key (a), key(b)) ENGINE=MyISAM key_block_size=1024;
931
create temporary table t1 (a int not null, key `a` (a) key_block_size=1024) ENGINE=MyISAM;
932
show create table t1;
935
create temporary table t1 (a int not null, key `a` (a) key_block_size=2048) ENGINE=MyISAM;
936
show create table t1;
939
create temporary table t1 (a varchar(2048), key `a` (a)) ENGINE=MyISAM;
940
show create table t1;
943
create temporary table t1 (a varchar(2048), key `a` (a) key_block_size=1024) ENGINE=MyISAM;
944
show create table t1;
947
create temporary table t1 (a int not null, b varchar(2048), key (a), key(b)) ENGINE=MyISAM key_block_size=1024;
1217
948
show create table t1;
1218
949
alter table t1 key_block_size=2048;
1219
950
show create table t1;
1224
955
show create table t1;
1227
create table t1 (a int not null, b varchar(2048), key (a), key(b)) ENGINE=MyISAM key_block_size=8192;
1228
show create table t1;
1231
create table t1 (a int not null, b varchar(2048), key (a) key_block_size=1024, key(b)) ENGINE=MyISAM key_block_size=8192;
1232
show create table t1;
1235
create table t1 (a int not null, b int, key (a) key_block_size=1024, key(b) key_block_size=8192) ENGINE=MyISAM key_block_size=16384;
958
create temporary table t1 (a int not null, b varchar(2048), key (a), key(b)) ENGINE=MyISAM key_block_size=8192;
959
show create table t1;
962
create temporary table t1 (a int not null, b varchar(2048), key (a) key_block_size=1024, key(b)) ENGINE=MyISAM key_block_size=8192;
963
show create table t1;
966
create temporary table t1 (a int not null, b int, key (a) key_block_size=1024, key(b) key_block_size=8192) ENGINE=MyISAM key_block_size=16384;
1236
967
show create table t1;
1240
971
# Test limits and errors of key_block_size
1242
create table t1 (a int not null, key `a` (a) key_block_size=512) ENGINE=MyISAM;
1243
show create table t1;
1246
create table t1 (a varchar(2048), key `a` (a) key_block_size=1000000000000000000) ENGINE=MyISAM;
1247
show create table t1;
1250
create table t1 (a int not null, key `a` (a) key_block_size=1025) ENGINE=MyISAM;
1251
show create table t1;
1255
create table t1 (a int not null, key key_block_size=1024 (a)) ENGINE=MyISAM;
1257
create table t1 (a int not null, key `a` key_block_size=1024 (a)) ENGINE=MyISAM;
973
create temporary table t1 (a int not null, key `a` (a) key_block_size=512) ENGINE=MyISAM;
974
show create table t1;
977
create temporary table t1 (a varchar(2048), key `a` (a) key_block_size=1000000000000000000) ENGINE=MyISAM;
978
show create table t1;
981
create temporary table t1 (a int not null, key `a` (a) key_block_size=1025) ENGINE=MyISAM;
982
show create table t1;
986
create temporary table t1 (a int not null, key key_block_size=1024 (a)) ENGINE=MyISAM;
988
create temporary table t1 (a int not null, key `a` key_block_size=1024 (a)) ENGINE=MyISAM;
1260
991
# Bug#22119 - Changing MI_KEY_BLOCK_LENGTH makes a wrong myisamchk
993
CREATE temporary TABLE t1 (
1264
995
c2 VARCHAR(300),
1265
996
KEY (c1) KEY_BLOCK_SIZE 1024,