Merge from mysql-trunk-bugfixing.

1 files changed, 0 insertions, 326 deletions

diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result
index 71b81d6acd2..6d130a64e35 100644
--- a/mysql-test/r/index_merge_innodb.result
+++ b/mysql-test/r/index_merge_innodb.result
@@ -1,329 +1,3 @@
-#---------------- 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	#	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;
-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
-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) 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;