Fixed bug#28404.

This patch adds cost estimation for the queries with ORDER BY / GROUP BY
and LIMIT. 
If there was a ref/range access to the table whose rows were required
to be ordered in the result set the optimizer always employed this access
though a scan by a different index that was compatible with the required 
order could be cheaper to produce the first L rows of the result set.
Now for such queries the optimizer makes a choice between the cheapest
ref/range accesses not compatible with the given order and index scans
compatible with it.


mysql-test/r/distinct.result:
  Adjusted results for test cases affected fy the fix for bug #28404.
mysql-test/r/endspace.result:
  Adjusted results for test cases affected fy the fix for bug #28404.
mysql-test/r/group_by.result:
  Adjusted results for test cases affected fy the fix for bug #28404.
mysql-test/r/group_min_max.result:
  Adjusted results for test cases affected fy the fix for bug #28404.
mysql-test/r/innodb.result:
  Adjusted results for test cases affected fy the fix for bug #28404.
mysql-test/r/innodb_mysql.result:
  Adjusted results for test cases affected fy the fix for bug #28404.
mysql-test/r/merge.result:
  Adjusted results for test cases affected fy the fix for bug #28404.
mysql-test/r/order_by.result:
  Added a test case for bug #28404.
mysql-test/r/select_found.result:
  Adjusted results for test cases affected fy the fix for bug #28404.
mysql-test/r/subselect.result:
  Adjusted results for test cases affected fy the fix for bug #28404.
mysql-test/t/distinct.test:
  Changed a test case after adding the fix for bug #28404.
mysql-test/t/order_by.test:
  Added a test case for bug #28404.
sql/sql_select.cc:
  Fixed bug#28404.
  This patch adds cost estimation for the queries with ORDER BY / GROUP BY
  and LIMIT. 
  Now for such queries the optimizer makes a choice between the cheapest
  ref/range accesses not compatible with the given order and index scans
  compatible with it.
  
  Modified the function test_if_skip_sort_order to make the above mentioned
  choice cost based.
sql/sql_select.h:
  Fixed bug#28404.
  This patch adds cost estimation for the queries with ORDER BY / GROUP BY
  and LIMIT. 
  Added a new field fot the JOIN_TAB structure.

1 files changed, 37 insertions, 0 deletions

diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 1e520da9f00..fb08dbe74e6 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -739,3 +739,40 @@ INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2);
 EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b;
 
 DROP TABLE t1,t2;
+
+# End of 5.0
+
+#
+# Bug #28404: query with ORDER BY and ref access
+#
+
+CREATE TABLE t1(
+  id int auto_increment PRIMARY KEY, c2 int, c3 int, INDEX k2(c2), INDEX k3(c3));
+
+INSERT INTO t1 (c2,c3) VALUES
+ (31,34),(35,38),(34,31),(32,35),(31,39),
+ (11,14),(15,18),(14,11),(12,15),(11,19);
+
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
+
+SELECT COUNT(*) FROM t1;
+
+EXPLAIN SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 20;
+EXPLAIN SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 100;
+EXPLAIN SELECT id,c3 FROM t1 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 100;
+EXPLAIN SELECT id,c3 FROM t1 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 2000;
+
+SELECT id,c3 FROM t1 WHERE c2=11 ORDER BY c3 LIMIT 20;
+
+DROP TABLE t1;