List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:October 30 2012 9:32am
Subject:bzr push into mysql-trunk branch (jorgen.loland:4851 to 4852) Bug#11754168
View as plain text  
 4852 Jorgen Loland	2012-10-30
      Bug#11754168 - PARTS OF INDEX_MERGE_INNODB.TEST ARE DISABLED 
                     DUE TO EXPLAIN DIFFS
      
      When a huge amount of rows are updated, InnoDB's #records
      estimates go crazy for a while because indexes are full of 
      delete-marked rows. InnoDB does not understand that 
      delete-marked rows are deleted so they are counted. To 
      improve testing, the CREATE TABLE - INSERT sequence is 
      modified to not update rows. The result is that a bunch of 
      tests now use index_merge where table scan used to be 
      performed. 
      
      There are no differences to the tests other than the way the
      tables are created and populated.

    modified:
      mysql-test/include/index_merge1.inc
      mysql-test/r/index_merge_innodb.result
      mysql-test/r/index_merge_myisam.result
 4851 Sergey Glukhov	2012-10-30 [merge]
      null merge from 5.6

=== modified file 'mysql-test/include/index_merge1.inc'
--- a/mysql-test/include/index_merge1.inc	2012-06-28 12:46:30 +0000
+++ b/mysql-test/include/index_merge1.inc	2012-10-30 09:31:31 +0000
@@ -29,31 +29,37 @@ drop table if exists t0, t1, t2, t3, t4;
 create table t0
 (
   key1 int not null,
-  INDEX i1(key1)
+  key2 int not null,
+  key3 int not null,
+  key4 int not null,
+  key5 int not null,
+  key6 int not null,
+  key7 int not null,
+  key8 int not null,
+  INDEX i1(key1),
+  INDEX i2(key2),
+  INDEX i3(key3),
+  INDEX i4(key4),
+  INDEX i5(key5),
+  INDEX i6(key6),
+  INDEX i7(key7),
+  INDEX i8(key8)
 );
 
 --disable_query_log
-insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
+insert into t0 values (1,1,1,1,1,1,1,1023),(2,2,2,2,2,2,2,1022);
 
-let $1=7;
-set @d=8;
+let $1=9;
+set @d=2;
 while ($1)
 {
-  eval insert into t0 select key1+@d from t0;
+  eval insert into t0 select key1+@d, key2+@d, key3+@d, key4+@d, key5+@d,
+                             key6+@d, key7+@d, key8-@d from t0;
   eval set @d=@d*2;
   dec $1;
 }
 --enable_query_log
 
-alter table t0 add key2 int not null, add index i2(key2);
-alter table t0 add key3 int not null, add index i3(key3);
-alter table t0 add key4 int not null, add index i4(key4);
-alter table t0 add key5 int not null, add index i5(key5);
-alter table t0 add key6 int not null, add index i6(key6);
-alter table t0 add key7 int not null, add index i7(key7);
-alter table t0 add key8 int not null, add index i8(key8);
-
-update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1;
 analyze table t0;
 
 # 1. One index

=== modified file 'mysql-test/r/index_merge_innodb.result'
--- a/mysql-test/r/index_merge_innodb.result	2012-10-30 08:00:36 +0000
+++ b/mysql-test/r/index_merge_innodb.result	2012-10-30 09:31:31 +0000
@@ -4,16 +4,22 @@ drop table if exists t0, t1, t2, t3, t4;
 create table t0
 (
 key1 int not null,
-INDEX i1(key1)
+key2 int not null,
+key3 int not null,
+key4 int not null,
+key5 int not null,
+key6 int not null,
+key7 int not null,
+key8 int not null,
+INDEX i1(key1),
+INDEX i2(key2),
+INDEX i3(key3),
+INDEX i4(key4),
+INDEX i5(key5),
+INDEX i6(key6),
+INDEX i7(key7),
+INDEX i8(key8)
 );
-alter table t0 add key2 int not null, add index i2(key2);
-alter table t0 add key3 int not null, add index i3(key3);
-alter table t0 add key4 int not null, add index i4(key4);
-alter table t0 add key5 int not null, add index i5(key5);
-alter table t0 add key6 int not null, add index i6(key6);
-alter table t0 add key7 int not null, add index i7(key7);
-alter table t0 add key8 int not null, add index i8(key8);
-update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1;
 analyze table t0;
 Table	Op	Msg_type	Msg_text
 test.t0	analyze	status	OK
