bk commit into 4.1 tree (evgen:1.2584) BUG#17711

[eugene]

Below is the list of changes that have just been committed into a local
4.1 repository of evgen. When evgen 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, 2006-12-23 19:53:02+03:00, evgen@stripped +3 -0
  Bug#17711: DELETE isn't using index when ORDER BY, LIMIT and non-restricting
  WHERE is present.
  
  If an index is defined and WHERE clause is present but can't be used for index
  access (like WHERE @a_var:= field) DELETE always falls back to filesort.
  Even if there is an index which can be used to speedup sorting by the ORDER BY
  list.
  
  Now if there is the WHERE clause present but it can't be used to build a 
  quick select then the mysql_delete() tries to use an index like there is no
  WHERE clause at all.

  mysql-test/r/delete.result@stripped, 2006-12-23 19:51:58+03:00, evgen@stripped +7 -0
    Added a test case for bug#17711: DELETE isn't using index when ORDER BY, LIMIT and non-restricting WHERE is present.

  mysql-test/t/delete.test@stripped, 2006-12-23 19:51:26+03:00, evgen@stripped +9 -0
    Added a test case for bug#17711: DELETE isn't using index when ORDER BY, LIMIT and non-restricting
    WHERE is present.

  sql/sql_delete.cc@stripped, 2006-12-23 19:52:09+03:00, evgen@stripped +1 -1
    Bug#17711: DELETE isn't using index when ORDER BY, LIMIT and non-restricting WHERE is present.
    Now if there is the WHERE clause present but it can't be used to build a 
    quick select then the mysql_delete() tries to use an index like there is no
    WHERE clause at all.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	evgen
# Host:	moonbone.local
# Root:	/work/17711-bug-4.1-opt-mysql

--- 1.142/sql/sql_delete.cc	2006-11-03 01:20:32 +03:00
+++ 1.143/sql/sql_delete.cc	2006-12-23 19:52:09 +03:00
@@ -142,7 +142,7 @@
       DBUG_RETURN(-1);			// This will force out message
     }
     
-    if (!select && limit != HA_POS_ERROR)
+    if ((!select || table->quick_keys.is_clear_all()) && limit != HA_POS_ERROR)
       usable_index= get_index_for_order(table, (ORDER*)(order->first), limit);
 
     if (usable_index == MAX_KEY)

--- 1.22/mysql-test/t/delete.test	2006-10-27 17:08:47 +04:00
+++ 1.23/mysql-test/t/delete.test	2006-12-23 19:51:26 +03:00
@@ -174,4 +174,13 @@
 select count(*) from t1; 
 drop table t1; 
 
+#
+# Bug#1771: 
+#
+create table t1(f1 int primary key);
+insert into t1 values (1),(2),(3),(4);
+delete from t1 where (@a:= f1) order by f1 limit 1;
+select @a;
+drop table t1;
+
 --echo End of 4.1 tests

--- 1.23/mysql-test/r/delete.result	2006-10-27 17:08:47 +04:00
+++ 1.24/mysql-test/r/delete.result	2006-12-23 19:51:58 +03:00
@@ -186,4 +186,11 @@
 count(*)
 0
 drop table t1;
+create table t1(f1 int primary key);
+insert into t1 values (1),(2),(3),(4);
+delete from t1 where (@a:= f1) order by f1 limit 1;
+select @a;
+@a
+1
+drop table t1;
 End of 4.1 tests