bk commit into 5.0 tree (dlenev:1.1943)

[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.1943 05/05/16 17:42:18 dlenev@stripped +6 -0
  Fix for bugs:
   #5860 "Multi-table UPDATE does not activate update triggers"
   #6812 "Triggers are not activated for INSERT ... SELECT"
   #8755 "Trigger is not activated by LOAD DATA".
  
  Note: I am not adding proper handling of errors which happen inside of
  triggers in this patch, since it is simplier to do this for all triggers'
  invocations at once. (I'm planning to fix this after we will be able to
  access tables in triggers and thus it will be easier to write tests for
  this functionality).

  sql/sql_update.cc
    1.157 05/05/16 17:42:12 dlenev@stripped +28 -5
    safe_update_on_fly():
      When we are trying to understand if we can update first table in multi
      update on the fly we should take into account that BEFORE UPDATE
      trigger can change field values.
    multi_update::send_data()/do_updates()
      We should execute proper triggers when doing multi-update
      (in both cases when we do it on the fly and using temporary tables).

  sql/sql_trigger.h
    1.7 05/05/16 17:42:12 dlenev@stripped +5 -0
    Added Table_triggers_list::has_before_update_triggers() to be able to
    determine if BEFORE UPDATE triggers exist for table.

  sql/sql_load.cc
    1.79 05/05/16 17:42:12 dlenev@stripped +33 -8
    read_fixed_length()/read_sep_field():
      We should execute INSERT triggers when processing LOAD DATA statement.
      Also small code cleanup in auto-increment related code.

  sql/sql_insert.cc
    1.149 05/05/16 17:42:12 dlenev@stripped +27 -5
    select_insert::send_data():
      We should execute INSERT triggers when processing INSERT ... SELECT
      statement.

  mysql-test/t/trigger.test
    1.10 05/05/16 17:42:12 dlenev@stripped +58 -0
    Added tests for bugs #5860 "Multi-table UPDATE does not activate update
    triggers", #6812 "Triggers are not activated for INSERT ... SELECT" and
    #8755 "Trigger is not activated by LOAD DATA".

  mysql-test/r/trigger.result
    1.6 05/05/16 17:42:12 dlenev@stripped +58 -0
    Added tests for bugs #5860 "Multi-table UPDATE does not activate update
    triggers", #6812 "Triggers are not activated for INSERT ... SELECT" and
    #8755 "Trigger is not activated by LOAD DATA".

# 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-bg5860

--- 1.148/sql/sql_insert.cc	Wed May 11 03:37:38 2005
+++ 1.149/sql/sql_insert.cc	Mon May 16 17:42:12 2005
@@ -2004,6 +2004,11 @@
   thd->count_cuted_fields= CHECK_FIELD_IGNORE;
   if (thd->net.report_error)
     DBUG_RETURN(1);
+
+  if (table->triggers)
+    table->triggers->process_triggers(thd, TRG_EVENT_INSERT,
+                                      TRG_ACTION_BEFORE);
+
   if (table_list)                               // Not CREATE ... SELECT
   {
     switch (table_list->view_check_option(thd, info.ignore)) {
@@ -2013,12 +2018,29 @@
       DBUG_RETURN(1);
     }
   }
-  if (!(error= write_record(thd, table,&info)) &&
table->next_number_field)
+  if (!(error= write_record(thd, table, &info)))
   {
-    /* Clear for next record */
-    table->next_number_field->reset();
-    if (! last_insert_id && thd->insert_id_used)
-      last_insert_id=thd->insert_id();
+    if (table->triggers)
+    {
+      table->triggers->process_triggers(thd, TRG_EVENT_INSERT,
+                                        TRG_ACTION_AFTER);
+      /*
+        If triggers exist then whey can modify some fields which were not
+        originally touched by INSERT ... SELECT, so we have to restore
+        their original values for the next row.
+      */
+      restore_record(table, s->default_values);
+    }
+    if (table->next_number_field)
+    {
+      /*
+        Clear auto-increment field for the next record, if triggers are used
+        we will clear it twice, but this should be cheap.
+      */
+      table->next_number_field->reset();
+      if (!last_insert_id && thd->insert_id_used)
+        last_insert_id= thd->insert_id();
+    }
   }
   DBUG_RETURN(error);
 }