@@ -34,7 +40,7 @@ key1	key2	key3	key4	key5	key6	key7	key8
 1024	1024	1024	1024	1024	1024	1024	0
 explain select * from t0 where key1 < 2 or key2 <3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i2	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t0	index_merge	i1,i2	i1,i2	4,4	NULL	#	Using sort_union(i1,i2); Using where
 explain
 select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -58,7 +64,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t0	ref	i1,i2,i3	i3	4	const	#	Using where
 explain select * from t0 use index (i1,i2) where (key1 < 2 or key2 <3) and key3 = 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i2	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t0	index_merge	i1,i2	i1,i2	4,4	NULL	#	Using sort_union(i1,i2); Using where
 explain select * from t0 where (key1 > 1 or key2  > 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t0	ALL	i1,i2	NULL	NULL	NULL	#	Using where
@@ -69,7 +75,7 @@ explain
 select * from t0 where key1<2 or key2<3 or (key1>5 and key1<7) or
 (key1>10 and key1<12) or (key2>100 and key2<102);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i2	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t0	index_merge	i1,i2	i1,i2	4,4	NULL	#	Using sort_union(i1,i2); Using where
 explain select * from t0 where key2 = 45 or key1 <=> null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t0	range	i1,i2	i2	4	NULL	#	Using where
@@ -88,7 +94,7 @@ id	select_type	table	type	possible_keys	
 explain select key1 from t0 where (key1 <=> null) or (key2 < 2) or
 (key3=10) or (key4 <=> null);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i2,i3,i4	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t0	index_merge	i1,i2,i3,i4	i2,i3	4,4	NULL	#	Using sort_union(i2,i3); Using where
 explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or
 (key3=10) or (key4 <=> null);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -96,11 +102,11 @@ id	select_type	table	type	possible_keys	
 explain select * from t0 where
 (key1 < 2 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 5 or key6 < 5);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i2,i3,i4,i5,i6	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t0	index_merge	i1,i2,i3,i4,i5,i6	i1,i2	4,4	NULL	#	Using sort_union(i1,i2); Using where
 explain
 select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i2,i3	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t0	index_merge	i1,i2,i3	i1,i2	4,4	NULL	#	Using sort_union(i1,i2); Using where
 select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3);
 key1	key2	key3	key4	key5	key6	key7	key8
 1	1	1	1	1	1	1	1023
