« back to all changes in this revision
Viewing changes to tests/r/heap_hash.result
- browse files at revision 520.1.8
- compare with another revision
- compare with revision 2456.1.5
- download diff
- download tarball
- view history from revision 520.1.8
- Committer: Brian Aker
- Date: 2008-10-18 15:43:20 UTC
- mto: (492.3.20 drizzle-clean-code)
- mto: This revision was merged to the branch mainline in revision 530.
- Revision ID: brian@tangent.org-20081018154320-jc9jyij3mdf08abp
Updating tests.
- files removed:
- tests/r/bootstrap.result
- files modified:
- tests/Makefile.am
added
removed
tests/r/heap_hash.result
120
120
1 2
121
121
1 1
122
122
drop table t1;
123
create table t1 (id int unsigned not null, primary key using HASH (id)) engine=HEAP;
123
create table t1 (id int not null, primary key using HASH (id)) engine=HEAP;
124
124
insert into t1 values(1);
125
125
select max(id) from t1;
126
126
max(id)
169
169
1 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where
170
170
explain select * from t1 where btn="a" and new_col="a";
171
171
id select_type table type possible_keys key key_len ref rows Extra
172
1 SIMPLE t1 ref btn btn 11 const,const 2 Using where
172
1 SIMPLE t1 ref btn btn 48 const,const 2 Using where
173
173
drop table t1;
174
174
CREATE TABLE t1 (
175
175
a int default NULL,
220
220
insert into t1 values ('aaah', 'prefill-hash=6',0);
221
221
explain select * from t1 where a='aaaa';
222
222
id select_type table type possible_keys key key_len ref rows Extra
223
1 SIMPLE t1 ref a a 8 const 2 Using where
223
1 SIMPLE t1 ref a a 34 const 2 Using where
224
224
explain select * from t1 where a='aaab';
225
225
id select_type table type possible_keys key key_len ref rows Extra
226
1 SIMPLE t1 ref a a 8 const 2 Using where
226
1 SIMPLE t1 ref a a 34 const 2 Using where
227
227
explain select * from t1 where a='aaac';
228
228
id select_type table type possible_keys key key_len ref rows Extra
229
1 SIMPLE t1 ref a a 8 const 2 Using where
229
1 SIMPLE t1 ref a a 34 const 2 Using where
230
230
explain select * from t1 where a='aaad';
231
231
id select_type table type possible_keys key key_len ref rows Extra
232
1 SIMPLE t1 ref a a 8 const 2 Using where
232
1 SIMPLE t1 ref a a 34 const 2 Using where
233
233
insert into t1 select * from t1;
234
234
flush tables;
235
235
explain select * from t1 where a='aaaa';
236
236
id select_type table type possible_keys key key_len ref rows Extra
237
1 SIMPLE t1 ref a a 8 const 2 Using where
237
1 SIMPLE t1 ref a a 34 const 2 Using where
238
238
explain select * from t1 where a='aaab';
239
239
id select_type table type possible_keys key key_len ref rows Extra
240
1 SIMPLE t1 ref a a 8 const 2 Using where
240
1 SIMPLE t1 ref a a 34 const 2 Using where
241
241
explain select * from t1 where a='aaac';
242
242
id select_type table type possible_keys key key_len ref rows Extra
243
1 SIMPLE t1 ref a a 8 const 2 Using where
243
1 SIMPLE t1 ref a a 34 const 2 Using where
244
244
explain select * from t1 where a='aaad';
245
245
id select_type table type possible_keys key key_len ref rows Extra
246
1 SIMPLE t1 ref a a 8 const 2 Using where
246
1 SIMPLE t1 ref a a 34 const 2 Using where
247
247
flush tables;
248
248
explain select * from t1 where a='aaaa';
249
249
id select_type table type possible_keys key key_len ref rows Extra
250
1 SIMPLE t1 ref a a 8 const 2 Using where
250
1 SIMPLE t1 ref a a 34 const 2 Using where
251
251
explain select * from t1 where a='aaab';
252
252
id select_type table type possible_keys key key_len ref rows Extra
253
1 SIMPLE t1 ref a a 8 const 2 Using where
253
1 SIMPLE t1 ref a a 34 const 2 Using where
254
254
explain select * from t1 where a='aaac';
255
255
id select_type table type possible_keys key key_len ref rows Extra
256
1 SIMPLE t1 ref a a 8 const 2 Using where
256
1 SIMPLE t1 ref a a 34 const 2 Using where
257
257
explain select * from t1 where a='aaad';
258
258
id select_type table type possible_keys key key_len ref rows Extra
259
1 SIMPLE t1 ref a a 8 const 2 Using where
259
1 SIMPLE t1 ref a a 34 const 2 Using where
260
260
create table t2 as select * from t1;
261
261
delete from t1;
262
262
insert into t1 select * from t2;
263
263
explain select * from t1 where a='aaaa';
264
264
id select_type table type possible_keys key key_len ref rows Extra
265
1 SIMPLE t1 ref a a 8 const 2 Using where
265
1 SIMPLE t1 ref a a 34 const 2 Using where
266
266
explain select * from t1 where a='aaab';
267
267
id select_type table type possible_keys key key_len ref rows Extra
268
1 SIMPLE t1 ref a a 8 const 2 Using where
268
1 SIMPLE t1 ref a a 34 const 2 Using where
269
269
explain select * from t1 where a='aaac';
270
270
id select_type table type possible_keys key key_len ref rows Extra
271
1 SIMPLE t1 ref a a 8 const 2 Using where
271
1 SIMPLE t1 ref a a 34 const 2 Using where
272
272
explain select * from t1 where a='aaad';
273
273
id select_type table type possible_keys key key_len ref rows Extra
274
1 SIMPLE t1 ref a a 8 const 2 Using where
274
1 SIMPLE t1 ref a a 34 const 2 Using where
275
275
drop table t1, t2;
276
276
create table t1 (
277
id int unsigned not null primary key auto_increment,
277
id int not null primary key auto_increment,
278
278
name varchar(20) not null,
279
279
index heap_idx(name),
280
280
index btree_idx using btree(name)
281
281
) engine=heap;
282
282
create table t2 (
283
id int unsigned not null primary key auto_increment,
283
id int not null primary key auto_increment,
284
284
name varchar(20) not null,
285
285
index btree_idx using btree(name),
286
286
index heap_idx(name)
291
291
insert into t2 select * from t1;
292
292
explain select * from t1 where name='matt';
293
293
id select_type table type possible_keys key key_len ref rows Extra
294
1 SIMPLE t1 ref heap_idx,btree_idx btree_idx 22 const 1 Using where
294
1 SIMPLE t1 ref heap_idx,btree_idx btree_idx 82 const 1 Using where
295
295
explain select * from t2 where name='matt';
296
296
id select_type table type possible_keys key key_len ref rows Extra
297
1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 22 const 1 Using where
297
1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 82 const 1 Using where
298
298
explain select * from t1 where name='Lilu';
299
299
id select_type table type possible_keys key key_len ref rows Extra
300
1 SIMPLE t1 ref heap_idx,btree_idx btree_idx 22 const 1 Using where
300
1 SIMPLE t1 ref heap_idx,btree_idx btree_idx 82 const 1 Using where
301
301
explain select * from t2 where name='Lilu';
302
302
id select_type table type possible_keys key key_len ref rows Extra
303
1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 22 const 1 Using where
303
1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 82 const 1 Using where
304
304
explain select * from t1 where name='Phil';
305
305
id select_type table type possible_keys key key_len ref rows Extra
306
1 SIMPLE t1 ref heap_idx,btree_idx btree_idx 22 const 1 Using where
306
1 SIMPLE t1 ref heap_idx,btree_idx btree_idx 82 const 1 Using where
307
307
explain select * from t2 where name='Phil';
308
308
id select_type table type possible_keys key key_len ref rows Extra
309
1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 22 const 1 Using where
309
1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 82 const 1 Using where
310
310
explain select * from t1 where name='Lilu';
311
311
id select_type table type possible_keys key key_len ref rows Extra
312
1 SIMPLE t1 ref heap_idx,btree_idx btree_idx 22 const 1 Using where
312
1 SIMPLE t1 ref heap_idx,btree_idx btree_idx 82 const 1 Using where
313
313
explain select * from t2 where name='Lilu';
314
314
id select_type table type possible_keys key key_len ref rows Extra
315
1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 22 const 1 Using where
315
1 SIMPLE t2 ref btree_idx,heap_idx btree_idx 82 const 1 Using where
316
316
insert into t1 (name) select name from t2;
317
317
insert into t1 (name) select name from t2;
318
318
insert into t1 (name) select name from t2;
325
325
7
326
326
explain select * from t1 ignore index (btree_idx) where name='matt';
327
327
id select_type table type possible_keys key key_len ref rows Extra
328
1 SIMPLE t1 ref heap_idx heap_idx 22 const 7 Using where
328
1 SIMPLE t1 ref heap_idx heap_idx 82 const 9 Using where
329
329
show index from t1;
330
330
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
331
331
t1 0 PRIMARY 1 id NULL 91 NULL NULL HASH
332
t1 1 heap_idx 1 name NULL 13 NULL NULL HASH
332
t1 1 heap_idx 1 name NULL 10 NULL NULL HASH
333
333
t1 1 btree_idx 1 name A NULL NULL NULL BTREE
334
334
show index from t1;
335
335
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
336
336
t1 0 PRIMARY 1 id NULL 91 NULL NULL HASH
337
t1 1 heap_idx 1 name NULL 13 NULL NULL HASH
337
t1 1 heap_idx 1 name NULL 10 NULL NULL HASH
338
338
t1 1 btree_idx 1 name A NULL NULL NULL BTREE
339
339
create table t3
340
340
(
353
353
t3 1 a 2 b NULL 13 NULL NULL HASH
354
354
explain select * from t1 ignore key(btree_idx), t3 where t1.name='matt' and t3.a = concat('',t1.name) and t3.b=t1.name;
355
355
id select_type table type possible_keys key key_len ref rows Extra
356
1 SIMPLE t1 ref heap_idx heap_idx 22 const 7 Using where
357
1 SIMPLE t3 ref a a 44 func,const 7 Using where
356
1 SIMPLE t1 ref heap_idx heap_idx 82 const 9 Using where
357
1 SIMPLE t3 ref a a 164 func,const 7 Using where
358
358
drop table t1, t2, t3;
359
359
create temporary table t1 ( a int, index (a) ) engine=memory;
360
360
insert into t1 values (1),(2),(3),(4),(5);
367
367
1 SIMPLE t1 range a a 5 NULL 4 Using where
368
368
drop table t1;
369
369
End of 4.1 tests
370
CREATE TABLE t1(col1 VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
371
col2 VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
370
CREATE TABLE t1(col1 VARCHAR(32) COLLATE utf8_bin NOT NULL,
371
col2 VARCHAR(32) COLLATE utf8_bin NOT NULL,
372
372
UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
373
373
INSERT INTO t1 VALUES('A', 'A');
374
374
INSERT INTO t1 VALUES('A ', 'A ');
375
375
ERROR 23000: Duplicate entry 'A -A ' for key 'key1'
376
376
DROP TABLE t1;
377
CREATE TABLE t1(col1 VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
378
col2 VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
377
CREATE TABLE t1(col1 VARCHAR(32) COLLATE utf8_bin NOT NULL,
378
col2 VARCHAR(32) COLLATE utf8_bin NOT NULL,
379
379
UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
380
380
INSERT INTO t1 VALUES('A', 'A');
381
381
INSERT INTO t1 VALUES('A ', 'A ');
Loggerhead is a web-based interface for Breezy
Version: 2.0.1