[Commits] Rev 3187: Fixed bug mdev-3845. in file:///home/igor/maria/maria-5.2-mdev3845/

Igor Babaev igor at askmonty.org
Sat Nov 10 09:26:15 EET 2012


At file:///home/igor/maria/maria-5.2-mdev3845/

------------------------------------------------------------
revno: 3187
revision-id: igor at askmonty.org-20121110072610-ubgy5ltxmx873oia
parent: timour at askmonty.org-20121109104912-hu5asxvn8ip8kuhg
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.2-mdev3845
timestamp: Fri 2012-11-09 23:26:10 -0800
message:
  Fixed bug mdev-3845.
  If triggers are used for an insert/update/delete statement than the values of
  all virtual columns must be computed as any of them may be used by the triggers.
-------------- next part --------------
=== modified file 'mysql-test/suite/vcol/inc/vcol_trigger_sp.inc'
--- a/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc	2009-10-16 22:57:48 +0000
+++ b/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc	2012-11-10 07:26:10 +0000
@@ -108,3 +108,44 @@
 
 drop table t1,t2;
 drop procedure p1;
+
+--echo #
+--echo # Bug mdev-3845: values of virtual columns are not computed for triggers
+--echo #
+
+CREATE TABLE t1 (
+  a INTEGER UNSIGNED NULL DEFAULT NULL,
+  b INTEGER UNSIGNED GENERATED ALWAYS AS (a) VIRTUAL
+);
+
+CREATE TABLE t2 (c INTEGER UNSIGNED NOT NULL);
+
+DELIMITER |;
+
+CREATE TRIGGER t1_ins_aft
+  AFTER INSERT
+  ON t1
+  FOR EACH ROW
+BEGIN
+  INSERT INTO t2 (c) VALUES (NEW.b);
+END |
+
+CREATE TRIGGER t1_del_bef
+  BEFORE DELETE
+  ON t1
+  FOR EACH ROW
+BEGIN
+  INSERT INTO t2 (c) VALUES (OLD.b);
+END |
+
+DELIMITER ;|
+
+INSERT INTO t1 (a) VALUES (1), (2), (3);
+SELECT * FROM t2;
+DELETE FROM t1;
+SELECT * FROM t2;
+
+DROP TRIGGER t1_ins_aft;
+DROP TRIGGER t1_del_bef;
+DROP TABLE t1,t2;
+

=== modified file 'mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result'
--- a/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result	2009-10-16 22:57:48 +0000
+++ b/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result	2012-11-10 07:26:10 +0000
@@ -85,3 +85,43 @@
 300	30	30
 drop table t1,t2;
 drop procedure p1;
+#
+# Bug mdev-3845: values of virtual columns are not computed for triggers
+#
+CREATE TABLE t1 (
+a INTEGER UNSIGNED NULL DEFAULT NULL,
+b INTEGER UNSIGNED GENERATED ALWAYS AS (a) VIRTUAL
+);
+CREATE TABLE t2 (c INTEGER UNSIGNED NOT NULL);
+CREATE TRIGGER t1_ins_aft
+AFTER INSERT
+ON t1
+FOR EACH ROW
+BEGIN
+INSERT INTO t2 (c) VALUES (NEW.b);
+END |
+CREATE TRIGGER t1_del_bef
+BEFORE DELETE
+ON t1
+FOR EACH ROW
+BEGIN
+INSERT INTO t2 (c) VALUES (OLD.b);
+END |
+INSERT INTO t1 (a) VALUES (1), (2), (3);
+SELECT * FROM t2;
+c
+1
+2
+3
+DELETE FROM t1;
+SELECT * FROM t2;
+c
+1
+2
+3
+1
+2
+3
+DROP TRIGGER t1_ins_aft;
+DROP TRIGGER t1_del_bef;
+DROP TABLE t1,t2;

=== modified file 'mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result'
--- a/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result	2009-10-16 22:57:48 +0000
+++ b/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result	2012-11-10 07:26:10 +0000
@@ -85,3 +85,43 @@
 300	30	30
 drop table t1,t2;
 drop procedure p1;
