bk commit into 5.0 tree (dlenev:1.1945) BUG#11158

[dlenev]

Below is the list of changes that have just been committed into a local
5.0 repository of dlenev. When dlenev 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.1945 05/06/09 01:07:52 dlenev@stripped +8 -0
  Fix for bug #11158 "Can't perform multi-delete in stored procedure".
  
  In order to make multi-delete SP friendly we need to have all table 
  locks for the elements of main statement table list properly set 
  at the end of parsing.
  
  Also performed small cleanup: We don't need relink_tables_for_multidelete()
  any longer since the only case now when TABLE_LIST::correspondent_table
  is non-zero are tables in auxilary table list of multi-delete and these
  tables are handled specially in mysql_multi_delete_prepare().

  sql/sql_yacc.yy
    1.393 05/06/09 01:07:46 dlenev@stripped +8 -1
    delete:
      In order to make multi-delete SP friendly we need to have all table 
      locks for the elements of main statement table list properly set 
      at the end of parsing.

  sql/sql_prepare.cc
    1.117 05/06/09 01:07:46 dlenev@stripped +1 -2
    mysql_test_multidelete():
      Now multi_delete_precheck() takes only two arguments, so we don't
      need to pass fake third parameter. 

  sql/sql_parse.cc
    1.451 05/06/09 01:07:46 dlenev@stripped +31 -18
    multi_delete_precheck():
      Moved code which is responsible for iterating through auxilary table
      list and binding its elements with corresponding elements of main
      table list, and properly updating locks in it to separate function -
      multi_delete_set_locks_and_link_aux_tables(). This is because in order
      to make multi-delete SP friendly we need to have all locks set properly
      at the end of statement parsing. So we are introducing new function
      which will be called from parser.
      We also calculate number of tables from which we are going to perform
      deletions there and store this number for later usage in
      LEX::table_count.
      Also removed some no longer needed code.  

  sql/sql_lex.h
    1.182 05/06/09 01:07:46 dlenev@stripped +6 -1
    LEX::table_count
      Added description of new role of this LEX member for multi-delete. 
      Now for this statement we store number of tables from which we should
      delete records there.

  sql/sql_base.cc
    1.254 05/06/09 01:07:46 dlenev@stripped +0 -28
    Removed relink_tables_for_multidelete() routine and its invocations.
    We don't need them in 5.0 since the only case now when
    TABLE_LIST::correspondent_table is non-zero are tables in auxilary table
    list of multi-delete and these tables are handled specially in
    mysql_multi_delete_prepare().

  sql/mysql_priv.h
    1.310 05/06/09 01:07:46 dlenev@stripped +2 -1
    - Removed third argument from the declaration of multi_delete_precheck()
      as nowdays we calculate number of tables in multi-delete from which
      we are going to delete rows right at the end of statement parsing.
    - Introduced definition of multi_delete_set_locks_and_link_aux_tables()
      which is responsible for propagation of proper table locks from
      multi-delete's auxilary table list to the main list and binding
      corresponding tables in these two lists.

  mysql-test/t/sp-threads.test
    1.4 05/06/09 01:07:46 dlenev@stripped +26 -0
    Added test case for bug #11158 "Can't perform multi-delete in stored
    procedure".

  mysql-test/r/sp-threads.result
    1.3 05/06/09 01:07:46 dlenev@stripped +15 -0
    Added test case for bug #11158 "Can't perform multi-delete in stored
    procedure".

# 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:	dlenev
# Host:	brandersnatch.localdomain
# Root:	/home/dlenev/src/mysql-5.0-bgmd

--- 1.309/sql/mysql_priv.h	Tue Jun  7 12:30:02 2005
+++ 1.310/sql/mysql_priv.h	Thu Jun  9 01:07:46 2005
@@ -480,7 +480,7 @@
 			      TABLE_LIST *table_list);
 bool check_some_routine_access(THD *thd, const char *db, const char *name, bool is_proc);
 bool multi_update_precheck(THD *thd, TABLE_LIST *tables);
-bool multi_delete_precheck(THD *thd, TABLE_LIST *tables, uint *table_count);
+bool multi_delete_precheck(THD *thd, TABLE_LIST *tables);
 bool mysql_multi_update_prepare(THD *thd);
 bool mysql_multi_delete_prepare(THD *thd);
 bool mysql_insert_select_prepare(THD *thd);
@@ -575,6 +575,7 @@
 bool mysql_new_select(LEX *lex, bool move_down);
 void create_select_for_variable(const char *var_name);
 void mysql_init_multi_delete(LEX *lex);
+bool multi_delete_set_locks_and_link_aux_tables(LEX *lex);
 void init_max_user_conn(void);
 void init_update_queries(void);
 void free_max_user_conn(void);

