bk commit into 5.0 tree (sergefp:1.2041) BUG#17314

[Sergey Petrunia]

Below is the list of changes that have just been committed into a local
5.0 repository of psergey. When psergey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2041 06/02/11 21:51:43 sergefp@stripped +5 -0
  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.

  sql/ha_myisammrg.cc
    1.76 06/02/11 21:51:37 sergefp@stripped +21 -1
    BUG#17314: For MERGE tables, set handler::block_size to
myisam_block_size/#underlying_tables, and not to 0.

  mysql-test/t/index_merge.test
    1.14 06/02/11 21:51:37 sergefp@stripped +26 -0
    Testcase for BUG#17314

  mysql-test/r/merge.result
    1.46 06/02/11 21:51:37 sergefp@stripped +1 -1
    BUG#17314: update testcase result

  mysql-test/r/index_merge.result
    1.19 06/02/11 21:51:37 sergefp@stripped +22 -0
    Testcase for BUG#17314

  myisammrg/myrg_queue.c
    1.12 06/02/11 21:51:37 sergefp@stripped +17 -3
    BUG#17314: Make index scans on MERGE table return records ordered by (keytuple,
merge_table_rowid). 

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	sergefp
# Host:	newbox.mylan
# Root:	/home/psergey/mysql-5.0-csc8079

--- 1.75/sql/ha_myisammrg.cc	2005-11-20 21:46:59 +03:00
+++ 1.76/sql/ha_myisammrg.cc	2006-02-11 21:51:37 +03:00
@@ -288,7 +288,27 @@
   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

--- 1.45/mysql-test/r/merge.result	2005-12-26 12:32:49 +03:00
+++ 1.46/mysql-test/r/merge.result	2006-02-11 21:51:37 +03:00
@@ -56,8 +56,8 @@
 4	Testing
 5	table
 5	table
-6	t1
 6	t2
+6	t1
 7	Testing
 7	Testing
 8	table

--- 1.11/myisammrg/myrg_queue.c	2005-10-25 03:27:29 +04:00
+++ 1.12/myisammrg/myrg_queue.c	2006-02-11 21:51:37 +03:00
@@ -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 */
 
 

--- 1.18/mysql-test/r/index_merge.result	2006-01-12 10:48:13 +03:00
+++ 1.19/mysql-test/r/index_merge.result	2006-02-11 21:51:37 +03:00
@@ -402,3 +402,25 @@
 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;

--- 1.13/mysql-test/t/index_merge.test	2006-01-12 10:48:13 +03:00
+++ 1.14/mysql-test/t/index_merge.test	2006-02-11 21:51:37 +03:00
@@ -357,3 +357,29 @@
 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;