@@ -109,15 +115,15 @@ key1	key2	key3	key4	key5	key6	key7	key8
 explain select * from t0 where
 (key1 < 3 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 2 or key6 < 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i2,i3,i4,i5,i6	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t0	index_merge	i1,i2,i3,i4,i5,i6	i5,i6	4,4	NULL	#	Using sort_union(i5,i6); Using where
 explain select * from t0 where
 (key1 < 3 or key2 < 3) and (key3 < 70);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i2,i3	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t0	index_merge	i1,i2,i3	i1,i2	4,4	NULL	#	Using sort_union(i1,i2); Using where
 explain select * from t0 where
 (key1 < 3 or key2 < 3) and (key3 < 1000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i2,i3	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t0	index_merge	i1,i2,i3	i1,i2	4,4	NULL	#	Using sort_union(i1,i2); Using where
 explain select * from t0 where
 ((key1 < 3 or key2 < 3) and (key2 <4 or key3 < 3))
 or
@@ -129,7 +135,7 @@ explain select * from t0 where
 or
 key1 < 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i2,i3	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t0	index_merge	i1,i2,i3	i1,i2	4,4	NULL	#	Using sort_union(i1,i2); Using where
 select * from t0 where
 ((key1 < 4 or key2 < 4) and (key2 <4 or key3 < 3))
 or
@@ -144,25 +150,25 @@ explain select * from t0 where
 or
 ((key5 < 3 or key6 < 3) and (key7 <3 or key8 < 3));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i2,i3,i5,i6,i7,i8	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t0	index_merge	i1,i2,i3,i5,i6,i7,i8	i1,i2,i5,i6	4,4,4,4	NULL	#	Using sort_union(i1,i2,i5,i6); Using where
 explain select * from t0 where
 ((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 3))
 or
 ((key7 <5 or key8 < 3) and (key5 < 4 or key6 < 4));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i2,i3,i5,i6,i7,i8	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t0	index_merge	i1,i2,i3,i5,i6,i7,i8	i3,i5,i7,i8	4,4,4,4	NULL	#	Using sort_union(i3,i5,i7,i8); Using where
 explain select * from t0 where
 ((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 4))
 or
 ((key3 <4 or key5 < 2) and (key5 < 5 or key6 < 3));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i2,i3,i5,i6	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t0	index_merge	i1,i2,i3,i5,i6	i3,i5	4,4	NULL	#	Using sort_union(i3,i5); Using where
 explain select * from t0 where
 ((key3 <4 or key5 < 3) and (key1 < 3 or key2 < 3))
 or
 (((key3 <5 and key7 < 5) or key5 < 2) and (key5 < 4 or key6 < 4));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i2,i3,i5,i6,i7	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t0	index_merge	i1,i2,i3,i5,i6,i7	i3,i5	4,4	NULL	#	Using sort_union(i3,i5); Using where
 explain select * from t0 where
 ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
 or
@@ -190,7 +196,7 @@ key1	key2	key3	key4	key5	key6	key7	key8
 explain
 select * from t0 where key1 < 3 or key8 < 2 order by key1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ALL	i1,i8	NULL	NULL	NULL	#	Using where; Using filesort
+1	SIMPLE	t0	index_merge	i1,i8	i1,i8	4,4	NULL	#	Using sort_union(i1,i8); Using where; Using filesort
 create table t2 like t0;
 insert into t2 select * from t0;
 alter table t2 add index i1_3(key1, key3);

=== modified file 'mysql-test/r/index_merge_myisam.result'
--- a/mysql-test/r/index_merge_myisam.result	2012-10-30 08:00:36 +0000
+++ b/mysql-test/r/index_merge_myisam.result	2012-10-30 09:31:31 +0000
@@ -4,16 +4,22 @@ drop table if exists t0, t1, t2, t3, t4;
 create table t0
 (
 key1 int not null,
-INDEX i1(key1)
+key2 int not null,
+key3 int not null,
+key4 int not null,
+key5 int not null,
+key6 int not null,
+key7 int not null,
+key8 int not null,
+INDEX i1(key1),
+INDEX i2(key2),
+INDEX i3(key3),
+INDEX i4(key4),
+INDEX i5(key5),
+INDEX i6(key6),
+INDEX i7(key7),
+INDEX i8(key8)
 );
-alter table t0 add key2 int not null, add index i2(key2);
-alter table t0 add key3 int not null, add index i3(key3);
-alter table t0 add key4 int not null, add index i4(key4);
-alter table t0 add key5 int not null, add index i5(key5);
-alter table t0 add key6 int not null, add index i6(key6);
-alter table t0 add key7 int not null, add index i7(key7);
-alter table t0 add key8 int not null, add index i8(key8);
-update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1;
 analyze table t0;
 Table	Op	Msg_type	Msg_text
 test.t0	analyze	status	OK
@@ -23,7 +29,7 @@ id	select_type	table	type	possible_keys	
 explain
 select * from t0 where key1 < 3 or key2 > 1020;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	index_merge	i1,i2	i1,i2	4,4	NULL	45	Using sort_union(i1,i2); Using where
+1	SIMPLE	t0	index_merge	i1,i2	i1,i2	4,4	NULL	78	Using sort_union(i1,i2); Using where
 select * from t0 where key1 < 3 or key2 > 1020;
 key1	key2	key3	key4	key5	key6	key7	key8
 1	1	1	1	1	1	1	1023
@@ -150,7 +156,7 @@ explain select * from t0 where
 or
 ((key7 <5 or key8 < 3) and (key5 < 4 or key6 < 4));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	index_merge	i1,i2,i3,i5,i6,i7,i8	i3,i5,i7,i8	4,4,4,4	NULL	15	Using sort_union(i3,i5,i7,i8); Using where
+1	SIMPLE	t0	index_merge	i1,i2,i3,i5,i6,i7,i8	i3,i5,i7,i8	4,4,4,4	NULL	16	Using sort_union(i3,i5,i7,i8); Using where
 explain select * from t0 where
 ((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 4))
 or
@@ -190,7 +196,7 @@ key1	key2	key3	key4	key5	key6	key7	key8
 explain
 select * from t0 where key1 < 3 or key8 < 2 order by key1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	index_merge	i1,i8	i1,i8	4,4	NULL	5	Using sort_union(i1,i8); Using where; Using filesort
+1	SIMPLE	t0	index_merge	i1,i8	i1,i8	4,4	NULL	6	Using sort_union(i1,i8); Using where; Using filesort
 create table t2 like t0;
 insert into t2 select * from t0;
 alter table t2 add index i1_3(key1, key3);

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (jorgen.loland:4851 to 4852) Bug#11754168Jorgen Loland16 Nov