bk commit into 5.0 tree (mskold:1.2302) BUG#26242

[Martin Skold]

Below is the list of changes that have just been committed into a local
5.0 repository of marty. When marty 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-03-14 11:48:09+01:00, mskold@stripped +6 -0
  Bug #26242  UPDATE with subquery and triggers failing with cluster tables:
  Added checks if batching is possible for INSERT ON DUPLICATE UPDATE
  and REPLACE (doing DELETE). If not possible handler is inform by calls
  to extra() with HA_EXTRA_UPDATE_CANNOT_BATCH or HA_EXTRA_DELETE_CANNOT_BATCH

  include/my_base.h@stripped, 2007-03-14 11:48:06+01:00, mskold@stripped +8 -0
    Added explanatory comments

  mysql-test/r/ndb_trigger.result@stripped, 2007-03-14 11:48:06+01:00, mskold@stripped +36 -4
    Added new test cases for INSERT ON DUPLICATE and REPLACE

  mysql-test/t/ndb_trigger.test@stripped, 2007-03-14 11:48:06+01:00, mskold@stripped +12 -0
    Added new test cases for INSERT ON DUPLICATE and REPLACE

  sql/sql_delete.cc@stripped, 2007-03-14 11:48:06+01:00, mskold@stripped +3 -3
    Added checks if batching is possible for INSERT ON DUPLICATE UPDATE
    and REPLACE (doing DELETE). If not possible handler is inform by calls
    to extra() with HA_EXTRA_UPDATE_CANNOT_BATCH or HA_EXTRA_DELETE_CANNOT_BATCH

  sql/sql_insert.cc@stripped, 2007-03-14 11:48:06+01:00, mskold@stripped +23 -0
    Untabified

  sql/sql_update.cc@stripped, 2007-03-14 11:48:06+01:00, mskold@stripped +3 -3
    Untabified

# 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:	mskold
# Host:	linux.site
# Root:	/windows/Linux_space/MySQL/mysql-5.0

--- 1.82/include/my_base.h	2007-03-14 11:48:17 +01:00
+++ 1.83/include/my_base.h	2007-03-14 11:48:17 +01:00
@@ -162,7 +162,15 @@ enum ha_extra_function {
   */
   HA_EXTRA_WRITE_CAN_REPLACE,
   HA_EXTRA_WRITE_CANNOT_REPLACE,
+  /*
+    Informs handler that delete_row() cannot be batched since there are
+    triggers needing the operation to have been executed when returning.
+   */
   HA_EXTRA_DELETE_CANNOT_BATCH,
+  /*
+    Informs handler that update_row() cannot be batched since there are
+    triggers needing the operation to have been executed when returning.
+   */
   HA_EXTRA_UPDATE_CANNOT_BATCH
 };
 

--- 1.182/sql/sql_delete.cc	2007-03-14 11:48:17 +01:00
+++ 1.183/sql/sql_delete.cc	2007-03-14 11:48:17 +01:00
@@ -212,11 +212,11 @@ bool mysql_delete(THD *thd, TABLE_LIST *
   {
     table->triggers->mark_fields_used(thd, TRG_EVENT_DELETE);
     if (table->triggers->has_triggers(TRG_EVENT_DELETE,
-				      TRG_ACTION_AFTER))
+                                      TRG_ACTION_AFTER))
     {
       /*
-	The table table has AFTER DELETE triggers that need delete
-	to be done immediately, no batching is possible
+        The table table has AFTER DELETE triggers that need delete
+        to be done immediately, no batching is possible
       */
       (void) table->file->extra(HA_EXTRA_DELETE_CANNOT_BATCH);
     }

