List:Commits« Previous MessageNext Message »
From:Sneha MOdi Date:November 2 2011 11:32am
Subject:bzr push into mysql-trunk branch (sneha.modi:3544 to 3545)
View as plain text  
 3545 Sneha MOdi	2011-11-02 [merge]
      Merging from mysql-5.5 -> mysql-trunk

    modified:
      mysql-test/collections/default.experimental
      mysql-test/include/index_merge1.inc
      mysql-test/include/index_merge2.inc
      mysql-test/include/index_merge_ror.inc
      mysql-test/r/index_merge_innodb.result
      mysql-test/t/index_merge_innodb.test
 3544 Norvald H. Ryeng	2011-11-01
      Bug #13098726 MY_TIME.C:786: CALC_DAYNR: ASSERTION `DELSUM+(INT)
      Y/4-TEMP > 0' FAILED
      
      Problem: The date 0000-01-00 triggers an assertion guarding against
      invalid results in calc_daynr().
      
      An initial check in calc_daynr() returns day number 0 for all dates in
      month 0, year 0. The assertion at the end of the function states that
      other return values should be greater than 0. However, because of the
      way day numbers are calculated, the fictitious date 0000-01-00 is also
      represented as day number 0, and this wrongly triggers the assertion.
      
      In most cases, the NO_ZERO_IN_DATE SQL mode causes the problematic
      date to be handled outside calc_daynr(), but not all time functions
      check for such fictitious dates.
      
      Fix: Change assertion so that it states that return values should be
      greater than or equal to zero.
     @ mysql-test/r/func_time.result
        Add test for bug#13098726.
     @ mysql-test/t/func_time.test
        Add test for bug#13098726.
     @ sql-common/my_time.c
        Change assert to allow full range of valid return values.

    modified:
      mysql-test/r/func_time.result
      mysql-test/t/func_time.test
      sql-common/my_time.c
=== modified file 'mysql-test/collections/default.experimental'
--- a/mysql-test/collections/default.experimental	2011-10-07 08:36:04 +0000
+++ b/mysql-test/collections/default.experimental	2011-11-02 11:27:07 +0000
@@ -6,6 +6,7 @@ binlog.binlog_multi_engine
 funcs_1.charset_collation_1              # depends on compile-time decisions
 
 main.func_math @freebsd                  # Bug#11751977 2010-05-04 alik main.func_math fails on FreeBSD in PB2
+main.index_merge_innodb                  #Bug 11754168 - 45727: Disabled parts of index_merge_innodb due to explain diffs have been enabled 
 main.lock_multi_bug38499                 # Bug#11755645 2009-09-19 alik main.lock_multi_bug38499 times out sporadically
 main.mysqlslap @windows                  # Bug#11761520 2010-08-10 alik mysqlslap fails sporadically starting from Dahlia
 main.signal_demo3 @solaris               # Bug#11753919 2010-01-20 alik Several test cases fail on Solaris with error Thread stack overrun

=== modified file 'mysql-test/include/index_merge1.inc'
--- a/mysql-test/include/index_merge1.inc	2010-10-25 09:20:53 +0000
+++ b/mysql-test/include/index_merge1.inc	2011-11-02 11:27:07 +0000
@@ -11,6 +11,7 @@
 # Note: The comments/expectations refer to MyISAM.
 #       They might be not valid for other storage engines.
 #
+
 # Last update:
 # 2006-08-02 ML test refactored
 #               old name was t/index_merge.test
@@ -57,82 +58,129 @@ update t0 set key2=key1,key3=key1,key4=k
 analyze table t0;
 
 # 1. One index
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where key1 < 3 or key1 > 1020;
 
 # 2. Simple cases
+--sorted_result
+--replace_column 9 #
 explain
 select * from t0 where key1 < 3 or key2 > 1020;
 select * from t0 where key1 < 3 or key2 > 1020;
 
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where key1 < 3 or key2 <4;
 
+--sorted_result
+--replace_column 9 #
 explain
-select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
+select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40) ;
 # Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated
 select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
 
 # 3. Check that index_merge doesn't break "ignore/force/use index"
+--sorted_result
+--replace_column 9 #
 explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4;
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50;
+--sorted_result
+--replace_column 9 #
 explain select * from t0 use index (i1,i2) where (key1 < 3 or key2 <4) and key3 = 50;
 
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where (key1 > 1 or key2  > 2);
+--sorted_result
+--replace_column 9 #
 explain select * from t0 force index (i1,i2) where (key1 > 1 or key2  > 2);
 
 
 # 4. Check if conjuncts are grouped by keyuse
+--sorted_result
+--replace_column 9 #
 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);
 
 # 5. Check index_merge with conjuncts that are always true/false
 #    verify fallback to "range" if there is only one non-confluent condition
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where key2 = 45 or key1 <=> null;
 
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where key2 = 45 or key1 is not null;
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where key2 = 45 or key1 is null;
 
 #   the last conj. is always false and will be discarded
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where key2=10 or key3=3 or key4 <=> null;
 
 #   the last conj. is always true and will cause 'all' scan
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where key2=10 or key3=3 or key4 is null;
 
 #   some more complicated cases
