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-07-31 14:24:00+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, the cost calculation disregarded the extra
  cost of doing table scan by using a non-covering index.
  
  Fixed by setting the cost to #rows + #rows * cost of evaluating the where clause
  in this case.

  BitKeeper/etc/ignore@stripped, 2007-07-31 14:23:54+02:00, mhansson@stripped +3 -0
    Added cscope.in.out cscope.out cscope.po.out to the ignore list

  mysql-test/r/key.result@stripped, 2007-07-31 14:23:54+02:00, mhansson@stripped +21 -0
    Bug#18144: Correct result

  mysql-test/t/key.test@stripped, 2007-07-31 14:23:54+02:00, mhansson@stripped +20 -0
    Bug#18144: Test case

  sql/sql_select.cc@stripped, 2007-07-31 14:23:54+02:00, mhansson@stripped +12 -1
    Bug#18144: Added the penalty cost which occurs if FORCE INDEX is used 
    when table scan would be cheaper. Changed a comment to make a weaker assumption
    as the old assumption does not hold in this case.

diff -Nrup a/BitKeeper/etc/ignore b/BitKeeper/etc/ignore
--- a/BitKeeper/etc/ignore	2007-06-20 18:13:51 +02:00
+++ b/BitKeeper/etc/ignore	2007-07-31 14:23:54 +02:00
@@ -1344,3 +1344,6 @@ zlib/*.vcproj
 debian/control
 debian/defs.mk
 include/abi_check
+cscope.in.out
+cscope.out
+cscope.po.out
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-07-31 14:23:54 +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-07-31 14:23:54 +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/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-07-31 14:23:54 +02:00
@@ -4238,6 +4238,13 @@ best_access_path(JOIN      *join,
     {
       /* Estimate cost of reading table. */
       tmp= s->table->file->scan_time();
+      /*
+        If FORCE INDEX uses a non-covering index, the cost is one random read 
+        per record + the cost of evaluating the where clause.
+      */
+      if (s->table->force_index && !best_key)
+        tmp= rnd_records + rnd_records / TIME_FOR_COMPARE;
+
       if (s->table->map & join->outer_join)     // Can't use join cache
       {
         /*
@@ -4904,7 +4911,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))
         {