MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Sergey Petrunia Date:January 25 2009 2:40pm
Subject:bzr commit into mysql-6.0-opt branch (sergefp:2809) Bug#41136
View as plain text  
#At file:///home/spetrunia/dev/mysql-6.0-opt-look2/ based on revid:sergefp@stripped

 2809 Sergey Petrunia	2009-01-25
      BUG#41136: ORDER BY+range access: EXPLAIN shows "Using MRR" while MRR is actually not used
      - Testcase
modified:
  mysql-test/r/myisam_mrr.result
  mysql-test/r/order_by.result
  mysql-test/t/myisam_mrr.test
  sql/handler.cc
  sql/opt_range.cc
  sql/opt_range.h
  sql/sql_select.cc
  storage/falcon/ha_falcon.cpp

per-file messages:
  mysql-test/r/myisam_mrr.result
    BUG#41136: ORDER BY+range access: EXPLAIN shows "Using MRR" while MRR is actually not used
    - Testcase
  mysql-test/r/order_by.result
    BUG#41136: ORDER BY+range access: EXPLAIN shows "Using MRR" while MRR is actually not used
    - Update test results
  mysql-test/t/myisam_mrr.test
    BUG#41136: ORDER BY+range access: EXPLAIN shows "Using MRR" while MRR is actually not used
    - Testcase
  sql/opt_range.cc
    BUG#41136: ORDER BY+range access: EXPLAIN shows "Using MRR" while MRR is actually not used
    - Removed QUICK_SELECT_I::sorted, added QUICK_SELECT_I::need_sorted_output()
    - Always request sorted output when doing range analysis, so we can see if the engine's 
      MRR implementation can produce sorted output
    - Use that info in QUICK_RANGE_SELECT::need_sorted_output(): if quick select is
      requested to produce sorted output and native MRR implementation can't do that,
      switch to default.
  sql/opt_range.h
    BUG#41136: ORDER BY+range access: EXPLAIN shows "Using MRR" while MRR is actually not used
    - Remove QUICK_SELECT_I::sorted, add QUICK_SELECT_I::need_sorted_output()
  sql/sql_select.cc
    BUG#41136: ORDER BY+range access: EXPLAIN shows "Using MRR" while MRR is actually not used
    - Remove QUICK_SELECT_I::sorted, add QUICK_SELECT_I::need_sorted_output()
  storage/falcon/ha_falcon.cpp
    BUG#41136: ORDER BY+range access: EXPLAIN shows "Using MRR" while MRR is actually not used
    - Make Falcon's native MRR implementation conform to the changed calling 
      convention: always the output is not ordered.
=== modified file 'mysql-test/r/myisam_mrr.result'
--- a/mysql-test/r/myisam_mrr.result	2009-01-24 15:01:08 +0000
+++ b/mysql-test/r/myisam_mrr.result	2009-01-25 14:40:32 +0000
@@ -377,3 +377,15 @@ a	b	filler
 8	IIIIIIIIIIIIIIIIIIII	filler
 9	JJJJJJJJJJJJJJJJJJJJ	filler
 drop table t0,t1;
+#
+# BUG#41136: ORDER BY + range access: EXPLAIN shows "Using MRR" while MRR is actually not used
+#
+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, key(a));
+insert into t1 select A.a + 10 *(B.a + 10*C.a), A.a + 10 *(B.a + 10*C.a) from t0 A, t0 B, t0 C;
+This mustn't show "Using MRR":
+explain select * from t1 where a < 20  order by a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	20	Using index condition
+drop table t0, t1;

=== modified file 'mysql-test/r/order_by.result'
--- a/mysql-test/r/order_by.result	2009-01-24 15:01:08 +0000
+++ b/mysql-test/r/order_by.result	2009-01-25 14:40:32 +0000
@@ -1466,7 +1466,7 @@ SELECT d FROM t3 AS t1, t2 AS t2 
 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
 ORDER BY t2.c LIMIT 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	a,b,c	c	5	NULL	420	Using where; Using MRR
+1	SIMPLE	t2	range	a,b,c	c	5	NULL	420	Using where
 1	SIMPLE	t1	ref	a	a	39	test.t2.a,const	10	Using where; Using index
 SELECT d FROM t3 AS t1, t2 AS t2 
 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'

=== modified file 'mysql-test/t/myisam_mrr.test'
--- a/mysql-test/t/myisam_mrr.test	2009-01-24 15:01:08 +0000
+++ b/mysql-test/t/myisam_mrr.test	2009-01-25 14:40:32 +0000
@@ -86,3 +86,14 @@ explain select * from t1 where a < 10 an
 select * from t1 where a < 10 and b = repeat(char(65+a), 20);
 drop table t0,t1;
 
