heap_hash.test 8.81 KB
Newer Older
1 2 3 4
#
# Test of heap tables.
#

5
--disable_warnings
6
drop table if exists t1,t2;
7 8
--enable_warnings

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 11 12 13 14 15 16 17 18 19 20 21 22
insert into t1 values(1,1),(2,2),(3,3),(4,4);
delete from t1 where a=1 or a=0;
#show table status like "t1";
show keys from t1;
select * from t1;
select * from t1 where a=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;
alter table t1 add c int not null, add key using HASH (c,a);
drop table t1;

23
create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps";
24 25 26 27 28
insert into t1 values(1,1),(2,2),(3,3),(4,4);
delete from t1 where a > 0;
select * from t1;
drop table t1;

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 33 34 35
#show table status like "t1";
select * from t1;
drop table t1;

36
create table t1 (a int not null) engine=heap;
37
insert into t1 values (869751),(736494),(226312),(802616),(728912);
38 39 40 41 42 43
select * from t1 where a > 736494;
alter table t1 add unique uniq_id using HASH (a);
select * from t1 where a > 736494;
select * from t1 where a = 736494;
select * from t1 where a=869751 or a=736494;
select * from t1 where a in (869751,736494,226312,802616);
44
alter table t1 engine=myisam;
45 46 47 48
explain select * from t1 where a in (869751,736494,226312,802616);
drop table t1;

create table t1 (x int not null, y int not null, key x  using HASH (x), unique y  using HASH (y))
49
engine=heap;
50 51 52 53 54 55
insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
select * from t1 where x=1;
select * from t1,t1 as t2 where t1.x=t2.y;
explain select * from t1,t1 as t2 where t1.x=t2.y;
drop table t1;

56
create table t1 (a int) engine=heap;
57 58 59 60
insert into t1 values(1);
select max(a) from t1;
drop table 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 63 64 65 66 67
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
select * from t1 where a=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;
drop table t1;

68
create table t1 (id int unsigned not null, primary key  using HASH (id)) engine=HEAP;
69 70 71 72 73 74 75
insert into t1 values(1);
select max(id) from t1; 
insert into t1 values(2);
select max(id) from t1; 
replace into t1 values(1);
drop table t1;

76
create table t1 (n int) engine=heap;
77 78
drop table t1;

79
create table t1 (n int) engine=heap;
80 81 82 83 84
drop table if exists t1;

# Test of non unique index

CREATE table t1(f1 int not null,f2 char(20) not 
85
null,index(f2)) engine=heap;
86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102
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;
drop table t1;

#
# Test when using part key searches
#

103
create table t1 (btn char(10) not null, key using HASH (btn)) engine=heap;
104 105 106 107
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%";
select * from t1 where btn like "q%";
alter table t1 add column new_col char(1) not null, add key using HASH (btn,new_col), drop key btn;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
108
update t1 set new_col=left(btn,1);
109 110 111 112 113 114 115 116 117 118 119 120 121
explain select * from t1 where btn="a";
explain select * from t1 where btn="a" and new_col="a";
drop table t1;

#
# Test of NULL keys
#

CREATE TABLE t1 (
  a int default NULL,
  b int default NULL,
  KEY a using HASH (a),
  UNIQUE b using HASH (b)
122
) engine=heap;
123 124 125 126 127 128 129 130
INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);
SELECT * FROM t1 WHERE a=NULL;
explain SELECT * FROM t1 WHERE a IS NULL;
SELECT * FROM t1 WHERE a<=>NULL;
SELECT * FROM t1 WHERE b=NULL;
explain SELECT * FROM t1 WHERE b IS NULL;
SELECT * FROM t1 WHERE b<=>NULL;

131
--error ER_DUP_ENTRY_WITH_KEY_NAME
132 133 134 135 136 137 138
INSERT INTO t1 VALUES (1,3);
DROP TABLE t1;

#
# Test when deleting all rows
#

139
CREATE TABLE t1 (a int not null, primary key using HASH (a)) engine=heap;
140 141 142 143
INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
DELETE from t1 where a < 100;
SELECT * from t1;
DROP TABLE t1;
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


#
# Hash index # records estimate test
#
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';
explain select * from t1 where a='aaab';
explain select * from t1 where a='aaac';
explain select * from t1 where a='aaad';
insert into t1 select * from t1;
171

172 173
# avoid statistics differences between normal and ps-protocol tests
flush tables;
174 175 176 177 178 179 180
explain select * from t1 where a='aaaa';
explain select * from t1 where a='aaab';
explain select * from t1 where a='aaac';
explain select * from t1 where a='aaad';

# a known effect: table reload causes statistics to be updated:
flush tables;
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
explain select * from t1 where a='aaaa';
explain select * from t1 where a='aaab';
explain select * from t1 where a='aaac';
explain select * from t1 where a='aaad';

# Check if delete_all_rows() updates #hash_buckets
create table t2 as select * from t1;
delete from t1;
insert into t1 select * from t2;
explain select * from t1 where a='aaaa';
explain select * from t1 where a='aaab';
explain select * from t1 where a='aaac';
explain select * from t1 where a='aaad';
drop table t1, t2;


# Btree and hash index use costs. 
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';
explain select * from t2 where name='matt';

explain select * from t1 where name='Lilu';
explain select * from t2 where name='Lilu';

explain select * from t1 where name='Phil';
explain select * from t2 where name='Phil';

explain select * from t1 where name='Lilu';
explain select * from t2 where name='Lilu';

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;
234
flush tables;
235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250
select count(*) from t1 where name='Matt';
explain select * from t1 ignore index (btree_idx) where name='matt';
show index from t1;

show index from t1;

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;
show index from t3;

251 252 253 254
# test rec_per_key use for joins.
explain select * from t1 ignore key(btree_idx), t3 where t1.name='matt' and t3.a = concat('',t1.name) and t3.b=t1.name;

drop table t1, t2, t3;
255

256 257 258 259 260 261
# Fix for BUG#8371: wrong rec_per_key value for hash index on temporary table
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);
explain select a from t1 where a in (1,3);
drop table t1;
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
--echo End of 4.1 tests

#
# Bug #27643: query failed : 1114 (The table '' is full)
#
# Check that HASH indexes disregard trailing spaces when comparing 
# strings with binary collations

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');
--error ER_DUP_ENTRY
INSERT INTO t1 VALUES('A ', 'A ');
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');
--error ER_DUP_ENTRY
INSERT INTO t1 VALUES('A ', 'A ');
DROP TABLE t1;

--echo End of 5.0 tests