diff options
| -rw-r--r-- | mysql-test/r/distinct.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/order_by.result | 26 | ||||
| -rw-r--r-- | mysql-test/t/order_by.test | 32 | ||||
| -rw-r--r-- | sql/sql_select.cc | 7 |
4 files changed, 65 insertions, 2 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 15e4c3f15b3..1fbda27f5da 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -694,7 +694,7 @@ INSERT INTO t1(a, b, c) VALUES (1, 1, 1), (1, 2, 3); EXPLAIN SELECT DISTINCT a, b, d, c FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 16 NULL 6 Using index +1 SIMPLE t1 range NULL PRIMARY 16 NULL 7 Using index for group-by; Using temporary SELECT DISTINCT a, b, d, c FROM t1; a b d c 1 1 0 1 diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index faab8e49880..e70ce75797d 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1458,3 +1458,29 @@ ORDER BY t2.c LIMIT 1; d 52.5 DROP TABLE t1,t2,t3; +CREATE TABLE t1 ( +id1 INT NULL, +id2 INT NOT NULL, +junk INT NOT NULL, +PRIMARY KEY (id1, id2, junk), +INDEX id2_j_id1 (id2, junk, id1) +); +INSERT INTO t1 VALUES (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 1, 4); +INSERT INTO t1 VALUES (5, 2, 1), (6, 2, 2), (7, 2, 3), (8, 2, 4); +INSERT INTO t1 VALUES (9, 3, 1), (10, 3, 2), (11, 3, 3), (12, 3, 4); +INSERT INTO t1 VALUES (13, 4, 1), (14, 4, 2), (15, 4, 3), (16, 4, 4); +INSERT INTO t1 VALUES (17, 5, 1), (18, 5, 2), (19, 5, 3), (20, 5, 4); +INSERT INTO t1 VALUES (21, 6, 1), (22, 6, 2), (23, 6, 3), (24, 6, 4); +INSERT INTO t1 VALUES (25, 7, 1), (26, 7, 2), (27, 7, 3), (28, 7, 4); +INSERT INTO t1 VALUES (29, 8, 1), (30, 8, 2), (31, 8, 3), (32, 8, 4); +INSERT INTO t1 VALUES (33, 9, 1), (34, 9, 2), (35, 9, 3), (36, 9, 4); +EXPLAIN SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref id2_j_id1 id2_j_id1 4 const 4 Using where; Using index; Using filesort +SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1; +id1 +13 +14 +15 +16 +DROP TABLE t1; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 64665c6b5bd..bf4cc37a80f 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -1309,3 +1309,35 @@ WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; DROP TABLE t1,t2,t3; + + +# +# Bug#35844: Covering index for ref access not compatible with ORDER BY list +# + +CREATE TABLE t1 ( + id1 INT NULL, + id2 INT NOT NULL, + junk INT NOT NULL, + PRIMARY KEY (id1, id2, junk), + INDEX id2_j_id1 (id2, junk, id1) +); + +INSERT INTO t1 VALUES (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 1, 4); +INSERT INTO t1 VALUES (5, 2, 1), (6, 2, 2), (7, 2, 3), (8, 2, 4); +INSERT INTO t1 VALUES (9, 3, 1), (10, 3, 2), (11, 3, 3), (12, 3, 4); +INSERT INTO t1 VALUES (13, 4, 1), (14, 4, 2), (15, 4, 3), (16, 4, 4); +INSERT INTO t1 VALUES (17, 5, 1), (18, 5, 2), (19, 5, 3), (20, 5, 4); +INSERT INTO t1 VALUES (21, 6, 1), (22, 6, 2), (23, 6, 3), (24, 6, 4); +INSERT INTO t1 VALUES (25, 7, 1), (26, 7, 2), (27, 7, 3), (28, 7, 4); +INSERT INTO t1 VALUES (29, 8, 1), (30, 8, 2), (31, 8, 3), (32, 8, 4); +INSERT INTO t1 VALUES (33, 9, 1), (34, 9, 2), (35, 9, 3), (36, 9, 4); + +EXPLAIN SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1; + +SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1; + +DROP TABLE t1; + + + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 164edacc932..c01aaa18751 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -12966,6 +12966,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, uint tablenr= tab - join->join_tab; ha_rows table_records= table->file->stats.records; bool group= join->group && order == join->group_list; + ha_rows ref_key_quick_rows= HA_POS_ERROR; LINT_INIT(best_key_parts); LINT_INIT(best_key_direction); LINT_INIT(best_records); @@ -12999,6 +13000,9 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, else keys= usable_keys; + if (ref_key >= 0 && table->covering_keys.is_set(ref_key)) + ref_key_quick_rows= table->quick_rows[ref_key]; + read_time= join->best_positions[tablenr].read_time; for (uint i= tablenr+1; i < join->tables; i++) fanout*= join->best_positions[i].records_read; // fanout is always >= 1 @@ -13093,7 +13097,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, index_scan_time < read_time) { ha_rows quick_records= table_records; - if (is_best_covering && !is_covering) + if (is_best_covering && !is_covering || + is_covering && ref_key_quick_rows < select_limit) continue; if (table->quick_keys.is_set(nr)) quick_records= table->quick_rows[nr]; |