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, 2007-01-11 16:05:03+03:00, evgen@stripped +3 -0
Bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and non-restricting
WHERE is present.
If a DELETE statement with ORDER BY and LIMIT contains a WHERE clause
with conditions that for sure cannot be used for index access (like in
WHERE @var:= field) the execution always follows the filesort path.
It happens currently even when for the above case there is an index that
can be used to speedup sorting by the order by list.
Now if a DELETE statement with ORDER BY and LIMIT contains such WHERE
clause conditions that cannot be used to build any 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, 2007-01-11 16:04:02+03:00, evgen@stripped +7 -0
Added a test case for bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and non-restricting WHERE is present.
mysql-test/t/delete.test@stripped, 2007-01-11 16:03:54+03:00, evgen@stripped +10 -0
Added a test case for bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and non-restricting
WHERE is present.
sql/sql_delete.cc@stripped, 2007-01-11 16:04:10+03:00, evgen@stripped +1 -1
Bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and non-restricting WHERE is present.
Now if a DELETE statement with ORDER BY and LIMIT contains such WHERE
clause conditions that cannot be used to build any 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 2007-01-11 16:04:10 +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 2007-01-11 16:03:54 +03:00
@@ -174,4 +174,14 @@
select count(*) from t1;
drop table t1;
+#
+# Bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and
+# non-restricting WHERE is present.
+#
+create table t1(f1 int primary key);
+insert into t1 values (4),(3),(1),(2);
+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 2007-01-11 16:04:02 +03:00
@@ -186,4 +186,11 @@
count(*)
0
drop table t1;
+create table t1(f1 int primary key);
+insert into t1 values (4),(3),(1),(2);
+delete from t1 where (@a:= f1) order by f1 limit 1;
+select @a;
+@a
+1
+drop table t1;
End of 4.1 tests
Thread |
---|
• bk commit into 4.1 tree (evgen:1.2584) BUG#17711 | eugene | 11 Jan |