--- 1.78/sql/sql_load.cc	Fri Apr  1 16:04:44 2005
+++ 1.79/sql/sql_load.cc	Mon May 16 17:42:12 2005
@@ -21,6 +21,8 @@
 #include <my_dir.h>
 #include <m_ctype.h>
 #include "sql_repl.h"
+#include "sp_head.h"
+#include "sql_trigger.h"
 
 class READ_INFO {
   File	file;
@@ -568,9 +570,14 @@
                           ER(ER_WARN_TOO_MANY_RECORDS), thd->row_count); 
     }
 
-    if (fill_record(thd, set_fields, set_values, ignore_check_option_errors))
+    if (fill_record(thd, set_fields, set_values, ignore_check_option_errors) ||
+        thd->killed)
       DBUG_RETURN(1);
 
+    if (table->triggers)
+      table->triggers->process_triggers(thd, TRG_EVENT_INSERT,
+                                        TRG_ACTION_BEFORE);
+
     switch (table_list->view_check_option(thd,
                                           ignore_check_option_errors)) {
     case VIEW_CHECK_SKIP:
@@ -580,8 +587,13 @@
       DBUG_RETURN(-1);
     }
 
-    if (thd->killed || write_record(thd,table,&info))
+    if (write_record(thd, table, &info))
       DBUG_RETURN(1);
+
+    if (table->triggers)
+      table->triggers->process_triggers(thd, TRG_EVENT_INSERT,
+                                        TRG_ACTION_AFTER);
+
     thd->no_trans_update= no_trans_update;
    
     /*
@@ -592,8 +604,10 @@
     */
     if (!id && thd->insert_id_used)
       id= thd->last_insert_id;
-    if (table->next_number_field)
-      table->next_number_field->reset();	// Clear for next record
+    /*
+      We don't need to reset auto-increment field since we are restoring
+      its default value at the beginning of each loop iteration.
+    */
     if (read_info.next_line())			// Skip to next line
       break;
     if (read_info.line_cuted)
@@ -725,9 +739,14 @@
       }
     }
 
-    if (fill_record(thd, set_fields, set_values, ignore_check_option_errors))
+    if (fill_record(thd, set_fields, set_values, ignore_check_option_errors) ||
+        thd->killed)
       DBUG_RETURN(1);
 
+    if (table->triggers)
+      table->triggers->process_triggers(thd, TRG_EVENT_INSERT,
+                                        TRG_ACTION_BEFORE);
+
     switch (table_list->view_check_option(thd,
                                           ignore_check_option_errors)) {
     case VIEW_CHECK_SKIP:
@@ -738,8 +757,12 @@
     }
 
 
-    if (thd->killed || write_record(thd, table, &info))
+    if (write_record(thd, table, &info))
       DBUG_RETURN(1);
+
+    if (table->triggers)
+      table->triggers->process_triggers(thd, TRG_EVENT_INSERT,
+                                        TRG_ACTION_AFTER);
     /*
       If auto_increment values are used, save the first one
        for LAST_INSERT_ID() and for the binary/update log.
@@ -748,8 +771,10 @@
     */
     if (!id && thd->insert_id_used)
       id= thd->last_insert_id;
-    if (table->next_number_field)
-      table->next_number_field->reset();	// Clear for next record
+    /*
+      We don't need to reset auto-increment field since we are restoring
+      its default value at the beginning of each loop iteration.
+    */
     thd->no_trans_update= no_trans_update;
     if (read_info.next_line())			// Skip to next line
       break;

--- 1.156/sql/sql_update.cc	Wed May 11 03:13:44 2005
+++ 1.157/sql/sql_update.cc	Mon May 16 17:42:12 2005
@@ -1073,8 +1073,8 @@
 
   NOTES
     We can update the first table in join on the fly if we know that
