diff options
| -rw-r--r-- | mysql-test/r/myisam_explain_non_select_all.result | 16 | ||||
| -rw-r--r-- | mysql-test/r/order_by.result | 13 | ||||
| -rw-r--r-- | mysql-test/r/update.result | 2 | ||||
| -rw-r--r-- | mysql-test/t/order_by.test | 12 | ||||
| -rw-r--r-- | sql/sql_select.cc | 5 |
5 files changed, 36 insertions, 12 deletions
diff --git a/mysql-test/r/myisam_explain_non_select_all.result b/mysql-test/r/myisam_explain_non_select_all.result index 86e3ffbff6c..285a1ca6786 100644 --- a/mysql-test/r/myisam_explain_non_select_all.result +++ b/mysql-test/r/myisam_explain_non_select_all.result @@ -1172,12 +1172,12 @@ INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), # EXPLAIN DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 8 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 5 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 5 100.00 Using where # Status of EXPLAIN EXTENDED query Variable_name Value FLUSH STATUS; @@ -1479,12 +1479,12 @@ INSERT INTO t2 (i) SELECT i FROM t1; # EXPLAIN DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 Using where +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 5 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 5 100.00 Using where # Status of EXPLAIN EXTENDED query Variable_name Value FLUSH STATUS; @@ -1606,12 +1606,12 @@ INSERT INTO t2 (i) SELECT i FROM t1; # EXPLAIN UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 Using where; Using buffer +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 5 Using where; Using buffer FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where; Using buffer +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 5 100.00 Using where; Using buffer # Status of EXPLAIN EXTENDED query Variable_name Value FLUSH STATUS; @@ -1915,12 +1915,12 @@ INSERT INTO t2 (i) SELECT i FROM t1; # EXPLAIN UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 Using where; Using buffer +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 5 Using where; Using buffer FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where; Using buffer +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 5 100.00 Using where; Using buffer # Status of EXPLAIN EXTENDED query Variable_name Value FLUSH STATUS; diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index a8e5cbb295c..294142737d9 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -2936,3 +2936,16 @@ where A.b = B.b order by A.col2, B.col2 limit 10, 1000000; drop table t1,t2,t3; End of 5.5 tests +# +# MDEV-5884: EXPLAIN UPDATE ... ORDER BY LIMIT shows wrong #rows +# +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (key1 int, col1 int, key(key1)); +insert into t1 +select A.a + 10 * B.a + 100 * C.a, 1234 from t2 A, t2 B, t2 C; +# Should show rows=2, not rows=100 +explain update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range key1 key1 5 NULL 2 Using where; Using buffer +drop table t1,t2; diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index bc0f9411d15..9eaf1a46d89 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -615,7 +615,7 @@ select A.a + 10 * B.a + 100 * C.a, 1234 from t2 A, t2 B, t2 C; explain update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 limit 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range key1 key1 5 NULL 100 Using where; Using buffer +1 SIMPLE t1 range key1 key1 5 NULL 2 Using where; Using buffer flush status; update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 limit 2; show status like 'Handler_read%'; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index c4a85e4b111..cf6a4d473c3 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -1947,4 +1947,14 @@ drop table t1,t2,t3; --echo End of 5.5 tests - +--echo # +--echo # MDEV-5884: EXPLAIN UPDATE ... ORDER BY LIMIT shows wrong #rows +--echo # +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (key1 int, col1 int, key(key1)); +insert into t1 +select A.a + 10 * B.a + 100 * C.a, 1234 from t2 A, t2 B, t2 C; +--echo # Should show rows=2, not rows=100 +explain update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 limit 2; +drop table t1,t2; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b88aed1f1bb..6cf8cfc32b5 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -24905,7 +24905,8 @@ uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select, switch (test_if_order_by_key(order, table, select->quick->index, &used_key_parts)) { case 1: // desired order - *need_sort= FALSE; + *need_sort= FALSE; + *scanned_limit= MY_MIN(limit, select->quick->records); return select->quick->index; case 0: // unacceptable order *need_sort= TRUE; @@ -24918,7 +24919,7 @@ uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select, { select->set_quick(reverse_quick); *need_sort= FALSE; - *scanned_limit= select->quick->records; + *scanned_limit= MY_MIN(limit, select->quick->records); return select->quick->index; } else |