Bug#11762052: 54599: BUG IN QUERY PLANNER ON QUERIES WITH

                     "ORDER BY" AND "LIMIT BY" CLAUSE

PROBLEM:
When a 'limit' clause is specified in a query along with
group by and order by, optimizer chooses wrong index
there by examining more number of rows than required.
However without the 'limit' clause, optimizer chooses
the right index.

ANALYSIS:
With respect to the query specified, range optimizer chooses
the first index as there is a range present ( on 'a'). Optimizer
then checks for an index which would give records in sorted
order for the 'group by' clause.

While checking chooses the second index (on 'c,b,a') based on
the 'limit' specified and the selectivity of
'quick_condition_rows' (number of rows present in the range)
in 'test_if_skip_sort_order' function. 
But, it fails to consider that an order by clause on a
different column will result in scanning the entire index and 
hence the estimated number of rows calculated above are 
wrong (which results in choosing the second index).

FIX:
Do not enforce the 'limit' clause in the call to
'test_if_skip_sort_order' if we are creating a temporary
table. Creation of temporary table indicates that there would be
more post-processing and hence will need all the rows.

This fix is backported from 5.6. This problem is fixed in 5.6 as   
part of changes for work log #5558


mysql-test/r/subselect.result:
  Changes for Bug#11762052 results in the correct number of rows.
sql/sql_select.cc:
  Do not pass the actual 'limit' value if 'need_tmp' is true.

1 files changed, 0 insertions, 2 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 26fe129feed..49cf73677b1 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4555,8 +4555,6 @@ SELECT * FROM t1
 WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
 pk	a
 1	10
-3	30
-2	20
 DROP TABLE t1,t2;
 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b));
 INSERT INTO t1 VALUES (1,NULL), (9,NULL);