--- 1.205/sql/sql_insert.cc	2007-03-14 11:48:17 +01:00
+++ 1.206/sql/sql_insert.cc	2007-03-14 11:48:17 +01:00
@@ -1134,6 +1134,17 @@ int write_record(THD *thd, TABLE *table,
         if (res == VIEW_CHECK_ERROR)
           goto before_trg_err;
 
+        if (table->triggers &&
+            table->triggers->has_triggers(TRG_EVENT_UPDATE,
+                                          TRG_ACTION_AFTER))
+        {
+          /*
+            The table table has AFTER UPDATE triggers that need update
+            to be done immediately, no batching is possible
+          */
+          (void) table->file->extra(HA_EXTRA_UPDATE_CANNOT_BATCH);
+        }
+
         if
((error=table->file->update_row(table->record[1],table->record[0])))
 	{
 	  if ((error == HA_ERR_FOUND_DUPP_KEY) && info->ignore)
@@ -1192,6 +1203,18 @@ int write_record(THD *thd, TABLE *table,
               table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
                                                 TRG_ACTION_BEFORE, TRUE))
             goto before_trg_err;
+
+          if (table->triggers &&
+              table->triggers->has_triggers(TRG_EVENT_DELETE,
+                                            TRG_ACTION_AFTER))
+          {
+            /*
+              The table table has AFTER DELETE triggers that need delete
+              to be done immediately, no batching is possible
+            */
+            (void) table->file->extra(HA_EXTRA_DELETE_CANNOT_BATCH);
+          }
+
           if ((error=table->file->delete_row(table->record[1])))
             goto err;
           info->deleted++;

--- 1.202/sql/sql_update.cc	2007-03-14 11:48:17 +01:00
+++ 1.203/sql/sql_update.cc	2007-03-14 11:48:17 +01:00
@@ -438,11 +438,11 @@ int mysql_update(THD *thd,
   {
     table->triggers->mark_fields_used(thd, TRG_EVENT_UPDATE);
     if (table->triggers->has_triggers(TRG_EVENT_UPDATE,
-				      TRG_ACTION_AFTER))
+                                      TRG_ACTION_AFTER))
     {
       /*
-	The table table has AFTER UPDATE triggers that need update
-	to be done immediately, no batching is possible
+        The table table has AFTER UPDATE triggers that need update
+        to be done immediately, no batching is possible
       */
       (void) table->file->extra(HA_EXTRA_UPDATE_CANNOT_BATCH);
     }

--- 1.2/mysql-test/r/ndb_trigger.result	2007-03-14 11:48:17 +01:00
+++ 1.3/mysql-test/r/ndb_trigger.result	2007-03-14 11:48:17 +01:00
@@ -143,6 +143,13 @@ xy
 SELECT xy FROM t2 where id = 1;
 xy
 4
+INSERT INTO t1 VALUES (1,0) ON DUPLICATE KEY UPDATE xy = 5;
+SELECT xy FROM t1 where id = 1;
+xy
+5
+SELECT xy FROM t2 where id = 1;
+xy
+5
 DROP TRIGGER t1_update;
 CREATE TRIGGER t1_delete AFTER DELETE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT *
FROM t1 WHERE t1.id > 4; END //
 INSERT INTO t1 VALUES (5, 0),(6,0);
@@ -150,23 +157,48 @@ INSERT INTO t2 VALUES (5, 1),(6,1);
 INSERT INTO t3 VALUES (5);
 SELECT * FROM t1 order by id;
 id	xy
-1	4
+1	5
 5	0
 6	0
 SELECT * FROM t2 order by id;
 id	xy
-1	4
+1	5
 2	0
 5	1
 6	1
 DELETE FROM t1 WHERE id IN (SELECT id FROM t3 WHERE id = 5);
 SELECT * FROM t1 order by id;
 id	xy
-1	4
+1	5
 6	0
 SELECT * FROM t2 order by id;
 id	xy
-1	4
+1	5
+2	0
+5	1
+6	0
+INSERT INTO t1 VALUES (5, 0);
+REPLACE INTO t2 VALUES (6,1);
+SELECT * FROM t1 order by id;
+id	xy
+1	5
+5	0
+6	0
+SELECT * FROM t2 order by id;
+id	xy
+1	5
+2	0
+5	1
+6	1
+REPLACE INTO t1 VALUES (5, 1);
+SELECT * FROM t1 order by id;
+id	xy
+1	5
+5	1
+6	0
+SELECT * FROM t2 order by id;
+id	xy
+1	5
 2	0
 5	1
 6	0

--- 1.2/mysql-test/t/ndb_trigger.test	2007-03-14 11:48:17 +01:00
+++ 1.3/mysql-test/t/ndb_trigger.test	2007-03-14 11:48:17 +01:00
@@ -123,6 +123,10 @@ UPDATE t1 SET xy  = 4 WHERE id IN (SELEC
 SELECT xy FROM t1 where id = 1;
 SELECT xy FROM t2 where id = 1;
 
+INSERT INTO t1 VALUES (1,0) ON DUPLICATE KEY UPDATE xy = 5;
+SELECT xy FROM t1 where id = 1;
+SELECT xy FROM t2 where id = 1;
+
 DROP TRIGGER t1_update;
 
 DELIMITER //;
@@ -135,6 +139,14 @@ INSERT INTO t3 VALUES (5);
 SELECT * FROM t1 order by id;
 SELECT * FROM t2 order by id;
 DELETE FROM t1 WHERE id IN (SELECT id FROM t3 WHERE id = 5);
+SELECT * FROM t1 order by id;
+SELECT * FROM t2 order by id;
+
+INSERT INTO t1 VALUES (5, 0);
+REPLACE INTO t2 VALUES (6,1);
+SELECT * FROM t1 order by id;
+SELECT * FROM t2 order by id;
+REPLACE INTO t1 VALUES (5, 1);
 SELECT * FROM t1 order by id;
 SELECT * FROM t2 order by id;