From: Ole John Aske
Date: January 5 2012 1:30pm
Subject: bzr push into mysql-trunk branch (ole.john.aske:3704 to 3705) WL#5940
List-Archive: http://lists.mysql.com/commits/142299
Message-Id: <20120105133050.DE209235@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3705 Ole John Aske	2012-01-05
      Establish new baseline branch for WL#5940 review: mysqltrunk-wl5949_2:
      
      - There has been lots of refactoring in the optimizer since the original WL5940
        branch was created. We need to refactor the WL5940 patches to due to this.
      
      - One of the inital comments from Evgeny suggested that WL5940 would be cleaner
        if the Abstract Query Plan didn't have to call test_if_skip_sort_order(). Which
        had the sideffect that the query plan could be changed. This required us
        to implement WL5558 first, and then base a simpler wl5940 on this.
      
      This commit add proposed implementation for WL5558 to mysql-trunk and 
      the bugfixes it depends on - Look in wl5558 for a description of these.

    modified:
      mysql-test/include/icp_tests.inc
      mysql-test/include/order_by.inc
      mysql-test/r/group_by.result
      mysql-test/r/group_min_max.result
      mysql-test/r/innodb_icp.result
      mysql-test/r/innodb_icp_none.result
      mysql-test/r/innodb_mrr.result
      mysql-test/r/innodb_mrr_cost.result
      mysql-test/r/innodb_mrr_cost_icp.result
      mysql-test/r/innodb_mrr_icp.result
      mysql-test/r/innodb_mrr_none.result
      mysql-test/r/join_cache_bka.result
      mysql-test/r/join_cache_bka_nixbnl.result
      mysql-test/r/join_cache_bkaunique.result
      mysql-test/r/join_cache_bnl.result
      mysql-test/r/join_cache_nojb.result
      mysql-test/r/myisam_icp.result
      mysql-test/r/myisam_icp_none.result
      mysql-test/r/order_by_icp_mrr.result
      mysql-test/r/order_by_none.result
      mysql-test/r/select_found.result
      mysql-test/r/subquery_mat_none.result
      mysql-test/suite/opt_trace/r/bugs_no_prot_none.result
      mysql-test/suite/opt_trace/r/range_no_prot.result
      sql/opt_range.cc
      sql/records.cc
      sql/sql_executor.cc
      sql/sql_optimizer.cc
      sql/sql_optimizer.h
      sql/sql_select.cc
 3704 Anitha Gopi	2012-01-05
      Bug#12682554 - FAILED ASSERT AT HANDLER0ALTER.CC IN MANY TESTS 
      Removed tests from experimental group

    modified:
      mysql-test/collections/default.experimental
=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc	2011-10-05 13:16:38 +0000
+++ b/mysql-test/include/icp_tests.inc	2012-01-05 13:13:15 +0000
@@ -1043,7 +1043,7 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
 
 let $query=
 SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
 WHERE t2.pk < 7 AND t2.col_int_key <> 7
 GROUP BY field1;
 

=== modified file 'mysql-test/include/order_by.inc'
--- a/mysql-test/include/order_by.inc	2011-12-19 09:22:28 +0000
+++ b/mysql-test/include/order_by.inc	2012-01-05 13:13:15 +0000
@@ -1750,3 +1750,63 @@ INSERT INTO t1 VALUES (1), (2);
 INSERT INTO t2 VALUES (1,2), (2,3);
 SELECT (SELECT 1 FROM t1 WHERE a=b AND c=1 ORDER BY a DESC) FROM t2;
 DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug #13528826 