+--sorted_result
+--replace_column 9 #
 explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or
                                    (key3=10) or (key4 <=> null);
+--sorted_result
+--replace_column 9 #
 explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or
                                    (key3=10) or (key4 <=> null);
 
 # 6.Several ways to do index_merge, (ignored) index_merge vs. range
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where
   (key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5);
 
+--sorted_result
+--replace_column 9 #
 explain
 select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
 
 select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
 
-
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where
   (key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 2 or key6 < 2);
 
 #   now index_merge is not used at all when "range" is possible
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where
   (key1 < 3 or key2 < 3) and (key3 < 100);
 
 #   this even can cause "all" scan:
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where
   (key1 < 3 or key2 < 3) and (key3 < 1000);
 
 
 # 7. Complex cases
 #   tree_or(List<SEL_IMERGE>, range SEL_TREE).
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where
     ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
   or
     key2 > 5;
 
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where
     ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
   or
@@ -144,31 +192,43 @@ select * from t0 where
     key1 < 7;
 
 #   tree_or(List<SEL_IMERGE>, List<SEL_IMERGE>).
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where
     ((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4))
   or
     ((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4));
 
+--sorted_result
+--replace_column 9 #
 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));
 
+--sorted_result
+--replace_column 9 #
 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));
 
+--sorted_result
+--replace_column 9 #
 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));
 
+--sorted_result
+--replace_column 9 #
 explain select * from t0 where
     ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
   or
     ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
 
+--sorted_result
+--replace_column 9 #
 explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
     ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
   or
@@ -177,6 +237,8 @@ explain select * from t0 force index(i1,
 # 8. Verify that "order by" after index merge uses filesort
 select * from t0 where key1 < 5 or key8 < 4 order by key1;
 
+--sorted_result
+--replace_column 9 #
 explain
 select * from t0 where key1 < 5 or key8 < 4 order by key1;
 
@@ -191,12 +253,18 @@ alter table t2 drop index i2;
 alter table t2 add index i321(key3, key2, key1);
 
 #   index_merge vs 'index', index_merge is better.
+--sorted_result
+--replace_column 9 #
 explain select key3 from t2 where key1 = 100 or key2 = 100;
 
 #   index_merge vs 'index', 'index' is better.
+--sorted_result
+--replace_column 9 #
 explain select key3 from t2 where key1 <100 or key2 < 100;
 
 #   index_merge vs 'all', index_merge is better.
+--sorted_result
+--replace_column 9 #
 explain select key7 from t2 where key1 <100 or key2 < 100;
 
 # 10. Multipart keys.
@@ -217,13 +285,21 @@ insert into t4 select key1,key1,key1 div
 
 #   the following will be handled by index_merge:
 select * from t4 where key1a = 3 or key1b = 4;
+--sorted_result
+--replace_column 9 #
 explain select * from t4 where key1a = 3 or key1b = 4;
 
 #   and the following will not
+--sorted_result
+--replace_column 9 #
 explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5);
 
+--sorted_result
+--replace_column 9 #
 explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5);
 
+--sorted_result
+--replace_column 9 #
 explain select * from t4 where key2_1 = 1 or key2_2 = 5;
 
 
@@ -232,38 +308,54 @@ create table t1 like t0;
 insert into t1 select * from t0;
 
 #  index_merge on first table in join
+--sorted_result
+--replace_column 9 #
 explain select * from t0 left join t1 on (t0.key1=t1.key1)
   where t0.key1=3 or t0.key2=4;
 
 select * from t0 left join t1 on (t0.key1=t1.key1)
   where t0.key1=3 or t0.key2=4;
 
+--sorted_result
+--replace_column 9 #
 explain
 select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4);
 
 #  index_merge vs. ref
+--sorted_result
+--replace_column 8 test.t0.key1 4 # 6 # 9 # 7 # 10 #
 explain
 select * from t0,t1 where (t0.key1=t1.key1) and
   (t0.key1=3 or t0.key2=4) and t1.key1<200;
 
 #  index_merge vs. ref
+--sorted_result
+--replace_column 8 test.t0.key1 9 #
 explain
 select * from t0,t1 where (t0.key1=t1.key1) and
   (t0.key1=3 or t0.key2<4) and t1.key1=2;
 
 #  index_merge on second table in join
+--sorted_result
+--replace_column 8 test.t0.key1 9 #
 explain select * from t0,t1 where t0.key1 = 5 and
   (t1.key1 = t0.key1 or t1.key8 = t0.key1);
 
 # Fix for bug#1974
+--sorted_result
+--replace_column 8 test.t0.key1 9 #
 explain select * from t0,t1 where t0.key1 < 3 and
   (t1.key1 = t0.key1 or t1.key8 = t0.key1);
 
 #  index_merge inside union
+--sorted_result
+--replace_column 9 #
 explain select * from t1 where key1=3 or key2=4
   union select * from t1 where key1<4 or key3=5;
 
 #  index merge in subselect
