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;
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	note	The storage engine for the table doesn't support analyze
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
t1	0	PRIMARY	1	a	NULL	3	NULL	NULL		HASH			YES	NULL
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";
Warnings:
Note	3502	This storage engine does not support the HASH index algorithm, storage engine default was used instead.
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;
Warnings:
Note	3502	This storage engine does not support the HASH index algorithm, storage engine default was used instead.
explain select * from t1 where a in (869751,736494,226312,802616);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	uniq_id	uniq_id	4	NULL	4	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`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))
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	x	NULL	NULL	NULL	6	100.00	NULL
1	SIMPLE	t2	NULL	eq_ref	y	y	4	test.t1.x	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y`,`test`.`t2`.`x` AS `x`,`test`.`t2`.`y` AS `y` from `test`.`t1` join `test`.`t1` `t2` where (`test`.`t2`.`y` = `test`.`t1`.`x`)
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)) charset utf8mb4 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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	btn	NULL	NULL	NULL	14	11.11	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`btn` AS `btn` from `test`.`t1` where (`test`.`t1`.`btn` like 'q%')
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	btn	NULL	NULL	NULL	14	10.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`btn` AS `btn`,`test`.`t1`.`new_col` AS `new_col` from `test`.`t1` where (`test`.`t1`.`btn` = 'a')
explain select * from t1 where btn="a" and new_col="a";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	btn	btn	44	const,const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`btn` AS `btn`,`test`.`t1`.`new_col` AS `new_col` from `test`.`t1` where ((`test`.`t1`.`new_col` = 'a') and (`test`.`t1`.`btn` = 'a'))
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	5	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` is null)
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	b	b	5	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` is null)
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)
) charset utf8mb4 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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaaa')
explain select * from t1 where a='aaab';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaab')
explain select * from t1 where a='aaac';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaac')
explain select * from t1 where a='aaad';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaad')
insert into t1 select * from t1;
flush tables;
explain select * from t1 where a='aaaa';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaaa')
explain select * from t1 where a='aaab';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaab')
explain select * from t1 where a='aaac';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaac')
explain select * from t1 where a='aaad';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaad')
flush tables;
explain select * from t1 where a='aaaa';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaaa')
explain select * from t1 where a='aaab';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaab')
explain select * from t1 where a='aaac';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaac')
explain select * from t1 where a='aaad';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaad')
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaaa')
explain select * from t1 where a='aaab';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaab')
explain select * from t1 where a='aaac';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaac')
explain select * from t1 where a='aaad';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	32	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a` = 'aaad')
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)
) charset latin1 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)
) charset latin1 engine=heap;
insert into t1 (name) values ('Matt'), ('Lilu'), ('Corbin'), ('Carly'), 
('Suzy'), ('Hoppy'), ('Burrito'), ('Mimi'), ('Sherry'), ('Ben'), ('Phil'), 
('Emily');
insert into t2 select * from t1;
explain select * from t1 where name='matt';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	heap_idx,btree_idx	btree_idx	22	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t1` where (`test`.`t1`.`name` = 'matt')
explain select * from t2 where name='matt';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ref	btree_idx,heap_idx	btree_idx	22	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name` from `test`.`t2` where (`test`.`t2`.`name` = 'matt')
explain select * from t1 where name='Lilu';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	heap_idx,btree_idx	btree_idx	22	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t1` where (`test`.`t1`.`name` = 'Lilu')
explain select * from t2 where name='Lilu';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ref	btree_idx,heap_idx	btree_idx	22	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name` from `test`.`t2` where (`test`.`t2`.`name` = 'Lilu')
explain select * from t1 where name='Phil';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	heap_idx,btree_idx	btree_idx	22	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t1` where (`test`.`t1`.`name` = 'Phil')
explain select * from t2 where name='Phil';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ref	btree_idx,heap_idx	btree_idx	22	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name` from `test`.`t2` where (`test`.`t2`.`name` = 'Phil')
explain select * from t1 where name='Lilu';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	heap_idx,btree_idx	btree_idx	22	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t1` where (`test`.`t1`.`name` = 'Lilu')
explain select * from t2 where name='Lilu';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ref	btree_idx,heap_idx	btree_idx	22	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name` from `test`.`t2` where (`test`.`t2`.`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;
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	heap_idx	heap_idx	22	const	7	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t1` IGNORE INDEX (`btree_idx`) where (`test`.`t1`.`name` = 'matt')
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	note	The storage engine for the table doesn't support analyze
show index from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
t1	0	PRIMARY	1	id	NULL	84	NULL	NULL		HASH			YES	NULL
t1	1	heap_idx	1	name	NULL	11	NULL	NULL		HASH			YES	NULL
t1	1	btree_idx	1	name	A	NULL	NULL	NULL		BTREE			YES	NULL
show index from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
t1	0	PRIMARY	1	id	NULL	84	NULL	NULL		HASH			YES	NULL
t1	1	heap_idx	1	name	NULL	11	NULL	NULL		HASH			YES	NULL
t1	1	btree_idx	1	name	A	NULL	NULL	NULL		BTREE			YES	NULL
create table t3
(
a varchar(20) not null,
b varchar(20) not null,
key (a,b)
) charset latin1 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	Index_comment	Visible	Expression
t3	1	a	1	a	NULL	NULL	NULL	NULL		HASH			YES	NULL
t3	1	a	2	b	NULL	11	NULL	NULL		HASH			YES	NULL
show index from t3;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
t3	1	a	1	a	NULL	NULL	NULL	NULL		HASH			YES	NULL
t3	1	a	2	b	NULL	11	NULL	NULL		HASH			YES	NULL
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	heap_idx	heap_idx	22	const	7	100.00	NULL
1	SIMPLE	t3	NULL	ALL	a	NULL	NULL	NULL	84	1.19	Using where; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` IGNORE INDEX (`btree_idx`) join `test`.`t3` where ((`test`.`t1`.`name` = 'matt') and (`test`.`t3`.`b` = 'matt') and (`test`.`t3`.`a` = concat('',`test`.`t1`.`name`)))
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	a	a	5	NULL	4	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` in (1,3))
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;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Warning	3778	'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Warning	3778	'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
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
#
# Bug #55472: Assertion failed in heap_rfirst function of hp_rfirst.c
#             on DELETE statement
#
CREATE TABLE t1 (col_int_nokey INT,
col_int_key INT,
INDEX(col_int_key) USING HASH) ENGINE = HEAP;
INSERT INTO t1 (col_int_nokey, col_int_key) VALUES (3, 0), (4, 0), (3, 1);
DELETE FROM t1 WHERE col_int_nokey = 5 ORDER BY col_int_key LIMIT 2;
DROP TABLE t1;
#
# Bug #44771: Unique Hash index in memory engine will give wrong
#             query result for NULL value.
#
CREATE TABLE t1
(
pk INT PRIMARY KEY,
val INT,
UNIQUE KEY USING HASH(val)
) ENGINE=MEMORY;
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t1 VALUES (2, NULL);
INSERT INTO t1 VALUES (3, NULL);
INSERT INTO t1 VALUES (4, NULL);
SELECT * FROM t1 WHERE val IS NULL;
pk	val
4	NULL
3	NULL
2	NULL
1	NULL
EXPLAIN SELECT * FROM t1 WHERE val IS NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	val	val	5	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`val` AS `val` from `test`.`t1` where (`test`.`t1`.`val` is null)
DROP TABLE t1;
End of 5.5 tests
# Bug#24364448: Check that hash indexes are not used to provide ordering
CREATE TABLE t1(
id INT AUTO_INCREMENT PRIMARY KEY,
c1 INT NOT NULL,
c2 INT NOT NULL,
UNIQUE KEY USING HASH (c2,c1)) ENGINE = MEMORY;
INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
EXPLAIN SELECT c2, COUNT(c1) FROM t1 GROUP BY c2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	c2	NULL	NULL	NULL	5	100.00	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`c2` AS `c2`,count(`test`.`t1`.`c1`) AS `COUNT(c1)` from `test`.`t1` group by `test`.`t1`.`c2`
SELECT c2, COUNT(c1) FROM t1 GROUP BY c2;
c2	COUNT(c1)
1	2
5	1
3	2
DROP TABLE t1;