-    a row in this tabel will never be read twice. This is true under
-    the folloing conditions:
+    a row in this table will never be read twice. This is true under
+    the following conditions:
 
     - We are doing a table scan and the data is in a separate file (MyISAM) or
       if we don't update a clustered key.
@@ -1082,6 +1082,10 @@
     - We are doing a range scan and we don't update the scan key or
       the primary key for a clustered table handler.
 
+    When checking for above cases we also should take into account that
+    BEFORE UPDATE trigger potentially may change value of any field in row
+    being updated.
+
   WARNING
     This code is a bit dependent of how make_join_readinfo() works.
 
@@ -1099,15 +1103,21 @@
   case JT_EQ_REF:
     return TRUE;				// At most one matching row
   case JT_REF:
-    return !check_if_key_used(table, join_tab->ref.key, *fields);
+    return !check_if_key_used(table, join_tab->ref.key, *fields) &&
+           !(table->triggers &&
+             table->triggers->has_before_update_triggers());
   case JT_ALL:
     /* If range search on index */
     if (join_tab->quick)
-      return !join_tab->quick->check_if_keys_used(fields);
+      return !join_tab->quick->check_if_keys_used(fields) &&
+             !(table->triggers &&
+               table->triggers->has_before_update_triggers());
     /* If scanning in clustered key */
     if ((table->file->table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) &&
 	table->s->primary_key < MAX_KEY)
-      return !check_if_key_used(table, table->s->primary_key, *fields);
+      return !check_if_key_used(table, table->s->primary_key, *fields) &&
+             !(table->triggers &&
+               table->triggers->has_before_update_triggers());
     return TRUE;
   default:
     break;					// Avoid compler warning
@@ -1175,6 +1185,10 @@
 	DBUG_RETURN(1);
 
       found++;
+
+      if (table->triggers)
+        table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
TRG_ACTION_BEFORE);
+
       if (compare_record(table, thd->query_id))
       {
 	int error;
@@ -1210,6 +1224,9 @@
         else if (!table->file->has_transactions())
           thd->no_trans_update= 1;
       }
+
+      if (table->triggers)
+        table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER);
     }
     else
     {
@@ -1329,6 +1346,9 @@
 	   copy_field_ptr++)
 	(*copy_field_ptr->do_copy)(copy_field_ptr);
 
+      if (table->triggers)
+        table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
TRG_ACTION_BEFORE);
+
       if (compare_record(table, thd->query_id))
       {
 	if ((local_error=table->file->update_row(table->record[1],
@@ -1339,6 +1359,9 @@
 	}
 	updated++;
       }
+
+      if (table->triggers)
+        table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER);
     }
 
     if (updated != org_updated)

--- 1.5/mysql-test/r/trigger.result	Sun Mar 27 16:15:15 2005
+++ 1.6/mysql-test/r/trigger.result	Mon May 16 17:42:12 2005
@@ -206,3 +206,61 @@
 create trigger trg1 before insert on t1 for each row set @a:= 1;
 drop database mysqltest;
 use test;
+create table t1 (i int, j int default 10, k int not null, key (k));
+create table t2 (i int);
+insert into t1 (i, k) values (1, 1);
+insert into t2 values (1);
+create trigger trg1 before update on t1 for each row set @a:= @a + new.j - old.j;
+create trigger trg2 after update on t1 for each row set @b:= "Fired";
+set @a:= 0, @b:= "";
+update t1, t2 set j = j + 10 where t1.i = t2.i;
+select @a, @b;
+@a	@b
+10	Fired
+insert into t1 values (2, 13, 2);
+insert into t2 values (2);
+set @a:= 0, @b:= "";
+update t1, t2 set j = j + 15 where t1.i = t2.i and t1.k >= 2;
+select @a, @b;
+@a	@b
+15	Fired
+drop table t1, t2;
+create table t1 (i int, j int default 10)|
+create table t2 (i int)|
+insert into t2 values (1), (2)|
+create trigger trg1 before insert on t1 for each row 
+begin
+if new.i = 1 then
+set new.j := 1;
+end if;
+end|
+create trigger trg2 after insert on t1 for each row set @a:= 1|
+set @a:= 0|
+insert into t1 (i) select * from t2|
+select * from t1|
+i	j
+1	1
+2	10
+select @a|
+@a
+1
+drop table t1, t2|
+create table t1 (i int, j int, k int);
+create trigger trg1 before insert on t1 for each row set new.k = new.i;
+create trigger trg2 after insert on t1 for each row set @b:= "Fired";
+set @b:="";
+load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, i);
+select *, @b from t1;
+i	j	k	@b
+10	NULL	10	Fired
+15	NULL	15	Fired
+set @b:="";
+load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by ''
enclosed by '' (i, j);
+select *, @b from t1;
+i	j	k	@b
+10	NULL	10	Fired
+15	NULL	15	Fired
+1	2	1	Fired
+3	4	3	Fired
+5	6	5	Fired
+drop table t1;