+--echo # TEST_IF_CHEAPER_ORDERING(): CALCULATES INCORRECT 'SELECT_LIMIT'
+--echo # 
+--echo #
+
+CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
+INSERT INTO t1 VALUES
+  (5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
+CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
+INSERT INTO t2 VALUES
+  (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
+  (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
+
+--echo # number of rows in t1 was incorrectly used as an 
+--echo # implicit limit-clause if not explicit specified
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
+
+--echo # Query above used to be explained identical to this:
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
+
+-- echo # A really high limit was required to give the correct explain
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000;
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug #13531865
+--echo # TEST_IF_SKIP_SORT_ORDER() INCORRECTLY SKIP FILESORT IF
+--echo # 'TYPE' IS REF_OR_NULL 
+--echo # 
+--echo #
+
+CREATE TABLE t1 (
+  a INT,
+  c INT,
+  UNIQUE KEY a_c (a,c),
+  KEY (a)) engine=myisam;
+
+INSERT INTO t1 VALUES (1,10), (2,NULL), (2,10);
+ANALYZE TABLE t1;
+
+--echo # Using 'KEY a_c' for order-by opt, would have required 
+--echo # REF_OR_NULL access which never can be order_by skipped.
+--echo # -> Keep initial REF on 'KEY a' selected by cond. optimizer
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL);
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+
+DROP TABLE t1;

=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/group_by.result	2012-01-05 13:13:15 +0000
@@ -1340,10 +1340,10 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	144	
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index; Using filesort
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index; Using filesort
 SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
 a
 1
@@ -2165,7 +2165,7 @@ INSERT INTO t1 VALUES (1),(2),(3),(4),(5
 EXPLAIN SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
 FROM t1 GROUP BY field1, field2;;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	idx	5	NULL	6	Using index for group-by; Using filesort
+1	SIMPLE	t1	range	NULL	idx	5	NULL	6	Using index for group-by
 SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
 FROM t1 GROUP BY field1, field2;;
 field1	field2

=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/group_min_max.result	2012-01-05 13:13:15 +0000
@@ -2256,7 +2256,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHE
 a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1_outer	index	NULL	a	10	NULL	15	Using where; Using index
-2	DEPENDENT SUBQUERY	t1	index	NULL	a	10	NULL	1	Using index
+2	DEPENDENT SUBQUERY	t1	index	NULL	a	10	NULL	15	Using index
 EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING 
 a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	2011-12-01 14:12:10 +0000
+++ b/mysql-test/r/innodb_icp.result	2012-01-05 13:13:15 +0000
@@ -988,14 +988,14 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
 set @old_opt_switch=@@optimizer_switch;
 SET optimizer_switch="block_nested_loop=off";
 EXPLAIN SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
 WHERE t2.pk < 7 AND t2.col_int_key <> 7
 GROUP BY field1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	PRIMARY,col_int_key	col_int_key	5	NULL	5	Using where
+1	SIMPLE	t2	range	col_int_key	col_int_key	5	NULL	5	Using where; Using index
 1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	4	Using index
 SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
 WHERE t2.pk < 7 AND t2.col_int_key <> 7
 GROUP BY field1;
 field1

=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result	2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/innodb_icp_none.result	2012-01-05 13:13:15 +0000
@@ -987,14 +987,14 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
 set @old_opt_switch=@@optimizer_switch;
 SET optimizer_switch="block_nested_loop=off";
 EXPLAIN SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
 WHERE t2.pk < 7 AND t2.col_int_key <> 7
 GROUP BY field1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	PRIMARY,col_int_key	col_int_key	5	NULL	5	Using where
+1	SIMPLE	t2	range	col_int_key	col_int_key	5	NULL	5	Using where; Using index
 1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	4	Using index
 SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
 WHERE t2.pk < 7 AND t2.col_int_key <> 7
 GROUP BY field1;
 field1

=== modified file 'mysql-test/r/innodb_mrr.result'
--- a/mysql-test/r/innodb_mrr.result	2011-11-23 19:55:56 +0000
+++ b/mysql-test/r/innodb_mrr.result	2012-01-05 13:13:15 +0000
@@ -539,7 +539,7 @@ WHERE t2.i1 > 5
 AND t2.pk IS  NULL
 ORDER BY i1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	
 1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using where
 SELECT i1
 FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2

=== modified file 'mysql-test/r/innodb_mrr_cost.result'
--- a/mysql-test/r/innodb_mrr_cost.result	2011-11-23 19:55:56 +0000
+++ b/mysql-test/r/innodb_mrr_cost.result	2012-01-05 13:13:15 +0000
@@ -539,7 +539,7 @@ WHERE t2.i1 > 5
 AND t2.pk IS  NULL
 ORDER BY i1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	
 1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using where
 SELECT i1
 FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2

=== modified file 'mysql-test/r/innodb_mrr_cost_icp.result'
--- a/mysql-test/r/innodb_mrr_cost_icp.result	2011-12-01 14:12:10 +0000
+++ b/mysql-test/r/innodb_mrr_cost_icp.result	2012-01-05 13:13:15 +0000
@@ -539,7 +539,7 @@ WHERE t2.i1 > 5
 AND t2.pk IS  NULL
 ORDER BY i1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	
 1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using where
 SELECT i1
 FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2

=== modified file 'mysql-test/r/innodb_mrr_icp.result'
--- a/mysql-test/r/innodb_mrr_icp.result	2011-12-01 14:12:10 +0000
+++ b/mysql-test/r/innodb_mrr_icp.result	2012-01-05 13:13:15 +0000
@@ -539,7 +539,7 @@ WHERE t2.i1 > 5
 AND t2.pk IS  NULL
 ORDER BY i1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	
 1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using where
 SELECT i1
 FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2

=== modified file 'mysql-test/r/innodb_mrr_none.result'
--- a/mysql-test/r/innodb_mrr_none.result	2011-11-23 19:55:56 +0000
+++ b/mysql-test/r/innodb_mrr_none.result	2012-01-05 13:13:15 +0000
@@ -538,7 +538,7 @@ WHERE t2.i1 > 5
 AND t2.pk IS  NULL
 ORDER BY i1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	
 1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using where
 SELECT i1
 FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2

=== modified file 'mysql-test/r/join_cache_bka.result'
--- a/mysql-test/r/join_cache_bka.result	2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/join_cache_bka.result	2012-01-05 13:13:15 +0000
@@ -2233,7 +2233,7 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)

=== modified file 'mysql-test/r/join_cache_bka_nixbnl.result'
--- a/mysql-test/r/join_cache_bka_nixbnl.result	2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/join_cache_bka_nixbnl.result	2012-01-05 13:13:15 +0000
@@ -2233,7 +2233,7 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)

=== modified file 'mysql-test/r/join_cache_bkaunique.result'
--- a/mysql-test/r/join_cache_bkaunique.result	2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/join_cache_bkaunique.result	2012-01-05 13:13:15 +0000
@@ -2234,7 +2234,7 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)

=== modified file 'mysql-test/r/join_cache_bnl.result'
--- a/mysql-test/r/join_cache_bnl.result	2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/join_cache_bnl.result	2012-01-05 13:13:15 +0000
@@ -2216,7 +2216,7 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	ref	col_int_key	col_int_key	5	const	1	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1,t2
@@ -2234,7 +2234,7 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)

=== modified file 'mysql-test/r/join_cache_nojb.result'
--- a/mysql-test/r/join_cache_nojb.result	2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/join_cache_nojb.result	2012-01-05 13:13:15 +0000
@@ -1445,7 +1445,7 @@ explain
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index
 1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -2216,7 +2216,7 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	ref	col_int_key	col_int_key	5	const	1	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1,t2
@@ -2234,7 +2234,7 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)

=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result	2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/myisam_icp.result	2012-01-05 13:13:15 +0000
@@ -983,14 +983,14 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
 set @old_opt_switch=@@optimizer_switch;
 SET optimizer_switch="block_nested_loop=off";
 EXPLAIN SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
 WHERE t2.pk < 7 AND t2.col_int_key <> 7
 GROUP BY field1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index; Using temporary; Using filesort