+--sorted_result
+--replace_column 9 #
 explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5;
 
 # 12. check for long index_merges.
@@ -275,6 +367,8 @@ alter table t3 add keyB int not null, ad
 alter table t3 add keyC int not null, add index iC(keyC);
 update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1;
 
+--sorted_result
+--replace_column 9 #
 explain 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
@@ -286,6 +380,8 @@ select * from t3 where
   key9=9 or keyA=10 or keyB=11 or keyC=12;
 
 # Test for Bug#3183
+--sorted_result
+--replace_column 9 # 4 # 6 # 7 # 10 #
 explain select * from t0 where key1 < 3 or key2 < 4;
 # Bug#21277: InnoDB, wrong result set, index_merge strategy, second index not evaluated
 select * from t0 where key1 < 3 or key2 < 4;
@@ -304,6 +400,8 @@ create table t4 (a int);
 insert into t4 values (1),(4),(3);
 set @save_join_buffer_size=@@join_buffer_size;
 set join_buffer_size= 4096;
+--sorted_result
+--replace_column 9 # 
 explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
  from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
   where (A.key1 < 500000 or A.key2 < 3)
@@ -315,6 +413,8 @@ select max(A.key1 + B.key1 + A.key2 + B.
   and   (B.key1 < 500000 or B.key2 < 3);
 
 update t0 set key1=1;
+--sorted_result
+--replace_column 9 #
 explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
  from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
   where (A.key1 = 1 or A.key2 = 1)
@@ -331,8 +431,11 @@ update t0 set key2=1, key3=1, key4=1, ke
 # The next query will not use index i7 in intersection if the OS doesn't
 # support file sizes > 2GB. (ha_myisam::ref_length depends on this and index
 # scan cost estimates depend on ha_myisam::ref_length)
+--sorted_result
 --replace_column 9 #
 --replace_result "4,4,4,4,4,4,4" X "4,4,4,4,4,4" X "i6,i7" "i6,i7?" "i6" "i6,i7?"
+--sorted_result
+--replace_column 9 # 4 # 6 # 7 # 10 #
 explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
  from t0 as A, t0 as B
  where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
@@ -374,7 +477,9 @@ while ($1)
 
 OPTIMIZE TABLE t1;
 select count(*) from t1;
+--replace_column 9 # 
 explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
+--replace_column 9 # 
 explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
 drop table t1;
 
@@ -399,9 +504,11 @@ create table t3 (
   key(a),key(b)
 ) engine=merge union=(t1,t2);
 
---replace_column 9 #
+--sorted_result
+--replace_column 9 # 
 explain select * from t1 where a=1 and b=1;
---replace_column 9 #
+--sorted_result
+--replace_column 9 # 
 explain select * from t3 where a=1 and b=1;
 
 drop table t3;
@@ -472,6 +579,7 @@ create table t2(
 insert into t2 select * from t1;
 
 --echo must use sort-union rather than union:
+--sorted_result
 --replace_column 9 #
 explain select * from t1 where a=4 or b=4;
 --sorted_result
@@ -480,6 +588,7 @@ select * from t1 where a=4 or b=4;
 select * from t1 ignore index(a,b) where a=4 or b=4;
 
 --echo must use union, not sort-union:
+--sorted_result
 --replace_column 9 #
 explain select * from t2 where a=4 or b=4;
 --sorted_result
@@ -517,6 +626,8 @@ insert into t3 select 1000, 1000,'filler
 insert into t3 values (1,1,'data');
 insert into t3 values (1,1,'data');
 -- echo The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3)
+--sorted_result
+--replace_column 9 #
 explain select * from t1 
 where exists (select 1 from t2, t3 
               where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
@@ -540,7 +651,9 @@ INSERT INTO t1 SELECT * FROM t1;
 INSERT INTO t1 SELECT * FROM t1;
 INSERT INTO t1 SELECT * FROM t1;
 SET SESSION sort_buffer_size=1;
-EXPLAIN 
+--sorted_result
+--replace_column 9 #
+explain 
 SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' 
   ORDER BY a,b;
 # we don't actually care about the result : we're checking if it crashes

=== modified file 'mysql-test/include/index_merge2.inc'
--- a/mysql-test/include/index_merge2.inc	2010-10-26 09:10:59 +0000
+++ b/mysql-test/include/index_merge2.inc	2011-11-02 11:27:07 +0000
@@ -124,6 +124,7 @@ select count(*) from t1;
 
 if ($index_merge_random_rows_in_EXPLAIN)
 {
+  --sorted_result
   --replace_column 9 #
 }
 explain select count(*) from t1 where
@@ -134,6 +135,7 @@ select count(*) from t1 where
 
 if ($index_merge_random_rows_in_EXPLAIN)
 {
+  --sorted_result
   --replace_column 9 #
 }
 explain select count(*) from t1 where
@@ -324,32 +326,32 @@ SELECT COUNT(*) FROM t1 WHERE b = 0 AND
 drop table t1;
 
 # BUG#21277: Index Merge/sort_union: wrong query results
-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);
-
-let $1=7;
-set @d=8;
-while ($1)
-{
-  eval insert into t1 (key1) select key1+@d from t1;
-  eval set @d=@d*2;
-  dec $1;
-}
-
-alter table t1 add index i2(key2);
-alter table t1 add index i3(key3);
-update t1 set key2=key1,key3=key1;
+#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);
+
+#let $1=7;
+#set @d=8;
+#while ($1)
+#{
+#  eval insert into t1 (key1) select key1+@d from t1;
+#  eval set @d=@d*2;
+#  dec $1;
+#}
+
+#alter table t1 add index i2(key2);
+#alter table t1 add index i3(key3);
+#update t1 set key2=key1,key3=key1;
 
 # to test the bug, the following must use "sort_union":
-explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
-select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
-drop table t1;
+#explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
+#select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
+#drop table t1;
 
 --echo #
 --echo # Bug#56423: Different count with SELECT and CREATE SELECT queries

=== modified file 'mysql-test/include/index_merge_ror.inc'
--- a/mysql-test/include/index_merge_ror.inc	2010-10-25 09:20:53 +0000
+++ b/mysql-test/include/index_merge_ror.inc	2011-11-02 11:27:07 +0000
@@ -118,8 +118,12 @@ alter table t1 enable keys;
 select count(*) from t1;
 
 # One row results tests for cases where a single row matches all conditions
+--sorted_result
+--replace_column 9 #
 explain select key1,key2 from t1 where key1=100 and key2=100;
 select key1,key2 from t1 where key1=100 and key2=100;
+--sorted_result
+--replace_column 9 #
 explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
 select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
 
@@ -128,25 +132,35 @@ insert into t1 (key1, key2, key3, key4,
 insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
 
 #  ROR-intersection, not covering
+--sorted_result
+--replace_column 9 #
 explain select key1,key2,filler1 from t1 where key1=100 and key2=100;
 select key1,key2,filler1 from t1 where key1=100 and key2=100;
 
 #  ROR-intersection, covering
+--sorted_result
+--replace_column 9 #
 explain select key1,key2 from t1 where key1=100 and key2=100;
 select key1,key2 from t1 where key1=100 and key2=100;
 
 #  ROR-union of ROR-intersections
+--sorted_result
+--replace_column 9 #
 explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
 select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or 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;
 select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
 
 #  3-way ROR-intersection
+--sorted_result
+--replace_column 9 #
 explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
 select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
 
 #  ROR-union(ROR-intersection, ROR-range)
 insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101');
+--sorted_result
+--replace_column 9 #
 explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
 select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
 
@@ -159,16 +173,22 @@ select key1,key2,filler1 from t1 where k
 
 # ROR-union(ROR-intersection) with one of ROR-intersection giving empty
 # results
+--sorted_result
+--replace_column 9 #
 explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
 select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
 
 delete from t1 where key3=100 and key4=100;
 
 # ROR-union with all ROR-intersections giving empty results
+--sorted_result
+--replace_column 9 #
 explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
 select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
 
 # ROR-intersection with empty result
+--sorted_result
+--replace_column 9 #
 explain select key1,key2 from t1 where key1=100 and key2=100;
 select key1,key2 from t1 where key1=100 and key2=100;
 
@@ -178,16 +198,22 @@ insert into t1 (key1, key2, key3, key4,
 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');
 
+--sorted_result
+--replace_column 9 #
 explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
 select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
 
 insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4');
 
+--sorted_result
+--replace_column 9 #
 explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
 select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
 
 insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3');
 
+--sorted_result
+--replace_column 9 #
 explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
 select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
 
@@ -196,10 +222,16 @@ select key1,key2,key3,key4,filler1 from
 ##
 
 # Check that the shortest key is used for ROR-intersection, covering and non-covering.
+--sorted_result
+--replace_column 9 # 6 #
 explain select * from t1 where st_a=1 and st_b=1;
+--sorted_result
+--replace_column 9 #
 explain select st_a,st_b from t1 where st_a=1 and st_b=1;
 
 # Check if "ingore index" syntax works
+--sorted_result
+--replace_column 9 # 6 #
 explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;
 
 # Do many tests
@@ -208,30 +240,49 @@ explain select st_a from t1 ignore index
 
 # Different value on 32 and 64 bit
 --replace_result sta_swt12a sta_swt21a sta_swt12a, sta_swt12a,
+--replace_column 9 #
 explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;
 
+--sorted_result
+--replace_column 9 #
 explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1;
 
+--sorted_result
+--replace_column 9 #
 explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
 
+--sorted_result
+--replace_column 9 #
 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;
 
+--sorted_result
+--replace_column 9 #
 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;
 
+--sorted_result
+--replace_column 9 #
 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;
 
+--sorted_result
+--replace_column 9 #
 explain select * from t1
   where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;
 
+--sorted_result
+--replace_column 9 #
 explain select * from t1
   where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
 
+--sorted_result
+--replace_column 9 #
 explain select st_a from t1
   where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
 
+--sorted_result
+--replace_column 9 #
 explain select st_a from t1
   where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
 
@@ -270,6 +321,7 @@ select count(a) from t2 where a='AAAAAAA
 select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA';
 
 insert into t2 values ('ab', 'ab', 'uh', 'oh');
+--replace_column 9 #
 explain select a from t2 where a='ab';
 drop table t2;
 

=== modified file 'mysql-test/r/index_merge_innodb.result'
--- a/mysql-test/r/index_merge_innodb.result	2011-10-27 08:40:40 +0000
+++ b/mysql-test/r/index_merge_innodb.result	2011-11-02 11:27:07 +0000
@@ -1,3 +1,1159 @@
+#---------------- Index merge test 1 -------------------------------------------
+SET SESSION STORAGE_ENGINE = InnoDB;
+drop table if exists t0, t1, t2, t3, t4;
+create table t0
+(
+key1 int not null,
+INDEX i1(key1)
+);
+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
+explain select * from t0 where key1 < 3 or key1 > 1020;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	range	i1	i1	4	NULL	#	Using where
+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	#	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
+2	2	2	2	2	2	2	1022
+1021	1021	1021	1021	1021	1021	1021	3
+1022	1022	1022	1022	1022	1022	1022	2
+1023	1023	1023	1023	1023	1023	1023	1
+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	ALL	i1,i2	NULL	NULL	NULL	#	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	#	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
+32	32	32	32	32	32	32	992
+33	33	33	33	33	33	33	991
+34	34	34	34	34	34	34	990
+35	35	35	35	35	35	35	989
+36	36	36	36	36	36	36	988
+37	37	37	37	37	37	37	987
+38	38	38	38	38	38	38	986
+39	39	39	39	39	39	39	985
+explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	i1	NULL	NULL	NULL	#	Using where
+explain select * from t0 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	ref	i1,i2,i3	i3	4	const	#	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	ALL	i1,i2	NULL	NULL	NULL	#	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
+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	#	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	ALL	i1,i2	NULL	NULL	NULL	#	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
+explain select * from t0 where key2 = 45 or key1 is not null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	i1,i2	NULL	NULL	NULL	#	Using where
+explain select * from t0 where key2 = 45 or key1 is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ref	i2	i2	4	const	#	
+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	#	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	i2,i3	4,4	NULL	#	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	ALL	i1,i2,i3,i4	NULL	NULL	NULL	#	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	#	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	ALL	i1,i2,i3,i4,i5,i6	NULL	NULL	NULL	#	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	ALL	i1,i2,i3	NULL	NULL	NULL	#	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
+2	2	2	2	2	2	2	1022
+3	3	3	3	3	3	3	1021
+4	4	4	4	4	4	4	1020
+5	5	5	5	5	5	5	1019
+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	ALL	i1,i2,i3,i4,i5,i6	NULL	NULL	NULL	#	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
+1	SIMPLE	t0	ALL	i1,i2,i3	NULL	NULL	NULL	#	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
+explain select * from t0 where
+((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
+or
+key2 > 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
+explain select * from t0 where
+((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
+or
+key1 < 7;
+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
+select * from t0 where
+((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
+or
+key1 < 7;
+key1	key2	key3	key4	key5	key6	key7	key8
+1	1	1	1	1	1	1	1023
+2	2	2	2	2	2	2	1022
+3	3	3	3	3	3	3	1021
+4	4	4	4	4	4	4	1020
+5	5	5	5	5	5	5	1019
+6	6	6	6	6	6	6	1018
+explain select * from t0 where
+((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4))
+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	ALL	i1,i2,i3,i5,i6,i7,i8	NULL	NULL	NULL	#	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	ALL	i1,i2,i3,i5,i6,i7,i8	NULL	NULL	NULL	#	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	ALL	i1,i2,i3,i5,i6	NULL	NULL	NULL	#	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	ALL	i1,i2,i3,i5,i6,i7	NULL	NULL	NULL	#	Using where
+explain select * from t0 where
+((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+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	ALL	i1,i2,i3,i5,i6	NULL	NULL	NULL	#	Using where
+explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
+((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+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	#	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
+2	2	2	2	2	2	2	1022
+3	3	3	3	3	3	3	1021
+4	4	4	4	4	4	4	1020
+1021	1021	1021	1021	1021	1021	1021	3
+1022	1022	1022	1022	1022	1022	1022	2
+1023	1023	1023	1023	1023	1023	1023	1
+1024	1024	1024	1024	1024	1024	1024	0
+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	ALL	i1,i8	NULL	NULL	NULL	#	Using where; Using filesort
+create table t2 like t0;
+insert into t2 select * from t0;
+alter table t2 add index i1_3(key1, key3);
+alter table t2 add index i2_3(key2, key3);
+alter table t2 drop index i1;
+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	#	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	#	Using where; Using index
+explain select key7 from t2 where key1 <100 or key2 < 100;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	i1_3,i2_3	NULL	NULL	NULL	#	Using where
+create table t4 (
+key1a int not null,
+key1b int not null,
+key2  int not null,
+key2_1 int not null,
+key2_2 int not null,
+key3  int not null,
+index i1a (key1a, key1b),
+index i1b (key1b, key1a),
+index i2_1(key2, key2_1),
+index i2_2(key2, key2_1)
+);
+insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0;
+select * from t4 where key1a = 3 or key1b = 4;
+key1a	key1b	key2	key2_1	key2_2	key3
+3	3	0	3	3	3
+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	#	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	#	Using where
+explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 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	#	Using where
+explain select * from t4 where key2_1 = 1 or key2_2 = 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	#	Using where
+create table t1 like t0;
+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	#	Using union(i1,i2); Using where
+1	SIMPLE	t1	ref	i1	i1	4	test.t0.key1	#	
+select * from t0 left join t1 on (t0.key1=t1.key1)
+where t0.key1=3 or t0.key2=4;
+key1	key2	key3	key4	key5	key6	key7	key8	key1	key2	key3	key4	key5	key6	key7	key8
+3	3	3	3	3	3	3	1021	3	3	3	3	3	3	3	1021
+4	4	4	4	4	4	4	1020	4	4	4	4	4	4	4	1020
+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	#	Using union(i1,i2); Using where
+1	SIMPLE	t1	ref	i1	i1	4	test.t0.key1	#	
+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	#	i1,i2	#	#	test.t0.key1	#	#
+1	SIMPLE	t1	#	i1	#	#	test.t0.key1	#	#
+explain
+select * from t0,t1 where (t0.key1=t1.key1) and
+(t0.key1=3 or t0.key2<4) and t1.key1=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ref	i1,i2	i1	4	test.t0.key1	#	Using where
+1	SIMPLE	t1	ref	i1	i1	4	test.t0.key1	#	
+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	test.t0.key1	#	
+1	SIMPLE	t1	index_merge	i1,i8	i1,i8	4,4	test.t0.key1	#	Using union(i1,i8); Using where; Using join buffer
+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
+1	SIMPLE	t0	range	i1	i1	4	test.t0.key1	#	Using where
+1	SIMPLE	t1	ALL	i1,i8	NULL	NULL	test.t0.key1	#	Range checked for each record (index map: 0x81)
+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	#	Using union(i1,i2); Using where
+2	UNION	t1	index_merge	i1,i3	i1,i3	4,4	NULL	#	Using sort_union(i1,i3); Using where
+NULL	UNION RESULT	<union1,2>	ALL	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	#	
+2	DERIVED	t1	index_merge	i1,i2	i1,i2	4,4	NULL	#	Using union(i1,i2); Using where; Using index
+create table t3 like t0;
+insert into t3 select * from t0;
+alter table t3 add key9 int not null, add index i9(key9);
+alter table t3 add keyA int not null, add index iA(keyA);
+alter table t3 add keyB int not null, add index iB(keyB);
+alter table t3 add keyC int not null, add index iC(keyC);
+update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1;
+explain 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
+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	#	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
+key9=9 or keyA=10 or keyB=11 or keyC=12;
+key1	key2	key3	key4	key5	key6	key7	key8	key9	keyA	keyB	keyC
+1	1	1	1	1	1	1	1023	1	1	1	1
+2	2	2	2	2	2	2	1022	2	2	2	2
+3	3	3	3	3	3	3	1021	3	3	3	3
+4	4	4	4	4	4	4	1020	4	4	4	4
+5	5	5	5	5	5	5	1019	5	5	5	5
+6	6	6	6	6	6	6	1018	6	6	6	6
+7	7	7	7	7	7	7	1017	7	7	7	7
+9	9	9	9	9	9	9	1015	9	9	9	9
+10	10	10	10	10	10	10	1014	10	10	10	10
+11	11	11	11	11	11	11	1013	11	11	11	11
+12	12	12	12	12	12	12	1012	12	12	12	12
+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	#	i1,i2	#	#	NULL	#	#
+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
+2	2	2	2	2	2	2	1022
+3	3	3	3	3	3	3	1021
+update t0 set key8=123 where key1 < 3 or key2 < 4;
+select * from t0 where key1 < 3 or key2 < 4;
+key1	key2	key3	key4	key5	key6	key7	key8
+1	1	1	1	1	1	1	123
+2	2	2	2	2	2	2	123
+3	3	3	3	3	3	3	123
+delete from t0 where key1 < 3 or key2 < 4;
+select * from t0 where key1 < 3 or key2 < 4;
+key1	key2	key3	key4	key5	key6	key7	key8
+select count(*) from t0;
+count(*)
+1021
+drop table t4;
+create table t4 (a int);
+insert into t4 values (1),(4),(3);
+set @save_join_buffer_size=@@join_buffer_size;
+set join_buffer_size= 4096;
+explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
+where (A.key1 < 500000 or A.key2 < 3)
+and   (B.key1 < 500000 or B.key2 < 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	A	index_merge	i1,i2	i1,i2	4,4	NULL	#	Using sort_union(i1,i2); Using where
+1	SIMPLE	B	index_merge	i1,i2	i1,i2	4,4	NULL	#	Using sort_union(i1,i2); Using where; Using join buffer
+select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
+where (A.key1 < 500000 or A.key2 < 3)
+and   (B.key1 < 500000 or B.key2 < 3);
+max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+10240
+update t0 set key1=1;
+explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
+where (A.key1 = 1 or A.key2 = 1)
+and   (B.key1 = 1 or B.key2 = 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	A	index_merge	i1,i2	i1,i2	4,4	NULL	#	Using union(i1,i2); Using where
+1	SIMPLE	B	index_merge	i1,i2	i1,i2	4,4	NULL	#	Using union(i1,i2); Using where; Using join buffer
+select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
+where (A.key1 = 1 or A.key2 = 1)
+and   (B.key1 = 1 or B.key2 = 1);
+max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+8194
+alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200);
+update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500;
+explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+from t0 as A, t0 as B
+where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
+and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	A	#	i1,i2,i3,i4,i5,i6,i7?,i8	#	#	NULL	#	#
+1	SIMPLE	B	#	i1,i2,i3,i4,i5,i6,i7?,i8	#	#	NULL	#	#
+select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+from t0 as A, t0 as B
+where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
+and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1);
+max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
+8186
+set join_buffer_size= @save_join_buffer_size;
+drop table t0, t1, t2, t3, t4;
+CREATE TABLE t1 (
+cola char(3) not null, colb char(3) not null,  filler char(200),
+key(cola), key(colb)
+);
+INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.t1	optimize	status	OK
+select count(*) from t1;
+count(*)
+8704
+explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	cola,colb	cola,colb	3,3	NULL	#	Using intersect(cola,colb); Using where
+explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	cola,colb	cola,colb	3,3	NULL	#	Using intersect(cola,colb); Using where
+drop table t1;
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(1);
+CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
+INSERT INTO t2(a,b) VALUES
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
+(1,2);
+LOCK TABLES t1 WRITE, t2 WRITE;
+INSERT INTO t2(a,b) VALUES(1,2);
+SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1;
+a
+1
+1
+UNLOCK TABLES;
+DROP TABLE t1, t2;
+CREATE TABLE `t1` (
+`a` int(11) DEFAULT NULL,
+`filler` char(200) DEFAULT NULL,
+`b` int(11) DEFAULT NULL,
+KEY `a` (`a`),
+KEY `b` (`b`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1;
+insert into t1 values
+(0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3), 
+(4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7), 
+(8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1), 
+(2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5), 
+(6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9), 
+(10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13),
+(14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17), 
+(18, 'filler', 18), (19, 'filler', 19), (4, '5      ', 0), (5, '4      ', 0), 
+(4, '4      ', 0), (4, 'qq     ', 5), (5, 'qq     ', 4), (4, 'zz     ', 4);
+create table t2(
+`a` int(11) DEFAULT NULL,
+`filler` char(200) DEFAULT NULL,
+`b` int(11) DEFAULT NULL,
+KEY USING BTREE (`a`),
+KEY USING BTREE (`b`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1;
+insert into t2 select * from t1;
+must use sort-union rather than union:
+explain select * from t1 where a=4 or b=4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	#	Using sort_union(a,b); Using where
+select * from t1 where a=4 or b=4;
+a	filler	b
+4	4	0
+4	5	0
+4	filler	4
+4	filler	4
+4	qq	5
+4	zz	4
+5	qq	4
+select * from t1 ignore index(a,b) where a=4 or b=4;
+a	filler	b
+4	4	0
+4	5	0
+4	filler	4
+4	filler	4
+4	qq	5
+4	zz	4
+5	qq	4
+must use union, not sort-union:
+explain select * from t2 where a=4 or b=4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index_merge	a,b	a,b	5,5	NULL	#	Using union(a,b); Using where
+select * from t2 where a=4 or b=4;
+a	filler	b
+4	4	0
+4	5	0
+4	filler	4
+4	filler	4
+4	qq	5
+4	zz	4
+5	qq	4
+drop table t1, t2;
+CREATE TABLE t1 (a varchar(8), b set('a','b','c','d','e','f','g','h'),
+KEY b(b), KEY a(a));
+INSERT INTO t1 VALUES ('y',''), ('z','');
+SELECT b,a from t1 WHERE (b!='c' AND b!='f' && b!='h') OR 
+(a='pure-S') OR (a='DE80337a') OR (a='DE80799');
+b	a
+	y
+	z
+DROP TABLE t1;
+#
+# BUG#40974: Incorrect query results when using clause evaluated using range check
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int);
+insert into t1 values (1),(2);
+create table t2(a int, b int);
+insert into t2 values (1,1), (2, 1000);
+create table t3 (a int, b int, filler char(100), key(a), key(b));
+insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C;
+insert into t3 values (1,1,'data');
+insert into t3 values (1,1,'data');
+The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3)
+explain select * from t1 
+where exists (select 1 from t2, t3 
+where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	#	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	a,b	NULL	NULL	NULL	#	Range checked for each record (index map: 0x3)
+select * from t1 
+where exists (select 1 from t2, t3 
+where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
+a
+1
+2
+drop table t0, t1, t2, t3;
+#
+# BUG#44810: index merge and order by with low sort_buffer_size 
+# crashes server!
+#
+CREATE TABLE t1(a VARCHAR(128),b VARCHAR(128),KEY(A),KEY(B));
+INSERT INTO t1 VALUES (REPEAT('a',128),REPEAT('b',128));
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+SET SESSION sort_buffer_size=1;
+Warnings:
+Warning	1292	Truncated incorrect sort_buffer_size value: '1'
+explain 
+SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' 
+ORDER BY a,b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	a,b	a,b	131,131	NULL	#	Using sort_union(a,b); Using where; Using filesort
+SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' 
+ORDER BY a,b;
+SET SESSION sort_buffer_size=DEFAULT;
+DROP TABLE t1;
+End of 5.0 tests
+#---------------- ROR-index_merge tests -----------------------
+SET SESSION STORAGE_ENGINE = InnoDB;
+drop table if exists  t0,t1,t2;
+create table t1
+(
+/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
+st_a int not null default 0,
+swt1a int not null default 0,
+swt2a int not null default 0,
+st_b int not null default 0,
+swt1b int not null default 0,
+swt2b int not null default 0,
+/* fields/keys for row retrieval tests */
+key1 int,
+key2 int,
+key3 int,
+key4 int,
+/* make rows much bigger then keys */
+filler1 char (200),
+filler2 char (200),
+filler3 char (200),
+filler4 char (200),
+filler5 char (200),
+filler6 char (200),
+/* order of keys is important */
+key sta_swt12a(st_a,swt1a,swt2a),
+key sta_swt1a(st_a,swt1a),
+key sta_swt2a(st_a,swt2a),
+key sta_swt21a(st_a,swt2a,swt1a),
+key st_a(st_a),
+key stb_swt1a_2b(st_b,swt1b,swt2a),
+key stb_swt1b(st_b,swt1b),
+key st_b(st_b),
+key(key1),
+key(key2),
+key(key3),
+key(key4)
+) ;
+create table t0 as select * from t1;
+# Printing of many insert into t0 values (....) disabled.
+alter table t1 disable keys;
+Warnings:
+Note	1031	Table storage engine for 't1' doesn't have this option
+# Printing of many insert into t1 select .... from t0 disabled.
+# Printing of many insert into t1 (...) values (....) disabled.
+alter table t1 enable keys;
+Warnings:
+Note	1031	Table storage engine for 't1' doesn't have this option
+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	#	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	#	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	#	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	#	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	#	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	4	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	#	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	#	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	#	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	#	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	#	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	#	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-1
+100	100	200	200	key1-key2-key3-key4-2
+100	100	200	200	key1-key2-key3-key4-3
+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	#	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-1
+100	100	200	200	key1-key2-key3-key4-2
+100	100	200	200	key1-key2-key3-key4-3
+-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	#	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-1
+100	100	200	200	key1-key2-key3-key4-2
+100	100	200	200	key1-key2-key3-key4-3
+-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	ref	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	#	4	const	#	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	#	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	#	4	const	#	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_swt21a	12	const,const,const	#	
+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_swt1a_2b	8	const,const	#	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	#	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	#	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	#	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	#	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	#	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	#	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	#	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	#	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
+expla_or_bin select count(a_or_b) from t2 where a_or_b='AAAAAAAA' a_or_bnd a_or_b='AAAAAAAA';
+id	select_type	ta_or_ba_or_ble	type	possia_or_ble_keys	key	key_len	ref	rows	Extra_or_b
+1	SIMPLE	t2	ref	a_or_b,a_or_b	a_or_b	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	#	Using where
+drop table t2;
+CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '',
+KEY(c1), KEY(c2), KEY(c3));
+INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
+INSERT INTO t1 VALUES(0,0,0);
+CREATE TABLE t2(c1 int);
+INSERT INTO t2 VALUES(1);
+DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0;
+SELECT * FROM t1;
+c1	c2	c3
+DROP TABLE t1,t2;
+#---------------- 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;
+#
+# 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 -------------------------------
 SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
 drop table if exists t1;

=== modified file 'mysql-test/t/index_merge_innodb.test'
--- a/mysql-test/t/index_merge_innodb.test	2011-10-27 08:40:40 +0000
+++ b/mysql-test/t/index_merge_innodb.test	2011-11-02 11:27:07 +0000
@@ -22,9 +22,9 @@ let $index_merge_random_rows_in_EXPLAIN
 let $merge_table_support= 0;
 
 # -- [DISABLED Bug#45727]
-# --source include/index_merge1.inc
-# --source include/index_merge_ror.inc
-# --source include/index_merge2.inc
+ --source include/index_merge1.inc
+ --source include/index_merge_ror.inc
+ --source include/index_merge2.inc
 
 --source include/index_merge_2sweeps.inc
 --source include/index_merge_ror_cpk.inc

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (sneha.modi:3544 to 3545) Sneha MOdi7 Nov