diff options
| -rw-r--r-- | mysql-test/r/order_by.result | 57 | ||||
| -rw-r--r-- | mysql-test/t/order_by.test | 41 | ||||
| -rw-r--r-- | sql/sql_select.cc | 25 | ||||
| -rw-r--r-- | sql/table.h | 8 |
4 files changed, 118 insertions, 13 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index dc29c5ec226..306fce1f3c2 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1500,3 +1500,60 @@ id1 15 16 DROP TABLE t1; +CREATE TABLE t1 ( +a INT, +b INT NOT NULL, +c char(100), +KEY (b, c), +KEY (b, a, c) +) +DEFAULT CHARSET = utf8; +INSERT INTO t1 VALUES +(1, 1, 1), +(2, 2, 2), +(3, 3, 3), +(4, 4, 4), +(5, 5, 5), +(6, 6, 6), +(7, 7, 7), +(8, 8, 8), +(9, 9, 9); +INSERT INTO t1 SELECT a + 10, b, c FROM t1; +INSERT INTO t1 SELECT a + 20, b, c FROM t1; +INSERT INTO t1 SELECT a + 40, b, c FROM t1; +INSERT INTO t1 SELECT a + 80, b, c FROM t1; +INSERT INTO t1 SELECT a + 160, b, c FROM t1; +INSERT INTO t1 SELECT a + 320, b, c FROM t1; +INSERT INTO t1 SELECT a + 640, b, c FROM t1; +INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; +EXPLAIN +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +a +2071 +2061 +2051 +2041 +2031 +2021 +2011 +2001 +1991 +EXPLAIN +SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where; Using temporary +SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9; +a +2071 +2061 +2051 +2041 +2031 +2021 +2011 +2001 +1991 +DROP TABLE t1; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index f09c1aa7bd4..cca1e3209cc 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -1361,3 +1361,44 @@ DROP TABLE t1; +# +# Bug#46454: MySQL wrong index optimisation leads to incorrect result & crashes +# +CREATE TABLE t1 ( + a INT, + b INT NOT NULL, + c char(100), + KEY (b, c), + KEY (b, a, c) +) +DEFAULT CHARSET = utf8; + +INSERT INTO t1 VALUES +(1, 1, 1), +(2, 2, 2), +(3, 3, 3), +(4, 4, 4), +(5, 5, 5), +(6, 6, 6), +(7, 7, 7), +(8, 8, 8), +(9, 9, 9); + +INSERT INTO t1 SELECT a + 10, b, c FROM t1; +INSERT INTO t1 SELECT a + 20, b, c FROM t1; +INSERT INTO t1 SELECT a + 40, b, c FROM t1; +INSERT INTO t1 SELECT a + 80, b, c FROM t1; +INSERT INTO t1 SELECT a + 160, b, c FROM t1; +INSERT INTO t1 SELECT a + 320, b, c FROM t1; +INSERT INTO t1 SELECT a + 640, b, c FROM t1; +INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; + +EXPLAIN +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; + +EXPLAIN +SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9; +SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9; + +DROP TABLE t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index c900588212c..7f6c5e834a3 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -6151,7 +6151,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) } } - if (tmp || !cond) + if (tmp || !cond || tab->type == JT_REF) { DBUG_EXECUTE("where",print_where(tmp,tab->table->alias, QT_ORDINARY);); SQL_SELECT *sel= tab->select= ((SQL_SELECT*) @@ -6165,7 +6165,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) The guard will turn the predicate on only after the first match for outer tables is encountered. */ - if (cond) + if (cond && tmp) { /* Because of QUICK_GROUP_MIN_MAX_SELECT there may be a select without @@ -12946,6 +12946,8 @@ find_field_in_item_list (Field *field, void *data) The index must cover all fields in <order>, or it will not be considered. + @param no_changes No changes will be made to the query plan. + @todo - sergeyp: Results of all index merge selects actually are ordered by clustered PK values. @@ -13280,6 +13282,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, } if (!no_changes) { + /* + If ref_key used index tree reading only ('Using index' in EXPLAIN), + and best_key doesn't, then revert the decision. + */ + if (!table->covering_keys.is_set(best_key) && table->key_read) + { + table->key_read= 0; + table->file->extra(HA_EXTRA_NO_KEYREAD); + } if (!quick_created) { tab->index= best_key; @@ -13296,16 +13307,6 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, table->key_read=1; table->file->extra(HA_EXTRA_KEYREAD); } - else if (table->key_read) - { - /* - Clear the covering key read flags that might have been - previously set for some key other than the current best_key. - */ - table->key_read= 0; - table->file->extra(HA_EXTRA_NO_KEYREAD); - } - table->file->ha_index_or_rnd_end(); if (join->select_options & SELECT_DESCRIBE) { diff --git a/sql/table.h b/sql/table.h index 98ede52cd99..653d04b149e 100644 --- a/sql/table.h +++ b/sql/table.h @@ -755,7 +755,13 @@ struct st_table { */ my_bool force_index; my_bool distinct,const_table,no_rows; - my_bool key_read, no_keyread; + + /** + If set, the optimizer has found that row retrieval should access index + tree only. + */ + my_bool key_read; + my_bool no_keyread; /* Placeholder for an open table which prevents other connections from taking name-locks on this table. Typically used with |