~drizzle-trunk/drizzle/development : contents of mysql-test/r/index_merge

~drizzle-trunk/drizzle/development : (revision 77.1.33)

#---------------- Index merge test 2 -------------------------------------------
SET SESSION STORAGE_ENGINE = InnoDB;
drop table if exists t1,t2;
create table t1
(
key1 int not null,
key2 int not null,
INDEX i1(key1),
INDEX i2(key2)
);
explain select * from t1 where key1 < 5 or key2 > 197;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	i1,i2	i1,i2	4,4	NULL	8	Using sort_union(i1,i2); Using where
select * from t1 where key1 < 5 or key2 > 197;
key1	key2
0	200
1	199
2	198
3	197
4	196
explain select * from t1 where key1 < 3 or key2 > 195;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	i1,i2	i1,i2	4,4	NULL	8	Using sort_union(i1,i2); Using where
select * from t1 where key1 < 3 or key2 > 195;
key1	key2
0	200
1	199
2	198
3	197
4	196
alter table t1 add str1 char (255) not null,
add zeroval int not null default 0,
add str2 char (255) not null,
add str3 char (255) not null;
update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));
alter table t1 add primary key (str1, zeroval, str2, str3);
explain select * from t1 where key1 < 5 or key2 > 197;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	i1,i2	i1,i2	4,4	NULL	8	Using sort_union(i1,i2); Using where
select * from t1 where key1 < 5 or key2 > 197;
key1	key2	str1	zeroval	str2	str3
4	196	aaa	0	bbb	196-2_a
3	197	aaa	0	bbb	197-1_A
2	198	aaa	0	bbb	198-1_a
1	199	aaa	0	bbb	199-0_A
0	200	aaa	0	bbb	200-0_a
explain select * from t1 where key1 < 3 or key2 > 195;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	i1,i2	i1,i2	4,4	NULL	8	Using sort_union(i1,i2); Using where
select * from t1 where key1 < 3 or key2 > 195;
key1	key2	str1	zeroval	str2	str3
4	196	aaa	0	bbb	196-2_a
3	197	aaa	0	bbb	197-1_A
2	198	aaa	0	bbb	198-1_a
1	199	aaa	0	bbb	199-0_A
0	200	aaa	0	bbb	200-0_a
drop table t1;
create table t1 (
pk    integer not null auto_increment primary key,
key1  integer,
key2  integer not null,
filler char  (200),
index (key1),
index (key2)
);
show warnings;
Level	Code	Message
explain select pk from t1 where key1 = 1 and key2 = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	key1,key2	key1,key2	5,4	NULL	1	Using intersect(key1,key2); Using where; Using index
select pk from t1 where key2 = 1 and key1 = 1;
pk
26
27
select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1;
pk
26
27
drop table t1;
create table t1 (
pk int primary key auto_increment,
key1a  int,
key2a  int,
key1b  int,
key2b  int,
dummy1 int,
dummy2 int,
dummy3 int,
dummy4 int,
key3a  int,
key3b  int,
filler1 char (200),
index i1(key1a, key1b),
index i2(key2a, key2b),
index i3(key3a, key3b)
);
create table t2 (a int);
insert into t2 values (0),(1),(2),(3),(4),(NULL);
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D;
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
select count(*) from t1;
count(*)
5184
explain select count(*) from t1 where
key1a = 2 and key1b is null and  key2a = 2 and key2b is null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	i1,i2	i1,i2	10,10	NULL	4	Using intersect(i1,i2); Using where; Using index
select count(*) from t1 where
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
count(*)
4
explain select count(*) from t1 where
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	i1,i3	i1,i3	10,10	NULL	4	Using intersect(i1,i3); Using where; Using index
select count(*) from t1 where
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
count(*)
4
drop table t1,t2;
create table t1 (
id1 int,
id2 date ,
index idx2 (id1,id2),
index idx1 (id2)
);
insert into t1 values(1,'20040101'), (2,'20040102');
select * from t1  where id1 = 1  and id2= '20040101';
id1	id2
1	2004-01-01
drop table t1;
create table t1
(
key1 int not null, 
key2 int not null default 0,
key3 int not null default 0
);
insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8);
set @d=8;
insert into t1 (key1) select key1+@d from t1;
set @d=@d*2;
insert into t1 (key1) select key1+@d from t1;
set @d=@d*2;
insert into t1 (key1) select key1+@d from t1;
set @d=@d*2;
insert into t1 (key1) select key1+@d from t1;
set @d=@d*2;
insert into t1 (key1) select key1+@d from t1;
set @d=@d*2;
insert into t1 (key1) select key1+@d from t1;
set @d=@d*2;
insert into t1 (key1) select key1+@d from t1;
set @d=@d*2;
alter table t1 add index i2(key2);
alter table t1 add index i3(key3);
update t1 set key2=key1,key3=key1;
explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	i2,i3	i3,i2	4,4	NULL	9	Using sort_union(i3,i2); Using where
select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
key1	key2	key3
31	31	31
32	32	32
33	33	33
34	34	34
35	35	35
36	36	36
37	37	37
38	38	38
39	39	39
drop table t1;
#---------------- 2-sweeps read Index merge test 2 -------------------------------
SET SESSION STORAGE_ENGINE = InnoDB;
drop table if exists t1;
create table t1 (
pk int primary key,
key1 int,
key2 int,
filler char(200),
filler2 char(200),
index(key1),
index(key2)
);
select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 );
pk	key1	key2	filler	filler2
2	2	2	filler-data	filler-data-2
3	3	3	filler-data	filler-data-2
9	9	9	filler-data	filler-data-2
10	10	10	filler-data	filler-data-2
4	4	4	filler-data	filler-data-2
5	5	5	filler-data	filler-data-2
6	6	6	filler-data	filler-data-2
7	7	7	filler-data	filler-data-2
8	8	8	filler-data	filler-data-2
set @maxv=1000;
select * from t1 where
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
or key1=18 or key1=60;
pk	key1	key2	filler	filler2
18	18	18	filler-data	filler-data-2
60	60	60	filler-data	filler-data-2
1	1	1	filler-data	filler-data-2
2	2	2	filler-data	filler-data-2
3	3	3	filler-data	filler-data-2
4	4	4	filler-data	filler-data-2
11	11	11	filler-data	filler-data-2
12	12	12	filler-data	filler-data-2
13	13	13	filler-data	filler-data-2
14	14	14	filler-data	filler-data-2
50	50	50	filler-data	filler-data-2
51	51	51	filler-data	filler-data-2
52	52	52	filler-data	filler-data-2
53	53	53	filler-data	filler-data-2
54	54	54	filler-data	filler-data-2
991	991	991	filler-data	filler-data-2
992	992	992	filler-data	filler-data-2
993	993	993	filler-data	filler-data-2
994	994	994	filler-data	filler-data-2
995	995	995	filler-data	filler-data-2
996	996	996	filler-data	filler-data-2
997	997	997	filler-data	filler-data-2
998	998	998	filler-data	filler-data-2
999	999	999	filler-data	filler-data-2
1000	1000	1000	filler-data	filler-data-2
select * from t1 where
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
or key1 < 3 or key1 > @maxv-11;
pk	key1	key2	filler	filler2
990	990	990	filler-data	filler-data-2
1	1	1	filler-data	filler-data-2
2	2	2	filler-data	filler-data-2
3	3	3	filler-data	filler-data-2
4	4	4	filler-data	filler-data-2
11	11	11	filler-data	filler-data-2
12	12	12	filler-data	filler-data-2
13	13	13	filler-data	filler-data-2
14	14	14	filler-data	filler-data-2
50	50	50	filler-data	filler-data-2
51	51	51	filler-data	filler-data-2
52	52	52	filler-data	filler-data-2
53	53	53	filler-data	filler-data-2
54	54	54	filler-data	filler-data-2
991	991	991	filler-data	filler-data-2
992	992	992	filler-data	filler-data-2
993	993	993	filler-data	filler-data-2
994	994	994	filler-data	filler-data-2
995	995	995	filler-data	filler-data-2
996	996	996	filler-data	filler-data-2
997	997	997	filler-data	filler-data-2
998	998	998	filler-data	filler-data-2
999	999	999	filler-data	filler-data-2
1000	1000	1000	filler-data	filler-data-2
select * from t1 where
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
or
(key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10);
pk	key1	key2	filler	filler2
1	1	1	filler-data	filler-data-2
2	2	2	filler-data	filler-data-2
3	3	3	filler-data	filler-data-2
4	4	4	filler-data	filler-data-2
11	11	11	filler-data	filler-data-2
12	12	12	filler-data	filler-data-2
13	13	13	filler-data	filler-data-2
14	14	14	filler-data	filler-data-2
50	50	50	filler-data	filler-data-2
51	51	51	filler-data	filler-data-2
52	52	52	filler-data	filler-data-2
53	53	53	filler-data	filler-data-2
54	54	54	filler-data	filler-data-2
991	991	991	filler-data	filler-data-2
992	992	992	filler-data	filler-data-2
993	993	993	filler-data	filler-data-2
994	994	994	filler-data	filler-data-2
995	995	995	filler-data	filler-data-2
996	996	996	filler-data	filler-data-2
997	997	997	filler-data	filler-data-2
998	998	998	filler-data	filler-data-2
999	999	999	filler-data	filler-data-2
1000	1000	1000	filler-data	filler-data-2
select * from t1 where
(pk > 10 and pk < 15) or (pk >= 50 and pk < 55 )
or
(key1 < 5) or (key1 > @maxv-10);
pk	key1	key2	filler	filler2
1	1	1	filler-data	filler-data-2
2	2	2	filler-data	filler-data-2
3	3	3	filler-data	filler-data-2
4	4	4	filler-data	filler-data-2
991	991	991	filler-data	filler-data-2
992	992	992	filler-data	filler-data-2
993	993	993	filler-data	filler-data-2
994	994	994	filler-data	filler-data-2
995	995	995	filler-data	filler-data-2
996	996	996	filler-data	filler-data-2
997	997	997	filler-data	filler-data-2
998	998	998	filler-data	filler-data-2
999	999	999	filler-data	filler-data-2
1000	1000	1000	filler-data	filler-data-2
11	11	11	filler-data	filler-data-2
12	12	12	filler-data	filler-data-2
13	13	13	filler-data	filler-data-2
14	14	14	filler-data	filler-data-2
50	50	50	filler-data	filler-data-2
51	51	51	filler-data	filler-data-2
52	52	52	filler-data	filler-data-2
53	53	53	filler-data	filler-data-2
54	54	54	filler-data	filler-data-2
drop table t1;
#---------------- Clustered PK ROR-index_merge tests -----------------------------
SET SESSION STORAGE_ENGINE = InnoDB;
drop table if exists  t1;
create table t1
(
pk1 int not null,
pk2 int not null,
key1 int not null,
key2 int not null,
pktail1ok  int not null,
pktail2ok  int not null,
pktail3bad int not null,
pktail4bad int not null,
pktail5bad int not null,
pk2copy int not null,
badkey  int not null,
filler1 char (200),
filler2 char (200),
key (key1),
key (key2),
/* keys with tails from CPK members */
key (pktail1ok, pk1),
key (pktail2ok, pk1, pk2),
key (pktail3bad, pk2, pk1),
key (pktail4bad, pk1, pk2copy),
key (pktail5bad, pk1, pk2, pk2copy),
primary key (pk1, pk2)
);
explain select * from t1 where pk1 = 1 and pk2 < 80  and key1=0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY,key1	PRIMARY	8	NULL	9	Using where
select * from t1 where pk1 = 1 and pk2 < 80  and key1=0;
pk1	pk2	key1	key2	pktail1ok	pktail2ok	pktail3bad	pktail4bad	pktail5bad	pk2copy	badkey	filler1	filler2
1	10	0	0	0	0	0	0	0	10	0	filler-data-10	filler2
1	11	0	0	0	0	0	0	0	11	0	filler-data-11	filler2
1	12	0	0	0	0	0	0	0	12	0	filler-data-12	filler2
1	13	0	0	0	0	0	0	0	13	0	filler-data-13	filler2
1	14	0	0	0	0	0	0	0	14	0	filler-data-14	filler2
1	15	0	0	0	0	0	0	0	15	0	filler-data-15	filler2
1	16	0	0	0	0	0	0	0	16	0	filler-data-16	filler2
1	17	0	0	0	0	0	0	0	17	0	filler-data-17	filler2
1	18	0	0	0	0	0	0	0	18	0	filler-data-18	filler2
1	19	0	0	0	0	0	0	0	19	0	filler-data-19	filler2
explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	key1,key2	key1,key2	4,4	NULL	1	Using intersect(key1,key2); Using where; Using index
select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
pk1	pk2
95	50
95	51
95	52
95	53
95	54
95	55
95	56
95	57
95	58
95	59
explain select * from t1 where badkey=1 and key1=10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	key1	key1	4	const	100	Using where
explain select * from t1 where pk1 < 7500 and key1 = 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	PRIMARY,key1	key1,PRIMARY	4,4	NULL	ROWS	Using intersect(key1,PRIMARY); Using where
explain select * from t1 where pktail1ok=1 and key1=10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	key1,pktail1ok	key1,pktail1ok	4,4	NULL	1	Using intersect(key1,pktail1ok); Using where
explain select * from t1 where pktail2ok=1 and key1=10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	key1,pktail2ok	key1,pktail2ok	4,4	NULL	1	Using intersect(key1,pktail2ok); Using where
explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	PRIMARY,key1,pktail2ok	pktail2ok,key1	8,4	NULL	199	Using sort_union(pktail2ok,key1); Using where
explain select * from t1 where pktail3bad=1 and key1=10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	key1,pktail3bad	key1	4	const	100	Using where
explain select * from t1 where pktail4bad=1 and key1=10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	key1,pktail4bad	key1	4	const	100	Using where
explain select * from t1 where pktail5bad=1 and key1=10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	key1,pktail5bad	key1	4	const	100	Using where
explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	key1,key2	key1,key2	4,4	NULL	1	Using intersect(key1,key2); Using where; Using index
select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
pk1	pk2	key1	key2
95	50	10	10
95	51	10	10
95	52	10	10
95	53	10	10
95	54	10	10
95	55	10	10
95	56	10	10
95	57	10	10
95	58	10	10
95	59	10	10
drop table t1;
create table t1
(
RUNID varchar(22),
SUBMITNR varchar(5),
ORDERNR char(1),
PROGRAMM varchar(8),
TESTID varchar(4),
UCCHECK char(1),
ETEXT varchar(80),
ETEXT_TYPE char(1),
INFO char(1),
SEVERITY tinyint(3),
TADIRFLAG char(1),
PRIMARY KEY  (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK),
KEY `TVERM~KEY`  (PROGRAMM,TESTID,UCCHECK)
) DEFAULT CHARSET=latin1;
update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`=''
WHERE
`RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND
`TESTID`='' AND `UCCHECK`='';
drop table t1;

