path: root/mysql-test/main/index_merge_innodb.result

connect disable_purge,localhost,root,,;
# Disable the purge of InnoDB history, to make the test run faster.
START TRANSACTION WITH CONSISTENT SNAPSHOT;
connection default;
SET DEFAULT_STORAGE_ENGINE = InnoDB;
set @optimizer_switch_save= @@optimizer_switch;
set optimizer_switch='index_merge_sort_intersection=off';
set optimizer_switch='rowid_filter=off';
SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent;
SET GLOBAL innodb_stats_persistent=0;
#---------------- Index merge test 2 -------------------------------------------
create table t1
(
key1 int not null,
key2 int not null,
INDEX i1(key1),
INDEX i2(key2)
);
INSERT INTO t1 SELECT seq,200-seq FROM seq_0_to_200;
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
INSERT INTO t1 (key1, key2, filler)
SELECT seq/4, seq/8, 'filler-data' FROM seq_30_to_0;
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	Engine-independent statistics collected
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	REF	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	REF	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;
drop view if exists v1;
CREATE TABLE t1 (
`oid` int(11) unsigned NOT NULL auto_increment,
`fk_bbk_niederlassung` int(11) unsigned NOT NULL,
`fk_wochentag` int(11) unsigned NOT NULL,
`uhrzeit_von` time NOT NULL COMMENT 'HH:MM',
`uhrzeit_bis` time NOT NULL COMMENT 'HH:MM',
`geloescht` tinyint(4) NOT NULL,
`version` int(5) NOT NULL,
PRIMARY KEY  (`oid`),
KEY `fk_bbk_niederlassung` (`fk_bbk_niederlassung`),
KEY `fk_wochentag` (`fk_wochentag`),
KEY `ix_version` (`version`)
) DEFAULT CHARSET=latin1;
insert  into t1 values
(1, 38, 1, '08:00:00', '13:00:00', 0, 1),
(2, 38, 2, '08:00:00', '13:00:00', 0, 1),
(3, 38, 3, '08:00:00', '13:00:00', 0, 1),
(4, 38, 4, '08:00:00', '13:00:00', 0, 1),
(5, 38, 5, '08:00:00', '13:00:00', 0, 1),
(6, 38, 5, '08:00:00', '13:00:00', 1, 2),
(7, 38, 3, '08:00:00', '13:00:00', 1, 2),
(8, 38, 1, '08:00:00', '13:00:00', 1, 2),
(9, 38, 2, '08:00:00', '13:00:00', 1, 2),
(10, 38, 4, '08:00:00', '13:00:00', 1, 2),
(11, 38, 1, '08:00:00', '13:00:00', 0, 3),
(12, 38, 2, '08:00:00', '13:00:00', 0, 3),
(13, 38, 3, '08:00:00', '13:00:00', 0, 3),
(14, 38, 4, '08:00:00', '13:00:00', 0, 3),
(15, 38, 5, '08:00:00', '13:00:00', 0, 3),
(16, 38, 4, '08:00:00', '13:00:00', 0, 4),
(17, 38, 5, '08:00:00', '13:00:00', 0, 4),
(18, 38, 1, '08:00:00', '13:00:00', 0, 4),
(19, 38, 2, '08:00:00', '13:00:00', 0, 4),
(20, 38, 3, '08:00:00', '13:00:00', 0, 4),
(21, 7, 1, '08:00:00', '13:00:00', 0, 1),
(22, 7, 2, '08:00:00', '13:00:00', 0, 1),
(23, 7, 3, '08:00:00', '13:00:00', 0, 1),
(24, 7, 4, '08:00:00', '13:00:00', 0, 1),
(25, 7, 5, '08:00:00', '13:00:00', 0, 1);
create view v1 as
select
zeit1.oid AS oid,
zeit1.fk_bbk_niederlassung AS fk_bbk_niederlassung,
zeit1.fk_wochentag AS fk_wochentag,
zeit1.uhrzeit_von AS uhrzeit_von,
zeit1.uhrzeit_bis AS uhrzeit_bis,
zeit1.geloescht AS geloescht,
zeit1.version AS version
from
t1 zeit1
where
(zeit1.version =
(select max(zeit2.version) AS `max(version)`
   from t1 zeit2
where
((zeit1.fk_bbk_niederlassung = zeit2.fk_bbk_niederlassung) and
(zeit1.fk_wochentag = zeit2.fk_wochentag) and
(zeit1.uhrzeit_von = zeit2.uhrzeit_von) and
(zeit1.uhrzeit_bis = zeit2.uhrzeit_bis)
)
)
)
and (zeit1.geloescht = 0);
select * from v1 where oid = 21;
oid	fk_bbk_niederlassung	fk_wochentag	uhrzeit_von	uhrzeit_bis	geloescht	version
21	7	1	08:00:00	13:00:00	0	1
drop view v1;
drop table t1;
CREATE TABLE t1(
t_cpac varchar(2) NOT NULL,
t_vers varchar(4) NOT NULL,
t_rele varchar(2) NOT NULL,
t_cust varchar(4) NOT NULL,
filler1 char(250) default NULL,
filler2 char(250) default NULL,
PRIMARY KEY (t_cpac,t_vers,t_rele,t_cust),
UNIQUE KEY IX_4 (t_cust,t_cpac,t_vers,t_rele),
KEY IX_5 (t_vers,t_rele,t_cust)
);
insert into t1 values
('tm','2.5 ','a ','    ','',''), ('tm','2.5U','a ','stnd','',''),
('da','3.3 ','b ','    ','',''), ('da','3.3U','b ','stnd','',''),
('tl','7.6 ','a ','    ','',''), ('tt','7.6 ','a ','    ','',''),
('bc','B61 ','a ','    ','',''), ('bp','B61 ','a ','    ','',''),
('ca','B61 ','a ','    ','',''), ('ci','B61 ','a ','    ','',''),
('cp','B61 ','a ','    ','',''), ('dm','B61 ','a ','    ','',''),
('ec','B61 ','a ','    ','',''), ('ed','B61 ','a ','    ','',''),
('fm','B61 ','a ','    ','',''), ('nt','B61 ','a ','    ','',''),
('qm','B61 ','a ','    ','',''), ('tc','B61 ','a ','    ','',''),
('td','B61 ','a ','    ','',''), ('tf','B61 ','a ','    ','',''),
('tg','B61 ','a ','    ','',''), ('ti','B61 ','a ','    ','',''),
('tp','B61 ','a ','    ','',''), ('ts','B61 ','a ','    ','',''),
('wh','B61 ','a ','    ','',''), ('bc','B61U','a ','stnd','',''),
('bp','B61U','a ','stnd','',''), ('ca','B61U','a ','stnd','',''),
('ci','B61U','a ','stnd','',''), ('cp','B61U','a ','stnd','',''),
('dm','B61U','a ','stnd','',''), ('ec','B61U','a ','stnd','',''),
('fm','B61U','a ','stnd','',''), ('nt','B61U','a ','stnd','',''),
('qm','B61U','a ','stnd','',''), ('tc','B61U','a ','stnd','',''),
('td','B61U','a ','stnd','',''), ('tf','B61U','a ','stnd','',''),
('tg','B61U','a ','stnd','',''), ('ti','B61U','a ','stnd','',''),
('tp','B61U','a ','stnd','',''), ('ts','B61U','a ','stnd','',''),
('wh','B61U','a ','stnd','','');
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t_cpac` varchar(2) NOT NULL,
  `t_vers` varchar(4) NOT NULL,
  `t_rele` varchar(2) NOT NULL,
  `t_cust` varchar(4) NOT NULL,
  `filler1` char(250) DEFAULT NULL,
  `filler2` char(250) DEFAULT NULL,
  PRIMARY KEY (`t_cpac`,`t_vers`,`t_rele`,`t_cust`),
  UNIQUE KEY `IX_4` (`t_cust`,`t_cpac`,`t_vers`,`t_rele`),
  KEY `IX_5` (`t_vers`,`t_rele`,`t_cust`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6';
t_vers	t_rele	t_cust	filler1
7.6 	a 	    	
7.6 	a 	    	
select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6'
  and t_rele='a' and t_cust = ' ';
t_vers	t_rele	t_cust	filler1
7.6 	a 	    	
7.6 	a 	    	
drop table t1;
create table t1 (
pk int(11) not null auto_increment,
a int(11) not null default '0',
b int(11) not null default '0',
c int(11) not null default '0',
filler1 datetime, filler2 varchar(15),
filler3 longtext,
kp1 varchar(4), kp2 varchar(7),
kp3 varchar(2), kp4 varchar(4),
kp5 varchar(7),
filler4 char(1),
primary key (pk),
key idx1(a,b,c),
key idx2(c),
key idx3(kp1,kp2,kp3,kp4,kp5)
) default charset=latin1;
set @fill=NULL;
SELECT COUNT(*) FROM t1 WHERE b = 0 AND a = 0 AND c = 13286427 AND
kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND  kp5 = 'R        ';
COUNT(*)
1
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) select seq from seq_1_to_1024;
alter table t1 add index i2(key2);
alter table t1 add index i3(key3);
update t1 set key2=key1,key3=key1;
insert into t1 select 10000+key1, 10000+key2,10000+key3 from t1;
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
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	REF	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;
#
# Bug#56423: Different count with SELECT and CREATE SELECT queries
#
CREATE TABLE t1 (
a INT,
b INT,
c INT,
d INT,
PRIMARY KEY (a),
KEY (c),
KEY bd (b,d)
);
INSERT INTO t1 VALUES
(1, 0, 1, 0),
(2, 1, 1, 1),
(3, 1, 1, 1),
(4, 0, 1, 1);
EXPLAIN
SELECT a
FROM t1
WHERE c = 1 AND b = 1 AND d = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	c,bd	bd	10	const,const	2	Using where
CREATE TABLE t2 ( a INT )
SELECT a
FROM t1
WHERE c = 1 AND b = 1 AND d = 1;
SELECT * FROM t2;
a
2
3
DROP TABLE t1, t2;
CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) );
INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2);
SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2;
a	b
1	2
1	2
1	2
1	2
DROP TABLE t1;
# Code coverage of fix.
CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
INSERT INTO t1 (b) VALUES (1);
UPDATE t1 SET b = 2 WHERE a = 1;
SELECT * FROM t1;
a	b
1	2
CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) );
INSERT INTO t2 (b) VALUES ('a');
UPDATE t2 SET b = 'b' WHERE a = 1;
SELECT * FROM t2;
a	b
1	b
DROP TABLE t1, t2;
#---------------- 2-sweeps read Index merge test 2 -------------------------------
create table t1 (
pk int primary key,
key1 int,
key2 int,
filler char(200),
filler2 char(200),
index(key1),
index(key2)
);
insert into t1 select seq, seq, seq, 'filler-data', 'filler-data-2'
from seq_1000_to_1;
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 -----------------------------
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	10	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
set @tmp_index_merge_ror_cpk=@@optimizer_switch;
set optimizer_switch='extended_keys=off';
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	ref	PRIMARY,key1	key1	4	const	ROWS	Using index condition
set optimizer_switch=@tmp_index_merge_ror_cpk;
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	200	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;
#
# Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB
#
CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1));
INSERT INTO t1 VALUES (2);
CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1),
PRIMARY KEY (f1), KEY (f2), KEY (f3) );
INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, '');
SELECT t1.f1 FROM t1
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
f1
2
EXPLAIN SELECT t1.f1 FROM t1
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	
2	SUBQUERY	t2	index_merge	f2,f3	f3,f2	2,5	NULL	1	Using intersect(f3,f2); Using where; Using index
DROP TABLE t1,t2;
# 
# BUG#56862/640419: Wrong result with sort_union index merge when one
#                   of the merged index scans is the primary key scan
#
CREATE TABLE t0(a int, b int) ENGINE=MyISAM;
CREATE TABLE t1 (
pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
a int,
b int,
INDEX idx(a))
ENGINE=INNODB;
INSERT INTO t0(a,b) VALUES
(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
INSERT INTO t0(a,b) SELECT a+20, b+2000 FROM t0;
INSERT INTO t0(a,b) SELECT a+40, b+4000 FROM t0;
INSERT INTO t0(a,b) SELECT a+80, b+8000 FROM t0;
begin;
INSERT INTO t1(a,b) SELECT t0.a,t0.b FROM t0, seq_1_to_1024;
INSERT INTO t1 VALUES (1000000, 0, 0);
commit;
DROP TABLE t0;
SET SESSION sort_buffer_size = 1024*36;
set @tmp_optimizer_switch=@@optimizer_switch;
set optimizer_switch='derived_merge=off,derived_with_keys=off';
EXPLAIN
SELECT COUNT(*) FROM 
(SELECT * FROM t1 FORCE INDEX(primary,idx)
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	#	
2	DERIVED	t1	index_merge	PRIMARY,idx	idx,PRIMARY	5,4	NULL	#	Using sort_union(idx,PRIMARY); Using where
SELECT COUNT(*) FROM 
(SELECT * FROM t1 FORCE INDEX(primary,idx)
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
COUNT(*)
6145
EXPLAIN
SELECT COUNT(*) FROM 
(SELECT * FROM t1 IGNORE INDEX(idx)
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	#	
2	DERIVED	t1	ALL	PRIMARY	NULL	NULL	NULL	#	Using where
SELECT COUNT(*) FROM 
(SELECT * FROM t1 IGNORE INDEX(idx)
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
COUNT(*)
6145
DROP TABLE t1;
set optimizer_switch=@tmp_optimizer_switch;
#
# Testcase Backport: BUG#48093: 6.0 Server not processing equivalent IN clauses properly
#            with Innodb tables
#
CREATE TABLE t1 (
i int(11) DEFAULT NULL,
v1 varchar(1) DEFAULT NULL,
v2 varchar(20) DEFAULT NULL,
KEY i (i),
KEY v (v1,i)
) ENGINE=innodb;
INSERT INTO t1 VALUES (1,'f','no');
INSERT INTO t1 VALUES (2,'u','yes-u');
INSERT INTO t1 VALUES (2,'h','yes-h');
INSERT INTO t1 VALUES (3,'d','no');

SELECT v2
FROM t1
WHERE v1  IN  ('f', 'd', 'h', 'u' ) AND i  =  2;
v2
yes-u
yes-h

# Should not use index_merge
EXPLAIN
SELECT v2
FROM t1
WHERE v1  IN  ('f', 'd', 'h', 'u' ) AND i  =  2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	i,v	i	5	const	2	Using where
DROP TABLE t1;
#
# BUG#1006164: Multi-table DELETE that uses innodb + index_merge/intersect may fail to delete rows
#
create table t1 (
pk int auto_increment, 
zone_id int,
modified tinyint,
primary key(pk),
key (zone_id),
key (modified)
) engine=innodb;
insert into t1 (zone_id, modified) select 0,0 from seq_1_to_10000;
update t1 set zone_id=487, modified=9 where pk=7259;
update t1 set zone_id=487, modified=9 where pk=7260;
update t1 set zone_id=830, modified=9 where pk=8434;
update t1 set zone_id=830, modified=9 where pk=8435;
update t1 set zone_id=830, modified=9 where pk=8436;
update t1 set zone_id=830, modified=9 where pk=8437;
select * from t1 where t1.zone_id=830 AND modified=9;
pk	zone_id	modified
8434	830	9
8435	830	9
8436	830	9
8437	830	9
begin;
DELETE t1 FROM t1 WHERE t1.zone_id=830 AND modified=9;
commit;
select * from t1 where t1.zone_id=830 AND modified=9;
pk	zone_id	modified
drop table t1;
#
# MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join
#
CREATE TABLE t1 (
a INT, b CHAR(1), c CHAR(1), KEY(a), KEY(b)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (8,'v','v'),(8,'m','m'),(9,'d','d');
SELECT ta.* FROM t1 AS ta, t1 AS tb
WHERE ( tb.b != ta.b OR tb.a = ta.a )
AND ( tb.b = ta.c OR tb.b = ta.b );
a	b	c
8	v	v
8	m	m
9	d	d
DROP TABLE t1;
set optimizer_switch= @optimizer_switch_save;
#
# MDEV-10927: Crash When Using sort_union Optimization
#
set @tmp_optimizer_switch=@@optimizer_switch;
SET optimizer_switch='index_merge_sort_intersection=on';
SET SESSION sort_buffer_size = 1024;
create table t1 (
pk int(11) NOT NULL AUTO_INCREMENT,
col1 int(11) NOT NULL,
col2 int(11) NOT NULL,
col3 int(11) NOT NULL,
key2 int(11) NOT NULL,
col4 int(11) NOT NULL,
key1 int(11) NOT NULL,
PRIMARY KEY (pk),
KEY key1 (key1),
KEY key2 (key2)
) ENGINE=InnoDB AUTO_INCREMENT=12860259 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
insert into t1 (key1, key2, col1,col2,col3,col4)
select seq,seq,seq,seq,seq,seq from seq_1_to_10000;
SELECT sum(col1) FROM t1 FORCE INDEX (key1,key2) WHERE  (key1 between 10 and 8191+10) or (key2= 5);
sum(col1)
33632261
drop table t1;
set optimizer_switch=@tmp_optimizer_switch;
#
# MDEV-22728: SIGFPE in Unique::get_cost_calc_buff_size from prepare_search_best_index_intersect
# on optimized builds
#
SET @save_sort_buffer_size=@@sort_buffer_size;
SET sort_buffer_size=2048;
CREATE TABLE t1 (
a VARCHAR(1024) CHARACTER SET UTF8 PRIMARY KEY,
b INT,
c INT,
INDEX (b)
) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC;
INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_100;
EXPLAIN SELECT * FROM t1 WHERE a='1' OR b < 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	PRIMARY,b	NULL	NULL	NULL	100	Using where
SELECT * FROM t1 WHERE a='1' OR b < 5;
a	b	c
1	1	1
2	2	2
3	3	3
4	4	4
DROP TABLE t1;
SET sort_buffer_size= @save_sort_buffer_size;
disconnect disable_purge;
SET GLOBAL innodb_stats_persistent=@save_stats_persistent;