BUG#17314: Can't use index_merge/intersection for MERGE tables

1. Fix index access costs for MERGE tables, set block_size=myisam_block_size/#underlying_tables 
   instead of 0 which it was before.
2. Make index scans on MERGE table to return records in (key_tuple, merge_table_rowid) order, 
   instead of just (key_tuple) order. This makes an index scan on MERGE table to be truly a ROR-scan
   which is a requirement for index_merge union/intersection.


myisammrg/myrg_queue.c:
  BUG#17314: Make index scans on MERGE table return records ordered by (keytuple, merge_table_rowid).
mysql-test/r/index_merge.result:
  Testcase for BUG#17314
mysql-test/r/merge.result:
  BUG#17314: update testcase result
mysql-test/t/index_merge.test:
  Testcase for BUG#17314
sql/ha_myisammrg.cc:
  BUG#17314: For MERGE tables, set handler::block_size to myisam_block_size/#underlying_tables, and not to 0.

5 files changed, 87 insertions, 5 deletions

diff --git a/myisammrg/myrg_queue.c b/myisammrg/myrg_queue.c
index 7172b9f0e2a..2e600a526c0 100644
--- a/myisammrg/myrg_queue.c
+++ b/myisammrg/myrg_queue.c
@@ -18,12 +18,26 @@
 
 static int queue_key_cmp(void *keyseg, byte *a, byte *b)
 {
-  MI_INFO *aa=((MYRG_TABLE *)a)->table;
-  MI_INFO *bb=((MYRG_TABLE *)b)->table;
+  MYRG_TABLE *ma= (MYRG_TABLE *)a;
+  MYRG_TABLE *mb= (MYRG_TABLE *)b;
+  MI_INFO *aa= ma->table;
+  MI_INFO *bb= mb->table;
   uint not_used[2];
   int ret= ha_key_cmp((HA_KEYSEG *)keyseg, aa->lastkey, bb->lastkey,
 		       USE_WHOLE_KEY, SEARCH_FIND, not_used);
-  return ret < 0 ? -1 : ret > 0 ? 1 : 0;
+  if (ret < 0)
+    return -1;
+  if (ret > 0)
+    return 1;
+ 
+  /*
+    If index tuples have the same values, let the record with least rowid
+    value be "smaller", so index scans return records ordered by (keytuple,
+    rowid). This is used by index_merge access method, grep for ROR in
+    sql/opt_range.cc for details.
+  */
+  return (ma->file_offset < mb->file_offset)? -1 : (ma->file_offset > 
+                                                    mb->file_offset) ? 1 : 0;
 } /* queue_key_cmp */
 
 
diff --git a/mysql-test/r/index_merge.result b/mysql-test/r/index_merge.result
index db87253e19a..3a69f56cbd3 100644
--- a/mysql-test/r/index_merge.result
+++ b/mysql-test/r/index_merge.result
@@ -402,3 +402,25 @@ explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'b
 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	24	Using intersect(cola,colb); Using where
 drop table t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (
+a int, b int, 
+filler1 char(200), filler2 char(200),
+key(a),key(b)
+);
+insert into t1 select @v:= A.a, @v, 't1', 'filler2' from t0 A, t0 B, t0 C;
+create table t2 like t1;
+create table t3 (
+a int, b int, 
+filler1 char(200), filler2 char(200),
+key(a),key(b)
+) engine=merge union=(t1,t2);
+explain select * from t1 where a=1 and b=1;
+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 intersect(a,b); Using where
+explain select * from t3 where a=1 and b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	index_merge	a,b	a,b	5,5	NULL	#	Using intersect(a,b); Using where
+drop table t3;
+drop table t0, t1, t2;
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index 7e3ccc83d73..58e1f86b3f9 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -56,8 +56,8 @@ a	b
 4	Testing
 5	table
 5	table
-6	t1
 6	t2
+6	t1
 7	Testing
 7	Testing
 8	table
diff --git a/mysql-test/t/index_merge.test b/mysql-test/t/index_merge.test
index 10512902409..3da5711bf7a 100644
--- a/mysql-test/t/index_merge.test
+++ b/mysql-test/t/index_merge.test
@@ -357,3 +357,29 @@ explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
 explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
 drop table t1;
 
+#
+# BUG#17314: Index_merge/intersection not choosen by the optimizer for MERGE tables
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (
+  a int, b int, 
+  filler1 char(200), filler2 char(200),
+  key(a),key(b)
+);
+insert into t1 select @v:= A.a, @v, 't1', 'filler2' from t0 A, t0 B, t0 C;
+create table t2 like t1;
+
+create table t3 (
+  a int, b int, 
+  filler1 char(200), filler2 char(200),
+  key(a),key(b)
+) engine=merge union=(t1,t2);
+
+--replace_column 9 #
+explain select * from t1 where a=1 and b=1;
+--replace_column 9 #
+explain select * from t3 where a=1 and b=1;
+
+drop table t3;
+drop table t0, t1, t2;
diff --git a/sql/ha_myisammrg.cc b/sql/ha_myisammrg.cc
index da4136def68..9780f163634 100644
--- a/sql/ha_myisammrg.cc
+++ b/sql/ha_myisammrg.cc
@@ -288,7 +288,27 @@ void ha_myisammrg::info(uint flag)
   table->s->db_options_in_use= info.options;
   table->s->is_view= 1;
   mean_rec_length= info.reclength;
-  block_size=0;
+  
+  /* 
+    The handler::block_size is used all over the code in index scan cost
+    calculations. It is used to get number of disk seeks required to
+    retrieve a number of index tuples.
+    If the merge table has N underlying tables, then (assuming underlying
+    tables have equal size, the only "simple" approach we can use)
+    retrieving X index records from a merge table will require N times more
+    disk seeks compared to doing the same on a MyISAM table with equal
+    number of records.
+    In the edge case (file_tables > myisam_block_size) we'll get
+    block_size==0, and index calculation code will act as if we need one
+    disk seek to retrieve one index tuple.
+
+    TODO: In 5.2 index scan cost calculation will be factored out into a
+    virtual function in class handler and we'll be able to remove this hack.
+  */
+  block_size= 0;
+  if (file->tables)
+    block_size= myisam_block_size / file->tables;
+  
   update_time=0;
 #if SIZEOF_OFF_T > 4
   ref_length=6;					// Should be big enough