~drizzle-trunk/drizzle/development : contents of tests/r/index_merge

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

#---------------- 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 (100) not null,
add zeroval int not null default 0,
add str2 char (100) not null,
add str3 char (100) 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	#	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	#	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;
begin;
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;
commit;
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 int,
TADIRFLAG char(1),
PRIMARY KEY  (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK),
KEY `TVERM~KEY`  (PROGRAMM,TESTID,UCCHECK)
);
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
764 by Brian Aker Fixed index_merge_innodb test.	31	alter table t1 add str1 char (100) not null,
1 by brian clean slate	32	add zeroval int not null default 0,
764 by Brian Aker Fixed index_merge_innodb test.	33	add str2 char (100) not null,
764 by Brian Aker Fixed index_merge_innodb test.	34	add str3 char (100) not null;
1 by brian clean slate	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)
1063.9.3 by Brian Aker Partial fix for tests for tmp	96	);
1 by brian clean slate	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
1063.9.4 by Stewart Smith Fix index_merge2.inc for myisam temp only. Needs to be non-myisam table as there are queries which require opening the table multiple times	111	explain select count(*) from t1 where
1 by brian clean slate	112	key1a = 2 and key1b is null and key2a = 2 and key2b is null;
1063.9.4 by Stewart Smith Fix index_merge2.inc for myisam temp only. Needs to be non-myisam table as there are queries which require opening the table multiple times	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 # Using intersect(i1,i2); Using where; Using index
1 by brian clean slate	115	select count(*) from t1 where
	116	key1a = 2 and key1b is null and key2a = 2 and key2b is null;
	117	count(*)
	118	4
1063.9.4 by Stewart Smith Fix index_merge2.inc for myisam temp only. Needs to be non-myisam table as there are queries which require opening the table multiple times	119	explain select count(*) from t1 where
1 by brian clean slate	120	key1a = 2 and key1b is null and key3a = 2 and key3b is null;
1063.9.4 by Stewart Smith Fix index_merge2.inc for myisam temp only. Needs to be non-myisam table as there are queries which require opening the table multiple times	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 # Using intersect(i1,i3); Using where; Using index
1 by brian clean slate	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;
910.4.13 by Stewart Smith batch up more INSERTs into transactions to help tests run quicker.	147	begin;
	148	insert into t1 (key1) select key1+@d from t1;
	149	set @d=@d*2;
	150	insert into t1 (key1) select key1+@d from t1;
	151	set @d=@d*2;
	152	insert into t1 (key1) select key1+@d from t1;
	153	set @d=@d*2;
	154	insert into t1 (key1) select key1+@d from t1;
	155	set @d=@d*2;
	156	insert into t1 (key1) select key1+@d from t1;
	157	set @d=@d*2;
	158	insert into t1 (key1) select key1+@d from t1;
	159	set @d=@d*2;
	160	insert into t1 (key1) select key1+@d from t1;
	161	set @d=@d*2;
	162	commit;