+#
+# Bug mdev-3845: values of virtual columns are not computed for triggers
+#
+CREATE TABLE t1 (
+a INTEGER UNSIGNED NULL DEFAULT NULL,
+b INTEGER UNSIGNED GENERATED ALWAYS AS (a) VIRTUAL
+);
+CREATE TABLE t2 (c INTEGER UNSIGNED NOT NULL);
+CREATE TRIGGER t1_ins_aft
+AFTER INSERT
+ON t1
+FOR EACH ROW
+BEGIN
+INSERT INTO t2 (c) VALUES (NEW.b);
+END |
+CREATE TRIGGER t1_del_bef
+BEFORE DELETE
+ON t1
+FOR EACH ROW
+BEGIN
+INSERT INTO t2 (c) VALUES (OLD.b);
+END |
+INSERT INTO t1 (a) VALUES (1), (2), (3);
+SELECT * FROM t2;
+c
+1
+2
+3
+DELETE FROM t1;
+SELECT * FROM t2;
+c
+1
+2
+3
+1
+2
+3
+DROP TRIGGER t1_ins_aft;
+DROP TRIGGER t1_del_bef;
+DROP TABLE t1,t2;

=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h	2012-08-24 17:12:47 +0000
+++ b/sql/mysql_priv.h	2012-11-10 07:26:10 +0000
@@ -1366,7 +1366,8 @@
                     bool allow_rowid, uint *cached_field_index_ptr);
 Field *
 find_field_in_table_sef(TABLE *table, const char *name);
-int update_virtual_fields(THD *thd, TABLE *table, bool ignore_stored= FALSE);
+int update_virtual_fields(THD *thd, TABLE *table,
+      enum enum_vcol_update_mode vcol_update_mode= VCOL_UPDATE_FOR_READ);
 
 #endif /* MYSQL_SERVER */
 

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2012-08-24 13:37:39 +0000
+++ b/sql/sql_base.cc	2012-11-10 07:26:10 +0000
@@ -8460,7 +8460,9 @@
   {
     if (vcol_table->vfield)
     {
-      if (update_virtual_fields(thd, vcol_table, TRUE))
+      if (update_virtual_fields(thd, vcol_table,
+                                vcol_table->triggers ? VCOL_UPDATE_ALL :
+                                                       VCOL_UPDATE_FOR_WRITE))
         goto err;
     }
   }
@@ -8524,7 +8526,9 @@
       if (item_field && item_field->field &&
           (table= item_field->field->table) &&
         table->vfield)
-        result= update_virtual_fields(thd, table, TRUE);
+        result= update_virtual_fields(thd, table,
+                                      table->triggers ? VCOL_UPDATE_ALL :
+                                                        VCOL_UPDATE_FOR_WRITE);
     }
   }
   return result;
@@ -8604,7 +8608,10 @@
   }
   /* Update virtual fields*/
   thd->abort_on_warning= FALSE;
-  if (table->vfield && update_virtual_fields(thd, table, TRUE))
+  if (table->vfield &&
+      update_virtual_fields(thd, table, 
+                            table->triggers ? VCOL_UPDATE_ALL :
+                                              VCOL_UPDATE_FOR_WRITE))
     goto err;
   thd->abort_on_warning= abort_on_warning_saved;
   DBUG_RETURN(thd->is_error());
@@ -8657,7 +8664,9 @@
   {
     TABLE *table= (*ptr)->table;
     if (table->vfield)
-      result= update_virtual_fields(thd, table, TRUE);
+      result= update_virtual_fields(thd, table,
+                                    table->triggers ? VCOL_UPDATE_ALL : 
+                                                      VCOL_UPDATE_FOR_WRITE);
   }
   return result;
 

=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	2012-02-03 10:24:55 +0000
+++ b/sql/sql_delete.cc	2012-11-10 07:26:10 +0000
@@ -313,7 +313,9 @@
   while (!(error=info.read_record(&info)) && !thd->killed &&
 	 ! thd->is_error())
   {
-    update_virtual_fields(thd, table);
+    update_virtual_fields(thd, table,
+                          triggers_applicable ? VCOL_UPDATE_ALL :
+                                                VCOL_UPDATE_FOR_READ);
     thd->examined_row_count++;
     // thd->is_error() is tested to disallow delete row on error
     if (!select || select->skip_record(thd) > 0)

=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc	2012-08-24 10:32:46 +0000
+++ b/sql/sql_table.cc	2012-11-10 07:26:10 +0000
@@ -8066,7 +8066,7 @@
       copy_ptr->do_copy(copy_ptr);
     }
     prev_insert_id= to->file->next_insert_id;