1 by brian clean slate	1	#---------------- Index merge test 2 -------------------------------------------
	2	SET SESSION STORAGE_ENGINE = InnoDB;
	3	drop table if exists t1,t2;
	4	create table t1
	5	(
	6	key1 int not null,
	7	key2 int not null,
	8	INDEX i1(key1),
	9	INDEX i2(key2)
	10	);
	11	explain select * from t1 where key1 < 5 or key2 > 197;
	12	id select_type table type possible_keys key key_len ref rows Extra
	13	1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where
	14	select * from t1 where key1 < 5 or key2 > 197;
	15	key1 key2
	16	0 200
	17	1 199
	18	2 198
	19	3 197
	20	4 196
	21	explain select * from t1 where key1 < 3 or key2 > 195;
	22	id select_type table type possible_keys key key_len ref rows Extra
	23	1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where
	24	select * from t1 where key1 < 3 or key2 > 195;
	25	key1 key2
	26	0 200
	27	1 199
	28	2 198
	29	3 197
	30	4 196
	31	alter table t1 add str1 char (255) not null,
	32	add zeroval int not null default 0,
	33	add str2 char (255) not null,
	34	add str3 char (255) not null;
	35	update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));
	36	alter table t1 add primary key (str1, zeroval, str2, str3);
	37	explain select * from t1 where key1 < 5 or key2 > 197;
	38	id select_type table type possible_keys key key_len ref rows Extra
	39	1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where
	40	select * from t1 where key1 < 5 or key2 > 197;
	41	key1 key2 str1 zeroval str2 str3
	42	4 196 aaa 0 bbb 196-2_a
	43	3 197 aaa 0 bbb 197-1_A
	44	2 198 aaa 0 bbb 198-1_a
	45	1 199 aaa 0 bbb 199-0_A
	46	0 200 aaa 0 bbb 200-0_a
	47	explain select * from t1 where key1 < 3 or key2 > 195;
	48	id select_type table type possible_keys key key_len ref rows Extra
	49	1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where
	50	select * from t1 where key1 < 3 or key2 > 195;
	51	key1 key2 str1 zeroval str2 str3
	52	4 196 aaa 0 bbb 196-2_a
	53	3 197 aaa 0 bbb 197-1_A
	54	2 198 aaa 0 bbb 198-1_a
	55	1 199 aaa 0 bbb 199-0_A
	56	0 200 aaa 0 bbb 200-0_a
	57	drop table t1;
	58	create table t1 (
	59	pk integer not null auto_increment primary key,
	60	key1 integer,
	61	key2 integer not null,
	62	filler char (200),
	63	index (key1),
	64	index (key2)
65	);
66	show warnings;
67	Level Code Message
68	explain select pk from t1 where key1 = 1 and key2 = 1;
69	id select_type table type possible_keys key key_len ref rows Extra
70	1 SIMPLE t1 index_merge key1,key2 key1,key2 5,4 NULL 1 Using intersect(key1,key2); Using where; Using index
71	select pk from t1 where key2 = 1 and key1 = 1;
72	pk
73	26
74	27
75	select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1;
76	pk
77	26
78	27
79	drop table t1;
80	create table t1 (
81	pk int primary key auto_increment,
82	key1a int,
83	key2a int,
84	key1b int,
85	key2b int,
86	dummy1 int,
87	dummy2 int,
88	dummy3 int,
89	dummy4 int,
90	key3a int,
91	key3b int,
92	filler1 char (200),
93	index i1(key1a, key1b),
94	index i2(key2a, key2b),
95	index i3(key3a, key3b)
96	);
97	create table t2 (a int);
98	insert into t2 values (0),(1),(2),(3),(4),(NULL);
99	insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
100	select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D;
101	insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
102	select key1a, key1b, key2a, key2b, key3a, key3b from t1;
103	insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
104	select key1a, key1b, key2a, key2b, key3a, key3b from t1;
105	analyze table t1;
106	Table Op Msg_type Msg_text
107	test.t1 analyze status OK
108	select count(*) from t1;
109	count(*)
110	5184
111	explain select count(*) from t1 where
112	key1a = 2 and key1b is null and key2a = 2 and key2b is null;
113	id select_type table type possible_keys key key_len ref rows Extra
114	1 SIMPLE t1 index_merge i1,i2 i1,i2 10,10 NULL 4 Using intersect(i1,i2); Using where; Using index
115	select count(*) from t1 where
116	key1a = 2 and key1b is null and key2a = 2 and key2b is null;
117	count(*)
118	4
119	explain select count(*) from t1 where
120	key1a = 2 and key1b is null and key3a = 2 and key3b is null;
121	id select_type table type possible_keys key key_len ref rows Extra
122	1 SIMPLE t1 index_merge i1,i3 i1,i3 10,10 NULL 4 Using intersect(i1,i3); Using where; Using index
123	select count(*) from t1 where
124	key1a = 2 and key1b is null and key3a = 2 and key3b is null;
125	count(*)
126	4
127	drop table t1,t2;
128	create table t1 (
129	id1 int,
130	id2 date ,
131	index idx2 (id1,id2),
132	index idx1 (id2)
133	);
134	insert into t1 values(1,'20040101'), (2,'20040102');
135	select * from t1 where id1 = 1 and id2= '20040101';
136	id1 id2
137	1 2004-01-01
138	drop table t1;
139	create table t1
140	(
141	key1 int not null,
142	key2 int not null default 0,
143	key3 int not null default 0
144	);
145	insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8);
146	set @d=8;
147	insert into t1 (key1) select key1+@d from t1;
148	set @d=@d*2;
149	insert into t1 (key1) select key1+@d from t1;
150	set @d=@d*2;
151	insert into t1 (key1) select key1+@d from t1;
152	set @d=@d*2;
153	insert into t1 (key1) select key1+@d from t1;
154	set @d=@d*2;
155	insert into t1 (key1) select key1+@d from t1;
156	set @d=@d*2;
157	insert into t1 (key1) select key1+@d from t1;
158	set @d=@d*2;
159	insert into t1 (key1) select key1+@d from t1;
160	set @d=@d*2;
161	alter table t1 add index i2(key2);
162	alter table t1 add index i3(key3);
163	update t1 set key2=key1,key3=key1;
164	explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
165	id select_type table type possible_keys key key_len ref rows Extra
166	1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 9 Using sort_union(i3,i2); Using where
167	select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
168	key1 key2 key3
169	31 31 31
170	32 32 32
171	33 33 33
172	34 34 34
173	35 35 35
174	36 36 36
175	37 37 37
176	38 38 38
177	39 39 39
178	drop table t1;
179	#---------------- 2-sweeps read Index merge test 2 -------------------------------
180	SET SESSION STORAGE_ENGINE = InnoDB;
181	drop table if exists t1;
182	create table t1 (
183	pk int primary key,
184	key1 int,
185	key2 int,
186	filler char(200),
187	filler2 char(200),
188	index(key1),
189	index(key2)
190	);
191	select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 );
192	pk key1 key2 filler filler2
193	2 2 2 filler-data filler-data-2
194	3 3 3 filler-data filler-data-2
195	9 9 9 filler-data filler-data-2
196	10 10 10 filler-data filler-data-2
197	4 4 4 filler-data filler-data-2
198	5 5 5 filler-data filler-data-2
199	6 6 6 filler-data filler-data-2
200	7 7 7 filler-data filler-data-2
201	8 8 8 filler-data filler-data-2
202	set @maxv=1000;
203	select * from t1 where
204	(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
205	or key1=18 or key1=60;
206	pk key1 key2 filler filler2
207	18 18 18 filler-data filler-data-2
208	60 60 60 filler-data filler-data-2
209	1 1 1 filler-data filler-data-2
210	2 2 2 filler-data filler-data-2
211	3 3 3 filler-data filler-data-2
212	4 4 4 filler-data filler-data-2
213	11 11 11 filler-data filler-data-2
214	12 12 12 filler-data filler-data-2
215	13 13 13 filler-data filler-data-2
216	14 14 14 filler-data filler-data-2
217	50 50 50 filler-data filler-data-2
218	51 51 51 filler-data filler-data-2
219	52 52 52 filler-data filler-data-2
220	53 53 53 filler-data filler-data-2
221	54 54 54 filler-data filler-data-2
222	991 991 991 filler-data filler-data-2
223	992 992 992 filler-data filler-data-2
224	993 993 993 filler-data filler-data-2
225	994 994 994 filler-data filler-data-2
226	995 995 995 filler-data filler-data-2
227	996 996 996 filler-data filler-data-2
228	997 997 997 filler-data filler-data-2
229	998 998 998 filler-data filler-data-2
230	999 999 999 filler-data filler-data-2
231	1000 1000 1000 filler-data filler-data-2
232	select * from t1 where
233	(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
234	or key1 < 3 or key1 > @maxv-11;
235	pk key1 key2 filler filler2
236	990 990 990 filler-data filler-data-2
237	1 1 1 filler-data filler-data-2
238	2 2 2 filler-data filler-data-2
239	3 3 3 filler-data filler-data-2
240	4 4 4 filler-data filler-data-2
241	11 11 11 filler-data filler-data-2
242	12 12 12 filler-data filler-data-2
243	13 13 13 filler-data filler-data-2
244	14 14 14 filler-data filler-data-2
245	50 50 50 filler-data filler-data-2
246	51 51 51 filler-data filler-data-2
247	52 52 52 filler-data filler-data-2
248	53 53 53 filler-data filler-data-2
249	54 54 54 filler-data filler-data-2
250	991 991 991 filler-data filler-data-2
251	992 992 992 filler-data filler-data-2
252	993 993 993 filler-data filler-data-2
253	994 994 994 filler-data filler-data-2
254	995 995 995 filler-data filler-data-2
255	996 996 996 filler-data filler-data-2
256	997 997 997 filler-data filler-data-2
257	998 998 998 filler-data filler-data-2
258	999 999 999 filler-data filler-data-2
259	1000 1000 1000 filler-data filler-data-2
260	select * from t1 where
261	(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
262	or
263	(key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10);
264	pk key1 key2 filler filler2
265	1 1 1 filler-data filler-data-2
266	2 2 2 filler-data filler-data-2
267	3 3 3 filler-data filler-data-2
268	4 4 4 filler-data filler-data-2
269	11 11 11 filler-data filler-data-2
270	12 12 12 filler-data filler-data-2
271	13 13 13 filler-data filler-data-2
272	14 14 14 filler-data filler-data-2
273	50 50 50 filler-data filler-data-2
274	51 51 51 filler-data filler-data-2
275	52 52 52 filler-data filler-data-2
276	53 53 53 filler-data filler-data-2
277	54 54 54 filler-data filler-data-2
278	991 991 991 filler-data filler-data-2
279	992 992 992 filler-data filler-data-2
280	993 993 993 filler-data filler-data-2
281	994 994 994 filler-data filler-data-2
282	995 995 995 filler-data filler-data-2
283	996 996 996 filler-data filler-data-2
284	997 997 997 filler-data filler-data-2
285	998 998 998 filler-data filler-data-2
286	999 999 999 filler-data filler-data-2
287	1000 1000 1000 filler-data filler-data-2
288	select * from t1 where
289	(pk > 10 and pk < 15) or (pk >= 50 and pk < 55 )
290	or
291	(key1 < 5) or (key1 > @maxv-10);
292	pk key1 key2 filler filler2
293	1 1 1 filler-data filler-data-2
294	2 2 2 filler-data filler-data-2
295	3 3 3 filler-data filler-data-2
296	4 4 4 filler-data filler-data-2
297	991 991 991 filler-data filler-data-2
298	992 992 992 filler-data filler-data-2
299	993 993 993 filler-data filler-data-2
300	994 994 994 filler-data filler-data-2
301	995 995 995 filler-data filler-data-2
302	996 996 996 filler-data filler-data-2
303	997 997 997 filler-data filler-data-2
304	998 998 998 filler-data filler-data-2
305	999 999 999 filler-data filler-data-2
306	1000 1000 1000 filler-data filler-data-2
307	11 11 11 filler-data filler-data-2
308	12 12 12 filler-data filler-data-2
309	13 13 13 filler-data filler-data-2
310	14 14 14 filler-data filler-data-2
311	50 50 50 filler-data filler-data-2
312	51 51 51 filler-data filler-data-2
313	52 52 52 filler-data filler-data-2
314	53 53 53 filler-data filler-data-2
315	54 54 54 filler-data filler-data-2
316	drop table t1;
317	#---------------- Clustered PK ROR-index_merge tests -----------------------------
318	SET SESSION STORAGE_ENGINE = InnoDB;
319	drop table if exists t1;
320	create table t1
321	(
322	pk1 int not null,
323	pk2 int not null,
324	key1 int not null,
325	key2 int not null,
326	pktail1ok int not null,
327	pktail2ok int not null,
328	pktail3bad int not null,
329	pktail4bad int not null,
330	pktail5bad int not null,
331	pk2copy int not null,
332	badkey int not null,
333	filler1 char (200),
334	filler2 char (200),
335	key (key1),
336	key (key2),
337	/* keys with tails from CPK members */
338	key (pktail1ok, pk1),
339	key (pktail2ok, pk1, pk2),
340	key (pktail3bad, pk2, pk1),
341	key (pktail4bad, pk1, pk2copy),
342	key (pktail5bad, pk1, pk2, pk2copy),
343	primary key (pk1, pk2)
344	);
345	explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
346	id select_type table type possible_keys key key_len ref rows Extra
347	1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 9 Using where
348	select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
349	pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2
350	1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2
351	1 11 0 0 0 0 0 0 0 11 0 filler-data-11 filler2
352	1 12 0 0 0 0 0 0 0 12 0 filler-data-12 filler2
353	1 13 0 0 0 0 0 0 0 13 0 filler-data-13 filler2
354	1 14 0 0 0 0 0 0 0 14 0 filler-data-14 filler2
355	1 15 0 0 0 0 0 0 0 15 0 filler-data-15 filler2
356	1 16 0 0 0 0 0 0 0 16 0 filler-data-16 filler2
357	1 17 0 0 0 0 0 0 0 17 0 filler-data-17 filler2
358	1 18 0 0 0 0 0 0 0 18 0 filler-data-18 filler2
359	1 19 0 0 0 0 0 0 0 19 0 filler-data-19 filler2
360	explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2pk1+1 < 296+1;
361	id select_type table type possible_keys key key_len ref rows Extra
362	1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where; Using index
363	select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2pk1+1 < 296+1;
364	pk1 pk2
365	95 50
366	95 51
367	95 52
368	95 53
369	95 54
370	95 55
371	95 56
372	95 57
373	95 58
374	95 59
375	explain select * from t1 where badkey=1 and key1=10;
376	id select_type table type possible_keys key key_len ref rows Extra
377	1 SIMPLE t1 ref key1 key1 4 const 100 Using where
378	explain select * from t1 where pk1 < 7500 and key1 = 10;
379	id select_type table type possible_keys key key_len ref rows Extra
380	1 SIMPLE t1 index_merge PRIMARY,key1 key1,PRIMARY 4,4 NULL ROWS Using intersect(key1,PRIMARY); Using where
381	explain select * from t1 where pktail1ok=1 and key1=10;
382	id select_type table type possible_keys key key_len ref rows Extra
383	1 SIMPLE t1 index_merge key1,pktail1ok key1,pktail1ok 4,4 NULL 1 Using intersect(key1,pktail1ok); Using where
384	explain select * from t1 where pktail2ok=1 and key1=10;
385	id select_type table type possible_keys key key_len ref rows Extra
386	1 SIMPLE t1 index_merge key1,pktail2ok key1,pktail2ok 4,4 NULL 1 Using intersect(key1,pktail2ok); Using where
387	explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
388	id select_type table type possible_keys key key_len ref rows Extra
389	1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 199 Using sort_union(pktail2ok,key1); Using where
390	explain select * from t1 where pktail3bad=1 and key1=10;
391	id select_type table type possible_keys key key_len ref rows Extra
392	1 SIMPLE t1 ref key1,pktail3bad key1 4 const 100 Using where
393	explain select * from t1 where pktail4bad=1 and key1=10;
394	id select_type table type possible_keys key key_len ref rows Extra
395	1 SIMPLE t1 ref key1,pktail4bad key1 4 const 100 Using where
396	explain select * from t1 where pktail5bad=1 and key1=10;
397	id select_type table type possible_keys key key_len ref rows Extra
398	1 SIMPLE t1 ref key1,pktail5bad key1 4 const 100 Using where
399	explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
400	id select_type table type possible_keys key key_len ref rows Extra
401	1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where; Using index
402	select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
403	pk1 pk2 key1 key2
404	95 50 10 10
405	95 51 10 10
406	95 52 10 10
407	95 53 10 10
408	95 54 10 10
409	95 55 10 10
410	95 56 10 10
411	95 57 10 10
412	95 58 10 10
413	95 59 10 10
414	drop table t1;
415	create table t1
416	(
417	RUNID varchar(22),
418	SUBMITNR varchar(5),
419	ORDERNR char(1),
420	PROGRAMM varchar(8),
421	TESTID varchar(4),
422	UCCHECK char(1),
423	ETEXT varchar(80),
424	ETEXT_TYPE char(1),
425	INFO char(1),
426	SEVERITY tinyint(3),
427	TADIRFLAG char(1),
428	PRIMARY KEY (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK),
429	KEY `TVERM~KEY` (PROGRAMM,TESTID,UCCHECK)
430	) DEFAULT CHARSET=latin1;
431	update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`=''
432	WHERE
433	`RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND
434	`TESTID`='' AND `UCCHECK`='';
435	drop table t1;