+-- echo #
+-- echo # BUG#41136: ORDER BY + range access: EXPLAIN shows "Using MRR" while MRR is actually not used
+-- echo #
+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, key(a));
+insert into t1 select A.a + 10 *(B.a + 10*C.a), A.a + 10 *(B.a + 10*C.a) from t0 A, t0 B, t0 C; 
+-- echo This mustn't show "Using MRR":
+explain select * from t1 where a < 20  order by a;
+drop table t0, t1;
+

=== modified file 'sql/handler.cc'
--- a/sql/handler.cc	2009-01-14 10:29:36 +0000
+++ b/sql/handler.cc	2009-01-25 14:40:32 +0000
@@ -4612,6 +4612,7 @@ ha_rows DsMrr_impl::dsmrr_info(uint keyn
   }
   else
   {
+    /* *flags and *bufsz were set by choose_mrr_impl */
     DBUG_PRINT("info", ("DS-MRR implementation choosen"));
   }
   return 0;
@@ -4653,7 +4654,7 @@ ha_rows DsMrr_impl::dsmrr_info_const(uin
   }
   else
   {
-    *flags &= ~HA_MRR_USE_DEFAULT_IMPL;
+    /* *flags and *bufsz were set by choose_mrr_impl */
     DBUG_PRINT("info", ("DS-MRR implementation choosen"));
   }
   return rows;
