6
drop table if exists t1,t2;
9
create 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;
10
insert into t1 values(1,1),(2,2),(3,3),(4,4);
11
delete from t1 where a=1 or a=0;
12
#show table status like "t1";
15
select * from t1 where a=4;
16
update t1 set b=5 where a=4;
17
update t1 set b=b+1 where a>=3;
18
replace t1 values (3,3);
20
alter table t1 add c int not null, add key using HASH (c,a);
23
create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps";
24
insert into t1 values(1,1),(2,2),(3,3),(4,4);
25
delete from t1 where a > 0;
29
create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps";
30
insert into t1 values(1,1),(2,2),(3,3),(4,4);
31
alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table";
32
#show table status like "t1";
36
create table t1 (a int not null) engine=heap;
37
insert into t1 values (869751),(736494),(226312),(802616),(728912);
38
select * from t1 where a > 736494;
39
alter table t1 add unique uniq_id using HASH (a);
40
select * from t1 where a > 736494;
41
select * from t1 where a = 736494;
42
select * from t1 where a=869751 or a=736494;
43
select * from t1 where a in (869751,736494,226312,802616);
44
alter table t1 engine=myisam;
45
explain select * from t1 where a in (869751,736494,226312,802616);
48
create table t1 (x int not null, y int not null, key x using HASH (x), unique y using HASH (y))
50
insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
51
select * from t1 where x=1;
52
select * from t1,t1 as t2 where t1.x=t2.y;
53
explain select * from t1,t1 as t2 where t1.x=t2.y;
56
create table t1 (a int) engine=heap;
57
insert into t1 values(1);
58
select max(a) from t1;
61
CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key using HASH (a), key using HASH (b) ) ENGINE=HEAP;
62
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
63
select * from t1 where a=1;
64
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
65
select * from t1 where a=1;
68
create table t1 (id int unsigned not null, primary key using HASH (id)) engine=HEAP;
69
insert into t1 values(1);
70
select max(id) from t1;
71
insert into t1 values(2);
72
select max(id) from t1;
73
replace into t1 values(1);
76
create table t1 (n int) engine=heap;
79
create table t1 (n int) engine=heap;
80
drop table if exists t1;
82
# Test of non unique index
84
CREATE table t1(f1 int not null,f2 char(20) not
85
null,index(f2)) engine=heap;
86
INSERT into t1 set f1=12,f2="bill";
87
INSERT into t1 set f1=13,f2="bill";
88
INSERT into t1 set f1=14,f2="bill";
89
INSERT into t1 set f1=15,f2="bill";
90
INSERT into t1 set f1=16,f2="ted";
91
INSERT into t1 set f1=12,f2="ted";
92
INSERT into t1 set f1=12,f2="ted";
93
INSERT into t1 set f1=12,f2="ted";
94
INSERT into t1 set f1=12,f2="ted";
95
delete from t1 where f2="bill";
100
# Test when using part key searches
103
create table t1 (btn char(10) not null, key using HASH (btn)) engine=heap;
104
insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
105
explain select * from t1 where btn like "q%";
106
select * from t1 where btn like "q%";
107
alter table t1 add column new_col char(1) not null, add key using HASH (btn,new_col), drop key btn;
108
update t1 set new_col=left(btn,1);
109
explain select * from t1 where btn="a";
110
explain select * from t1 where btn="a" and new_col="a";
120
KEY a using HASH (a),
121
UNIQUE b using HASH (b)
123
INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);
124
SELECT * FROM t1 WHERE a=NULL;
125
explain SELECT * FROM t1 WHERE a IS NULL;
126
SELECT * FROM t1 WHERE a<=>NULL;
127
SELECT * FROM t1 WHERE b=NULL;
128
explain SELECT * FROM t1 WHERE b IS NULL;
129
SELECT * FROM t1 WHERE b<=>NULL;
132
INSERT INTO t1 VALUES (1,3);
136
# Test when deleting all rows
139
CREATE TABLE t1 (a int not null, primary key using HASH (a)) engine=heap;
140
INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
141
DELETE from t1 where a < 100;
147
# Hash index # records estimate test
157
insert into t1 values ('aaaa', 'prefill-hash=5',0);
158
insert into t1 values ('aaab', 'prefill-hash=0',0);
159
insert into t1 values ('aaac', 'prefill-hash=7',0);
160
insert into t1 values ('aaad', 'prefill-hash=2',0);
161
insert into t1 values ('aaae', 'prefill-hash=1',0);
162
insert into t1 values ('aaaf', 'prefill-hash=4',0);
163
insert into t1 values ('aaag', 'prefill-hash=3',0);
164
insert into t1 values ('aaah', 'prefill-hash=6',0);
166
explain select * from t1 where a='aaaa';
167
explain select * from t1 where a='aaab';
168
explain select * from t1 where a='aaac';
169
explain select * from t1 where a='aaad';
170
insert into t1 select * from t1;
172
# avoid statistics differences between normal and ps-protocol tests
174
explain select * from t1 where a='aaaa';
175
explain select * from t1 where a='aaab';
176
explain select * from t1 where a='aaac';
177
explain select * from t1 where a='aaad';
179
# a known effect: table reload causes statistics to be updated:
181
explain select * from t1 where a='aaaa';
182
explain select * from t1 where a='aaab';
183
explain select * from t1 where a='aaac';
184
explain select * from t1 where a='aaad';
186
# Check if delete_all_rows() updates #hash_buckets
187
create table t2 as select * from t1;
189
insert into t1 select * from t2;
190
explain select * from t1 where a='aaaa';
191
explain select * from t1 where a='aaab';
192
explain select * from t1 where a='aaac';
193
explain select * from t1 where a='aaad';
197
# Btree and hash index use costs.
199
id int unsigned not null primary key auto_increment,
200
name varchar(20) not null,
201
index heap_idx(name),
202
index btree_idx using btree(name)
206
id int unsigned not null primary key auto_increment,
207
name varchar(20) not null,
208
index btree_idx using btree(name),
212
insert into t1 (name) values ('Matt'), ('Lilu'), ('Corbin'), ('Carly'),
213
('Suzy'), ('Hoppy'), ('Burrito'), ('Mimi'), ('Sherry'), ('Ben'), ('Phil'),
215
insert into t2 select * from t1;
216
explain select * from t1 where name='matt';
217
explain select * from t2 where name='matt';
219
explain select * from t1 where name='Lilu';
220
explain select * from t2 where name='Lilu';
222
explain select * from t1 where name='Phil';
223
explain select * from t2 where name='Phil';
225
explain select * from t1 where name='Lilu';
226
explain select * from t2 where name='Lilu';
228
insert into t1 (name) select name from t2;
229
insert into t1 (name) select name from t2;
230
insert into t1 (name) select name from t2;
231
insert into t1 (name) select name from t2;
232
insert into t1 (name) select name from t2;
233
insert into t1 (name) select name from t2;
235
select count(*) from t1 where name='Matt';
236
explain select * from t1 ignore index (btree_idx) where name='matt';
243
a varchar(20) not null,
244
b varchar(20) not null,
247
insert into t3 select name, name from t1;
251
# test rec_per_key use for joins.
252
explain select * from t1 ignore key(btree_idx), t3 where t1.name='matt' and t3.a = concat('',t1.name) and t3.b=t1.name;
254
drop table t1, t2, t3;
256
# Fix for BUG#8371: wrong rec_per_key value for hash index on temporary table
257
create temporary table t1 ( a int, index (a) ) engine=memory;
258
insert into t1 values (1),(2),(3),(4),(5);
259
select a from t1 where a in (1,3);
260
explain select a from t1 where a in (1,3);
263
--echo End of 4.1 tests
266
# Bug #27643: query failed : 1114 (The table '' is full)
268
# Check that HASH indexes disregard trailing spaces when comparing
269
# strings with binary collations
271
CREATE TABLE t1(col1 VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
272
col2 VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
273
UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
274
INSERT INTO t1 VALUES('A', 'A');
276
INSERT INTO t1 VALUES('A ', 'A ');
278
CREATE TABLE t1(col1 VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
279
col2 VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
280
UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
281
INSERT INTO t1 VALUES('A', 'A');
283
INSERT INTO t1 VALUES('A ', 'A ');
286
--echo End of 5.0 tests
added
removed
mysql-test/t/heap_hash.test