--- 1.253/sql/sql_base.cc	Tue Jun  7 14:54:41 2005
+++ 1.254/sql/sql_base.cc	Thu Jun  9 01:07:46 2005
@@ -42,7 +42,6 @@
 			    uint db_stat, uint prgflag,
 			    uint ha_open_flags, TABLE *outparam,
 			    TABLE_LIST *table_desc, MEM_ROOT *mem_root);
-static void relink_tables_for_multidelete(THD *thd);
 
 extern "C" byte *table_cache_key(const byte *record,uint *length,
 				 my_bool not_used __attribute__((unused)))
@@ -2089,7 +2088,6 @@
       (thd->fill_derived_tables() &&
        mysql_handle_derived(thd->lex, &mysql_derived_filling)))
     DBUG_RETURN(TRUE); /* purecov: inspected */
-  relink_tables_for_multidelete(thd);
   DBUG_RETURN(0);
 }
 
@@ -2119,33 +2117,7 @@
   if (open_tables(thd, &tables, &counter) ||
       mysql_handle_derived(thd->lex, &mysql_derived_prepare))
     DBUG_RETURN(TRUE); /* purecov: inspected */
-  relink_tables_for_multidelete(thd);           // Not really needed, but
   DBUG_RETURN(0);
-}
-
-
-/*
-  Let us propagate pointers to open tables from global table list
-  to table lists for multi-delete
-*/
-
-static void relink_tables_for_multidelete(THD *thd)
-{
-  if (thd->lex->all_selects_list->next_select_in_list())
-  {
-    for (SELECT_LEX *sl= thd->lex->all_selects_list;
-	 sl;
-	 sl= sl->next_select_in_list())
-    {
-      for (TABLE_LIST *cursor= (TABLE_LIST *) sl->table_list.first;
-           cursor;
-           cursor=cursor->next_local)
-      {
-        if (cursor->correspondent_table)
-          cursor->table= cursor->correspondent_table->table;
-      }
-    }
-  }
 }
 
 

--- 1.181/sql/sql_lex.h	Tue Jun  7 14:11:28 2005
+++ 1.182/sql/sql_lex.h	Thu Jun  9 01:07:46 2005
@@ -750,7 +750,12 @@
   uint grant, grant_tot_col, which_columns;
   uint fk_delete_opt, fk_update_opt, fk_match_option;
   uint slave_thd_opt, start_transaction_opt;
-  uint table_count; /* used when usual update transformed in multiupdate */
+  /*
+    In LEX representing update which were transformed to multi-update
+    stores total number of tables. For LEX representing multi-delete
+    holds number of tables from which we will delete records.
+  */
+  uint table_count;
   uint8 describe;
   uint8 derived_tables;
   uint8 create_view_algorithm;

--- 1.450/sql/sql_parse.cc	Tue Jun  7 16:28:05 2005
+++ 1.451/sql/sql_parse.cc	Thu Jun  9 01:07:46 2005
@@ -3291,10 +3291,9 @@
     DBUG_ASSERT(first_table == all_tables && first_table != 0);
     TABLE_LIST *aux_tables=
       (TABLE_LIST *)thd->lex->auxilliary_table_list.first;
-    uint table_count;
     multi_delete *result;
 
-    if ((res= multi_delete_precheck(thd, all_tables, &table_count)))
+    if ((res= multi_delete_precheck(thd, all_tables)))
       break;
 
     /* condition will be TRUE on SP re-excuting */
