MDEV-5884: EXPLAIN UPDATE ... ORDER BY LIMIT shows wrong #rows

- Make get_index_for_order() return correct #rows. 
  changed EXPLAIN outputs are checked - only #rows is different.

3 files changed, 22 insertions, 9 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%';