-1	SIMPLE	t2	ALL	PRIMARY,col_int_key	NULL	NULL	NULL	6	Using where
+1	SIMPLE	t2	ALL	col_int_key	NULL	NULL	NULL	6	Using where; Using temporary; Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index
 SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
 WHERE t2.pk < 7 AND t2.col_int_key <> 7
 GROUP BY field1;
 field1

=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result	2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/myisam_icp_none.result	2012-01-05 13:13:15 +0000
@@ -982,14 +982,14 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
 set @old_opt_switch=@@optimizer_switch;
 SET optimizer_switch="block_nested_loop=off";
 EXPLAIN SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
 WHERE t2.pk < 7 AND t2.col_int_key <> 7
 GROUP BY field1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index; Using temporary; Using filesort
-1	SIMPLE	t2	ALL	PRIMARY,col_int_key	NULL	NULL	NULL	6	Using where
+1	SIMPLE	t2	ALL	col_int_key	NULL	NULL	NULL	6	Using where; Using temporary; Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index
 SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
 WHERE t2.pk < 7 AND t2.col_int_key <> 7
 GROUP BY field1;
 field1

=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result	2011-12-19 09:22:28 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result	2012-01-05 13:13:15 +0000
@@ -1461,7 +1461,7 @@ INSERT INTO t1 VALUES (1, 10), (2, NULL)
 EXPLAIN
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref_or_null	a_c,a	a_c	10	const,const	1	Using where
+1	SIMPLE	t1	ref	a_c,a	a	5	const	1	Using where; Using filesort
 # Must return 1 row
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
 col
@@ -1470,7 +1470,7 @@ col
 EXPLAIN
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-x	x	x	ref_or_null	a_c,a	x	x	x	x	x
+x	x	x	ref	a_c,a	x	x	x	x	x
 # Must return 1 row
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
 col
