1drop table if exists t1,t2; 2create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100; 3insert into t1 values(1,1),(2,2),(3,3),(4,4); 4delete from t1 where a=1 or a=0; 5show keys from t1; 6Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 7t1 0 PRIMARY 1 a NULL 3 NULL NULL HASH 8select * from t1; 9a b 102 2 113 3 124 4 13select * from t1 where a=4; 14a b 154 4 16update t1 set b=5 where a=4; 17update t1 set b=b+1 where a>=3; 18replace t1 values (3,3); 19select * from t1; 20a b 212 2 223 3 234 6 24alter table t1 add c int not null, add key using HASH (c,a); 25drop table t1; 26create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps"; 27insert into t1 values(1,1),(2,2),(3,3),(4,4); 28delete from t1 where a > 0; 29select * from t1; 30a b 31drop table t1; 32create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps"; 33insert into t1 values(1,1),(2,2),(3,3),(4,4); 34alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table"; 35select * from t1; 36a b 371 1 382 2 393 3 404 4 41drop table t1; 42create table t1 (a int not null) engine=heap; 43insert into t1 values (869751),(736494),(226312),(802616),(728912); 44select * from t1 where a > 736494; 45a 46869751 47802616 48alter table t1 add unique uniq_id using HASH (a); 49select * from t1 where a > 736494; 50a 51869751 52802616 53select * from t1 where a = 736494; 54a 55736494 56select * from t1 where a=869751 or a=736494; 57a 58736494 59869751 60select * from t1 where a in (869751,736494,226312,802616); 61a 62226312 63736494 64802616 65869751 66alter table t1 engine=myisam; 67explain select * from t1 where a in (869751,736494,226312,802616); 68id select_type table type possible_keys key key_len ref rows Extra 691 SIMPLE t1 range uniq_id uniq_id 4 NULL 4 Using where; Using index 70drop table t1; 71create table t1 (x int not null, y int not null, key x using HASH (x), unique y using HASH (y)) 72engine=heap; 73insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6); 74select * from t1 where x=1; 75x y 761 3 771 1 78select * from t1,t1 as t2 where t1.x=t2.y; 79x y x y 801 1 1 1 812 2 2 2 821 3 1 1 832 4 2 2 842 5 2 2 852 6 2 2 86explain select * from t1,t1 as t2 where t1.x=t2.y; 87id select_type table type possible_keys key key_len ref rows Extra 881 SIMPLE t1 ALL x NULL NULL NULL 6 NULL 891 SIMPLE t2 eq_ref y y 4 test.t1.x 1 NULL 90drop table t1; 91create table t1 (a int) engine=heap; 92insert into t1 values(1); 93select max(a) from t1; 94max(a) 951 96drop table t1; 97CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key using HASH (a), key using HASH (b) ) ENGINE=HEAP; 98insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); 99select * from t1 where a=1; 100a b 1011 6 1021 5 1031 4 1041 3 1051 2 1061 1 107insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); 108select * from t1 where a=1; 109a b 1101 6 1111 5 1121 4 1131 3 1141 2 1151 1 1161 6 1171 5 1181 4 1191 3 1201 2 1211 1 122drop table t1; 123create table t1 (id int unsigned not null, primary key using HASH (id)) engine=HEAP; 124insert into t1 values(1); 125select max(id) from t1; 126max(id) 1271 128insert into t1 values(2); 129select max(id) from t1; 130max(id) 1312 132replace into t1 values(1); 133drop table t1; 134create table t1 (n int) engine=heap; 135drop table t1; 136create table t1 (n int) engine=heap; 137drop table if exists t1; 138CREATE table t1(f1 int not null,f2 char(20) not 139null,index(f2)) engine=heap; 140INSERT into t1 set f1=12,f2="bill"; 141INSERT into t1 set f1=13,f2="bill"; 142INSERT into t1 set f1=14,f2="bill"; 143INSERT into t1 set f1=15,f2="bill"; 144INSERT into t1 set f1=16,f2="ted"; 145INSERT into t1 set f1=12,f2="ted"; 146INSERT into t1 set f1=12,f2="ted"; 147INSERT into t1 set f1=12,f2="ted"; 148INSERT into t1 set f1=12,f2="ted"; 149delete from t1 where f2="bill"; 150select * from t1; 151f1 f2 15216 ted 15312 ted 15412 ted 15512 ted 15612 ted 157drop table t1; 158create table t1 (btn char(10) not null, key using HASH (btn)) engine=heap; 159insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i"); 160explain select * from t1 where btn like "q%"; 161id select_type table type possible_keys key key_len ref rows Extra 1621 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where 163select * from t1 where btn like "q%"; 164btn 165alter table t1 add column new_col char(1) not null, add key using HASH (btn,new_col), drop key btn; 166update t1 set new_col=left(btn,1); 167explain select * from t1 where btn="a"; 168id select_type table type possible_keys key key_len ref rows Extra 1691 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where 170explain select * from t1 where btn="a" and new_col="a"; 171id select_type table type possible_keys key key_len ref rows Extra 1721 SIMPLE t1 ref btn btn 11 const,const 2 Using where 173drop table t1; 174CREATE TABLE t1 ( 175a int default NULL, 176b int default NULL, 177KEY a using HASH (a), 178UNIQUE b using HASH (b) 179) engine=heap; 180INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3); 181SELECT * FROM t1 WHERE a=NULL; 182a b 183explain SELECT * FROM t1 WHERE a IS NULL; 184id select_type table type possible_keys key key_len ref rows Extra 1851 SIMPLE t1 ref a a 5 const 2 Using where 186SELECT * FROM t1 WHERE a<=>NULL; 187a b 188NULL 99 189SELECT * FROM t1 WHERE b=NULL; 190a b 191explain SELECT * FROM t1 WHERE b IS NULL; 192id select_type table type possible_keys key key_len ref rows Extra 1931 SIMPLE t1 ref b b 5 const 1 Using where 194SELECT * FROM t1 WHERE b<=>NULL; 195a b 19699 NULL 197INSERT INTO t1 VALUES (1,3); 198ERROR 23000: Duplicate entry '3' for key 'b' 199DROP TABLE t1; 200CREATE TABLE t1 (a int not null, primary key using HASH (a)) engine=heap; 201INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); 202DELETE from t1 where a < 100; 203SELECT * from t1; 204a 205DROP TABLE t1; 206create table t1 207( 208a char(8) not null, 209b char(20) not null, 210c int not null, 211key (a) 212) engine=heap; 213insert into t1 values ('aaaa', 'prefill-hash=5',0); 214insert into t1 values ('aaab', 'prefill-hash=0',0); 215insert into t1 values ('aaac', 'prefill-hash=7',0); 216insert into t1 values ('aaad', 'prefill-hash=2',0); 217insert into t1 values ('aaae', 'prefill-hash=1',0); 218insert into t1 values ('aaaf', 'prefill-hash=4',0); 219insert into t1 values ('aaag', 'prefill-hash=3',0); 220insert into t1 values ('aaah', 'prefill-hash=6',0); 221explain select * from t1 where a='aaaa'; 222id select_type table type possible_keys key key_len ref rows Extra 2231 SIMPLE t1 ref a a 8 const 2 Using where 224explain select * from t1 where a='aaab'; 225id select_type table type possible_keys key key_len ref rows Extra 2261 SIMPLE t1 ref a a 8 const 2 Using where 227explain select * from t1 where a='aaac'; 228id select_type table type possible_keys key key_len ref rows Extra 2291 SIMPLE t1 ref a a 8 const 2 Using where 230explain select * from t1 where a='aaad'; 231id select_type table type possible_keys key key_len ref rows Extra 2321 SIMPLE t1 ref a a 8 const 2 Using where 233insert into t1 select * from t1; 234flush tables; 235explain select * from t1 where a='aaaa'; 236id select_type table type possible_keys key key_len ref rows Extra 2371 SIMPLE t1 ref a a 8 const 2 Using where 238explain select * from t1 where a='aaab'; 239id select_type table type possible_keys key key_len ref rows Extra 2401 SIMPLE t1 ref a a 8 const 2 Using where 241explain select * from t1 where a='aaac'; 242id select_type table type possible_keys key key_len ref rows Extra 2431 SIMPLE t1 ref a a 8 const 2 Using where 244explain select * from t1 where a='aaad'; 245id select_type table type possible_keys key key_len ref rows Extra 2461 SIMPLE t1 ref a a 8 const 2 Using where 247flush tables; 248explain select * from t1 where a='aaaa'; 249id select_type table type possible_keys key key_len ref rows Extra 2501 SIMPLE t1 ref a a 8 const 2 Using where 251explain select * from t1 where a='aaab'; 252id select_type table type possible_keys key key_len ref rows Extra 2531 SIMPLE t1 ref a a 8 const 2 Using where 254explain select * from t1 where a='aaac'; 255id select_type table type possible_keys key key_len ref rows Extra 2561 SIMPLE t1 ref a a 8 const 2 Using where 257explain select * from t1 where a='aaad'; 258id select_type table type possible_keys key key_len ref rows Extra 2591 SIMPLE t1 ref a a 8 const 2 Using where 260create table t2 as select * from t1; 261delete from t1; 262insert into t1 select * from t2; 263explain select * from t1 where a='aaaa'; 264id select_type table type possible_keys key key_len ref rows Extra 2651 SIMPLE t1 ref a a 8 const 2 Using where 266explain select * from t1 where a='aaab'; 267id select_type table type possible_keys key key_len ref rows Extra 2681 SIMPLE t1 ref a a 8 const 2 Using where 269explain select * from t1 where a='aaac'; 270id select_type table type possible_keys key key_len ref rows Extra 2711 SIMPLE t1 ref a a 8 const 2 Using where 272explain select * from t1 where a='aaad'; 273id select_type table type possible_keys key key_len ref rows Extra 2741 SIMPLE t1 ref a a 8 const 2 Using where 275drop table t1, t2; 276create table t1 ( 277id int unsigned not null primary key auto_increment, 278name varchar(20) not null, 279index heap_idx(name), 280index btree_idx using btree(name) 281) engine=heap; 282create table t2 ( 283id int unsigned not null primary key auto_increment, 284name varchar(20) not null, 285index btree_idx using btree(name), 286index heap_idx(name) 287) engine=heap; 288insert into t1 (name) values ('Matt'), ('Lilu'), ('Corbin'), ('Carly'), 289('Suzy'), ('Hoppy'), ('Burrito'), ('Mimi'), ('Sherry'), ('Ben'), ('Phil'), 290('Emily'), ('Mike'); 291insert into t2 select * from t1; 292explain select * from t1 where name='matt'; 293id select_type table type possible_keys key key_len ref rows Extra 2941 SIMPLE t1 ref heap_idx,btree_idx btree_idx 22 const 1 Using where 295explain select * from t2 where name='matt'; 296id select_type table type possible_keys key key_len ref rows Extra 2971 SIMPLE t2 ref btree_idx,heap_idx btree_idx 22 const 1 Using where 298explain select * from t1 where name='Lilu'; 299id select_type table type possible_keys key key_len ref rows Extra 3001 SIMPLE t1 ref heap_idx,btree_idx btree_idx 22 const 1 Using where 301explain select * from t2 where name='Lilu'; 302id select_type table type possible_keys key key_len ref rows Extra 3031 SIMPLE t2 ref btree_idx,heap_idx btree_idx 22 const 1 Using where 304explain select * from t1 where name='Phil'; 305id select_type table type possible_keys key key_len ref rows Extra 3061 SIMPLE t1 ref heap_idx,btree_idx btree_idx 22 const 1 Using where 307explain select * from t2 where name='Phil'; 308id select_type table type possible_keys key key_len ref rows Extra 3091 SIMPLE t2 ref btree_idx,heap_idx btree_idx 22 const 1 Using where 310explain select * from t1 where name='Lilu'; 311id select_type table type possible_keys key key_len ref rows Extra 3121 SIMPLE t1 ref heap_idx,btree_idx btree_idx 22 const 1 Using where 313explain select * from t2 where name='Lilu'; 314id select_type table type possible_keys key key_len ref rows Extra 3151 SIMPLE t2 ref btree_idx,heap_idx btree_idx 22 const 1 Using where 316insert into t1 (name) select name from t2; 317insert into t1 (name) select name from t2; 318insert into t1 (name) select name from t2; 319insert into t1 (name) select name from t2; 320insert into t1 (name) select name from t2; 321insert into t1 (name) select name from t2; 322flush tables; 323select count(*) from t1 where name='Matt'; 324count(*) 3257 326explain select * from t1 ignore index (btree_idx) where name='matt'; 327id select_type table type possible_keys key key_len ref rows Extra 3281 SIMPLE t1 ref heap_idx heap_idx 22 const 7 Using where 329show index from t1; 330Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 331t1 0 PRIMARY 1 id NULL 91 NULL NULL HASH 332t1 1 heap_idx 1 name NULL 13 NULL NULL HASH 333t1 1 btree_idx 1 name A NULL NULL NULL BTREE 334show index from t1; 335Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 336t1 0 PRIMARY 1 id NULL 91 NULL NULL HASH 337t1 1 heap_idx 1 name NULL 13 NULL NULL HASH 338t1 1 btree_idx 1 name A NULL NULL NULL BTREE 339create table t3 340( 341a varchar(20) not null, 342b varchar(20) not null, 343key (a,b) 344) engine=heap; 345insert into t3 select name, name from t1; 346show index from t3; 347Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 348t3 1 a 1 a NULL NULL NULL NULL HASH 349t3 1 a 2 b NULL 13 NULL NULL HASH 350show index from t3; 351Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 352t3 1 a 1 a NULL NULL NULL NULL HASH 353t3 1 a 2 b NULL 13 NULL NULL HASH 354explain select * from t1 ignore key(btree_idx), t3 where t1.name='matt' and t3.a = concat('',t1.name) and t3.b=t1.name; 355id select_type table type possible_keys key key_len ref rows Extra 3561 SIMPLE t1 ref heap_idx heap_idx 22 const 7 Using where 3571 SIMPLE t3 ref a a 44 func,const 7 Using where 358drop table t1, t2, t3; 359create temporary table t1 ( a int, index (a) ) engine=memory; 360insert into t1 values (1),(2),(3),(4),(5); 361select a from t1 where a in (1,3); 362a 3631 3643 365explain select a from t1 where a in (1,3); 366id select_type table type possible_keys key key_len ref rows Extra 3671 SIMPLE t1 range a a 5 NULL 4 Using where 368drop table t1; 369End of 4.1 tests 370CREATE TABLE t1(col1 VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 371col2 VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 372UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY; 373INSERT INTO t1 VALUES('A', 'A'); 374INSERT INTO t1 VALUES('A ', 'A '); 375ERROR 23000: Duplicate entry 'A -A ' for key 'key1' 376DROP TABLE t1; 377CREATE TABLE t1(col1 VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, 378col2 VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, 379UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY; 380INSERT INTO t1 VALUES('A', 'A'); 381INSERT INTO t1 VALUES('A ', 'A '); 382ERROR 23000: Duplicate entry 'A -A ' for key 'key1' 383DROP TABLE t1; 384End of 5.0 tests 385# 386# Bug #55472: Assertion failed in heap_rfirst function of hp_rfirst.c 387# on DELETE statement 388# 389CREATE TABLE t1 (col_int_nokey INT, 390col_int_key INT, 391INDEX(col_int_key) USING HASH) ENGINE = HEAP; 392INSERT INTO t1 (col_int_nokey, col_int_key) VALUES (3, 0), (4, 0), (3, 1); 393DELETE FROM t1 WHERE col_int_nokey = 5 ORDER BY col_int_key LIMIT 2; 394DROP TABLE t1; 395# 396# Bug #44771: Unique Hash index in memory engine will give wrong 397# query result for NULL value. 398# 399CREATE TABLE t1 400( 401pk INT PRIMARY KEY, 402val INT, 403UNIQUE KEY USING HASH(val) 404) ENGINE=MEMORY; 405INSERT INTO t1 VALUES (1, NULL); 406INSERT INTO t1 VALUES (2, NULL); 407INSERT INTO t1 VALUES (3, NULL); 408INSERT INTO t1 VALUES (4, NULL); 409SELECT * FROM t1 WHERE val IS NULL; 410pk val 4114 NULL 4123 NULL 4132 NULL 4141 NULL 415EXPLAIN SELECT * FROM t1 WHERE val IS NULL; 416id select_type table type possible_keys key key_len ref rows Extra 4171 SIMPLE t1 ref val val 5 const 1 Using where 418DROP TABLE t1; 419End of 5.5 tests 420