« back to all changes in this revision
Viewing changes to tests/t/heap_hash.test
- browse files at revision 1106.3.1
- compare with another revision
- compare with revision 1819.5.262
- download diff
- download tarball
- view history from revision 1106.3.1
- Committer: Brian Aker
- Date: 2009-07-31 23:20:14 UTC
- mto: This revision was merged to the branch mainline in revision 1108.
- Revision ID: brian@gaz-20090731232014-amd6192q1n8wade0
Heap is now tmp only table
- files modified:
- plugin/heap/ha_heap.cc
added
removed
tests/t/heap_hash.test
6
6
drop table if exists t1,t2;
7
7
--enable_warnings
8
8
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;
9
create temporary 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
10
insert into t1 values(1,1),(2,2),(3,3),(4,4);
11
11
delete from t1 where a=1 or a=0;
12
12
#show table status like "t1";
20
20
alter table t1 add c int not null, add key using HASH (c,a);
21
21
drop table t1;
22
22
23
create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps";
23
create temporary table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps";
24
24
insert into t1 values(1,1),(2,2),(3,3),(4,4);
25
25
delete from t1 where a > 0;
26
26
select * from t1;
27
27
drop table t1;
28
28
29
create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps";
29
create temporary table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps";
30
30
insert into t1 values(1,1),(2,2),(3,3),(4,4);
31
31
alter table t1 modify a int not null auto_increment, engine=innodb, comment="new innodb table";
32
32
#show table status like "t1";
33
33
select * from t1;
34
34
drop table t1;
35
35
36
create table t1 (a int not null) engine=heap;
36
create temporary table t1 (a int not null) engine=heap;
37
37
insert into t1 values (869751),(736494),(226312),(802616),(728912);
38
38
select * from t1 where a > 736494;
39
39
alter table t1 add unique uniq_id using HASH (a);
45
45
explain select * from t1 where a in (869751,736494,226312,802616);
46
46
drop table t1;
47
47
48
create table t1 (x int not null, y int not null, key x using HASH (x), unique y using HASH (y))
48
create temporary table t1 (x int not null, y int not null, key x using HASH (x), unique y using HASH (y))
49
49
engine=heap;
50
50
insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
51
51
select * from t1 where x=1;
52
-- error 1137
52
53
select * from t1,t1 as t2 where t1.x=t2.y;
54
-- error 1137
53
55
explain select * from t1,t1 as t2 where t1.x=t2.y;
54
56
drop table t1;
55
57
56
create table t1 (a int) engine=heap;
58
create temporary table t1 (a int) engine=heap;
57
59
insert into t1 values(1);
58
60
select max(a) from t1;
59
61
drop table t1;
60
62
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;
63
CREATE TEMPORARY 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
64
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
63
65
select * from t1 where a=1;
64
66
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
65
67
select * from t1 where a=1;
66
68
drop table t1;
67
69
68
create table t1 (id int not null, primary key using HASH (id)) engine=HEAP;
70
create temporary table t1 (id int not null, primary key using HASH (id)) engine=HEAP;
69
71
insert into t1 values(1);
70
72
select max(id) from t1;
71
73
insert into t1 values(2);
73
75
replace into t1 values(1);
74
76
drop table t1;
75
77
76
create table t1 (n int) engine=heap;
78
create temporary table t1 (n int) engine=heap;
77
79
drop table t1;
78
80
79
create table t1 (n int) engine=heap;
81
create temporary table t1 (n int) engine=heap;
80
82
drop table if exists t1;
81
83
82
84
# Test of non unique index
83
85
84
CREATE table t1(f1 int not null,f2 char(20) not
86
CREATE TEMPORARY table t1(f1 int not null,f2 char(20) not
85
87
null,index(f2)) engine=heap;
86
88
INSERT into t1 set f1=12,f2="bill";
87
89
INSERT into t1 set f1=13,f2="bill";
100
102
# Test when using part key searches
101
103
#
102
104
103
create table t1 (btn char(10) not null, key using HASH (btn)) engine=heap;
105
create temporary table t1 (btn char(10) not null, key using HASH (btn)) engine=heap;
104
106
insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
105
107
explain select * from t1 where btn like "q%";
106
108
select * from t1 where btn like "q%";
114
116
# Test of NULL keys
115
117
#
116
118
117
CREATE TABLE t1 (
119
CREATE TEMPORARY TABLE t1 (
118
120
a int default NULL,
119
121
b int default NULL,
120
122
KEY a using HASH (a),
136
138
# Test when deleting all rows
137
139
#
138
140
139
CREATE TABLE t1 (a int not null, primary key using HASH (a)) engine=heap;
141
CREATE TEMPORARY TABLE t1 (a int not null, primary key using HASH (a)) engine=heap;
140
142
INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
141
143
DELETE from t1 where a < 100;
142
144
SELECT * from t1;
146
148
#
147
149
# Hash index # records estimate test
148
150
#
149
create table t1
151
create temporary table t1
150
152
(
151
153
a char(8) not null,
152
154
b char(20) not null,
167
169
explain select * from t1 where a='aaab';
168
170
explain select * from t1 where a='aaac';
169
171
explain select * from t1 where a='aaad';
172
-- error 1137
170
173
insert into t1 select * from t1;
171
174
172
175
# avoid statistics differences between normal and ps-protocol tests
184
187
explain select * from t1 where a='aaad';
185
188
186
189
# Check if delete_all_rows() updates #hash_buckets
187
create table t2 as select * from t1;
190
create temporary table t2 as select * from t1;
188
191
delete from t1;
189
192
insert into t1 select * from t2;
190
193
explain select * from t1 where a='aaaa';
195
198
196
199
197
200
# Btree and hash index use costs.
198
create table t1 (
201
create temporary table t1 (
199
202
id int not null primary key auto_increment,
200
203
name varchar(20) not null,
201
204
index heap_idx(name),
202
205
index btree_idx using btree(name)
203
206
) engine=heap;
204
207
205
create table t2 (
208
create temporary table t2 (
206
209
id int not null primary key auto_increment,
207
210
name varchar(20) not null,
208
211
index btree_idx using btree(name),
238
241
239
242
show index from t1;
240
243
241
create table t3
244
create temporary table t3
242
245
(
243
246
a varchar(20) not null,
244
247
b varchar(20) not null,
254
257
drop table t1, t2, t3;
255
258
256
259
# 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;
260
create temporary temporary table t1 ( a int, index (a) ) engine=memory;
258
261
insert into t1 values (1),(2),(3),(4),(5);
259
262
select a from t1 where a in (1,3);
260
263
explain select a from t1 where a in (1,3);
268
271
# Check that HASH indexes disregard trailing spaces when comparing
269
272
# strings with binary collations
270
273
271
CREATE TABLE t1(col1 VARCHAR(32) COLLATE utf8_bin NOT NULL,
274
CREATE TEMPORARY TABLE t1(col1 VARCHAR(32) COLLATE utf8_bin NOT NULL,
272
275
col2 VARCHAR(32) COLLATE utf8_bin NOT NULL,
273
276
UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
274
277
INSERT INTO t1 VALUES('A', 'A');
275
278
--error ER_DUP_ENTRY
276
279
INSERT INTO t1 VALUES('A ', 'A ');
277
280
DROP TABLE t1;
278
CREATE TABLE t1(col1 VARCHAR(32) COLLATE utf8_bin NOT NULL,
281
CREATE TEMPORARY TABLE t1(col1 VARCHAR(32) COLLATE utf8_bin NOT NULL,
279
282
col2 VARCHAR(32) COLLATE utf8_bin NOT NULL,
280
283
UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
281
284
INSERT INTO t1 VALUES('A', 'A');
Loggerhead is a web-based interface for Breezy
Version: 2.0.1