@@ -2600,4 +2600,75 @@ SELECT (SELECT 1 FROM t1 WHERE a=b AND c
 NULL
 NULL
 DROP TABLE t1, t2;
+#
+# Bug #13528826 
+# TEST_IF_CHEAPER_ORDERING(): CALCULATES INCORRECT 'SELECT_LIMIT'
+# 
+#
+CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
+INSERT INTO t1 VALUES
+(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
+CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
+INSERT INTO t2 VALUES
+(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
+(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
+# number of rows in t1 was incorrectly used as an 
+# implicit limit-clause if not explicit specified
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index
+1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using index
+# Query above used to be explained identical to this:
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index
+1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using index
+# A really high limit was required to give the correct explain
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index
+1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using index
+DROP TABLE t1, t2;
+#
+# Bug #13531865
+# TEST_IF_SKIP_SORT_ORDER() INCORRECTLY SKIP FILESORT IF
+# 'TYPE' IS REF_OR_NULL 
+# 
+#
+CREATE TABLE t1 (
+a INT,
+c INT,
+UNIQUE KEY a_c (a,c),
+KEY (a)) engine=myisam;
+INSERT INTO t1 VALUES (1,10), (2,NULL), (2,10);
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+# Using 'KEY a_c' for order-by opt, would have required 
+# REF_OR_NULL access which never can be order_by skipped.
+# -> Keep initial REF on 'KEY a' selected by cond. optimizer
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a_c,a	a	5	const	1	Using where
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a_c,a	a	5	const	1	Using where; Using filesort
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+c
+NULL
+10
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a_c,a	a	5	const	1	Using where; Using filesort
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+c
+10
+NULL
+DROP TABLE t1;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/order_by_none.result'
--- a/mysql-test/r/order_by_none.result	2011-12-19 09:22:28 +0000
+++ b/mysql-test/r/order_by_none.result	2012-01-05 13:13:15 +0000
@@ -1460,7 +1460,7 @@ INSERT INTO t1 VALUES (1, 10), (2, NULL)
 EXPLAIN
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref_or_null	a_c,a	a_c	10	const,const	1	Using where
+1	SIMPLE	t1	ref	a_c,a	a	5	const	1	Using where; Using filesort
 # Must return 1 row
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
 col
@@ -1469,7 +1469,7 @@ col
 EXPLAIN
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-x	x	x	ref_or_null	a_c,a	x	x	x	x	x
+x	x	x	ref	a_c,a	x	x	x	x	x
 # Must return 1 row
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
 col
@@ -2599,4 +2599,75 @@ SELECT (SELECT 1 FROM t1 WHERE a=b AND c
 NULL
 NULL
 DROP TABLE t1, t2;
+#
+# Bug #13528826 
+# TEST_IF_CHEAPER_ORDERING(): CALCULATES INCORRECT 'SELECT_LIMIT'
+# 
+#
+CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
+INSERT INTO t1 VALUES
+(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
+CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
+INSERT INTO t2 VALUES
+(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
+(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
+# number of rows in t1 was incorrectly used as an 
+# implicit limit-clause if not explicit specified
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index
+1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using index
+# Query above used to be explained identical to this:
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index
+1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using index
+# A really high limit was required to give the correct explain
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index
+1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using index
+DROP TABLE t1, t2;
+#
+# Bug #13531865
+# TEST_IF_SKIP_SORT_ORDER() INCORRECTLY SKIP FILESORT IF
+# 'TYPE' IS REF_OR_NULL 
+# 
+#
+CREATE TABLE t1 (
+a INT,
+c INT,
+UNIQUE KEY a_c (a,c),
+KEY (a)) engine=myisam;
+INSERT INTO t1 VALUES (1,10), (2,NULL), (2,10);
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+# Using 'KEY a_c' for order-by opt, would have required 
+# REF_OR_NULL access which never can be order_by skipped.
+# -> Keep initial REF on 'KEY a' selected by cond. optimizer
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a_c,a	a	5	const	1	Using where
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a_c,a	a	5	const	1	Using where; Using filesort
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+c
+NULL
+10
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a_c,a	a	5	const	1	Using where; Using filesort
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+c
+10
+NULL
+DROP TABLE t1;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/select_found.result'
--- a/mysql-test/r/select_found.result	2011-06-27 07:17:26 +0000
+++ b/mysql-test/r/select_found.result	2012-01-05 13:13:15 +0000
@@ -84,19 +84,19 @@ UNIQUE KEY e_n (email,name)
 EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1  ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	system	kid	NULL	NULL	NULL	0	const row not found
-1	SIMPLE	t2	index	NULL	e_n	104	NULL	10	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	200	Using temporary
 SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1  ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
 email
 email1
+email2
+email3
+email4
+email5
+email6
+email7
+email8
+email9
 email10
-email100
-email101
-email102
-email103
-email104
-email105
-email106
-email107
 SELECT FOUND_ROWS();
 FOUND_ROWS()
 200

=== modified file 'mysql-test/r/subquery_mat_none.result'
--- a/mysql-test/r/subquery_mat_none.result	2011-11-25 10:39:07 +0000
+++ b/mysql-test/r/subquery_mat_none.result	2012-01-05 13:13:15 +0000
@@ -137,7 +137,7 @@ explain extended
 select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-2	DEPENDENT SUBQUERY	t2i	index	NULL	it2i3	18	NULL	3	100.00	Using index
+2	DEPENDENT SUBQUERY	t2i	index	NULL	it2i3	18	NULL	5	60.00	Using index
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` group by `test`.`t2i`.`b1` having (((<cache>(`test`.`t1`.`a1`) = `test`.`t2i`.`b1`) or isnull(`test`.`t2i`.`b1`)) and ((<cache>(`test`.`t1`.`a2`) = max(`test`.`t2i`.`b2`)) or isnull(max(`test`.`t2i`.`b2`))) and <is_not_null_test>(`test`.`t2i`.`b1`) and <is_not_null_test>(max(`test`.`t2i`.`b2`)))))
 select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
@@ -148,11 +148,11 @@ prepare st1 from "explain select * from 
 execute st1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	t2i	index	NULL	it2i3	18	NULL	3	Using index
+2	DEPENDENT SUBQUERY	t2i	index	NULL	it2i3	18	NULL	5	Using index
 execute st1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	t2i	index	NULL	it2i3	18	NULL	3	Using index
+2	DEPENDENT SUBQUERY	t2i	index	NULL	it2i3	18	NULL	5	Using index
 prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
 execute st2;
 a1	a2
@@ -762,7 +762,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	9	Using where
-2	DEPENDENT SUBQUERY	t1	index	NULL	a	8	NULL	1	Using filesort
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES (1),(2);

=== modified file 'mysql-test/suite/opt_trace/r/bugs_no_prot_none.result'
--- a/mysql-test/suite/opt_trace/r/bugs_no_prot_none.result	2011-11-29 08:41:50 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_no_prot_none.result	2012-01-05 13:13:15 +0000
@@ -1779,14 +1779,7 @@ SELECT 1 FROM t1 WHERE 1 LIKE
                             "table": "t1"
                           }
                         ] /* refine_plan */
-                      }
-                    ] /* steps */
-                  } /* join_optimization */
-                },
-                {
-                  "join_execution": {
-                    "select#": 2,
-                    "steps": [
+                      },
                       {
                         "reconsidering_access_paths_for_index_ordering": {
                           "index_order_summary": {
@@ -1800,6 +1793,13 @@ SELECT 1 FROM t1 WHERE 1 LIKE
                         } /* reconsidering_access_paths_for_index_ordering */
                       }
                     ] /* steps */
+                  } /* join_optimization */
+                },
+                {
+                  "join_execution": {
+                    "select#": 2,
+                    "steps": [
+                    ] /* steps */
                   } /* join_execution */
                 }
               ] /* steps */

=== modified file 'mysql-test/suite/opt_trace/r/range_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/range_no_prot.result	2011-12-01 14:12:10 +0000
+++ b/mysql-test/suite/opt_trace/r/range_no_prot.result	2012-01-05 13:13:15 +0000
@@ -875,10 +875,6 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
       "join_explain": {
         "select#": 1,
         "steps": [
-          {
-            "reconsidering_access_paths_for_index_ordering": {
-            } /* reconsidering_access_paths_for_index_ordering */
-          }
         ] /* steps */
       } /* join_explain */
     }
@@ -1410,10 +1406,6 @@ GROUP BY key2	{
       "join_explain": {
         "select#": 1,
         "steps": [
-          {
-            "reconsidering_access_paths_for_index_ordering": {
-            } /* reconsidering_access_paths_for_index_ordering */
-          }
         ] /* steps */
       } /* join_explain */
     }
@@ -4618,14 +4610,7 @@ EXPLAIN SELECT * FROM t1 WHERE i1 > '2' 
                 "access_type": "range"
               }
             ] /* refine_plan */
-          }
-        ] /* steps */
-      } /* join_optimization */
-    },
-    {
-      "join_explain": {
-        "select#": 1,
-        "steps": [
+          },
           {
             "reconsidering_access_paths_for_index_ordering": {
               "rows_estimation": {
@@ -4704,6 +4689,13 @@ EXPLAIN SELECT * FROM t1 WHERE i1 > '2' 
             } /* reconsidering_access_paths_for_index_ordering */
           }
         ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_explain": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
       } /* join_explain */
     }
   ] /* steps */
@@ -5044,18 +5036,6 @@ EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE
       "join_explain": {
         "select#": 1,
         "steps": [
-          {
-            "reconsidering_access_paths_for_index_ordering": {
-              "index_order_summary": {
-                "database": "test",
-                "table": "t1",
-                "index": "k1",
-                "order_direction": "desc",
-                "plan_changed": true,
-                "access_type": "index_scan"
-              } /* index_order_summary */
-            } /* reconsidering_access_paths_for_index_ordering */
-          }
         ] /* steps */
       } /* join_explain */
     }
@@ -6193,14 +6173,7 @@ EXPLAIN SELECT * FROM t1 WHERE c1 = '1' 
                 "table_condition_attached": null
               }
             ] /* refine_plan */
-          }
-        ] /* steps */
-      } /* join_optimization */
-    },
-    {
-      "join_explain": {
-        "select#": 1,
-        "steps": [
+          },
           {
             "reconsidering_access_paths_for_index_ordering": {
               "index_order_summary": {
@@ -6215,6 +6188,13 @@ EXPLAIN SELECT * FROM t1 WHERE c1 = '1' 
             } /* reconsidering_access_paths_for_index_ordering */
           }
         ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_explain": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
       } /* join_explain */
     }
   ] /* steps */

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2011-12-15 16:15:27 +0000
+++ b/sql/opt_range.cc	2012-01-05 13:13:15 +0000
@@ -1342,7 +1342,6 @@ QUICK_RANGE_SELECT::~QUICK_RANGE_SELECT(
     if (file) 
     {
       range_end();
-      head->set_keyread(FALSE);
       if (free_file)
       {
         DBUG_PRINT("info", ("Freeing separate handler %p (free: %d)", file,

=== modified file 'sql/records.cc'
--- a/sql/records.cc	2011-12-14 12:32:55 +0000
+++ b/sql/records.cc	2012-01-05 13:13:15 +0000
@@ -325,6 +325,10 @@ void end_read_record(READ_RECORD *info)
     my_free_lock(info->cache);
     info->cache=0;
   }
+  if (info->table && info->table->key_read)
+  {
+    info->table->set_keyread(FALSE);
+  }
   if (info->table && info->table->created)
   {
     filesort_free_buffers(info->table,0);

=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc	2011-12-19 09:53:30 +0000
+++ b/sql/sql_executor.cc	2012-01-05 13:13:15 +0000
@@ -118,11 +118,6 @@ static bool setup_copy_fields(THD *thd, 
   Execute select, executor entry point.
 
   @todo
-    Note, that create_sort_index calls test_if_skip_sort_order and may
-    finally replace sorting with index scan if there is a LIMIT clause in
-    the query.  It's never shown in EXPLAIN!
-
-  @todo
     When can we have here thd->net.report_error not zero?
 */
 
@@ -612,6 +607,9 @@ JOIN::execute(JOIN *parent)
           Note: here we call make_cond_for_table() a second time in order
           to get sort_table_cond. An alternative could be to use
           Item::copy_andor_structure() to make a copy of sort_table_cond.
+
+          TODO: This is now obsolete as test_if_skip_sort_order()
+                is not any longer called as part of JOIN::execute() !
         */
         if (curr_table->pre_idx_push_cond)
         {
@@ -670,9 +668,6 @@ JOIN::execute(JOIN *parent)
 	Here we sort rows for ORDER BY/GROUP BY clause, if the optimiser
 	chose FILESORT to be faster than INDEX SCAN or there is no 
 	suitable index present.
-	Note, that create_sort_index calls test_if_skip_sort_order and may
-	finally replace sorting with index scan if there is a LIMIT clause in
-	the query. XXX: it's never shown in EXPLAIN!
 	OPTION_FOUND_ROWS supersedes LIMIT and is taken into account.
       */
       DBUG_PRINT("info",("Sorting for order by/group by"));
@@ -944,11 +939,9 @@ JOIN::optimize_distinct()
   if (order && skip_sort_order)
   {
     /* Should always succeed */
-    if (test_if_skip_sort_order(&join_tab[const_tables],
-                                order, unit->select_limit_cnt, false, 
-                                &join_tab[const_tables].table->
-                                keys_in_use_for_order_by))
-      order= NULL;
+    DBUG_ASSERT(ordered_index_usage==ordered_index_order_by);
+    if (ordered_index_usage==ordered_index_order_by)
+      order=NULL;
   }
 }
 
@@ -3779,19 +3772,22 @@ create_sort_index(THD *thd, JOIN *join, 
   select= tab->select;
 
   /*
-    When there is SQL_BIG_RESULT do not sort using index for GROUP BY,
-    and thus force sorting on disk unless a group min-max optimization
-    is going to be used as it is applied now only for one table queries
-    with covering indexes.
+    JOIN::optimize may have prepared an access patch which makes
+    either the GROUP BY or ORDER BY sorting obsolete by using an
+    ordered index for the access. If the requested 'order' match
+    the prepared 'ordered_index_usage', we don't have to build 
+    a temporary sort index now.
   */
-  if ((order != join->group_list || 
-       !(join->select_options & SELECT_BIG_RESULT) ||
-       (select && select->quick &&
-        select->quick->get_type() == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)) &&
-      test_if_skip_sort_order(tab,order,select_limit,0, 
-                              is_order_by ?  &table->keys_in_use_for_order_by :
-                              &table->keys_in_use_for_group_by))
-    DBUG_RETURN(0);
+  {
+    DBUG_ASSERT((is_order_by) == (order==join->order));  // Obsolete arg !
+    const bool is_skippable=
+      (is_order_by) ? join->simple_order && join->ordered_index_usage==JOIN::ordered_index_order_by
+                    : join->simple_group && join->ordered_index_usage==JOIN::ordered_index_group_by;
+
+    if (is_skippable)
+      DBUG_RETURN(0);
+  }
+
   for (ORDER *ord= join->order; ord; ord= ord->next)
     length++;
   if (!(join->sortorder= 

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2011-12-15 17:14:28 +0000
+++ b/sql/sql_optimizer.cc	2012-01-05 13:13:15 +0000
@@ -570,8 +570,10 @@ JOIN::optimize()
     JOIN_TAB *tab= &join_tab[const_tables];
     bool all_order_fields_used;
     if (order)
+    {
       skip_sort_order= test_if_skip_sort_order(tab, order, m_select_limit, 1, 
         &tab->table->keys_in_use_for_order_by);
+    }
     if ((group_list=create_distinct_group(thd, ref_ptrs,
                                           order, fields_list, all_fields,
 				          &all_order_fields_used)))
@@ -791,6 +793,26 @@ JOIN::optimize()
 
   if (const_tables != tables)
   {
+    JOIN_TAB *tab= &join_tab[const_tables];
+
+    if (order)
+    {
+      /*
+        Force using of tmp table if sorting by a SP or UDF function due to
+        their expensive and probably non-deterministic nature.
+      */
+      for (ORDER *tmp_order= order; tmp_order ; tmp_order=tmp_order->next)
+      {
+        Item *item= *tmp_order->item;
+        if (item->is_expensive())
+        {
+          /* Force tmp table without sort */
+          need_tmp=1; simple_order=simple_group=0;
+          break;
+        }
+      }
+    }
+
     /*
       Because filesort always does a full table scan or a quick range scan
       we must add the removed reference to the select for the table.
@@ -798,54 +820,78 @@ JOIN::optimize()
       as in other cases the join is done before the sort.
     */
     if ((order || group_list) &&
-        join_tab[const_tables].type != JT_ALL &&
-        join_tab[const_tables].type != JT_FT &&
-        join_tab[const_tables].type != JT_REF_OR_NULL &&
+        tab->type != JT_ALL &&
+        tab->type != JT_FT &&
+        tab->type != JT_REF_OR_NULL &&
         ((order && simple_order) || (group_list && simple_group)))
     {
-      if (add_ref_to_table_cond(thd,&join_tab[const_tables])) {
+      if (add_ref_to_table_cond(thd,tab)) {
         DBUG_RETURN(1);
       }
     }
     
-    if (!(select_options & SELECT_BIG_RESULT) &&
-        ((group_list &&
-          (!simple_group ||
-           !test_if_skip_sort_order(&join_tab[const_tables], group_list,
-                                    unit->select_limit_cnt, 0, 
-                                    &join_tab[const_tables].table->
-                                    keys_in_use_for_group_by))) ||
-         select_distinct) &&
-        tmp_table_param.quick_group && !procedure)
-    {
-      need_tmp=1; simple_order=simple_group=0;	// Force tmp table without sort
-    }
-    if (order)
+    /*
+      Investigate whether we may use an ordered index as part of either
+      DISTINCT, GROUP BY or ORDER BY execution. An ordered index may be
+      used for only the first of any of these terms to be executed. This
+      is reflected in the order which we check for test_if_skip_sort_order()
+      below. However we do not check for DISTINCT here, as it would have
+      been transformed to a GROUP BY at this stage if it is a candidate for 
+      ordered index optimization.
+      If a decision was made to use an ordered index, the availability
+      if such an access path is stored in 'ordered_index_usage' for later
+      use by 'execute' or 'explain'
+    */
+    DBUG_ASSERT(ordered_index_usage==ordered_index_void);
+
+    if (group_list)   // GROUP BY honoured first
+                      // (DISTINCT was rewritten to GROUP BY if skippable)
     {
       /*
-        Do we need a temporary table due to the ORDER BY not being equal to
-        the GROUP BY? The call to test_if_skip_sort_order above tests for the
-        GROUP BY clause only and hence is not valid in this case. So the
-        estimated number of rows to be read from the first table is not valid.
-        We clear it here so that it doesn't show up in EXPLAIN.
-       */
-      if (need_tmp && (select_options & SELECT_DESCRIBE) != 0)
-        join_tab[const_tables].limit= 0;
-      /*
-        Force using of tmp table if sorting by a SP or UDF function due to
-        their expensive and probably non-deterministic nature.
+        When there is SQL_BIG_RESULT do not sort using index for GROUP BY,
+        and thus force sorting on disk unless a group min-max optimization
+        is going to be used as it is applied now only for one table queries
+        with covering indexes.
       */
-      for (ORDER *tmp_order= order; tmp_order ; tmp_order=tmp_order->next)
+      if (!(select_options & SELECT_BIG_RESULT) ||
+            (tab->select &&
+             tab->select->quick &&
+             tab->select->quick->get_type() == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX))
       {
-        Item *item= *tmp_order->item;
-        if (item->is_expensive())
+        if (simple_group &&              // GROUP BY is possibly skippable
+            !select_distinct)            // .. if not preceded by a DISTINCT
         {
-          /* Force tmp table without sort */
-          need_tmp=1; simple_order=simple_group=0;
-          break;
+          /*
+            Calculate a possible 'limit' of table rows for 'GROUP BY':
+            A specified 'LIMIT' is relative to the final resultset.
+            'need_tmp' implies that there will be more postprocessing 
+            so the specified 'limit' should not be enforced yet.
+           */
+          const ha_rows limit = (!need_tmp) ? m_select_limit : HA_POS_ERROR;
+
+          if (test_if_skip_sort_order(tab, group_list, limit, false, 
+                                      &tab->table->keys_in_use_for_group_by))
+          {
+            ordered_index_usage= ordered_index_group_by;
+          }
+        }
+
+        if ((ordered_index_usage != ordered_index_group_by) &&
+            tmp_table_param.quick_group && !procedure)
+        {
+          need_tmp=1; simple_order=simple_group=0;   // Force tmp table without sort
         }
       }
     }
+    else if (order &&                                // ORDER BY wo/ preceeding GROUP BY
+             (simple_order || skip_sort_order))      // which is possibly skippable
+    {
+      if (test_if_skip_sort_order(tab, order, m_select_limit, false, 
+                                  &tab->table->keys_in_use_for_order_by))
+      {
+        ordered_index_usage= ordered_index_order_by;
+      }
+    }
   }
 
   tmp_having= having;

=== modified file 'sql/sql_optimizer.h'
--- a/sql/sql_optimizer.h	2011-12-15 09:00:42 +0000
+++ b/sql/sql_optimizer.h	2012-01-05 13:13:15 +0000
@@ -181,6 +181,19 @@ public:
     GROUP/ORDER BY.
   */
   bool simple_order, simple_group;
+
+  /*
+    ordered_index_usage is set if an ordered index access
+    should be used instead of a filesort when computing 
+    ORDER/GROUP BY.
+  */
+  enum
+  {
+    ordered_index_void,       // No ordered index avail.
+    ordered_index_group_by,   // Use index for GROUP BY
+    ordered_index_order_by,   // Use index for ORDER BY
+  } ordered_index_usage;
+
   /**
     Is set only in case if we have a GROUP BY clause
     and no ORDER BY after constant elimination of 'order'.
@@ -317,6 +330,7 @@ public:
     no_order= 0;
     simple_order= 0;
     simple_group= 0;
+    ordered_index_usage= ordered_index_void;
     skip_sort_order= 0;
     need_tmp= 0;
     hidden_group_fields= 0; /*safety*/

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-12-19 09:53:30 +0000
+++ b/sql/sql_select.cc	2012-01-05 13:13:15 +0000
@@ -856,6 +856,7 @@ JOIN::explain()
     table to resolve ORDER BY: in that case, we only may need to do
     filesort for GROUP BY.
   */
+  bool is_order_by= true;
   if (!order && !no_order && (!skip_sort_order || !need_tmp))
   {
     /*
@@ -865,24 +866,35 @@ JOIN::explain()
     order= group_list;
     simple_order= simple_group;
     skip_sort_order= 0;
+    is_order_by= false;
   }
-  if (order && 
-      (order != group_list || !(select_options & SELECT_BIG_RESULT)) &&
-      (const_tables == tables ||
-       ((simple_order || skip_sort_order) &&
-        test_if_skip_sort_order(&join_tab[const_tables], order,
-                                m_select_limit, 0, 
-                                &join_tab[const_tables].table->
-                                keys_in_use_for_query))))
-    order=0;
+
   having= tmp_having;
   if (tables)
+  {
+    /*
+      JOIN::optimize may have prepared an access patch which makes
+      either the GROUP BY or ORDER BY sorting obsolete by using an
+      ordered index for the access. If the required 'order' match
+      the available 'ordered_index_usage' we will use ordered index
+      access instead of doing a filesort.
+
+      NOTE: This code is intentional similar to 'is_skippable' code
+            in create_sort_index() which is the ::execute()
+            counterpart of what we 'explain' here.
+    */
+    const bool is_skippable=
+      (is_order_by) ? simple_order && ordered_index_usage==ordered_index_order_by
+                    : simple_group && ordered_index_usage==ordered_index_group_by;
+
     explain_query_specification(thd, this, need_tmp,
-                                order != 0 && !skip_sort_order,
+                                (order!=NULL) && !is_skippable,
                                 select_distinct);
+  }
   else
+  {
     explain_no_table(thd, this, "No tables used");
-
+  }
   DBUG_VOID_RETURN;
 }
 
@@ -3572,6 +3584,38 @@ is_subkey(KEY_PART_INFO *key_part, KEY_P
 }
 
 /**
+  Test if REF_OR_NULL optimization will be used if the specified
+  ref_key is used for REF-access to 'tab'
+
+  @retval
+    true	JT_REF_OR_NULL we be used
+  @retval
+    false	no JT_REF_OR_NULL access
+*/
+bool
+is_ref_or_null_optimized(const JOIN_TAB *tab, uint ref_key)
+{
+  if (tab->keyuse)
+  {
+    const Key_use *keyuse= tab->keyuse;
+    while (keyuse->key != ref_key && keyuse->table == tab->table)
+      keyuse++;
+
+    table_map const_tables= tab->join->const_table_map;
+    do
+    {
+      if (!(keyuse->used_tables & ~const_tables))
+      {
+        if (keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL)
+          return true;
+      }
+      keyuse++;
+    } while (keyuse->key == ref_key && keyuse->table == tab->table);
+  }
+  return false;
+}
+
+/**
   Test if we can use one of the 'usable_keys' instead of 'ref' key
   for sorting.
 
@@ -3584,12 +3628,13 @@ is_subkey(KEY_PART_INFO *key_part, KEY_P
 */
 
 static uint
-test_if_subkey(ORDER *order, TABLE *table, uint ref, uint ref_key_parts,
+test_if_subkey(ORDER *order, JOIN_TAB *tab, uint ref, uint ref_key_parts,
 	       const key_map *usable_keys)
 {
   uint nr;
   uint min_length= (uint) ~0;
   uint best= MAX_KEY;
+  TABLE *table= tab->table;
   KEY_PART_INFO *ref_key_part= table->key_info[ref].key_part;
   KEY_PART_INFO *ref_key_part_end= ref_key_part + ref_key_parts;
 
@@ -3600,6 +3645,7 @@ test_if_subkey(ORDER *order, TABLE *tabl
 	table->key_info[nr].key_parts >= ref_key_parts &&
 	is_subkey(table->key_info[nr].key_part, ref_key_part,
 		  ref_key_part_end) &&
+        !is_ref_or_null_optimized(tab, nr) &&
 	test_if_order_by_key(order, table, nr))
     {
       min_length= table->key_info[nr].key_length;
@@ -3729,6 +3775,14 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
 
   Plan_change_watchdog watchdog(tab, no_changes);
 
+  /* Sorting a single row can always be skipped */
+  if (tab->type == JT_EQ_REF ||
+      tab->type == JT_CONST  ||
+      tab->type == JT_SYSTEM)
+  {
+    DBUG_RETURN(1);
+  }
+
   /*
     Keys disabled by ALTER TABLE ... DISABLE KEYS should have already
     been taken into account.
@@ -3810,7 +3864,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
       if (table->covering_keys.is_set(ref_key))
 	usable_keys.intersect(table->covering_keys);
 
-      if ((new_ref_key= test_if_subkey(order, table, ref_key, ref_key_parts,
+      if ((new_ref_key= test_if_subkey(order, tab, ref_key, ref_key_parts,
 				       &usable_keys)) < MAX_KEY)
       {
 	/* Found key that can be used to retrieve data in sorted order */
@@ -3832,6 +3886,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
                                  tab->join->const_table_map))
             goto use_filesort;
 
+          DBUG_ASSERT(tab->type != JT_REF_OR_NULL && tab->type != JT_FT);
           pick_table_access_method(tab);
 	}
 	else
@@ -3973,11 +4028,16 @@ check_reverse_order:                  
         (select && select->quick && select->quick!=save_quick);
 
       /* 
-         If ref_key used index tree reading only ('Using index' in EXPLAIN),
-         and best_key doesn't, then revert the decision.
+         If 'best_key' has changed from  prev. 'ref_key':
+         Update strategy for using index tree reading only
+         ('Using index' in EXPLAIN)
       */
-      if (!table->covering_keys.is_set(best_key))
-        table->set_keyread(FALSE);
+      if (best_key != ref_key)
+      {
+        const bool using_index= 
+          (table->covering_keys.is_set(best_key) && !table->no_keyread);
+        table->set_keyread(using_index);
+      }
       if (!quick_created)
       {
         if (select)                  // Throw any existing quick select
@@ -3988,8 +4048,6 @@ check_reverse_order:                  
                                 join_read_first:join_read_last;
         tab->type=JT_INDEX_SCAN;       // Read with index_first(), index_next()
 
-        if (table->covering_keys.is_set(best_key))
-          table->set_keyread(TRUE);
         table->file->ha_index_or_rnd_end();
         if (tab->join->select_options & SELECT_DESCRIBE)
         {
@@ -4007,6 +4065,7 @@ check_reverse_order:                  
           method is actually used.
         */
         DBUG_ASSERT(tab->select->quick);
+        DBUG_ASSERT(tab->select->quick->index==(uint)best_key);
         tab->type=JT_ALL;
         tab->use_quick=QS_RANGE;
         tab->ref.key= -1;
@@ -4636,7 +4695,7 @@ bool JOIN::change_result(select_result *
   @param          ref_key             
                 * 0 <= key < MAX_KEY   - key number (hint) to start the search
                 * -1                   - no key number provided
-  @param          select_limit        LIMIT value
+  @param          select_limit        LIMIT value, or HA_POS_ERROR if no limit
   @param [out]    new_key             Key number if success, otherwise undefined
   @param [out]    new_key_direction   Return -1 (reverse) or +1 if success,
                                       otherwise undefined
@@ -4684,6 +4743,7 @@ test_if_cheaper_ordering(const JOIN_TAB 
   ha_rows table_records= table->file->stats.records;
   bool group= join && join->group && order == join->group_list;
   ha_rows ref_key_quick_rows= HA_POS_ERROR;
+  const bool has_limit= (select_limit != HA_POS_ERROR);
 
   /*
     If not used with LIMIT, only use keys if the whole query can be
@@ -4776,38 +4836,47 @@ test_if_cheaper_ordering(const JOIN_TAB 
             be included into the result set.
           */  
           if (select_limit > table_records/rec_per_key)
-              select_limit= table_records;
+            select_limit= table_records;
           else
             select_limit= (ha_rows) (select_limit*rec_per_key);
         }
-        /* 
-          If tab=tk is not the last joined table tn then to get first
-          L records from the result set we can expect to retrieve
-          only L/fanout(tk,tn) where fanout(tk,tn) says how many
-          rows in the record set on average will match each row tk.
-          Usually our estimates for fanouts are too pessimistic.
-          So the estimate for L/fanout(tk,tn) will be too optimistic
-          and as result we'll choose an index scan when using ref/range
-          access + filesort will be cheaper.
-        */
-        select_limit= (ha_rows) (select_limit < fanout ?
-                                 1 : select_limit/fanout);
         /*
-          We assume that each of the tested indexes is not correlated
-          with ref_key. Thus, to select first N records we have to scan
-          N/selectivity(ref_key) index entries. 
-          selectivity(ref_key) = #scanned_records/#table_records =
-          table->quick_condition_rows/table_records.
-          In any case we can't select more than #table_records.
-          N/(table->quick_condition_rows/table_records) > table_records 
-          <=> N > table->quick_condition_rows.
-        */ 
-        if (select_limit > table->quick_condition_rows)
-          select_limit= table_records;
-        else
-          select_limit= (ha_rows) (select_limit *
-                                   (double) table_records /
-                                    table->quick_condition_rows);
+          If a LIMIT is in effect:
+          We take join-fanout and selectivity into consideration to
+          estimate how many rows to read from tab before we meet the
+          'limit' for the final resultset.
+        */
+        if (has_limit)
+        {
+          /* 
+            If tab=tk is not the last joined table tn then to get first
+            L records from the result set we can expect to retrieve
+            only L/fanout(tk,tn) where fanout(tk,tn) says how many
+            rows in the record set on average will match each row tk.
+            Usually our estimates for fanouts are too pessimistic.
+            So the estimate for L/fanout(tk,tn) will be too optimistic
+            and as result we'll choose an index scan when using ref/range
+            access + filesort will be cheaper.
+          */
+            select_limit= (ha_rows) (select_limit < fanout ?
+                                     1 : select_limit/fanout);
+          /*
+            We assume that each of the tested indexes is not correlated
+            with ref_key. Thus, to select first N records we have to scan
+            N/selectivity(ref_key) index entries. 
+            selectivity(ref_key) = #scanned_records/#table_records =
+            table->quick_condition_rows/table_records.
+            In any case we can't select more than #table_records.
+            N/(table->quick_condition_rows/table_records) > table_records 
+            <=> N > table->quick_condition_rows.
+          */ 
+          if (select_limit > table->quick_condition_rows)
+            select_limit= table_records;
+          else
+            select_limit= (ha_rows) (select_limit *
+                                     (double) table_records /
+                                      table->quick_condition_rows);
+        }
         rec_per_key= keyinfo->rec_per_key[keyinfo->key_parts-1];
         set_if_bigger(rec_per_key, 1);
         /*

No bundle (reason: useless for push emails).