--- 1.9/mysql-test/t/trigger.test	Sun Mar 27 16:15:15 2005
+++ 1.10/mysql-test/t/trigger.test	Mon May 16 17:42:12 2005
@@ -249,3 +249,61 @@
 # This should succeed
 drop database mysqltest;
 use test;
+
+# Test for bug #5860 "Multi-table UPDATE does not activate update triggers"
+create table t1 (i int, j int default 10, k int not null, key (k));
+create table t2 (i int);
+insert into t1 (i, k) values (1, 1);
+insert into t2 values (1);
+create trigger trg1 before update on t1 for each row set @a:= @a + new.j - old.j;
+create trigger trg2 after update on t1 for each row set @b:= "Fired";
+set @a:= 0, @b:= "";
+# Check that trigger works in case of update on the fly
+update t1, t2 set j = j + 10 where t1.i = t2.i;
+select @a, @b;
+insert into t1 values (2, 13, 2);
+insert into t2 values (2);
+set @a:= 0, @b:= "";
+# And now let us check that triggers work in case of multi-update which
+# is done through temporary tables...
+update t1, t2 set j = j + 15 where t1.i = t2.i and t1.k >= 2;
+select @a, @b;
+# This also will drop triggers
+drop table t1, t2;
+
+# Test for bug #6812 "Triggers are not activated for INSERT ... SELECT".
+# (We also check the fact that trigger modifies some field does not affect
+#  value of next record inserted).
+delimiter |;
+create table t1 (i int, j int default 10)|
+create table t2 (i int)|
+insert into t2 values (1), (2)|
+create trigger trg1 before insert on t1 for each row 
+begin
+  if new.i = 1 then
+    set new.j := 1;
+  end if;
+end|
+create trigger trg2 after insert on t1 for each row set @a:= 1|
+set @a:= 0|
+insert into t1 (i) select * from t2|
+select * from t1|
+select @a|
+# This also will drop triggers
+drop table t1, t2|
+delimiter ;|
+
+# Test for bug #8755 "Trigger is not activated by LOAD DATA"
+create table t1 (i int, j int, k int);
+create trigger trg1 before insert on t1 for each row set new.k = new.i;
+create trigger trg2 after insert on t1 for each row set @b:= "Fired"; 
+set @b:="";
+# Test triggers with file with separators
+load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, i);
+select *, @b from t1;
+set @b:="";
+# Test triggers with fixed size row file
+load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by ''
enclosed by '' (i, j);
+select *, @b from t1;
+# This also will drop triggers
+drop table t1;

--- 1.6/sql/sql_trigger.h	Tue May 10 17:55:17 2005
+++ 1.7/sql/sql_trigger.h	Mon May 16 17:42:12 2005
@@ -79,6 +79,11 @@
             bodies[TRG_EVENT_DELETE][TRG_ACTION_AFTER]);
   }
 
+  bool has_before_update_triggers()
+  {
+    return test(bodies[TRG_EVENT_UPDATE][TRG_ACTION_BEFORE]);
+  }
+
   friend class Item_trigger_field;
 
 private: