#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#41136 | Sergey Petrunia | 25 Jan |
