summaryrefslogtreecommitdiff
path: root/mysql-test/r/heap_hash.result
blob: ba7d79e491a6f857befd39936834563e85e2d3c7 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
drop table if exists t1,t2;
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;
insert into t1 values(1,1),(2,2),(3,3),(4,4);
delete from t1 where a=1 or a=0;
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	a	NULL	3	NULL	NULL		HASH	
select * from t1;
a	b
2	2
3	3
4	4
select * from t1 where a=4;
a	b
4	4
update t1 set b=5 where a=4;
update t1 set b=b+1 where a>=3;
replace t1 values (3,3);
select * from t1;
a	b
2	2
3	3
4	6
alter table t1 add c int not null, add key using HASH (c,a);
drop table t1;
create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps";
insert into t1 values(1,1),(2,2),(3,3),(4,4);
delete from t1 where a > 0;
select * from t1;
a	b
drop table t1;
create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps";
insert into t1 values(1,1),(2,2),(3,3),(4,4);
alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table";
select * from t1;
a	b
1	1
2	2
3	3
4	4
drop table t1;
create table t1 (a int not null) engine=heap;
insert into t1 values (869751),(736494),(226312),(802616),(728912);
select * from t1 where a > 736494;
a
869751
802616
alter table t1 add unique uniq_id using HASH (a);
select * from t1 where a > 736494;
a
869751
802616
select * from t1 where a = 736494;
a
736494
select * from t1 where a=869751 or a=736494;
a
736494
869751
select * from t1 where a in (869751,736494,226312,802616);
a
226312
736494
802616
869751
alter table t1 engine=myisam;
explain select * from t1 where a in (869751,736494,226312,802616);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	uniq_id	uniq_id	4	NULL	4	Using where; Using index
drop table t1;
create table t1 (x int not null, y int not null, key x  using HASH (x), unique y  using HASH (y))
engine=heap;
insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
select * from t1 where x=1;
x	y
1	3
1	1
select * from t1,t1 as t2 where t1.x=t2.y;
x	y	x	y
1	1	1	1
2	2	2	2
1	3	1	1
2	4	2	2
2	5	2	2
2	6	2	2
explain select * from t1,t1 as t2 where t1.x=t2.y;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	x	NULL	NULL	NULL	6	
1	SIMPLE	t2	eq_ref	y	y	4	test.t1.x	1	
drop table t1;
create table t1 (a int) engine=heap;
insert into t1 values(1);
select max(a) from t1;
max(a)
1
drop table t1;
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;
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
select * from t1 where a=1;
a	b
1	6
1	5
1	4
1	3
1	2
1	1
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
select * from t1 where a=1;
a	b
1	6
1	5
1	4
1	3
1	2
1	1
1	6
1	5
1	4
1	3
1	2
1	1
drop table t1;
create table t1 (id int unsigned not null, primary key  using HASH (id)) engine=HEAP;
insert into t1 values(1);
select max(id) from t1;
max(id)
1
insert into t1 values(2);
select max(id) from t1;
max(id)
2
replace into t1 values(1);
drop table t1;
create table t1 (n int) engine=heap;
drop table t1;
create table t1 (n int) engine=heap;
drop table if exists t1;
CREATE table t1(f1 int not null,f2 char(20) not 
null,index(f2)) engine=heap;
INSERT into t1 set f1=12,f2="bill";
INSERT into t1 set f1=13,f2="bill";
INSERT into t1 set f1=14,f2="bill";
INSERT into t1 set f1=15,f2="bill";
INSERT into t1 set f1=16,f2="ted";
INSERT into t1 set f1=12,f2="ted";
INSERT into t1 set f1=12,f2="ted";
INSERT into t1 set f1=12,f2="ted";
INSERT into t1 set f1=12,f2="ted";
delete from t1 where f2="bill";
select * from t1;
f1	f2
16	ted
12	ted
12	ted
12	ted
12	ted
drop table t1;
create table t1 (btn char(10) not null, key using HASH (btn)) engine=heap;
insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
explain select * from t1 where btn like "q%";
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	btn	NULL	NULL	NULL	14	Using where
select * from t1 where btn like "q%";
btn
alter table t1 add column new_col char(1) not null, add key using HASH (btn,new_col), drop key btn;
update t1 set new_col=left(btn,1);
explain select * from t1 where btn="a";
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	btn	NULL	NULL	NULL	14	Using where
explain select * from t1 where btn="a" and new_col="a";
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	btn	btn	11	const,const	2	Using where
drop table t1;
CREATE TABLE t1 (
a int default NULL,
b int default NULL,
KEY a using HASH (a),
UNIQUE b using HASH (b)
) engine=heap;
INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);
SELECT * FROM t1 WHERE a=NULL;
a	b
explain SELECT * FROM t1 WHERE a IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	5	const	2	Using where
SELECT * FROM t1 WHERE a<=>NULL;
a	b
NULL	99
SELECT * FROM t1 WHERE b=NULL;
a	b
explain SELECT * FROM t1 WHERE b IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	b	b	5	const	1	Using where
SELECT * FROM t1 WHERE b<=>NULL;
a	b
99	NULL
INSERT INTO t1 VALUES (1,3);
ERROR 23000: Duplicate entry '3' for key 'b'
DROP TABLE t1;
CREATE TABLE t1 (a int not null, primary key using HASH (a)) engine=heap;
INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
DELETE from t1 where a < 100;
SELECT * from t1;
a
DROP TABLE t1;
create table t1
(
a char(8) not null,
b char(20) not null,
c int not null,
key (a)
) engine=heap;
insert into t1 values ('aaaa', 'prefill-hash=5',0);
insert into t1 values ('aaab', 'prefill-hash=0',0);
insert into t1 values ('aaac', 'prefill-hash=7',0);
insert into t1 values ('aaad', 'prefill-hash=2',0);
insert into t1 values ('aaae', 'prefill-hash=1',0);
insert into t1 values ('aaaf', 'prefill-hash=4',0);
insert into t1 values ('aaag', 'prefill-hash=3',0);
insert into t1 values ('aaah', 'prefill-hash=6',0);
explain select * from t1 where a='aaaa';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaab';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaac';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaad';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
insert into t1 select * from t1;
flush tables;
explain select * from t1 where a='aaaa';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaab';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaac';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaad';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
flush tables;
explain select * from t1 where a='aaaa';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaab';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaac';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaad';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
create table t2 as select * from t1;
delete from t1;
insert into t1 select * from t2;
explain select * from t1 where a='aaaa';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaab';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaac';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
explain select * from t1 where a='aaad';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	8	const	2	Using where
drop table t1, t2;
create table t1 (
id int unsigned not null primary key auto_increment, 
name varchar(20) not null,
index heap_idx(name),
index btree_idx using btree(name)
) engine=heap;
create table t2 (
id int unsigned not null primary key auto_increment, 
name varchar(20) not null,
index btree_idx using btree(name),
index heap_idx(name)
) engine=heap;
insert into t1 (name) values ('Matt'), ('Lilu'), ('Corbin'), ('Carly'), 
('Suzy'), ('Hoppy'), ('Burrito'), ('Mimi'), ('Sherry'), ('Ben'), ('Phil'), 
('Emily'), ('Mike');
insert into t2 select * from t1;
explain select * from t1 where name='matt';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	heap_idx,btree_idx	btree_idx	22	const	1	Using where
explain select * from t2 where name='matt';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ref	btree_idx,heap_idx	btree_idx	22	const	1	Using where
explain select * from t1 where name='Lilu';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	heap_idx,btree_idx	btree_idx	22	const	1	Using where
explain select * from t2 where name='Lilu';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ref	btree_idx,heap_idx	btree_idx	22	const	1	Using where
explain select * from t1 where name='Phil';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	heap_idx,btree_idx	btree_idx	22	const	1	Using where
explain select * from t2 where name='Phil';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ref	btree_idx,heap_idx	btree_idx	22	const	1	Using where
explain select * from t1 where name='Lilu';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	heap_idx,btree_idx	btree_idx	22	const	1	Using where
explain select * from t2 where name='Lilu';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ref	btree_idx,heap_idx	btree_idx	22	const	1	Using where
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;
flush tables;
select count(*) from t1 where name='Matt';
count(*)
7
explain select * from t1 ignore index (btree_idx) where name='matt';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	heap_idx	heap_idx	22	const	7	Using where
show index from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	id	NULL	91	NULL	NULL		HASH	
t1	1	heap_idx	1	name	NULL	13	NULL	NULL		HASH	
t1	1	btree_idx	1	name	A	NULL	NULL	NULL		BTREE	
show index from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	id	NULL	91	NULL	NULL		HASH	
t1	1	heap_idx	1	name	NULL	13	NULL	NULL		HASH	
t1	1	btree_idx	1	name	A	NULL	NULL	NULL		BTREE	
create table t3
(
a varchar(20) not null,
b varchar(20) not null,
key (a,b)
) engine=heap;
insert into t3 select name, name from t1;
show index from t3;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t3	1	a	1	a	NULL	NULL	NULL	NULL		HASH	
t3	1	a	2	b	NULL	13	NULL	NULL		HASH	
show index from t3;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t3	1	a	1	a	NULL	NULL	NULL	NULL		HASH	
t3	1	a	2	b	NULL	13	NULL	NULL		HASH	
explain select * from t1 ignore key(btree_idx), t3 where t1.name='matt' and t3.a = concat('',t1.name) and t3.b=t1.name;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	heap_idx	heap_idx	22	const	7	Using where
1	SIMPLE	t3	ref	a	a	44	func,const	7	Using where
drop table t1, t2, t3;
create temporary table t1 ( a int, index (a) ) engine=memory;
insert into t1 values (1),(2),(3),(4),(5);
select a from t1 where a in (1,3);
a
1
3
explain select a from t1 where a in (1,3);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a	a	5	NULL	4	Using where
drop table t1;
End of 4.1 tests
CREATE TABLE t1(col1 VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
col2 VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
INSERT INTO t1 VALUES('A', 'A');
INSERT INTO t1 VALUES('A ', 'A ');
ERROR 23000: Duplicate entry 'A -A ' for key 'key1'
DROP TABLE t1;
CREATE TABLE t1(col1 VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, 
col2 VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, 
UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
INSERT INTO t1 VALUES('A', 'A');
INSERT INTO t1 VALUES('A ', 'A ');
ERROR 23000: Duplicate entry 'A -A ' for key 'key1'
DROP TABLE t1;
End of 5.0 tests
# bit index in heap tables
create table t1 (a bit(63) not null) engine=heap;
insert into t1 values (869751),(736494),(226312),(802616),(728912);
alter table t1 add unique uniq_id using HASH (a);
select 0+a from t1 where a > 736494;
0+a
869751
802616
explain select 0+a from t1 where a > 736494;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	uniq_id	NULL	NULL	NULL	5	Using where
select 0+a from t1 where a = 736494;
0+a
736494
explain select 0+a from t1 where a = 736494;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	const	uniq_id	uniq_id	8	const	1	
select 0+a from t1 where a=869751 or a=736494;
0+a
736494
869751
explain select 0+a from t1 where a=869751 or a=736494;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	uniq_id	uniq_id	8	NULL	2	Using where
select 0+a from t1 where a in (869751,736494,226312,802616);
0+a
226312
736494
802616
869751
explain select 0+a from t1 where a in (869751,736494,226312,802616);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	uniq_id	uniq_id	8	NULL	4	Using where
drop table t1;
End of 5.3 tests