bk commit into 5.0 tree (mskold:1.2301) 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-02-27 11:00:36+01:00, mskold@stripped +8 -0
  Bug #26242  UPDATE with subquery and triggers failing with cluster tables: Use
HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when
batching of delete/update is not possible

  include/my_base.h@stripped, 2007-02-27 11:00:14+01:00, mskold@stripped +3 -1
    Bug #26242  UPDATE with subquery and triggers failing with cluster tables: Use
HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when
batching of delete/update is not possible

  mysql-test/r/ndb_trigger.result@stripped, 2007-02-27 11:00:14+01:00, mskold@stripped +56 -0
    Bug #26242  UPDATE with subquery and triggers failing with cluster tables: Use
HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when
batching of delete/update is not possible

  mysql-test/t/ndb_trigger.test@stripped, 2007-02-27 11:00:14+01:00, mskold@stripped +53 -0
    Bug #26242  UPDATE with subquery and triggers failing with cluster tables: Use
HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when
batching of delete/update is not possible

  sql/ha_ndbcluster.cc@stripped, 2007-02-27 11:00:14+01:00, mskold@stripped +18 -3
    Bug #26242  UPDATE with subquery and triggers failing with cluster tables: Use
HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when
batching of delete/update is not possible

  sql/ha_ndbcluster.h@stripped, 2007-02-27 11:00:14+01:00, mskold@stripped +2 -0
    Bug #26242  UPDATE with subquery and triggers failing with cluster tables: Use
HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when
batching of delete/update is not possible

  sql/sql_delete.cc@stripped, 2007-02-27 11:00:15+01:00, mskold@stripped +11 -0
    Bug #26242  UPDATE with subquery and triggers failing with cluster tables: Use
HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when
batching of delete/update is not possible

  sql/sql_trigger.h@stripped, 2007-02-27 11:00:15+01:00, mskold@stripped +5 -0
    Bug #26242  UPDATE with subquery and triggers failing with cluster tables: Use
HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when
batching of delete/update is not possible

  sql/sql_update.cc@stripped, 2007-02-27 11:00:15+01:00, mskold@stripped +11 -0
    Bug #26242  UPDATE with subquery and triggers failing with cluster tables: Use
HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when
batching of delete/update is not possible

# 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.81/include/my_base.h	2007-02-27 11:00:47 +01:00
+++ 1.82/include/my_base.h	2007-02-27 11:00:47 +01:00
@@ -161,7 +161,9 @@ enum ha_extra_function {
     Off by default.
   */
   HA_EXTRA_WRITE_CAN_REPLACE,
-  HA_EXTRA_WRITE_CANNOT_REPLACE
+  HA_EXTRA_WRITE_CANNOT_REPLACE,
+  HA_EXTRA_DELETE_CANNOT_BATCH,
+  HA_EXTRA_UPDATE_CANNOT_BATCH
 };
 
 	/* The following is parameter to ha_panic() */

--- 1.181/sql/sql_delete.cc	2007-02-27 11:00:47 +01:00
+++ 1.182/sql/sql_delete.cc	2007-02-27 11:00:47 +01:00
@@ -209,7 +209,18 @@ bool mysql_delete(THD *thd, TABLE_LIST *
   thd->proc_info="updating";
 
   if (table->triggers)
+  {
     table->triggers->mark_fields_used(thd, TRG_EVENT_DELETE);
+    if (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);
+    }
+  }
 
   while (!(error=info.read_record(&info)) && !thd->killed &&
 	 !thd->net.report_error)

--- 1.201/sql/sql_update.cc	2007-02-27 11:00:47 +01:00
+++ 1.202/sql/sql_update.cc	2007-02-27 11:00:47 +01:00
@@ -435,7 +435,18 @@ int mysql_update(THD *thd,
                                 MODE_STRICT_ALL_TABLES)));
 
   if (table->triggers)