1 by brian clean slate	163	alter table t1 add index i2(key2);
	164	alter table t1 add index i3(key3);
	165	update t1 set key2=key1,key3=key1;
	166	explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
	167	id select_type table type possible_keys key key_len ref rows Extra
	168	1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 9 Using sort_union(i3,i2); Using where
	169	select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
	170	key1 key2 key3
	171	31 31 31
	172	32 32 32
	173	33 33 33
	174	34 34 34
	175	35 35 35
	176	36 36 36
	177	37 37 37
	178	38 38 38
	179	39 39 39
	180	drop table t1;
	181	#---------------- 2-sweeps read Index merge test 2 -------------------------------
	182	SET SESSION STORAGE_ENGINE = InnoDB;
	183	drop table if exists t1;
	184	create table t1 (
	185	pk int primary key,
	186	key1 int,
	187	key2 int,
	188	filler char(200),
	189	filler2 char(200),
	190	index(key1),
	191	index(key2)
	192	);
	193	select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 );
	194	pk key1 key2 filler filler2
	195	2 2 2 filler-data filler-data-2
	196	3 3 3 filler-data filler-data-2
	197	9 9 9 filler-data filler-data-2
	198	10 10 10 filler-data filler-data-2
	199	4 4 4 filler-data filler-data-2
	200	5 5 5 filler-data filler-data-2
	201	6 6 6 filler-data filler-data-2
	202	7 7 7 filler-data filler-data-2
	203	8 8 8 filler-data filler-data-2
	204	set @maxv=1000;
	205	select * from t1 where
	206	(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
	207	or key1=18 or key1=60;
	208	pk key1 key2 filler filler2
	209	18 18 18 filler-data filler-data-2
	210	60 60 60 filler-data filler-data-2
	211	1 1 1 filler-data filler-data-2
	212	2 2 2 filler-data filler-data-2
	213	3 3 3 filler-data filler-data-2
	214	4 4 4 filler-data filler-data-2
	215	11 11 11 filler-data filler-data-2
	216	12 12 12 filler-data filler-data-2
	217	13 13 13 filler-data filler-data-2
	218	14 14 14 filler-data filler-data-2
	219	50 50 50 filler-data filler-data-2
	220	51 51 51 filler-data filler-data-2
	221	52 52 52 filler-data filler-data-2
	222	53 53 53 filler-data filler-data-2
	223	54 54 54 filler-data filler-data-2
	224	991 991 991 filler-data filler-data-2
	225	992 992 992 filler-data filler-data-2
	226	993 993 993 filler-data filler-data-2
227	994 994 994 filler-data filler-data-2
228	995 995 995 filler-data filler-data-2
229	996 996 996 filler-data filler-data-2
230	997 997 997 filler-data filler-data-2
231	998 998 998 filler-data filler-data-2
232	999 999 999 filler-data filler-data-2
233	1000 1000 1000 filler-data filler-data-2
234	select * from t1 where
235	(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
236	or key1 < 3 or key1 > @maxv-11;
237	pk key1 key2 filler filler2
238	990 990 990 filler-data filler-data-2
239	1 1 1 filler-data filler-data-2
240	2 2 2 filler-data filler-data-2
241	3 3 3 filler-data filler-data-2
242	4 4 4 filler-data filler-data-2
243	11 11 11 filler-data filler-data-2
244	12 12 12 filler-data filler-data-2
245	13 13 13 filler-data filler-data-2
246	14 14 14 filler-data filler-data-2
247	50 50 50 filler-data filler-data-2
248	51 51 51 filler-data filler-data-2
249	52 52 52 filler-data filler-data-2
250	53 53 53 filler-data filler-data-2
251	54 54 54 filler-data filler-data-2
252	991 991 991 filler-data filler-data-2
253	992 992 992 filler-data filler-data-2
254	993 993 993 filler-data filler-data-2
255	994 994 994 filler-data filler-data-2
256	995 995 995 filler-data filler-data-2
257	996 996 996 filler-data filler-data-2
258	997 997 997 filler-data filler-data-2
259	998 998 998 filler-data filler-data-2
260	999 999 999 filler-data filler-data-2
261	1000 1000 1000 filler-data filler-data-2
262	select * from t1 where
263	(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
264	or
265	(key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10);
266	pk key1 key2 filler filler2
267	1 1 1 filler-data filler-data-2
268	2 2 2 filler-data filler-data-2
269	3 3 3 filler-data filler-data-2
270	4 4 4 filler-data filler-data-2
271	11 11 11 filler-data filler-data-2
272	12 12 12 filler-data filler-data-2
273	13 13 13 filler-data filler-data-2
274	14 14 14 filler-data filler-data-2
275	50 50 50 filler-data filler-data-2
276	51 51 51 filler-data filler-data-2
277	52 52 52 filler-data filler-data-2
278	53 53 53 filler-data filler-data-2
279	54 54 54 filler-data filler-data-2
280	991 991 991 filler-data filler-data-2
281	992 992 992 filler-data filler-data-2
282	993 993 993 filler-data filler-data-2
283	994 994 994 filler-data filler-data-2
284	995 995 995 filler-data filler-data-2
285	996 996 996 filler-data filler-data-2
286	997 997 997 filler-data filler-data-2
287	998 998 998 filler-data filler-data-2
288	999 999 999 filler-data filler-data-2
289	1000 1000 1000 filler-data filler-data-2
290	select * from t1 where
291	(pk > 10 and pk < 15) or (pk >= 50 and pk < 55 )
292	or
293	(key1 < 5) or (key1 > @maxv-10);
294	pk key1 key2 filler filler2
295	1 1 1 filler-data filler-data-2
296	2 2 2 filler-data filler-data-2
297	3 3 3 filler-data filler-data-2
298	4 4 4 filler-data filler-data-2
299	991 991 991 filler-data filler-data-2
300	992 992 992 filler-data filler-data-2
301	993 993 993 filler-data filler-data-2
302	994 994 994 filler-data filler-data-2
303	995 995 995 filler-data filler-data-2
304	996 996 996 filler-data filler-data-2
305	997 997 997 filler-data filler-data-2
306	998 998 998 filler-data filler-data-2
307	999 999 999 filler-data filler-data-2
308	1000 1000 1000 filler-data filler-data-2
309	11 11 11 filler-data filler-data-2
310	12 12 12 filler-data filler-data-2
311	13 13 13 filler-data filler-data-2
312	14 14 14 filler-data filler-data-2
313	50 50 50 filler-data filler-data-2
314	51 51 51 filler-data filler-data-2
315	52 52 52 filler-data filler-data-2
316	53 53 53 filler-data filler-data-2
317	54 54 54 filler-data filler-data-2
318	drop table t1;
319	#---------------- Clustered PK ROR-index_merge tests -----------------------------
320	SET SESSION STORAGE_ENGINE = InnoDB;
321	drop table if exists t1;
322	create table t1
323	(
324	pk1 int not null,
325	pk2 int not null,
326	key1 int not null,
327	key2 int not null,
328	pktail1ok int not null,
329	pktail2ok int not null,
330	pktail3bad int not null,
331	pktail4bad int not null,
332	pktail5bad int not null,
333	pk2copy int not null,
334	badkey int not null,
335	filler1 char (200),
336	filler2 char (200),
337	key (key1),
338	key (key2),
339	/* keys with tails from CPK members */
340	key (pktail1ok, pk1),
341	key (pktail2ok, pk1, pk2),
342	key (pktail3bad, pk2, pk1),
343	key (pktail4bad, pk1, pk2copy),
344	key (pktail5bad, pk1, pk2, pk2copy),
345	primary key (pk1, pk2)
346	);
347	explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
348	id select_type table type possible_keys key key_len ref rows Extra
349	1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 9 Using where
350	select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
351	pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2
352	1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2
353	1 11 0 0 0 0 0 0 0 11 0 filler-data-11 filler2
354	1 12 0 0 0 0 0 0 0 12 0 filler-data-12 filler2
355	1 13 0 0 0 0 0 0 0 13 0 filler-data-13 filler2
356	1 14 0 0 0 0 0 0 0 14 0 filler-data-14 filler2
357	1 15 0 0 0 0 0 0 0 15 0 filler-data-15 filler2
358	1 16 0 0 0 0 0 0 0 16 0 filler-data-16 filler2
359	1 17 0 0 0 0 0 0 0 17 0 filler-data-17 filler2
360	1 18 0 0 0 0 0 0 0 18 0 filler-data-18 filler2
361	1 19 0 0 0 0 0 0 0 19 0 filler-data-19 filler2
362	explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2pk1+1 < 296+1;
363	id select_type table type possible_keys key key_len ref rows Extra
364	1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where; Using index
365	select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2pk1+1 < 296+1;
366	pk1 pk2
367	95 50
368	95 51
369	95 52
370	95 53
371	95 54
372	95 55
373	95 56
374	95 57
375	95 58
376	95 59
377	explain select * from t1 where badkey=1 and key1=10;
378	id select_type table type possible_keys key key_len ref rows Extra
379	1 SIMPLE t1 ref key1 key1 4 const 100 Using where
380	explain select * from t1 where pk1 < 7500 and key1 = 10;
381	id select_type table type possible_keys key key_len ref rows Extra
382	1 SIMPLE t1 index_merge PRIMARY,key1 key1,PRIMARY 4,4 NULL ROWS Using intersect(key1,PRIMARY); Using where
383	explain select * from t1 where pktail1ok=1 and key1=10;
384	id select_type table type possible_keys key key_len ref rows Extra
385	1 SIMPLE t1 index_merge key1,pktail1ok key1,pktail1ok 4,4 NULL 1 Using intersect(key1,pktail1ok); Using where
386	explain select * from t1 where pktail2ok=1 and key1=10;
387	id select_type table type possible_keys key key_len ref rows Extra
388	1 SIMPLE t1 index_merge key1,pktail2ok key1,pktail2ok 4,4 NULL 1 Using intersect(key1,pktail2ok); Using where
389	explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
390	id select_type table type possible_keys key key_len ref rows Extra
391	1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 199 Using sort_union(pktail2ok,key1); Using where
392	explain select * from t1 where pktail3bad=1 and key1=10;
393	id select_type table type possible_keys key key_len ref rows Extra
394	1 SIMPLE t1 ref key1,pktail3bad key1 4 const 100 Using where
395	explain select * from t1 where pktail4bad=1 and key1=10;
396	id select_type table type possible_keys key key_len ref rows Extra
397	1 SIMPLE t1 ref key1,pktail4bad key1 4 const 100 Using where
398	explain select * from t1 where pktail5bad=1 and key1=10;
399	id select_type table type possible_keys key key_len ref rows Extra
400	1 SIMPLE t1 ref key1,pktail5bad key1 4 const 100 Using where
401	explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
402	id select_type table type possible_keys key key_len ref rows Extra
403	1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where; Using index
404	select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
405	pk1 pk2 key1 key2
406	95 50 10 10
407	95 51 10 10
408	95 52 10 10
409	95 53 10 10
410	95 54 10 10
411	95 55 10 10
412	95 56 10 10
413	95 57 10 10
414	95 58 10 10
415	95 59 10 10
416	drop table t1;
417	create table t1
418	(
419	RUNID varchar(22),
420	SUBMITNR varchar(5),
421	ORDERNR char(1),
422	PROGRAMM varchar(8),
423	TESTID varchar(4),
424	UCCHECK char(1),
425	ETEXT varchar(80),
426	ETEXT_TYPE char(1),
427	INFO char(1),
764 by Brian Aker Fixed index_merge_innodb test.	428	SEVERITY int,
1 by brian clean slate	429	TADIRFLAG char(1),
	430	PRIMARY KEY (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK),
	431	KEY `TVERM~KEY` (PROGRAMM,TESTID,UCCHECK)
764 by Brian Aker Fixed index_merge_innodb test.	432	);
1 by brian clean slate	433	update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`=''
	434	WHERE
	435	`RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND
	436	`TESTID`='' AND `UCCHECK`='';
	437	drop table t1;