Below is the list of changes that have just been committed into a local
5.0 repository of monty. When monty 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
1.1924 05/05/30 20:48:40 monty@stripped +6 -0
Fixed bug in multiple-table-delete where some rows was not deleted
sql/sql_delete.cc
1.149 05/05/30 20:48:36 monty@stripped +50 -38
Fixed bug in multiple-table-delete where some rows was not deleted
This happend when the first table-to-delete-from was not the the table that was scanned.
Fixed this by only doing 'delete-on-the-fly' for the first table.
Fixed also some wrong error handling in multi-table-delete
sql/sql_class.h
1.233 05/05/30 20:48:36 monty@stripped +3 -2
Fixed bug in multiple-table-delete where some rows was not deleted
sql/opt_range.cc
1.163 05/05/30 20:48:36 monty@stripped +4 -4
Code cleanup
sql/item_subselect.cc
1.100 05/05/30 20:48:35 monty@stripped +1 -2
Code cleanup
mysql-test/t/delete.test
1.20 05/05/30 20:48:35 monty@stripped +18 -1
Test case for bug in multiple-table-delete where some rows was not deleted
mysql-test/r/delete.result
1.22 05/05/30 20:48:35 monty@stripped +21 -1
Test case for bug in multiple-table-delete where some rows was not deleted
# 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: monty
# Host: narttu.mysql.com
# Root: /home/my/mysql-5.0
--- 1.162/sql/opt_range.cc 2005-05-25 18:33:25 +03:00
+++ 1.163/sql/opt_range.cc 2005-05-30 20:48:36 +03:00
@@ -7957,7 +7957,7 @@
max_used_key_length= real_prefix_len;
if (min_max_ranges.elements > 0)
{
- QUICK_RANGE *cur_range= 0;
+ QUICK_RANGE *cur_range;
if (have_min)
{ /* Check if the right-most range has a lower boundary. */
get_dynamic(&min_max_ranges, (gptr)&cur_range,
@@ -7965,7 +7965,7 @@
if (!(cur_range->flag & NO_MIN_RANGE))
{
max_used_key_length+= min_max_arg_len;
- ++used_key_parts;
+ used_key_parts++;
return;
}
}
@@ -7975,7 +7975,7 @@
if (!(cur_range->flag & NO_MAX_RANGE))
{
max_used_key_length+= min_max_arg_len;
- ++used_key_parts;
+ used_key_parts++;
return;
}
}
@@ -7983,7 +7983,7 @@
else if (have_min && min_max_arg_part && min_max_arg_part->field->is_null())
{
max_used_key_length+= min_max_arg_len;
- ++used_key_parts;
+ used_key_parts++;
}
}
--- 1.232/sql/sql_class.h 2005-05-26 22:01:46 +03:00
+++ 1.233/sql/sql_class.h 2005-05-30 20:48:36 +03:00
@@ -1854,7 +1854,8 @@
ha_rows deleted, found;
uint num_of_tables;
int error;
- bool do_delete, transactional_tables, normal_tables;
+ bool do_delete, transactional_tables, normal_tables, delete_while_scanning;
+
public:
multi_delete(THD *thd, TABLE_LIST *dt, uint num_of_tables);
~multi_delete();
@@ -1862,7 +1863,7 @@
bool send_data(List<Item> &items);
bool initialize_tables (JOIN *join);
void send_error(uint errcode,const char *err);
- int do_deletes (bool from_send_error);
+ int do_deletes();
bool send_eof();
};
--- 1.148/sql/sql_delete.cc 2005-05-24 21:25:44 +03:00
+++ 1.149/sql/sql_delete.cc 2005-05-30 20:48:36 +03:00
@@ -411,7 +411,7 @@
num_of_tables(num_of_tables_arg), error(0),
do_delete(0), transactional_tables(0), normal_tables(0)
{
- tempfiles = (Unique **) sql_calloc(sizeof(Unique *) * (num_of_tables-1));
+ tempfiles= (Unique **) sql_calloc(sizeof(Unique *) * num_of_tables);
}
@@ -441,6 +441,7 @@
tables_to_delete_from|= walk->table->map;
walk= delete_tables;
+ delete_while_scanning= 1;
for (JOIN_TAB *tab=join->join_tab, *end=join->join_tab+join->tables;
tab < end;
tab++)
@@ -460,10 +461,25 @@
else
normal_tables= 1;
}
+ else if ((tab->type != JT_SYSTEM && tab->type != JT_CONST) &&
+ walk == delete_tables)
+ {
+ /*
+ We are not deleting from the table we are scanning. In this
+ case send_data() shouldn't delete any rows a we may touch
+ the rows in the deleted table many times
+ */
+ delete_while_scanning= 0;
+ }
}
walk= delete_tables;
tempfiles_ptr= tempfiles;
- for (walk= walk->next_local ;walk ;walk= walk->next_local)
+ if (delete_while_scanning)
+ {
+ table_being_deleted= delete_tables;
+ walk= walk->next_local;
+ }
+ for (;walk ;walk= walk->next_local)
{
TABLE *table=walk->table;
*tempfiles_ptr++= new Unique (refpos_order_cmp,
@@ -482,12 +498,12 @@
table_being_deleted;
table_being_deleted= table_being_deleted->next_local)
{
- TABLE *t=table_being_deleted->table;
- free_io_cache(t); // Alloced by unique
- t->no_keyread=0;
+ TABLE *table= table_being_deleted->table;
+ free_io_cache(table); // Alloced by unique
+ table->no_keyread=0;
}
- for (uint counter= 0; counter < num_of_tables-1; counter++)
+ for (uint counter= 0; counter < num_of_tables; counter++)
{
if (tempfiles[counter])
delete tempfiles[counter];
@@ -497,14 +513,15 @@
bool multi_delete::send_data(List<Item> &values)
{
- int secure_counter= -1;
+ int secure_counter= delete_while_scanning ? -1 : 0;
+ TABLE_LIST *del_table;
DBUG_ENTER("multi_delete::send_data");
- for (table_being_deleted= delete_tables;
- table_being_deleted;
- table_being_deleted= table_being_deleted->next_local, secure_counter++)
+ for (del_table= delete_tables;
+ del_table;
+ del_table= del_table->next_local, secure_counter++)
{
- TABLE *table=table_being_deleted->table;
+ TABLE *table= del_table->table;
/* Check if we are using outer join and we didn't find the row */
if (table->status & (STATUS_NULL_ROW | STATUS_DELETED))
@@ -515,7 +532,8 @@
if (secure_counter < 0)
{
- /* If this is the table we are scanning */
+ /* We are scanning the current table */
+ DBUG_ASSERT(del_table == table_being_deleted);
if (table->triggers &&
table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
TRG_ACTION_BEFORE, FALSE))
@@ -529,8 +547,7 @@
TRG_ACTION_AFTER, FALSE))
DBUG_RETURN(1);
}
- else if (!table_being_deleted->next_local ||
- table_being_deleted->table->file->has_transactions())
+ else
{
table->file->print_error(error,MYF(0));
DBUG_RETURN(1);
@@ -541,7 +558,7 @@
error=tempfiles[secure_counter]->unique_add((char*) table->file->ref);
if (error)
{
- error=-1;
+ error= 1; // Fatal error
DBUG_RETURN(1);
}
}
@@ -564,22 +581,24 @@
/* Something already deleted so we have to invalidate cache */
query_cache_invalidate3(thd, delete_tables, 1);
- /* Below can happen when thread is killed early ... */
- if (!table_being_deleted)
- table_being_deleted=delete_tables;
-
/*
If rows from the first table only has been deleted and it is
transactional, just do rollback.
The same if all tables are transactional, regardless of where we are.
In all other cases do attempt deletes ...
*/
- if ((table_being_deleted->table->file->has_transactions() &&
- table_being_deleted == delete_tables) || !normal_tables)
+ if ((table_being_deleted == delete_tables &&
+ table_being_deleted->table->file->has_transactions()) ||
+ !normal_tables)
ha_rollback_stmt(thd);
else if (do_delete)
{
- VOID(do_deletes(1));
+ /*
+ We have to execute the recorded do_deletes() and write info into the
+ error log
+ */
+ error= 1;
+ send_eof();
}
DBUG_VOID_RETURN;
}
@@ -592,27 +611,20 @@
1 error
*/
-int multi_delete::do_deletes(bool from_send_error)
+int multi_delete::do_deletes()
{
int local_error= 0, counter= 0;
DBUG_ENTER("do_deletes");
+ DBUG_ASSERT(do_delete);
- if (from_send_error)
- {
- /* Found out table number for 'table_being_deleted*/
- for (TABLE_LIST *aux= delete_tables;
- aux != table_being_deleted;
- aux= aux->next_local)
- counter++;
- }
- else
- table_being_deleted = delete_tables;
-
- do_delete= 0;
+ do_delete= 0; // Mark called
if (!found)
DBUG_RETURN(0);
- for (table_being_deleted= table_being_deleted->next_local;
- table_being_deleted;
+
+ table_being_deleted= (delete_while_scanning ? delete_tables->next_local :
+ delete_tables);
+
+ for (; table_being_deleted;
table_being_deleted= table_being_deleted->next_local, counter++)
{
TABLE *table = table_being_deleted->table;
@@ -674,7 +686,7 @@
thd->proc_info="deleting from reference tables";
/* Does deletes for the last n - 1 tables, returns 0 if ok */
- int local_error= do_deletes(0); // returns 0 if success
+ int local_error= do_deletes(); // returns 0 if success
/* reset used flags */
thd->proc_info="end";
--- 1.99/sql/item_subselect.cc 2005-04-01 02:13:23 +03:00
+++ 1.100/sql/item_subselect.cc 2005-05-30 20:48:35 +03:00
@@ -773,9 +773,8 @@
Comp_creator *func)
{
Item_subselect::trans_res result= RES_ERROR;
- DBUG_ENTER("Item_in_subselect::single_value_transformer");
-
SELECT_LEX *select_lex= join->select_lex;
+ DBUG_ENTER("Item_in_subselect::single_value_transformer");
/*
Check that the right part of the subselect contains no more than one
--- 1.19/mysql-test/t/delete.test 2005-02-16 06:50:29 +02:00
+++ 1.20/mysql-test/t/delete.test 2005-05-30 20:48:35 +03:00
@@ -3,7 +3,7 @@
#
--disable_warnings
-drop table if exists t1,t11,t12,t2;
+drop table if exists t1,t2,t3,t11,t12;
--enable_warnings
CREATE TABLE t1 (a tinyint(3), b tinyint(5));
INSERT INTO t1 VALUES (1,1);
@@ -152,3 +152,20 @@
DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a LIMIT 1;
SELECT * FROM t1;
DROP TABLE t1;
+
+#
+# Test of multi-delete where we are not scanning the first table
+#
+
+CREATE TABLE t1 (a int not null,b int not null);
+CREATE TABLE t2 (a int not null, b int not null, primary key (a,b));
+CREATE TABLE t3 (a int not null, b int not null, primary key (a,b));
+insert into t1 values (1,1),(2,1),(1,3);
+insert into t2 values (1,1),(2,2),(3,3);
+insert into t3 values (1,1),(2,1),(1,3);
+select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
+explain select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
+delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
+# This should be empty
+select * from t3;
+drop table t1,t2,t3;
--- 1.21/mysql-test/r/delete.result 2005-02-16 07:03:13 +02:00
+++ 1.22/mysql-test/r/delete.result 2005-05-30 20:48:35 +03:00
@@ -1,4 +1,4 @@
-drop table if exists t1,t11,t12,t2;
+drop table if exists t1,t2,t3,t11,t12;
CREATE TABLE t1 (a tinyint(3), b tinyint(5));
INSERT INTO t1 VALUES (1,1);
INSERT LOW_PRIORITY INTO t1 VALUES (1,2);
@@ -172,3 +172,23 @@
0
2
DROP TABLE t1;
+CREATE TABLE t1 (a int not null,b int not null);
+CREATE TABLE t2 (a int not null, b int not null, primary key (a,b));
+CREATE TABLE t3 (a int not null, b int not null, primary key (a,b));
+insert into t1 values (1,1),(2,1),(1,3);
+insert into t2 values (1,1),(2,2),(3,3);
+insert into t3 values (1,1),(2,1),(1,3);
+select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
+a b a b a b
+1 1 1 1 1 1
+2 1 2 2 2 1
+1 3 1 1 1 3
+explain select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t2 index PRIMARY PRIMARY 8 NULL 3 Using where; Using index
+1 SIMPLE t3 index PRIMARY PRIMARY 8 NULL 3 Using where; Using index
+delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
+select * from t3;
+a b
+drop table t1,t2,t3;
| Thread |
|---|
| • bk commit into 5.0 tree (monty:1.1924) | monty | 31 May |