@@ -3311,7 +3310,7 @@
       goto error;
 
     if (!thd->is_fatal_error && (result= new multi_delete(thd,aux_tables,
-							  table_count)))
+							  lex->table_count)))
     {
       res= mysql_select(thd, &select_lex->ref_pointer_array,
 			select_lex->get_table_list(),
@@ -6799,23 +6798,19 @@
     multi_delete_precheck()
     thd			Thread handler
     tables		Global/local table list
-    table_count		Pointer to table counter
 
   RETURN VALUE
     FALSE OK
     TRUE  error
 */
 
-bool multi_delete_precheck(THD *thd, TABLE_LIST *tables, uint *table_count)
+bool multi_delete_precheck(THD *thd, TABLE_LIST *tables)
 {
   SELECT_LEX *select_lex= &thd->lex->select_lex;
   TABLE_LIST *aux_tables=
     (TABLE_LIST *)thd->lex->auxilliary_table_list.first;
-  TABLE_LIST *target_tbl;
   DBUG_ENTER("multi_delete_precheck");
 
-  *table_count= 0;
-
   /* sql_yacc guarantees that tables and aux_tables are not zero */
   DBUG_ASSERT(aux_tables != 0);
   if (check_db_used(thd, tables) || check_db_used(thd,aux_tables) ||
@@ -6828,9 +6823,35 @@
                ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
     DBUG_RETURN(TRUE);
   }
-  for (target_tbl= aux_tables; target_tbl; target_tbl= target_tbl->next_local)
+  DBUG_RETURN(FALSE);
+}
+
+
+/*
+  Link tables in auxilary table list of multi-delete with corresponding
+  elements in main table list, and set proper locks for them.
+
+  SYNOPSIS
+    multi_delete_set_locks_and_link_aux_tables()
+      lex - pointer to LEX representing multi-delete
+
+  RETURN VALUE
+    FALSE - success
+    TRUE  - error
+*/
+
+bool multi_delete_set_locks_and_link_aux_tables(LEX *lex)
+{
+  TABLE_LIST *tables= (TABLE_LIST*)lex->select_lex.table_list.first;
+  TABLE_LIST *target_tbl;
+  DBUG_ENTER("multi_delete_set_locks_and_link_aux_tables");
+
+  lex->table_count= 0;
+
+  for (target_tbl= (TABLE_LIST *)lex->auxilliary_table_list.first;
+       target_tbl; target_tbl= target_tbl->next_local)
   {
-    (*table_count)++;
+    lex->table_count++;
     /* All tables in aux_tables must be found in FROM PART */
     TABLE_LIST *walk;
     for (walk= tables; walk; walk= walk->next_local)
@@ -6848,14 +6869,6 @@
     }
     walk->lock_type= target_tbl->lock_type;
     target_tbl->correspondent_table= walk;	// Remember corresponding table
-    
-    /* in case of subselects, we need to set lock_type in
-     * corresponding table in list of all tables */
-    if (walk->correspondent_table)
-    {
-      target_tbl->correspondent_table= walk->correspondent_table;
-      walk->correspondent_table->lock_type= walk->lock_type;
-    }
   }
   DBUG_RETURN(FALSE);
 }

--- 1.392/sql/sql_yacc.yy	Tue Jun  7 18:47:07 2005
+++ 1.393/sql/sql_yacc.yy	Thu Jun  9 01:07:46 2005
@@ -6132,10 +6132,17 @@
 	| table_wild_list
 	  { mysql_init_multi_delete(Lex); }
           FROM join_table_list where_clause
+          { 
+            if (multi_delete_set_locks_and_link_aux_tables(Lex))
+              YYABORT;
+          }
 	| FROM table_wild_list
 	  { mysql_init_multi_delete(Lex); }
 	  USING join_table_list where_clause
-	  {}
+          { 
+            if (multi_delete_set_locks_and_link_aux_tables(Lex))
+              YYABORT;
+          }
 	;
 
 table_wild_list:

--- 1.2/mysql-test/r/sp-threads.result	Fri Apr 15 20:31:41 2005
+++ 1.3/mysql-test/r/sp-threads.result	Thu Jun  9 01:07:46 2005
@@ -40,3 +40,18 @@
 unlock tables;
 drop procedure bug9486;
 drop table t1, t2;
+drop procedure if exists bug11158;
+create procedure bug11158() delete t1 from t1, t2 where t1.id = t2.id;
+create table t1 (id int, j int);
+insert into t1 values (1, 1), (2, 2);
+create table t2 (id int);
+insert into t2 values (1);
+call bug11158();
+select * from t1;
+id	j
+2	2
+lock tables t2 read;
+call bug11158();
+unlock tables;
+drop procedure bug11158;
+drop table t1, t2;

--- 1.3/mysql-test/t/sp-threads.test	Fri Apr 15 20:31:41 2005
+++ 1.4/mysql-test/t/sp-threads.test	Thu Jun  9 01:07:46 2005
@@ -84,6 +84,32 @@
 drop procedure bug9486;
 drop table t1, t2;
 
+#
+# BUG#11158: Can't perform multi-delete in stored procedure
+#
+--disable_warnings
+drop procedure if exists bug11158;
+--enable_warnings
+create procedure bug11158() delete t1 from t1, t2 where t1.id = t2.id;
+create table t1 (id int, j int);
+insert into t1 values (1, 1), (2, 2);
+create table t2 (id int);
+insert into t2 values (1);
+# Procedure should work and cause proper effect (delete only first row)
+call bug11158();
+select * from t1;
+# Also let us test that we obtain only read (and thus non exclusive) lock
+# for table from which we are not going to delete rows.
+connection con2root;
+lock tables t2 read;
+connection con1root;
+call bug11158();
+connection con2root;
+unlock tables;
+connection con1root;
+# Clean-up
+drop procedure bug11158;
+drop table t1, t2;
 
 #
 # BUG#NNNN: New bug synopsis

--- 1.116/sql/sql_prepare.cc	Mon May 16 14:34:19 2005
+++ 1.117/sql/sql_prepare.cc	Thu Jun  9 01:07:46 2005
@@ -1448,8 +1448,7 @@
   if (add_item_to_list(stmt->thd, new Item_null()))
     return -1;
 
-  uint fake_counter;
-  if ((res= multi_delete_precheck(stmt->thd, tables, &fake_counter)))
+  if ((res= multi_delete_precheck(stmt->thd, tables)))
     return res;
   if ((res= select_like_stmt_test_with_open_n_lock(stmt, tables,
                                                    &mysql_multi_delete_prepare,