[Commits] 1652864: MDEV-9093 Persistent computed column is not updated when update query contains join

OleksandrByelkin sanja at mariadb.com
Mon Dec 14 19:25:06 EET 2015


revision-id: 1652864bd85a3a670e0e4370a27f231653b3092e (mariadb-10.0.22-57-g1652864)
parent(s): 3e206a518dec400e084451165f633b78eb2e7fee
committer: Oleksandr Byelkin
timestamp: 2015-12-14 18:25:06 +0100
message:

MDEV-9093 Persistent computed column is not updated when update query contains join

added lost virtual fields update call

---
 mysql-test/suite/vcol/inc/vcol_ins_upd.inc         | 27 ++++++++++++++++++++++
 mysql-test/suite/vcol/r/vcol_ins_upd_innodb.result | 26 +++++++++++++++++++++
 mysql-test/suite/vcol/r/vcol_ins_upd_myisam.result | 26 +++++++++++++++++++++
 sql/sql_update.cc                                  |  6 +++++
 4 files changed, 85 insertions(+)

diff --git a/mysql-test/suite/vcol/inc/vcol_ins_upd.inc b/mysql-test/suite/vcol/inc/vcol_ins_upd.inc
index 8d0a51f..e54a2db 100644
--- a/mysql-test/suite/vcol/inc/vcol_ins_upd.inc
+++ b/mysql-test/suite/vcol/inc/vcol_ins_upd.inc
@@ -287,3 +287,30 @@ select * from t1;
 
 set sql_warnings = 0;
 drop table t1;
+
+--echo #
+--echo # MDEV-9093: Persistent computed column is not updated when
+--echo # update query contains join
+--echo #
+
+CREATE TABLE `t1` (
+  `id` bigint(20) NOT NULL,
+  `name` varchar(254) DEFAULT NULL,
+  `name_hash` varchar(64) AS (sha2(name, 256)) PERSISTENT,
+  PRIMARY KEY (`id`)
+);
+
+insert into t1(id,name) values (2050, 'name1'),(2051, 'name2'),(2041, 'name3');
+
+create table t2 (id bigint);
+insert into t2 values (2050),(2051),(2041);
+
+select * from t1;
+
+update t1 join t2 using(id) set name = concat(name,
+'+1') where t1.id in (2051,2041);
+
+select * from t1;
+
+drop table t1,t2;
+
diff --git a/mysql-test/suite/vcol/r/vcol_ins_upd_innodb.result b/mysql-test/suite/vcol/r/vcol_ins_upd_innodb.result
index 44fcae7..a2d89e8 100644
--- a/mysql-test/suite/vcol/r/vcol_ins_upd_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_ins_upd_innodb.result
@@ -425,3 +425,29 @@ select * from t1;
 a	b	c	d
 set sql_warnings = 0;
 drop table t1;
+#
+# MDEV-9093: Persistent computed column is not updated when
+# update query contains join
+#
+CREATE TABLE `t1` (
+`id` bigint(20) NOT NULL,
+`name` varchar(254) DEFAULT NULL,
+`name_hash` varchar(64) AS (sha2(name, 256)) PERSISTENT,
+PRIMARY KEY (`id`)
+);
+insert into t1(id,name) values (2050, 'name1'),(2051, 'name2'),(2041, 'name3');
+create table t2 (id bigint);
+insert into t2 values (2050),(2051),(2041);
+select * from t1;
+id	name	name_hash
+2041	name3	b218ca013905fc528204bdadf9e104acd87d646a2d90ef834526fbf85b17e690
+2050	name1	9367417d63903350aeb7e092bca792263d4fd82d4912252e014e073a8931b4c1
+2051	name2	069587dcb8f8b63329ae53051ba79ba34ba0deb41c7a1e044280d7b6bb15e4f0
+update t1 join t2 using(id) set name = concat(name,
+'+1') where t1.id in (2051,2041);
+select * from t1;
+id	name	name_hash
+2041	name3+1	d6dc973da89612d369de19bb1b7696fd6bacd5276e1e2512a30a4001d608aa71
+2050	name1	9367417d63903350aeb7e092bca792263d4fd82d4912252e014e073a8931b4c1
+2051	name2+1	2dcb7ad37316c779a09a9a8b6b898c41a3039b60fd37b931b283ea5c56d76779
+drop table t1,t2;
diff --git a/mysql-test/suite/vcol/r/vcol_ins_upd_myisam.result b/mysql-test/suite/vcol/r/vcol_ins_upd_myisam.result
index 6674586..880e187 100644
--- a/mysql-test/suite/vcol/r/vcol_ins_upd_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_ins_upd_myisam.result
@@ -363,3 +363,29 @@ select * from t1;
 a	b	c	d
 set sql_warnings = 0;
 drop table t1;
+#
+# MDEV-9093: Persistent computed column is not updated when
+# update query contains join
+#
+CREATE TABLE `t1` (
+`id` bigint(20) NOT NULL,
+`name` varchar(254) DEFAULT NULL,
+`name_hash` varchar(64) AS (sha2(name, 256)) PERSISTENT,
+PRIMARY KEY (`id`)
+);
+insert into t1(id,name) values (2050, 'name1'),(2051, 'name2'),(2041, 'name3');
+create table t2 (id bigint);
+insert into t2 values (2050),(2051),(2041);
+select * from t1;
+id	name	name_hash
+2050	name1	9367417d63903350aeb7e092bca792263d4fd82d4912252e014e073a8931b4c1
+2051	name2	069587dcb8f8b63329ae53051ba79ba34ba0deb41c7a1e044280d7b6bb15e4f0
+2041	name3	b218ca013905fc528204bdadf9e104acd87d646a2d90ef834526fbf85b17e690
+update t1 join t2 using(id) set name = concat(name,
+'+1') where t1.id in (2051,2041);
+select * from t1;
+id	name	name_hash
+2050	name1	9367417d63903350aeb7e092bca792263d4fd82d4912252e014e073a8931b4c1
+2051	name2+1	2dcb7ad37316c779a09a9a8b6b898c41a3039b60fd37b931b283ea5c56d76779
+2041	name3+1	d6dc973da89612d369de19bb1b7696fd6bacd5276e1e2512a30a4001d608aa71
+drop table t1,t2;
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 0f7b28c..e468832 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -2394,6 +2394,12 @@ int multi_update::do_updates()
         int error;
         if (table->default_field && (error= table->update_default_fields()))
           goto err2;
+        if (table->vfield &&
+            update_virtual_fields(thd, table,
+                                  (table->triggers ?
+                                   VCOL_UPDATE_ALL :
+                                   VCOL_UPDATE_FOR_WRITE)))
+          goto err2;
         if ((error= cur_table->view_check_option(thd, ignore)) !=
             VIEW_CHECK_OK)
         {


More information about the commits mailing list