diff options
| -rw-r--r-- | mysql-test/r/order_by_innodb.result | 13 | ||||
| -rw-r--r-- | mysql-test/t/order_by_innodb.test | 23 | ||||
| -rw-r--r-- | sql/filesort.cc | 3 | ||||
| -rw-r--r-- | sql/opt_range.cc | 57 | ||||
| -rw-r--r-- | sql/opt_range.h | 15 |
5 files changed, 110 insertions, 1 deletions
diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result new file mode 100644 index 00000000000..3c6c4053741 --- /dev/null +++ b/mysql-test/r/order_by_innodb.result @@ -0,0 +1,13 @@ +drop table if exists t0,t1,t2,t3; +# +# MDEV-6434: Wrong result (extra rows) with ORDER BY, multiple-column index, InnoDB +# +CREATE TABLE t1 (a INT, b INT, c INT, d TEXT, KEY idx(a,b,c)) ENGINE=InnoDB; +INSERT INTO t1 (a,c) VALUES +(8, 9),(8, 10),(13, 15),(16, 17),(16, 18),(16, 19),(20, 21), +(20, 22),(20, 24),(20, 25),(20, 26),(20, 27),(20, 28); +SELECT * FROM t1 WHERE a = 8 AND (b = 1 OR b IS NULL) ORDER BY c; +a b c d +8 NULL 9 NULL +8 NULL 10 NULL +DROP TABLE t1; diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test new file mode 100644 index 00000000000..c20eaceb053 --- /dev/null +++ b/mysql-test/t/order_by_innodb.test @@ -0,0 +1,23 @@ +# +# ORDER BY handling (e.g. filesort) tests that require innodb +# +-- source include/have_innodb.inc + +--disable_warnings +drop table if exists t0,t1,t2,t3; +--enable_warnings + +--echo # +--echo # MDEV-6434: Wrong result (extra rows) with ORDER BY, multiple-column index, InnoDB +--echo # + +CREATE TABLE t1 (a INT, b INT, c INT, d TEXT, KEY idx(a,b,c)) ENGINE=InnoDB; + +INSERT INTO t1 (a,c) VALUES +(8, 9),(8, 10),(13, 15),(16, 17),(16, 18),(16, 19),(20, 21), +(20, 22),(20, 24),(20, 25),(20, 26),(20, 27),(20, 28); + +SELECT * FROM t1 WHERE a = 8 AND (b = 1 OR b IS NULL) ORDER BY c; + +DROP TABLE t1; + diff --git a/sql/filesort.cc b/sql/filesort.cc index 34cd11cbcf3..d6cb22ebe2c 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -642,6 +642,9 @@ static ha_rows find_all_keys(SORTPARAM *param, SQL_SELECT *select, /* Temporary set for register_used_fields and register_field_in_read_map */ sort_form->read_set= &sort_form->tmp_set; register_used_fields(param); + if (quick_select) + select->quick->add_used_key_part_to_set(sort_form->read_set); + Item *sort_cond= !select ? 0 : !select->pre_idx_push_select_cond ? select->cond : select->pre_idx_push_select_cond; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 5db080394eb..80807c01fd0 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -10538,6 +10538,7 @@ QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table, if (quick->init()) goto err; quick->records= records; + quick->max_used_key_length= ref->key_parts; if ((cp_buffer_from_ref(thd, table, ref) && thd->is_fatal_error) || !(range= new(alloc) QUICK_RANGE())) @@ -10547,7 +10548,7 @@ QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table, range->min_length= range->max_length= ref->key_length; range->min_keypart_map= range->max_keypart_map= make_prev_keypart_map(ref->key_parts); - range->flag= (ref->key_length == key_info->key_length ? EQ_RANGE : 0); + range->flag= EQ_RANGE; if (!(quick->key_parts=key_part=(KEY_PART *) alloc_root(&quick->alloc,sizeof(KEY_PART)*ref->key_parts))) @@ -11756,6 +11757,60 @@ void QUICK_ROR_UNION_SELECT::add_keys_and_lengths(String *key_names, } +void QUICK_RANGE_SELECT::add_used_key_part_to_set(MY_BITMAP *col_set) +{ + for (uint i=0; i < max_used_key_length; i++) + { + bitmap_set_bit(col_set, key_parts[i].field->field_index); + } +} + + +void QUICK_GROUP_MIN_MAX_SELECT::add_used_key_part_to_set(MY_BITMAP *col_set) +{ + for (uint i=0; i < max_used_key_length; i++) + { + bitmap_set_bit(col_set, index_info->key_part[i].field->field_index); + } +} + + +void QUICK_ROR_INTERSECT_SELECT::add_used_key_part_to_set(MY_BITMAP *col_set) +{ + List_iterator_fast<QUICK_SELECT_WITH_RECORD> it(quick_selects); + QUICK_SELECT_WITH_RECORD *quick; + while ((quick= it++)) + { + quick->quick->add_used_key_part_to_set(col_set); + } +} + + +void QUICK_INDEX_SORT_SELECT::add_used_key_part_to_set(MY_BITMAP *col_set) +{ + QUICK_RANGE_SELECT *quick; + List_iterator_fast<QUICK_RANGE_SELECT> it(quick_selects); + while ((quick= it++)) + { + quick->add_used_key_part_to_set(col_set); + } + if (pk_quick_select) + pk_quick_select->add_used_key_part_to_set(col_set); +} + + +void QUICK_ROR_UNION_SELECT::add_used_key_part_to_set(MY_BITMAP *col_set) +{ + QUICK_SELECT_I *quick; + List_iterator_fast<QUICK_SELECT_I> it(quick_selects); + + while ((quick= it++)) + { + quick->add_used_key_part_to_set(col_set); + } +} + + /******************************************************************************* * Implementation of QUICK_GROUP_MIN_MAX_SELECT *******************************************************************************/ diff --git a/sql/opt_range.h b/sql/opt_range.h index fff6e414ad9..b8b46ae5ab1 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -394,6 +394,13 @@ public: Returns a QUICK_SELECT with reverse order of to the index. */ virtual QUICK_SELECT_I *make_reverse(uint used_key_parts_arg) { return NULL; } + + /* + Add the key columns used by the quick select into table's read set. + + This is used by an optimization in filesort. + */ + virtual void add_used_key_part_to_set(MY_BITMAP *col_set)=0; }; @@ -484,6 +491,9 @@ public: #endif virtual void replace_handler(handler *new_file) { file= new_file; } QUICK_SELECT_I *make_reverse(uint used_key_parts_arg); + + virtual void add_used_key_part_to_set(MY_BITMAP *col_set); + private: /* Default copy ctor used by QUICK_SELECT_DESC */ friend class TRP_ROR_INTERSECT; @@ -644,6 +654,8 @@ public: virtual int read_keys_and_merge()= 0; /* used to get rows collected in Unique */ READ_RECORD read_record; + + virtual void add_used_key_part_to_set(MY_BITMAP *col_set); }; @@ -719,6 +731,7 @@ public: void add_keys_and_lengths(String *key_names, String *used_lengths); void add_info_string(String *str); bool is_keys_used(const MY_BITMAP *fields); + void add_used_key_part_to_set(MY_BITMAP *col_set); #ifndef DBUG_OFF void dbug_dump(int indent, bool verbose); #endif @@ -798,6 +811,7 @@ public: void add_keys_and_lengths(String *key_names, String *used_lengths); void add_info_string(String *str); bool is_keys_used(const MY_BITMAP *fields); + void add_used_key_part_to_set(MY_BITMAP *col_set); #ifndef DBUG_OFF void dbug_dump(int indent, bool verbose); #endif @@ -940,6 +954,7 @@ public: bool unique_key_range() { return false; } int get_type() { return QS_TYPE_GROUP_MIN_MAX; } void add_keys_and_lengths(String *key_names, String *used_lengths); + void add_used_key_part_to_set(MY_BITMAP *col_set); #ifndef DBUG_OFF void dbug_dump(int indent, bool verbose); #endif |