Bug #36259 (Optimizing with ORDER BY) and bug#45828 (Optimizer won't

use partial primary key if another index can prevent filesort

The fix for bug #28404 causes the covering ordering indexes to be 
preferred unconditionally over non-covering and ref indexes.

Fixed by comparing the cost of using a covering index to the cost of
using a ref index even for covering ordering indexes.
Added an assertion to clarify the condition the local variables should
be in.

mysql-test/include/mix1.inc:
  Bug #36259: fixed a non-stable test case
mysql-test/r/innodb_mysql.result:
  Bug #36259 and #45828 : test case
mysql-test/t/innodb_mysql.test:
  Bug #36259 and #45828 : test case
sql/sql_select.cc:
  Bug #36259 and #45828 : don't consider covering indexes supperior to
  ref keys.

4 files changed, 166 insertions, 6 deletions

diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
index 3f7b44bd9ef..19a327d079e 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -1498,9 +1498,9 @@ INSERT INTO t1 VALUES
   (4,1,3,'pk',NULL),(5,1,3,'c2',NULL),
   (2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL);
 
-EXPLAIN SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
 
-SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
 
 DROP TABLE t1;
 
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 272782f6647..3ff5f04b6c6 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -1701,10 +1701,10 @@ INSERT INTO t1 VALUES
 (4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL),
 (4,1,3,'pk',NULL),(5,1,3,'c2',NULL),
 (2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL);
-EXPLAIN SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	vid	PRIMARY	12	NULL	16	Using where
-SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+1	SIMPLE	t1	index	NULL	PRIMARY	12	NULL	16	Using where
+SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
 vid	tid	idx	name	type
 3	1	4	c_extra	NULL
 3	1	3	c2	NULL
@@ -2137,4 +2137,75 @@ GROUP BY t1.b;
 a	b	c	d	a	b	e	a	b
 1	1	1	0	1	1	2	1	1
 DROP TABLE t1, t2, t3;
+#
+# Bug #45828: Optimizer won't use partial primary key if another 
+# index can prevent filesort
+#
+CREATE TABLE `t1` (
+c1 int NOT NULL,
+c2 int NOT NULL,
+c3 int NOT NULL,
+PRIMARY KEY (c1,c2),
+KEY  (c3)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (5,2,1246276747);
+INSERT INTO t1 VALUES (2,1,1246281721);
+INSERT INTO t1 VALUES (7,3,1246281756);
+INSERT INTO t1 VALUES (4,2,1246282139);
+INSERT INTO t1 VALUES (3,1,1246282230);
+INSERT INTO t1 VALUES (1,0,1246282712);
+INSERT INTO t1 VALUES (8,3,1246282765);
+INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1;
+INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1;
+INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1;
+INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1;
+INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1;
+INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1;
+SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+c1	c2	c3
+EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	PRIMARY,c3	PRIMARY	4	const	1	Using where; Using filesort
+EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	PRIMARY	PRIMARY	4	const	1	Using where; Using filesort
+CREATE TABLE t2 (
+c1 int NOT NULL,
+c2 int NOT NULL,
+c3 int NOT NULL,
+KEY (c1,c2),
+KEY (c3)
+) ENGINE=InnoDB;
+explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	c1,c3	c1	4	const	1	Using where; Using filesort
+DROP TABLE t1,t2;
+#
+# 36259: Optimizing with ORDER BY
+#
+CREATE TABLE t1 (
+a INT NOT NULL AUTO_INCREMENT,
+b INT NOT NULL,
+c INT NOT NULL,
+d VARCHAR(5),
+e INT NOT NULL,
+PRIMARY KEY (a), KEY i2 (b,c,d)
+) ENGINE=InnoDB;
+INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2);
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i2	i2	8	const,const	1	Using where; Using filesort
+EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i2	i2	8	const,const	1	Using where; Using filesort
+EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	128	Using where
+DROP TABLE t1;
 End of 5.1 tests
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index 3e1968b8007..c643465b2f3 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -380,4 +380,85 @@ SELECT * FROM t1, t2, t3
 
 DROP TABLE t1, t2, t3;
 
+--echo #
+--echo # Bug #45828: Optimizer won't use partial primary key if another 
+--echo # index can prevent filesort
+--echo #
+
+# Create the table
+CREATE TABLE `t1` (
+  c1 int NOT NULL,
+  c2 int NOT NULL,
+  c3 int NOT NULL,
+  PRIMARY KEY (c1,c2),
+  KEY  (c3)
+) ENGINE=InnoDB;
+
+# populate with data
+INSERT INTO t1 VALUES (5,2,1246276747);
+INSERT INTO t1 VALUES (2,1,1246281721);
+INSERT INTO t1 VALUES (7,3,1246281756);
+INSERT INTO t1 VALUES (4,2,1246282139);
+INSERT INTO t1 VALUES (3,1,1246282230);
+INSERT INTO t1 VALUES (1,0,1246282712);
+INSERT INTO t1 VALUES (8,3,1246282765);
+INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1;
+INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1;
+INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1;
+INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1;
+INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1;
+INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1;
+
+# query and no rows will match the c1 condition, whereas all will match c3
+SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+
+# SHOULD use the pk.
+# index on c3 will be used instead of primary key
+EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+
+# if we force the primary key, we can see the estimate is 1 
+EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+
+
+CREATE TABLE t2 (
+  c1 int NOT NULL,
+  c2 int NOT NULL,
+  c3 int NOT NULL,
+  KEY (c1,c2),
+  KEY (c3)
+) ENGINE=InnoDB;
+
+# SHOULD use the pk.
+# if we switch it from a primary key to a regular index, it works correctly as well
+explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+
+DROP TABLE t1,t2;
+
+
+--echo #
+--echo # 36259: Optimizing with ORDER BY
+--echo #
+
+CREATE TABLE t1 (
+  a INT NOT NULL AUTO_INCREMENT,
+  b INT NOT NULL,
+  c INT NOT NULL,
+  d VARCHAR(5),
+  e INT NOT NULL,
+  PRIMARY KEY (a), KEY i2 (b,c,d)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2);
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a;
+EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a;
+EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a;
+
+DROP TABLE t1;
+
 --echo End of 5.1 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 37721660184..401745bd9b8 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -13132,9 +13132,17 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
     for (nr=0; nr < table->s->keys ; nr++)
     {
       int direction;
+
       if (keys.is_set(nr) &&
           (direction= test_if_order_by_key(order, table, nr, &used_key_parts)))
       {
+        /*
+          At this point we are sure that ref_key is a non-ordering
+          key (where "ordering key" is a key that will return rows
+          in the order required by ORDER BY).
+        */
+        DBUG_ASSERT (ref_key != (int) nr);
+
         bool is_covering= table->covering_keys.is_set(nr) ||
                           (nr == table->s->primary_key &&
                           table->file->primary_key_is_clustered());
@@ -13215,7 +13223,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
 	  */
           index_scan_time= select_limit/rec_per_key *
 	                   min(rec_per_key, table->file->scan_time());
-          if (is_covering || 
+          if ((ref_key < 0 && is_covering) || 
               (ref_key < 0 && (group || table->force_index)) ||
               index_scan_time < read_time)
           {