-    update_virtual_fields(thd, to, TRUE);
+    update_virtual_fields(thd, to, VCOL_UPDATE_FOR_WRITE);
     if (thd->is_error())
     {
       error= 1;

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2012-02-03 08:28:23 +0000
+++ b/sql/sql_update.cc	2012-11-10 07:26:10 +0000
@@ -490,7 +490,9 @@
 
       while (!(error=info.read_record(&info)) && !thd->killed)
       {
-        update_virtual_fields(thd, table);
+        update_virtual_fields(thd, table,
+                              table->triggers ? VCOL_UPDATE_ALL :
+                                                VCOL_UPDATE_FOR_READ);
         thd->examined_row_count++;
 	if (!select || (error= select->skip_record(thd)) > 0)
 	{
@@ -605,7 +607,9 @@
 
   while (!(error=info.read_record(&info)) && !thd->killed)
   {
-    update_virtual_fields(thd, table);
+    update_virtual_fields(thd, table, 
+                          table->triggers ? VCOL_UPDATE_ALL :
+                                            VCOL_UPDATE_FOR_READ);
     thd->examined_row_count++;
     if (!select || select->skip_record(thd) > 0)
     {

=== modified file 'sql/table.cc'
--- a/sql/table.cc	2012-06-19 05:32:17 +0000
+++ b/sql/table.cc	2012-11-10 07:26:10 +0000
@@ -5500,22 +5500,25 @@
 
   @param  thd              Thread handle
   @param  table            The TABLE object
-  @param  for_write        Requests to compute only fields needed for write   
+  @param  vcol_update_mode Specifies what virtual column are computed   
   
   @details
     The function computes the values of the virtual columns of the table and
     stores them in the table record buffer.
-    Only fields from vcol_set are computed, and, when the flag for_write is not
-    set to TRUE, a virtual field is computed only if it's not stored.
-    The flag for_write is set to TRUE for row insert/update operations. 
- 
+    If vcol_update_mode is set to VCOL_UPDATE_ALL then all virtual column are
+    computed. Otherwise, only fields from vcol_set are computed: all of them,
+    if vcol_update_mode is set to VCOL_UPDATE_FOR_WRITE, and, only those with
+    the stored_in_db flag set to false, if vcol_update_mode is equal to
+    VCOL_UPDATE_FOR_READ.
+
   @retval
     0    Success
   @retval
     >0   Error occurred when storing a virtual field value
 */
 
-int update_virtual_fields(THD *thd, TABLE *table, bool for_write)
+int update_virtual_fields(THD *thd, TABLE *table,
+                          enum enum_vcol_update_mode vcol_update_mode)
 {
   DBUG_ENTER("update_virtual_fields");
   Field **vfield_ptr, *vfield;
@@ -5529,9 +5532,9 @@
   {
     vfield= (*vfield_ptr);
     DBUG_ASSERT(vfield->vcol_info && vfield->vcol_info->expr_item);
-    /* Only update those fields that are marked in the vcol_set bitmap */
-    if (bitmap_is_set(table->vcol_set, vfield->field_index) &&
-        (for_write || !vfield->stored_in_db))
+    if ((bitmap_is_set(table->vcol_set, vfield->field_index) &&
+         (vcol_update_mode == VCOL_UPDATE_FOR_WRITE || !vfield->stored_in_db)) ||
+        vcol_update_mode == VCOL_UPDATE_ALL)
     {
       /* Compute the actual value of the virtual fields */
       error= vfield->vcol_info->expr_item->save_in_field(vfield, 0);

=== modified file 'sql/table.h'
--- a/sql/table.h	2012-04-05 10:01:52 +0000
+++ b/sql/table.h	2012-11-10 07:26:10 +0000
@@ -156,6 +156,13 @@
 
 enum release_type { RELEASE_NORMAL, RELEASE_WAIT_FOR_DROP };
 
+enum enum_vcol_update_mode
+{
+  VCOL_UPDATE_FOR_READ= 0,
+  VCOL_UPDATE_FOR_WRITE,
+  VCOL_UPDATE_ALL
+};
+
 typedef struct st_filesort_info
 {
   IO_CACHE *io_cache;           /* If sorted through filesort */



More information about the commits mailing list