+  {
     table->triggers->mark_fields_used(thd, TRG_EVENT_UPDATE);
+    if (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);
+    }
+  }
 
   /*
     We can use compare_record() to optimize away updates if

--- 1.1/mysql-test/r/ndb_trigger.result	2007-02-27 11:00:47 +01:00
+++ 1.2/mysql-test/r/ndb_trigger.result	2007-02-27 11:00:47 +01:00
@@ -116,4 +116,60 @@ op	a	b
 d	1	1.050000000000000000000000000000
 d	2	2.050000000000000000000000000000
 drop tables t1, t2, t3;
+CREATE TABLE t1 (
+id INT NOT NULL PRIMARY KEY,
+xy INT
+) ENGINE=ndbcluster;
+INSERT INTO t1 VALUES (1, 0);
+CREATE TRIGGER t1_update AFTER UPDATE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT *
FROM t1 WHERE t1.id = NEW.id; END //
+CREATE TABLE t2 (
+id INT NOT NULL PRIMARY KEY,
+xy INT
+) ENGINE=ndbcluster;
+INSERT INTO t2 VALUES (2, 0);
+CREATE TABLE t3 (id INT NOT NULL PRIMARY KEY) ENGINE=ndbcluster;
+INSERT INTO t3 VALUES (1);
+UPDATE t1 SET xy = 3 WHERE id = 1;
+SELECT xy FROM t1 where id = 1;
+xy
+3
+SELECT xy FROM t2 where id = 1;
+xy
+3
+UPDATE t1 SET xy  = 4 WHERE id IN (SELECT id FROM t3 WHERE id = 1);
+SELECT xy FROM t1 where id = 1;
+xy
+4
+SELECT xy FROM t2 where id = 1;
+xy
+4
+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);
+INSERT INTO t2 VALUES (5, 1),(6,1);
+INSERT INTO t3 VALUES (5);
+SELECT * FROM t1 order by id;
+id	xy
+1	4
+5	0
+6	0
+SELECT * FROM t2 order by id;
+id	xy
+1	4
+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
+6	0
+SELECT * FROM t2 order by id;
+id	xy
+1	4
+2	0
+5	1
+6	0
+DROP TRIGGER t1_delete;
+DROP TABLE t1, t2, t3;
 End of 5.0 tests

--- 1.1/mysql-test/t/ndb_trigger.test	2007-02-27 11:00:47 +01:00
+++ 1.2/mysql-test/t/ndb_trigger.test	2007-02-27 11:00:47 +01:00
@@ -89,4 +89,57 @@ select * from t2 order by op, a, b;
 
 drop tables t1, t2, t3;
 
+# Test for bug#26242
+# Verify that AFTER UPDATE/DELETE triggers are executed
+# after the change has actually taken place
+
+CREATE TABLE t1 (
+  id INT NOT NULL PRIMARY KEY,
+  xy INT
+) ENGINE=ndbcluster;
+
+INSERT INTO t1 VALUES (1, 0);
+
+DELIMITER //;
+CREATE TRIGGER t1_update AFTER UPDATE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT *
FROM t1 WHERE t1.id = NEW.id; END //
+DELIMITER ;//
+
+CREATE TABLE t2 (
+  id INT NOT NULL PRIMARY KEY,
+  xy INT
+) ENGINE=ndbcluster;
+
+INSERT INTO t2 VALUES (2, 0);
+
+CREATE TABLE t3 (id INT NOT NULL PRIMARY KEY) ENGINE=ndbcluster;
+
+INSERT INTO t3 VALUES (1);
+
+UPDATE t1 SET xy = 3 WHERE id = 1;
+SELECT xy FROM t1 where id = 1;
+SELECT xy FROM t2 where id = 1;
+
+UPDATE t1 SET xy  = 4 WHERE id IN (SELECT id FROM t3 WHERE id = 1);
+SELECT xy FROM t1 where id = 1;
+SELECT xy FROM t2 where id = 1;
+
+DROP TRIGGER t1_update;
+
+DELIMITER //;
+CREATE TRIGGER t1_delete AFTER DELETE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT *
FROM t1 WHERE t1.id > 4; END //
+DELIMITER ;//
+
+INSERT INTO t1 VALUES (5, 0),(6,0);
+INSERT INTO t2 VALUES (5, 1),(6,1);
+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;
+
+
+DROP TRIGGER t1_delete;
+DROP TABLE t1, t2, t3;
+
 --echo End of 5.0 tests

--- 1.22/sql/sql_trigger.h	2007-02-27 11:00:47 +01:00
+++ 1.23/sql/sql_trigger.h	2007-02-27 11:00:47 +01:00
@@ -110,6 +110,11 @@ public:
                                 const char *old_table,
                                 const char *new_db,
                                 const char *new_table);
+  bool has_triggers(trg_event_type event_type, 
+		    trg_action_time_type action_time)
+  {
+    return (bodies[event_type][action_time]);
+  }
   bool has_delete_triggers()
   {
     return (bodies[TRG_EVENT_DELETE][TRG_ACTION_BEFORE] ||

--- 1.296/sql/ha_ndbcluster.cc	2007-02-27 11:00:47 +01:00
+++ 1.297/sql/ha_ndbcluster.cc	2007-02-27 11:00:47 +01:00
@@ -2524,8 +2524,11 @@ int ha_ndbcluster::update_row(const byte
       ERR_RETURN(op->getNdbError());
   }
 
-  // Execute update operation
-  if (!cursor && execute_no_commit(this,trans,false) != 0) {
+  // Execute update operation if we are not doing a scan for update
+  // and there exist UPDATE AFTER triggers
+
+  if ((!cursor || m_update_cannot_batch) && 
+      execute_no_commit(this,trans,false) != 0) {
     no_uncommitted_rows_execute_failure();
     DBUG_RETURN(ndb_err(trans));
   }
@@ -2566,7 +2569,7 @@ int ha_ndbcluster::delete_row(const byte
 
     no_uncommitted_rows_update(-1);
 
-    if (!m_primary_key_update)
+    if (!(m_primary_key_update || m_delete_cannot_batch))
       // If deleting from cursor, NoCommit will be handled in next_result
       DBUG_RETURN(0);
   }
@@ -3277,6 +3280,8 @@ int ha_ndbcluster::extra(enum ha_extra_f
     DBUG_PRINT("info", ("HA_EXTRA_RESET"));
     DBUG_PRINT("info", ("Clearing condition stack"));
     cond_clear();
+    m_delete_cannot_batch= FALSE;
+    m_update_cannot_batch= FALSE;
     break;
   case HA_EXTRA_CACHE:                 /* Cash record in HA_rrnd() */
     DBUG_PRINT("info", ("HA_EXTRA_CACHE"));
