bzr commit into mysql-5.1-bugteam branch (martin.hansson:3062) Bug#46454

[Martin Hansson]

#At file:///data0/martin/bzr/bug46454/5.1bt-gca/ based on revid:davi.arnaut@stripped

 3062 Martin Hansson	2009-08-07
      Bug#46454: MySQL wrong index optimisation leads to incorrect result & crashes
      
      When the 'Using index' optimization is used, the optimizer may still - after
      cost-based optimization - decide to use another index in order to avoid using
      a temporary table. But when this happens, the flag to the storage engine to 
      read index only (not table) was still set. Fixed by resetting the flag in the 
      storage engine and TABLE structure in the above scenario, unless the new index
      allows for the same optimization.
     @ mysql-test/r/order_by.result
        Bug#46454: Test result.
     @ mysql-test/t/order_by.test
        Bug#46454: Test case.
     @ sql/sql_select.cc
        Bug#46454: Fix. Removed code which would be a duplicate.
     @ sql/table.h
        Bug#46454: Added comment to field.

    modified:
      mysql-test/r/order_by.result
      mysql-test/t/order_by.test
      sql/sql_select.cc
      sql/table.h
=== modified file 'mysql-test/r/order_by.result'
--- a/mysql-test/r/order_by.result	2008-10-16 18:04:31 +0000
+++ b/mysql-test/r/order_by.result	2009-08-07 08:23:40 +0000
@@ -1500,3 +1500,39 @@ id1
 15
 16
 DROP TABLE t1;
+CREATE TABLE t1 (a INT, b INT, c char(100), KEY (b, c), KEY (b, a, c))
+DEFAULT CHARSET = utf8;
+INSERT INTO t1 VALUES 
+(1,  1, 1),
+(2,  2, 2),
+(3,  3, 3),
+(4,  4, 4),
+(5,  5, 5),
+(6,  6, 6),
+(7,  7, 7),
+(8,  8, 8),
+(9,  9, 9);
+INSERT INTO t1 SELECT a + 10,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 20,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 40,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 80,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 160, b, c FROM t1;
+INSERT INTO t1 SELECT a + 320, b, c FROM t1;
+INSERT INTO t1 SELECT a + 640, b, c FROM t1;
+INSERT INTO t1 SELECT a + 640, b, c FROM t1;
+EXPLAIN
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	b,b_2	b	5	NULL	226	Using where
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+a
+1911
+1901
+1891
+1881
+1871
+1861
+1851
+1841
+1831
+DROP TABLE t1;

=== modified file 'mysql-test/t/order_by.test'
--- a/mysql-test/t/order_by.test	2008-10-16 18:04:31 +0000
+++ b/mysql-test/t/order_by.test	2009-08-07 08:23:40 +0000
@@ -1361,3 +1361,34 @@ DROP TABLE t1;
 
 
 
+#
+# Bug#46454: MySQL wrong index optimisation leads to incorrect result & crashes 
+#
+CREATE TABLE t1 (a INT, b INT, c char(100), KEY (b, c), KEY (b, a, c))
+DEFAULT CHARSET = utf8;
+
+INSERT INTO t1 VALUES 
+(1,  1, 1),
+(2,  2, 2),
+(3,  3, 3),
+(4,  4, 4),
+(5,  5, 5),
+(6,  6, 6),
+(7,  7, 7),
+(8,  8, 8),
+(9,  9, 9);
+
+INSERT INTO t1 SELECT a + 10,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 20,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 40,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 80,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 160, b, c FROM t1;
+INSERT INTO t1 SELECT a + 320, b, c FROM t1;
+INSERT INTO t1 SELECT a + 640, b, c FROM t1;
+INSERT INTO t1 SELECT a + 640, b, c FROM t1;
+
+EXPLAIN
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+
+DROP TABLE t1;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-07-24 12:15:56 +0000
+++ b/sql/sql_select.cc	2009-08-07 08:23:40 +0000
@@ -12946,6 +12946,8 @@ find_field_in_item_list (Field *field, v
 
   The index must cover all fields in <order>, or it will not be considered.
 
+  @param no_changes No changes will be made to the query plan.
+
   @todo
     - sergeyp: Results of all index merge selects actually are ordered 
     by clustered PK values.
@@ -13280,6 +13282,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
       }
       if (!no_changes)
       {
+        /* 
+           If ref_key used index tree reading only ('Using index' in EXPLAIN),
+           and best_key doesn't, then revert the decision.
+        */
+        if (!table->covering_keys.is_set(best_key) && table->key_read)
+        {
+          table->key_read= 0;
+          table->file->extra(HA_EXTRA_NO_KEYREAD);          
+        }        
         if (!quick_created)
 	{
           tab->index= best_key;
@@ -13296,16 +13307,6 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
             table->key_read=1;
             table->file->extra(HA_EXTRA_KEYREAD);
           }
-          else if (table->key_read)
-          {
-            /*
-              Clear the covering key read flags that might have been
-              previously set for some key other than the current best_key.
-            */
-            table->key_read= 0;
-            table->file->extra(HA_EXTRA_NO_KEYREAD);
-          }
-
           table->file->ha_index_or_rnd_end();
           if (join->select_options & SELECT_DESCRIBE)
           {

=== modified file 'sql/table.h'
--- a/sql/table.h	2009-07-29 08:54:20 +0000
+++ b/sql/table.h	2009-08-07 08:23:40 +0000
@@ -755,7 +755,13 @@ struct st_table {
   */
   my_bool force_index;
   my_bool distinct,const_table,no_rows;
-  my_bool key_read, no_keyread;
+
+  /**
+     If set, the optimizer has found that row retrieval should access index 
+     tree only.
+   */
+  my_bool key_read;
+  my_bool no_keyread;
   /*
     Placeholder for an open table which prevents other connections
     from taking name-locks on this table. Typically used with


Attachment: [text/bzr-bundle] bzr/[e-mail]