summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/index_merge.result62
-rw-r--r--mysql-test/r/index_merge_innodb.result8
-rw-r--r--mysql-test/r/index_merge_ror.result196
-rw-r--r--mysql-test/r/index_merge_ror_cpk.result99
-rw-r--r--mysql-test/r/rowid_order_bdb.result186
-rw-r--r--mysql-test/r/rowid_order_innodb.result186
6 files changed, 702 insertions, 35 deletions
diff --git a/mysql-test/r/index_merge.result b/mysql-test/r/index_merge.result
index da86cc2910e..f3654db030c 100644
--- a/mysql-test/r/index_merge.result
+++ b/mysql-test/r/index_merge.result
@@ -21,7 +21,7 @@ id select_type table type possible_keys key key_len ref rows Extra
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 31 Using where
+1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 31 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
@@ -32,11 +32,11 @@ key1 key2 key3 key4 key5 key6 key7 key8
1024 1024 1024 1024 1024 1024 1024 0
explain select * from t0 where key1 < 3 or key2 <4;
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 7 Using where
+1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 7 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
-1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 9 Using where
+1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 9 Using sort_union(i1,i2); Using where
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
key1 key2 key3 key4 key5 key6 key7 key8
31 31 31 31 31 31 31 993
@@ -56,18 +56,18 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t0 ref i1,i2,i3 i3 4 const 1 Using where
explain select * from t0 use index (i1,i2) where (key1 < 3 or key2 <4) and key3 = 50;
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 7 Using where
+1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 7 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 1024 Using where
explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2);
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 1024 Using where
+1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 1024 Using sort_union(i1,i2); Using where
explain
select * from t0 where key1<3 or key2<3 or (key1>5 and key1<8) or
(key1>10 and key1<12) or (key2>100 and key2<110);
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 17 Using where
+1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 17 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 1 Using where
@@ -79,26 +79,26 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t0 range i1,i2 i2 4 NULL 1 Using where
explain select * from t0 where key2=10 or key3=3 or key4 <=> null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL 2 Using where
+1 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where
explain select * from t0 where key2=10 or key3=3 or key4 is null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL 2 Using where
+1 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where
explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or
(key3=10) or (key4 <=> null);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2,i3,i4 i2,i3 4,4 NULL 6 Using where
+1 SIMPLE t0 index_merge i1,i2,i3,i4 i2,i3 4,4 NULL 6 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
-1 SIMPLE t0 index_merge i1,i3,i4 i1,i3 4,4 NULL 5 Using where
+1 SIMPLE t0 index_merge i1,i3,i4 i1,i3 4,4 NULL 5 Using sort_union(i1,i3); Using where
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL 6 Using where
+1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL 6 Using sort_union(i1,i2); Using where
explain
select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 9 Using where
+1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 9 Using sort_union(i1,i2); Using where
select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
key1 key2 key3 key4 key5 key6 key7 key8
1 1 1 1 1 1 1 1023
@@ -109,7 +109,7 @@ key1 key2 key3 key4 key5 key6 key7 key8
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 2 or key6 < 2);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL 6 Using where
+1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL 6 Using sort_union(i1,i2); Using where
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 100);
id select_type table type possible_keys key key_len ref rows Extra
@@ -129,7 +129,7 @@ explain select * from t0 where
or
key1 < 7;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 10 Using where
+1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
select * from t0 where
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
or
@@ -146,25 +146,25 @@ explain select * from t0 where
or
((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 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 i1,i2,i5,i6 4,4,4,4 NULL 19 Using where
+1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i1,i2,i5,i6 4,4,4,4 NULL 19 Using sort_union(i1,i2,i5,i6); Using where
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key7 <7 or key8 < 4) and (key5 < 5 or key6 < 6));
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 21 Using where
+1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i3,i5,i7,i8 4,4,4,4 NULL 21 Using sort_union(i3,i5,i7,i8); Using where
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 <7 or key5 < 2) and (key5 < 5 or key6 < 6));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 11 Using where
+1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 11 Using sort_union(i3,i5); Using where
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
(((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL 11 Using where
+1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL 11 Using sort_union(i3,i5); Using where
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
@@ -176,7 +176,7 @@ explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 0,4 NULL 1024 Using where
+1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 0,4 NULL 1024 Using sort_union(i3,i5); Using where
select * from t0 where key1 < 5 or key8 < 4 order by key1;
key1 key2 key3 key4 key5 key6 key7 key8
1 1 1 1 1 1 1 1023
@@ -190,7 +190,7 @@ key1 key2 key3 key4 key5 key6 key7 key8
explain
select * from t0 where key1 < 5 or key8 < 4 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 9 Using where; Using filesort
+1 SIMPLE t0 index_merge i1,i8 i1,i8 4,4 NULL 9 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);
@@ -200,7 +200,7 @@ alter table t2 drop index i2;
alter table t2 add index i321(key3, key2, key1);
explain select key3 from t2 where key1 = 100 or key2 = 100;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL 2 Using where
+1 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL 2 Using sort_union(i1_3,i2_3); Using where
explain select key3 from t2 where key1 <100 or key2 < 100;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 index i1_3,i2_3 i321 12 NULL 1024 Using where; Using index
@@ -226,7 +226,7 @@ key1a key1b key2 key2_1 key2_2 key3
4 4 0 4 4 4
explain select * from t4 where key1a = 3 or key1b = 4;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 index_merge i1a,i1b i1a,i1b 4,4 NULL 2 Using where
+1 SIMPLE t4 index_merge i1a,i1b i1a,i1b 4,4 NULL 2 Using sort_union(i1a,i1b); Using where
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 10 Using where
@@ -241,7 +241,7 @@ insert into t1 select * from t0;
explain select * from t0 left join t1 on (t0.key1=t1.key1)
where t0.key1=3 or t0.key2=4;
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 2 Using where
+1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1
select * from t0 left join t1 on (t0.key1=t1.key1)
where t0.key1=3 or t0.key2=4;
@@ -251,13 +251,13 @@ key1 key2 key3 key4 key5 key6 key7 key8 key1 key2 key3 key4 key5 key6 key7 key8
explain
select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4);
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 2 Using where
+1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1
explain
select * from t0,t1 where (t0.key1=t1.key1) and
(t0.key1=3 or t0.key2=4) and t1.key1<200;
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 2 Using where
+1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 Using where
explain
select * from t0,t1 where (t0.key1=t1.key1) and
@@ -269,7 +269,7 @@ explain select * from t0,t1 where t0.key1 = 5 and
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t0 ref i1 i1 4 const 1 Using where
-1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using where
+1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using union(i1,i8); Using where
explain select * from t0,t1 where t0.key1 < 3 and
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
id select_type table type possible_keys key key_len ref rows Extra
@@ -278,13 +278,13 @@ id select_type table type possible_keys key key_len ref rows Extra
explain select * from t1 where key1=3 or key2=4
union select * from t1 where key1<4 or key3=5;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using where
-2 UNION t1 index_merge i1,i3 i1,i3 4,4 NULL 5 Using where
+1 PRIMARY t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
+2 UNION t1 index_merge i1,i3 i1,i3 4,4 NULL 5 Using sort_union(i1,i3); Using where
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
-2 DERIVED t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using where
+2 DERIVED t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
create table t3 like t0;
insert into t3 select * from t0;
alter table t3 add key9 int not null, add index i9(key9);
@@ -297,7 +297,7 @@ key1=1 or key2=2 or key3=3 or key4=4 or
key5=5 or key6=6 or key7=7 or key8=8 or
key9=9 or keyA=10 or keyB=11 or keyC=12;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 index_merge i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC 4,4,4,4,4,4,4,4,4,4,4,4 NULL 12 Using where
+1 SIMPLE t3 index_merge i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC 4,4,4,4,4,4,4,4,4,4,4,4 NULL 12 Using union(i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC); Using where
select * from t3 where
key1=1 or key2=2 or key3=3 or key4=4 or
key5=5 or key6=6 or key7=7 or key8=8 or
@@ -317,7 +317,7 @@ key1 key2 key3 key4 key5 key6 key7 key8 key9 keyA keyB keyC
1016 1016 1016 1016 1016 1016 1016 8 1016 1016 1016 1016
explain select * from t0 where key1 < 3 or key2 < 4;
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 7 Using where
+1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 7 Using sort_union(i1,i2); Using where
select * from t0 where key1 < 3 or key2 < 4;
key1 key2 key3 key4 key5 key6 key7 key8
1 1 1 1 1 1 1 1023
diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result
index 70dd3d4f4f0..afa17c79a6e 100644
--- a/mysql-test/r/index_merge_innodb.result
+++ b/mysql-test/r/index_merge_innodb.result
@@ -8,7 +8,7 @@ INDEX i2(key2)
) engine=innodb;
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 where
+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
@@ -18,7 +18,7 @@ key1 key2
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 where
+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
@@ -34,7 +34,7 @@ update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if
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 where
+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
@@ -44,7 +44,7 @@ key1 key2 str1 zeroval str2 str3
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 where
+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
diff --git a/mysql-test/r/index_merge_ror.result b/mysql-test/r/index_merge_ror.result
new file mode 100644
index 00000000000..15ad1026ca0
--- /dev/null
+++ b/mysql-test/r/index_merge_ror.result
@@ -0,0 +1,196 @@
+drop table if exists t0,t1,t2;
+select count(*) from t1;
+count(*)
+64801
+explain select key1,key2 from t1 where key1=100 and key2=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 3 Using intersect(key1,key2); Using where; Using index
+select key1,key2 from t1 where key1=100 and key2=100;
+key1 key2
+100 100
+explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 8 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+key1 key2 key3 key4 filler1
+100 100 100 100 key1-key2-key3-key4
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
+insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
+explain select key1,key2,filler1 from t1 where key1=100 and key2=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 3 Using intersect(key1,key2); Using where
+select key1,key2,filler1 from t1 where key1=100 and key2=100;
+key1 key2 filler1
+100 100 key1-key2-key3-key4
+100 100 key1-key2
+explain select key1,key2 from t1 where key1=100 and key2=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 3 Using intersect(key1,key2); Using where; Using index
+select key1,key2 from t1 where key1=100 and key2=100;
+key1 key2
+100 100
+100 100
+explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 8 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+key1 key2 key3 key4
+100 100 100 100
+100 100 -1 -1
+-1 -1 100 100
+explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 8 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+key1 key2 key3 key4 filler1
+100 100 100 100 key1-key2-key3-key4
+100 100 -1 -1 key1-key2
+-1 -1 100 100 key4-key3
+explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 1 Using intersect(key1,key2,key3); Using where; Using index
+select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
+key1 key2 key3
+100 100 100
+insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101');
+explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 5 Using union(intersect(key1,key2),key3); Using where
+select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
+key1 key2 key3 key4 filler1
+100 100 100 100 key1-key2-key3-key4
+100 100 -1 -1 key1-key2
+101 101 101 101 key1234-101
+select key1,key2, filler1 from t1 where key1=100 and key2=100;
+key1 key2 filler1
+100 100 key1-key2-key3-key4
+100 100 key1-key2
+update t1 set filler1='to be deleted' where key1=100 and key2=100;
+update t1 set key1=200,key2=200 where key1=100 and key2=100;
+delete from t1 where key1=200 and key2=200;
+select key1,key2,filler1 from t1 where key2=100 and key2=200;
+key1 key2 filler1
+explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 8 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+key1 key2 key3 key4 filler1
+-1 -1 100 100 key4-key3
+delete from t1 where key3=100 and key4=100;
+explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 8 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
+select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
+key1 key2 key3 key4 filler1
+explain select key1,key2 from t1 where key1=100 and key2=100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 3 Using intersect(key1,key2); Using where; Using index
+select key1,key2 from t1 where key1=100 and key2=100;
+key1 key2
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1');
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2');
+insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3');
+explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 16 Using union(key3,intersect(key1,key2),key4); Using where
+select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
+key1 key2 key3 key4 filler1
+100 100 200 200 key1-key2-key3-key4-3
+100 100 200 200 key1-key2-key3-key4-2
+100 100 200 200 key1-key2-key3-key4-1
+insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4');
+explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 18 Using union(key3,intersect(key1,key2),key4); Using where
+select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
+key1 key2 key3 key4 filler1
+100 100 200 200 key1-key2-key3-key4-3
+100 100 200 200 key1-key2-key3-key4-2
+100 100 200 200 key1-key2-key3-key4-1
+-1 -1 -1 200 key4
+insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3');
+explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 20 Using union(key3,intersect(key1,key2),key4); Using where
+select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
+key1 key2 key3 key4 filler1
+100 100 200 200 key1-key2-key3-key4-3
+100 100 200 200 key1-key2-key3-key4-2
+100 100 200 200 key1-key2-key3-key4-1
+-1 -1 -1 200 key4
+-1 -1 200 -1 key3
+explain select * from t1 where st_a=1 and st_b=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_a,st_b 4,4 NULL 2508 Using intersect(st_a,st_b); Using where
+explain select st_a,st_b from t1 where st_a=1 and st_b=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_a,st_b 4,4 NULL 2508 Using intersect(st_a,st_b); Using where; Using index
+explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b st_b 4 const 14720 Using where
+explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt12a 12 const,const,const 958 Using where
+explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1b 8 const,const 3757 Using where
+explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a,stb_swt1a_2b 12,12 NULL 42 Using intersect(sta_swt12a,stb_swt1a_2b); Using where
+explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b)
+where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt12a,stb_swt1b 12,8 NULL 42 Using intersect(sta_swt12a,stb_swt1b); Using where
+explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b)
+where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt1a,sta_swt2a,stb_swt1b 8,8,8 NULL 41 Using intersect(sta_swt1a,sta_swt2a,stb_swt1b); Using where
+explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b)
+where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,st_b sta_swt1a,sta_swt2a,st_b 8,8,4 NULL 159 Using intersect(sta_swt1a,sta_swt2a,st_b); Using where
+explain select * from t1
+where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a,stb_swt1a_2b 12,12 NULL 42 Using intersect(sta_swt12a,stb_swt1a_2b); Using where
+explain select * from t1
+where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 163 Using intersect(sta_swt1a,stb_swt1b); Using where
+explain select st_a from t1
+where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 163 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index
+explain select st_a from t1
+where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 163 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index
+drop table t0,t1;
+create table t2 (
+a char(10),
+b char(10),
+filler1 char(255),
+filler2 char(255),
+key(a(5)),
+key(b(5))
+);
+select count(a) from t2 where a='BBBBBBBB';
+count(a)
+4
+select count(a) from t2 where b='BBBBBBBB';
+count(a)
+4
+explain select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref a,b a 6 const 4 Using where
+select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
+count(a)
+4
+select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA';
+count(a)
+4
+insert into t2 values ('ab', 'ab', 'uh', 'oh');
+explain select a from t2 where a='ab';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref a a 6 const 1 Using where
+drop table t2;
diff --git a/mysql-test/r/index_merge_ror_cpk.result b/mysql-test/r/index_merge_ror_cpk.result
new file mode 100644
index 00000000000..f4bef25045b
--- /dev/null
+++ b/mysql-test/r/index_merge_ror_cpk.result
@@ -0,0 +1,99 @@
+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)
+) engine=innodb;
+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 ref PRIMARY,key1 PRIMARY 4 const 1 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 101 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 38 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
+select ' The following is actually a deficiency, it uses sort_union currently:' as 'note:';
+note:
+ The following is actually a deficiency, it uses sort_union currently:
+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 pktail3bad 4 const 98 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 pktail4bad 4 const 99 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 pktail5bad 4 const 99 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;
diff --git a/mysql-test/r/rowid_order_bdb.result b/mysql-test/r/rowid_order_bdb.result
new file mode 100644
index 00000000000..bbdc6f6ff77
--- /dev/null
+++ b/mysql-test/r/rowid_order_bdb.result
@@ -0,0 +1,186 @@
+drop table if exists t1, t2, t3,t4;
+create table t1 (
+pk1 int not NULL,
+key1 int(11),
+key2 int(11),
+PRIMARY KEY (pk1),
+KEY key1 (key1),
+KEY key2 (key2)
+) engine=bdb;
+insert into t1 values (-5, 1, 1),
+(-100, 1, 1),
+(3, 1, 1),
+(0, 1, 1),
+(10, 1, 1);
+explain select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 5 Using sort_union(key1,key2); Using where
+select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
+pk1 key1 key2
+-100 1 1
+-5 1 1
+0 1 1
+3 1 1
+10 1 1
+drop table t1;
+create table t1 (
+pk1 int unsigned not NULL,
+key1 int(11),
+key2 int(11),
+PRIMARY KEY (pk1),
+KEY key1 (key1),
+KEY key2 (key2)
+) engine=bdb;
+insert into t1 values (0, 1, 1),
+(0xFFFFFFFF, 1, 1),
+(0xFFFFFFFE, 1, 1),
+(1, 1, 1),
+(2, 1, 1);
+select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
+pk1 key1 key2
+0 1 1
+1 1 1
+2 1 1
+4294967294 1 1
+4294967295 1 1
+drop table t1;
+create table t1 (
+pk1 char(4) not NULL,
+key1 int(11),
+key2 int(11),
+PRIMARY KEY (pk1),
+KEY key1 (key1),
+KEY key2 (key2)
+) engine=bdb collate latin2_general_ci;
+insert into t1 values ('a1', 1, 1),
+('b2', 1, 1),
+('A3', 1, 1),
+('B4', 1, 1);
+select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
+pk1 key1 key2
+a1 1 1
+A3 1 1
+b2 1 1
+B4 1 1
+drop table t1;
+create table t1 (
+pk1 int not NULL,
+pk2 char(4) not NULL collate latin1_german1_ci,
+pk3 char(4) not NULL collate latin1_bin,
+key1 int(11),
+key2 int(11),
+PRIMARY KEY (pk1,pk2,pk3),
+KEY key1 (key1),
+KEY key2 (key2)
+) engine=bdb;
+insert into t1 values
+(1, 'u', 'u', 1, 1),
+(1, 'u', char(0xEC), 1, 1),
+(1, 'u', 'x', 1, 1);
+insert ignore into t1 select pk1, char(0xEC), pk3, key1, key2 from t1;
+insert ignore into t1 select pk1, 'x', pk3, key1, key2 from t1 where pk2='u';
+insert ignore into t1 select 2, pk2, pk3, key1, key2 from t1;
+select * from t1;
+pk1 pk2 pk3 key1 key2
+1 ì u 1 1
+1 ì x 1 1
+1 ì ì 1 1
+1 u u 1 1
+1 u x 1 1
+1 u ì 1 1
+1 x u 1 1
+1 x x 1 1
+1 x ì 1 1
+2 ì u 1 1
+2 ì x 1 1
+2 ì ì 1 1
+2 u u 1 1
+2 u x 1 1
+2 u ì 1 1
+2 x u 1 1
+2 x x 1 1
+2 x ì 1 1
+select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
+pk1 pk2 pk3 key1 key2
+1 ì u 1 1
+1 ì x 1 1
+1 ì ì 1 1
+1 u u 1 1
+1 u x 1 1
+1 u ì 1 1
+1 x u 1 1
+1 x x 1 1
+1 x ì 1 1
+2 ì u 1 1
+2 ì x 1 1
+2 ì ì 1 1
+2 u u 1 1
+2 u x 1 1
+2 u ì 1 1
+2 x u 1 1
+2 x x 1 1
+2 x ì 1 1
+alter table t1 drop primary key;
+select * from t1;
+pk1 pk2 pk3 key1 key2
+1 ì u 1 1
+1 ì x 1 1
+1 ì ì 1 1
+1 u u 1 1
+1 u x 1 1
+1 u ì 1 1
+1 x u 1 1
+1 x x 1 1
+1 x ì 1 1
+2 ì u 1 1
+2 ì x 1 1
+2 ì ì 1 1
+2 u u 1 1
+2 u x 1 1
+2 u ì 1 1
+2 x u 1 1
+2 x x 1 1
+2 x ì 1 1
+select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
+pk1 pk2 pk3 key1 key2
+1 ì u 1 1
+1 ì x 1 1
+1 ì ì 1 1
+1 u u 1 1
+1 u x 1 1
+1 u ì 1 1
+1 x u 1 1
+1 x x 1 1
+1 x ì 1 1
+2 ì u 1 1
+2 ì x 1 1
+2 ì ì 1 1
+2 u u 1 1
+2 u x 1 1
+2 u ì 1 1
+2 x u 1 1
+2 x x 1 1
+2 x ì 1 1
+drop table t1;
+create table t1 (
+pk1 varchar(8) NOT NULL default '',
+pk2 varchar(4) NOT NULL default '',
+key1 int(11),
+key2 int(11),
+primary key(pk1, pk2),
+KEY key1 (key1),
+KEY key2 (key2)
+) engine=bdb;
+insert into t1 values ('','empt',2,2),
+('a','a--a',2,2),
+('bb','b--b',2,2),
+('ccc','c--c',2,2),
+('dddd','d--d',2,2);
+select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
+pk1 pk2 key1 key2
+ empt 2 2
+a a--a 2 2
+bb b--b 2 2
+ccc c--c 2 2
+dddd d--d 2 2
+drop table t1;
diff --git a/mysql-test/r/rowid_order_innodb.result b/mysql-test/r/rowid_order_innodb.result
new file mode 100644
index 00000000000..c56eb1a5cde
--- /dev/null
+++ b/mysql-test/r/rowid_order_innodb.result
@@ -0,0 +1,186 @@
+drop table if exists t1, t2, t3,t4;
+create table t1 (
+pk1 int not NULL,
+key1 int(11),
+key2 int(11),
+PRIMARY KEY (pk1),
+KEY key1 (key1),
+KEY key2 (key2)
+) engine=innodb;
+insert into t1 values (-5, 1, 1),
+(-100, 1, 1),
+(3, 1, 1),
+(0, 1, 1),
+(10, 1, 1);
+explain select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 6 Using sort_union(key1,key2); Using where
+select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
+pk1 key1 key2
+-100 1 1
+-5 1 1
+0 1 1
+3 1 1
+10 1 1
+drop table t1;
+create table t1 (
+pk1 int unsigned not NULL,
+key1 int(11),
+key2 int(11),
+PRIMARY KEY (pk1),
+KEY key1 (key1),
+KEY key2 (key2)
+) engine=innodb;
+insert into t1 values (0, 1, 1),
+(0xFFFFFFFF, 1, 1),
+(0xFFFFFFFE, 1, 1),
+(1, 1, 1),
+(2, 1, 1);
+select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
+pk1 key1 key2
+0 1 1
+1 1 1
+2 1 1
+4294967294 1 1
+4294967295 1 1
+drop table t1;
+create table t1 (
+pk1 char(4) not NULL,
+key1 int(11),
+key2 int(11),
+PRIMARY KEY (pk1),
+KEY key1 (key1),
+KEY key2 (key2)
+) engine=innodb collate latin2_general_ci;
+insert into t1 values ('a1', 1, 1),
+('b2', 1, 1),
+('A3', 1, 1),
+('B4', 1, 1);
+select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
+pk1 key1 key2
+a1 1 1
+A3 1 1
+b2 1 1
+B4 1 1
+drop table t1;
+create table t1 (
+pk1 int not NULL,
+pk2 char(4) not NULL collate latin1_german1_ci,
+pk3 char(4) not NULL collate latin1_bin,
+key1 int(11),
+key2 int(11),
+PRIMARY KEY (pk1,pk2,pk3),
+KEY key1 (key1),
+KEY key2 (key2)
+) engine=innodb;
+insert into t1 values
+(1, 'u', 'u', 1, 1),
+(1, 'u', char(0xEC), 1, 1),
+(1, 'u', 'x', 1, 1);
+insert ignore into t1 select pk1, char(0xEC), pk3, key1, key2 from t1;
+insert ignore into t1 select pk1, 'x', pk3, key1, key2 from t1 where pk2='u';
+insert ignore into t1 select 2, pk2, pk3, key1, key2 from t1;
+select * from t1;
+pk1 pk2 pk3 key1 key2
+1 ì u 1 1
+1 ì x 1 1
+1 ì ì 1 1
+1 u u 1 1
+1 u x 1 1
+1 u ì 1 1
+1 x u 1 1
+1 x x 1 1
+1 x ì 1 1
+2 ì u 1 1
+2 ì x 1 1
+2 ì ì 1 1
+2 u u 1 1
+2 u x 1 1
+2 u ì 1 1
+2 x u 1 1
+2 x x 1 1
+2 x ì 1 1
+select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
+pk1 pk2 pk3 key1 key2
+1 ì u 1 1
+1 ì x 1 1
+1 ì ì 1 1
+1 u u 1 1
+1 u x 1 1
+1 u ì 1 1
+1 x u 1 1
+1 x x 1 1
+1 x ì 1 1
+2 ì u 1 1
+2 ì x 1 1
+2 ì ì 1 1
+2 u u 1 1
+2 u x 1 1
+2 u ì 1 1
+2 x u 1 1
+2 x x 1 1
+2 x ì 1 1
+alter table t1 drop primary key;
+select * from t1;
+pk1 pk2 pk3 key1 key2
+1 ì u 1 1
+1 ì x 1 1
+1 ì ì 1 1
+1 u u 1 1
+1 u x 1 1
+1 u ì 1 1
+1 x u 1 1
+1 x x 1 1
+1 x ì 1 1
+2 ì u 1 1
+2 ì x 1 1
+2 ì ì 1 1
+2 u u 1 1
+2 u x 1 1
+2 u ì 1 1
+2 x u 1 1
+2 x x 1 1
+2 x ì 1 1
+select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
+pk1 pk2 pk3 key1 key2
+1 ì u 1 1
+1 ì x 1 1
+1 ì ì 1 1
+1 u u 1 1
+1 u x 1 1
+1 u ì 1 1
+1 x u 1 1
+1 x x 1 1
+1 x ì 1 1
+2 ì u 1 1
+2 ì x 1 1
+2 ì ì 1 1
+2 u u 1 1
+2 u x 1 1
+2 u ì 1 1
+2 x u 1 1
+2 x x 1 1
+2 x ì 1 1
+drop table t1;
+create table t1 (
+pk1 varchar(8) NOT NULL default '',
+pk2 varchar(4) NOT NULL default '',
+key1 int(11),
+key2 int(11),
+primary key(pk1, pk2),
+KEY key1 (key1),
+KEY key2 (key2)
+) engine=innodb;
+insert into t1 values ('','empt',2,2),
+('a','a--a',2,2),
+('bb','b--b',2,2),
+('ccc','c--c',2,2),
+('dddd','d--d',2,2);
+select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
+pk1 pk2 key1 key2
+ empt 2 2
+a a--a 2 2
+bb b--b 2 2
+ccc c--c 2 2
+dddd d--d 2 2
+drop table t1;
View Lorry Controller Status