Bug#46454: MySQL wrong index optimisation leads to incorrect result & crashes

Problem 1:
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.
Problem 2:
When a 'ref' access method was employed by cost-based optimizer, (when the column
is non-NULLable), it was assumed that it needed no initialization if 'quick' access
methods (since they are based on range scan). When ORDER BY optimization overrides 
the decision, however, it expects to have this initialized and hence crashes. 
Fixed in 5.1 (was fixed in 6.0 already) by initializing 'quick' even when there's 
'ref' access. 

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: 
  Problem 1 fixed in make_join_select()
  Problem 2 fixed in test_if_skip_sort_order()
sql/table.h:
  Bug#46454: Added comment to field.

1 files changed, 7 insertions, 1 deletions

diff --git a/sql/table.h b/sql/table.h
index 98ede52cd99..653d04b149e 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -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