bk commit into 5.0 tree (mhansson:1.2485) BUG#18144

[mhansson]

Below is the list of changes that have just been committed into a local
5.0 repository of martin. When martin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-09-10 17:04:43+02:00, mhansson@stripped +4 -0
  Bug#18144: Cost with FORCE/USE index seems incorrect in some cases.
  
  Most of the times FORCE INDEX is ignored when the plan would be more expensive than
  table scan. In GROUP BY cases, however, it is not, but the cost-based optimizer
  used the same cost for performing a table scan by using a non-covering index.
  
  Fixed by setting the cost to the same as reading a single range the size of the table.

  mysql-test/r/key.result@stripped, 2007-09-10 17:04:40+02:00, mhansson@stripped +21 -0
    Bug#18144: Correct result

  mysql-test/t/key.test@stripped, 2007-09-10 17:04:41+02:00, mhansson@stripped +20 -0
    Bug#18144: Test case

  sql/handler.h@stripped, 2007-09-10 17:04:41+02:00, mhansson@stripped +12 -0
    Bug#18144: Added comment describing handler::read_time(uint, uint, ha_rows)

  sql/sql_select.cc@stripped, 2007-09-10 17:04:41+02:00, mhansson@stripped +10 -2
    Bug#18144: 
    
    - Added the correct cost which occurs if FORCE INDEX is used to scan a table.
    - Changed a comment to make a weaker assumption
      as the old assumption does not hold in this case.

diff -Nrup a/mysql-test/r/key.result b/mysql-test/r/key.result
--- a/mysql-test/r/key.result	2007-05-30 14:04:02 +02:00
+++ b/mysql-test/r/key.result	2007-09-10 17:04:40 +02:00
@@ -462,4 +462,25 @@ EXPLAIN SELECT MAX(a) FROM t1 FORCE INDE
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
 DROP TABLE t1;
+CREATE TABLE t1( a INT, b INT, KEY( a ) );
+INSERT INTO t1 values (1, 2), (1, 3), (2, 3), (2, 4), (3, 4), (3, 5);
+EXPLAIN SELECT a, SUM( b ) FROM t1 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
+SHOW STATUS LIKE 'Last_query_cost';
+Variable_name	Value
+Last_query_cost	9.212184
+EXPLAIN SELECT a, SUM( b ) FROM t1 USE INDEX( a ) GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
+SHOW STATUS LIKE 'Last_query_cost';
+Variable_name	Value
+Last_query_cost	9.212184
+EXPLAIN SELECT a, SUM( b ) FROM t1 FORCE INDEX( a ) GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	6	
+SHOW STATUS LIKE 'Last_query_cost';
+Variable_name	Value
+Last_query_cost	14.199000
+DROP TABLE t1;
 End of 5.0 tests.
diff -Nrup a/mysql-test/t/key.test b/mysql-test/t/key.test
--- a/mysql-test/t/key.test	2007-05-22 14:58:29 +02:00
+++ b/mysql-test/t/key.test	2007-09-10 17:04:41 +02:00
@@ -443,4 +443,24 @@ ALTER TABLE t1 DISABLE KEYS;
 EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);
 DROP TABLE t1;
 
+#
+# Bug #18144  	Cost with FORCE/USE index seems incorrect in some cases.
+#
+# We are interested in showing that the cost for the last plan is higher
+# than for the preceding two plans.
+#
+CREATE TABLE t1( a INT, b INT, KEY( a ) );  
+INSERT INTO t1 values (1, 2), (1, 3), (2, 3), (2, 4), (3, 4), (3, 5);
+
+EXPLAIN SELECT a, SUM( b ) FROM t1 GROUP BY a;
+SHOW STATUS LIKE 'Last_query_cost';
+
+EXPLAIN SELECT a, SUM( b ) FROM t1 USE INDEX( a ) GROUP BY a;
+SHOW STATUS LIKE 'Last_query_cost';
+
+EXPLAIN SELECT a, SUM( b ) FROM t1 FORCE INDEX( a ) GROUP BY a;
+SHOW STATUS LIKE 'Last_query_cost';
+
+DROP TABLE t1;
+
 --echo End of 5.0 tests.
diff -Nrup a/sql/handler.h b/sql/handler.h
--- a/sql/handler.h	2007-07-12 15:30:16 +02:00
+++ b/sql/handler.h	2007-09-10 17:04:41 +02:00
@@ -574,6 +574,18 @@ public:
   void change_table_ptr(TABLE *table_arg) { table=table_arg; }
   virtual double scan_time()
     { return ulonglong2double(data_file_length) / IO_SIZE + 2; }
+
+  /**
+     @brief The cost of reading a set of ranges from the table using an index
+     to access it.
+
+     @param index  The index number.
+     @param ranges The number of ranges to be read.
+     @param rows   Total number of rows to be read.
+
+     This method can be used to calculate the total cost of scanning a table
+     using an index by calling it using read_time(index, 1, table_size).
+  */
   virtual double read_time(uint index, uint ranges, ha_rows rows)
  { return rows2double(ranges+rows); }
   virtual const key_map *keys_to_use_for_scanning() { return &key_map_empty; }
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-07-23 05:25:59 +02:00
+++ b/sql/sql_select.cc	2007-09-10 17:04:41 +02:00
@@ -4237,7 +4237,11 @@ best_access_path(JOIN      *join,
     else
     {
       /* Estimate cost of reading table. */
-      tmp= s->table->file->scan_time();
+      if (s->table->force_index && !best_key)
+        tmp= s->table->file->read_time(s->ref.key, 1, s->records);
+      else
+        tmp= s->table->file->scan_time();
+
       if (s->table->map & join->outer_join)     // Can't use join cache
       {
         /*
@@ -4904,7 +4908,11 @@ best_extension_by_limited_search(JOIN   
         if (join->sort_by_table &&
             join->sort_by_table !=
             join->positions[join->const_tables].table->table)
-          /* We have to make a temp table */
+          /* 
+             We may have to make a temp table, note that this is only a 
+             heuristic since we cannot know for sure at this point. 
+             Hence it may be wrong.
+          */
           current_read_time+= current_record_count;
         if ((search_depth == 1) || (current_read_time < join->best_read))
         {