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

[Ole John Aske]

 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
 3782 Martin Hansson	2012-01-25
      Bug#13536661: Deactivates the test case until a better place for it is found.

    modified:
      mysql-test/r/temp_table.result
      mysql-test/t/temp_table.test
=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	2012-01-03 11:04:14 +0000
+++ b/mysql-test/r/innodb_icp.result	2012-01-25 12:45:56 +0000
@@ -992,7 +992,7 @@ FROM t2 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	PRIMARY,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

=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result	2012-01-03 11:04:14 +0000
+++ b/mysql-test/r/innodb_icp_none.result	2012-01-25 12:45:56 +0000
@@ -991,7 +991,7 @@ FROM t2 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	PRIMARY,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

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2012-01-12 13:22:52 +0000
+++ b/sql/opt_range.cc	2012-01-25 12:45:56 +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-25 12:45:56 +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_select.cc'
--- a/sql/sql_select.cc	2012-01-24 11:24:54 +0000
+++ b/sql/sql_select.cc	2012-01-25 12:45:56 +0000
@@ -3976,11 +3976,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
@@ -3991,8 +3996,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)
         {
@@ -4010,6 +4013,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;

No bundle (reason: useless for push emails).