@@ -3393,6 +3398,14 @@ int ha_ndbcluster::extra(enum ha_extra_f
     DBUG_PRINT("info", ("Turning OFF use of write instead of insert"));
     m_use_write= FALSE;
     break;
+  case HA_EXTRA_DELETE_CANNOT_BATCH:
+    DBUG_PRINT("info", ("HA_EXTRA_DELETE_CANNOT_BATCH"));
+    m_delete_cannot_batch= TRUE;
+    break;
+  case HA_EXTRA_UPDATE_CANNOT_BATCH:
+    DBUG_PRINT("info", ("HA_EXTRA_UPDATE_CANNOT_BATCH"));
+    m_update_cannot_batch= TRUE;
+    break;
   }
   
   DBUG_RETURN(0);
@@ -4744,6 +4757,8 @@ ha_ndbcluster::ha_ndbcluster(TABLE *tabl
   m_bulk_insert_rows((ha_rows) 1024),
   m_rows_changed((ha_rows) 0),
   m_bulk_insert_not_flushed(FALSE),
+  m_delete_cannot_batch(FALSE),
+  m_update_cannot_batch(FALSE),
   m_ops_pending(0),
   m_skip_auto_increment(TRUE),
   m_blobs_pending(0),

--- 1.109/sql/ha_ndbcluster.h	2007-02-27 11:00:47 +01:00
+++ 1.110/sql/ha_ndbcluster.h	2007-02-27 11:00:47 +01:00
@@ -774,6 +774,8 @@ bool uses_blob_value(bool all_fields);
   ha_rows m_bulk_insert_rows;
   ha_rows m_rows_changed;
   bool m_bulk_insert_not_flushed;
+  bool m_delete_cannot_batch;
+  bool m_update_cannot_batch;
   ha_rows m_ops_pending;
   bool m_skip_auto_increment;
   bool m_blobs_pending;