Bug#12713907:STRANGE OPTIMIZE & WRONG RESULT UNDER

                   ORDER BY COUNT(*) LIMIT.

PROBLEM:
With respect to problem in the bug description, we
exhibit different behaviors for the two tables
presented, because innodb statistics (rec_per_key
in this case) are updated for the first table
and not so for the second one. As a result the
query plan gets changed in test_if_skip_sort_order
to use 'index' scan. Hence the difference in the
explain output. (NOTE: We can reproduce the problem
with first table by reducing the number of tuples
and changing the table structure)

The varied output w.r.t the query on the second table
is because of the result in the query plan change.
When a query plan is changed to use 'index' scan,
after the call to test_if_skip_sort_order, we set
keyread to TRUE immedietly. If for some reason
we drop this index scan for a filesort later on,
we fetch only the keys not the entire tuple.
As a result we would see junk values in the result set.

Following is the code flow:

Call test_if_skip_sort_order
-Choose an index to give sorted output
-If this is a covering index, set_keyread to TRUE
-Set the scan to INDEX scan

Call test_if_skip_sort_order second time
-Index is not chosen (note that we do not pass the
actual limit value second time. Hence we do not choose
index scan second time which in itself is a bug fixed
in 5.6 with WL#5558)
-goto filesort

Call filesort
-Create quick range on a different index
-Since keyread is set to TRUE, we fetch only the columns of
the index
-results in the required columns are not fetched

FIX:
Remove the call to set_keyread(TRUE) from
test_if_skip_sort_order. The access function which is
'join_read_first' or 'join_read_last' calls set_keyread anyways.


mysql-test/r/func_group_innodb.result:
  Added test result for Bug#12713907
mysql-test/t/func_group_innodb.test:
  Added test case for Bug#12713907
sql/sql_select.cc:
  Remove the call to set_keyread as we do it from access
  functions 'join_read_first' and 'join_read_last'

1 files changed, 40 insertions, 0 deletions

diff --git a/mysql-test/r/func_group_innodb.result b/mysql-test/r/func_group_innodb.result
index 908e85c1652..e68242a8191 100644
--- a/mysql-test/r/func_group_innodb.result
+++ b/mysql-test/r/func_group_innodb.result
@@ -145,3 +145,43 @@ select count(*), min(7), max(7) from t2m, t1i;
 count(*)	min(7)	max(7)
 0	NULL	NULL
 drop table t1m, t1i, t2m, t2i;
+#
+# Bug#12713907: STRANGE OPTIMIZE & WRONG RESULT UNDER ORDER BY 
+# COUNT(*) LIMIT.
+#
+CREATE TABLE t1 (
+id BIGINT(20) ,
+member_id_to INT(11) ,
+r_date DATE ,
+PRIMARY KEY (id,r_date),
+KEY r_date_idx (r_date),
+KEY t1_idx01 (member_id_to)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(107924526,518491,'2011-05-01'),
+(107924527,518491,'2011-05-01'),
+(107924528,518491,'2011-05-01'),
+(107924529,518491,'2011-05-01'),
+(107924530,518491,'2011-05-01'),
+(107924531,518491,'2011-05-01'),
+(107924532,518491,'2011-05-01'),
+(107924534,518491,'2011-06-21'),
+(107924535,518491,'2011-06-21'),
+(107924536,518491,'2011-06-21'),
+(107924537,518491,'2011-06-21'),
+(107924538,518491,'2011-06-21'),
+(107924542,1601319,'2011-06-21'),
+(107924543,1601319,'2011-06-21'),
+(107924544,1601319,'2011-06-21'),
+(107924545,1601319,'2011-06-21'),
+(107924546,1601319,'2011-06-21'),
+(107924547,1601319,'2011-06-21'),
+(107924548,1601319,'2011-06-21'),
+(107924549,1601319,'2011-06-21'),
+(107924550,1601319,'2011-06-21');
+SELECT member_id_to, COUNT(*) FROM t1 WHERE r_date =
+'2011-06-21' GROUP BY member_id_to ORDER BY 2 LIMIT 1;
+member_id_to	COUNT(*)
+518491	5
+DROP TABLE t1;
+# End of test  BUG#12713907