bzr push into mysql-trunk branch (ole.john.aske:3783 to 3784) Bug#13529048

[Ole John Aske]

 3784 Ole John Aske	2012-01-25
      Fix for bug#13529048 TEST_IF_SKIP_SORT_ORDER() DON'T HAVE TO FILESORT A SINGLE ROW.
      
      test_if_skip_sort_order() should catch the jointypes
      JT_EQ_REF, JT_CONST and JT_SYSTEM and skip sort order for these.
      
      Didn't add a specific testcase for this bug as it should be
      covered by existing testcases which now will skip 'Using filesort'

    modified:
      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
      sql/sql_select.cc
 3783 Ole John Aske	2012-01-25
      Fix for Bug#13514959 QUERY PLAN FAILS TO 'USING INDEX' WHERE IT SHOULD
      
      Fix two issues related to 'keyread' from covering indexes (Explain: 'Using index')
      
      1)
         Recalculate (and set) keyread in test_if_skip_sort_order() whenever
         the best_key is changed (Optimizer may have changed its mind)
      
      2) If we during cleanup in test_if_skip_sort_order() decides
         to delete the 'save_quick' we had when entering
         test_if_skip_sort_order(), the ::~QUICK_RANGE_SELECT
         d'tor will set_keyread(FALSE)' even if another quick was
         set up to be used in 1) above.
     @ mysql-test/r/innodb_icp.result
        Updated resultfile where 'Using index' is expected
     @ mysql-test/r/innodb_icp_none.result
        Updated resultfile where 'Using index' is expected
     @ sql/opt_range.cc
        ::~QUICK_RANGE_SELECT Should not 'set_keyread(FALSE)' as that
        has nothing to do with destruction of the QUICK_RANGE_SELECT.
        ('head' might actually use another 'quick')
     @ sql/records.cc
        If 'keyread' is used, ut should be cleared when we clean up
        after have read this table. (Instead of doing it as a 
        side effect in QUICK_RANGE_SELECT d'tor)
     @ sql/sql_select.cc
        Fix code for setting of set_keyread() in test_if_skip_sort_order().
        Always recalculate it if 'best_key' is changed.
        ******
        Don't apply join-fanout and predicate selctivite if there was no
        limit-clause
        ******
        Fix up EXPLAIN to use the 'ordered_index_usage' as 
        calculated by ::optimize() instead of calling
        test_if_skip_sort_order()

    modified:
      mysql-test/r/innodb_icp.result
      mysql-test/r/innodb_icp_none.result
      sql/opt_range.cc
      sql/records.cc
      sql/sql_select.cc
=== 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-25 14:37:43 +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-25 14:37:43 +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-25 14:37:43 +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-25 14:37:43 +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-25 14:37:43 +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 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-01-25 12:45:56 +0000
+++ b/sql/sql_select.cc	2012-01-25 14:37:43 +0000
@@ -3732,6 +3732,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.

No bundle (reason: useless for push emails).