@@ -4719,10 +4720,8 @@ bool DsMrr_impl::choose_mrr_impl(uint ke
   COST_VECT dsmrr_cost;
   bool res;
   THD *thd= current_thd;
-  if ((thd->variables.optimizer_use_mrr == 2) || 
-      (*flags & HA_MRR_INDEX_ONLY) || (*flags & HA_MRR_SORTED) ||
-      (keyno == table->s->primary_key && 
-       h->primary_key_is_clustered()) || 
+  if (thd->variables.optimizer_use_mrr == 2 || *flags & HA_MRR_INDEX_ONLY ||
+      (keyno == table->s->primary_key && h->primary_key_is_clustered()) ||
        key_uses_partial_cols(table, keyno))
   {
     /* Use the default implementation */

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2009-01-14 10:29:36 +0000
+++ b/sql/opt_range.cc	2009-01-25 14:40:32 +0000
@@ -1159,7 +1159,6 @@ QUICK_RANGE_SELECT::QUICK_RANGE_SELECT(T
   DBUG_ENTER("QUICK_RANGE_SELECT::QUICK_RANGE_SELECT");
 
   in_ror_merged_scan= 0;
-  sorted= 0;
   index= key_nr;
   head=  table;
   key_part_info= head->key_info[index].key_part;
@@ -1195,6 +1194,20 @@ QUICK_RANGE_SELECT::QUICK_RANGE_SELECT(T
 }
 
 
+void QUICK_RANGE_SELECT::need_sorted_output()
+{
+  if (!(mrr_flags & HA_MRR_SORTED))
+  {
+    /*
+      Native implementation can't produce sorted output. We'll have to
+      switch to default
+    */
+    mrr_flags |= HA_MRR_USE_DEFAULT_IMPL; 
+  }
+  mrr_flags |= HA_MRR_SORTED;
+}
+
+
 int QUICK_RANGE_SELECT::init()
 {
   DBUG_ENTER("QUICK_RANGE_SELECT::init");
@@ -7543,7 +7556,10 @@ ha_rows check_quick_select(PARAM *param,
     param->is_ror_scan= FALSE;
   
   *mrr_flags= param->force_default_mrr? HA_MRR_USE_DEFAULT_IMPL: 0;
-  *mrr_flags|= HA_MRR_NO_ASSOCIATION;
+  /*
+    Pass HA_MRR_SORTED to see if MRR implementation can handle sorting.
+  */
+  *mrr_flags|= HA_MRR_NO_ASSOCIATION | HA_MRR_SORTED;
 
   bool pk_is_clustered= file->primary_key_is_clustered();
   if (index_only && 
@@ -8432,8 +8448,6 @@ int QUICK_RANGE_SELECT::reset()
   if (!mrr_buf_desc)
     empty_buf.buffer= empty_buf.buffer_end= empty_buf.end_of_used_area= NULL;
  
-  if (sorted)
-     mrr_flags |= HA_MRR_SORTED;
   RANGE_SEQ_IF seq_funcs= {quick_range_seq_init, quick_range_seq_next, 0, 0};
   error= file->multi_range_read_init(&seq_funcs, (void*)this, ranges.elements,
                                      mrr_flags, mrr_buf_desc? mrr_buf_desc: 
@@ -8626,7 +8640,7 @@ int QUICK_RANGE_SELECT::get_next_prefix(
     result= file->read_range_first(last_range->min_keypart_map ? &start_key : 0,
 				   last_range->max_keypart_map ? &end_key : 0,
                                    test(last_range->flag & EQ_RANGE),
-				   sorted);
+				   TRUE);
     if (last_range->flag == (UNIQUE_RANGE | EQ_RANGE))
       last_range= 0;			// Stop searching
 

=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h	2008-12-27 02:32:25 +0000
+++ b/sql/opt_range.h	2009-01-25 14:40:32 +0000
@@ -118,7 +118,6 @@ class QUICK_RANGE :public Sql_alloc {
 class QUICK_SELECT_I
 {
 public:
-  bool sorted;
   ha_rows records;  /* estimate of # of records to be retrieved */
   double  read_time; /* time to perform this retrieval          */
   TABLE   *head;
@@ -190,7 +189,13 @@ public:
 
   virtual bool reverse_sorted() = 0;
   virtual bool unique_key_range() { return false; }
-
+  
+  /*
+    Request that this quick select produces sorted output. Not all quick
+    selects can do it, the caller is responsible for calling this function
+    only for those quick selects that can.
+  */
+  virtual void need_sorted_output() = 0;
   enum {
     QS_TYPE_RANGE = 0,
     QS_TYPE_INDEX_MERGE = 1,
@@ -328,7 +333,8 @@ public:
   QUICK_RANGE_SELECT(THD *thd, TABLE *table,uint index_arg,bool no_alloc,
                      MEM_ROOT *parent_alloc, bool *create_err);
   ~QUICK_RANGE_SELECT();
-
+  
+  void need_sorted_output();
   int init();
   int reset(void);
   int get_next();
@@ -453,6 +459,7 @@ public:
   ~QUICK_INDEX_MERGE_SELECT();
 
   int  init();
+  void need_sorted_output() { DBUG_ASSERT(0); /* Can't do it */ }
   int  reset(void);
   int  get_next();
   bool reverse_sorted() { return false; }
@@ -512,6 +519,7 @@ public:
   ~QUICK_ROR_INTERSECT_SELECT();
 
   int  init();
+  void need_sorted_output() { DBUG_ASSERT(0); /* Can't do it */ }
   int  reset(void);
   int  get_next();
   bool reverse_sorted() { return false; }
@@ -566,6 +574,7 @@ public:
   ~QUICK_ROR_UNION_SELECT();
 
   int  init();
+  void need_sorted_output() { DBUG_ASSERT(0); /* Can't do it */ }
   int  reset(void);
   int  get_next();
   bool reverse_sorted() { return false; }
@@ -685,6 +694,7 @@ public:
   void adjust_prefix_ranges();
   bool alloc_buffers();
   int init();
+  void need_sorted_output() { /* always do it */ }
   int reset();
   int get_next();
   bool reverse_sorted() { return false; }

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-01-24 15:01:08 +0000
+++ b/sql/sql_select.cc	2009-01-25 14:40:32 +0000
@@ -18825,7 +18825,7 @@ check_reverse_order:                  
     }
   }
   else if (select && select->quick)
-    select->quick->sorted= 1;
+    select->quick->need_sorted_output();
   DBUG_RETURN(1);
 use_filesort:
   table->file->extra(HA_EXTRA_NO_ORDERBY_LIMIT);

=== modified file 'storage/falcon/ha_falcon.cpp'
--- a/storage/falcon/ha_falcon.cpp	2008-12-11 17:29:35 +0000
+++ b/storage/falcon/ha_falcon.cpp	2009-01-25 14:40:32 +0000
@@ -1849,7 +1849,7 @@ ha_rows StorageInterface::multi_range_re
 											   flags, cost);
 	if ((res != HA_POS_ERROR) && !native_requested)
 		{
-		*flags &= ~HA_MRR_USE_DEFAULT_IMPL;
+		*flags &= ~(HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED);
 		/* We'll be returning records without telling which range they are contained in */
 		*flags |= HA_MRR_NO_ASSOCIATION;
 		/* We'll use our own internal buffer so we won't need any buffer space from the SQL layer */
@@ -1870,7 +1870,7 @@ ha_rows StorageInterface::multi_range_re
 										 cost);
 	if ((res != HA_POS_ERROR) && !native_requested)
 		{
-		*flags &= ~HA_MRR_USE_DEFAULT_IMPL;
+		*flags &= ~(HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED);
 		/* See _info_const() function for explanation of these: */
 		*flags |= HA_MRR_NO_ASSOCIATION;
 		*bufsz = 0;

Thread
bzr commit into mysql-6.0-opt branch (sergefp:2809) Bug#41136Sergey Petrunia25 Jan