Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.46, 10.0.19, 10.0(EOL), 10.1(EOL)
-
None
-
5.5.48-0
Description
There is a table with persistent computed column:
CREATE TABLE `some_table` (
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`name` varchar(254) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
|
`name_hash` varchar(64) COLLATE utf8mb4_unicode_ci AS (sha2(name, 256)) PERSISTENT,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
I am updating name value using complex query which contains joins, this is much simplified version which does not work as well:
update some_table join some_other_table using(id) set name = concat(name, '+1') where some_table.id in (2051,2041)
|
It updates name values but name_hash remains untouched. I've tried different cases with join and all of them do not update computed column. Updates without joins work fine.
Attachments
Activity
test suite (join in the UPDATE is important)
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;
|
test suite for 5.5
REATE TABLE `t1` ( |
`id` bigint(20) NOT NULL, |
`name` varchar(254) DEFAULT NULL, |
`name_hash` varchar(64) AS (sha1(name)) 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; |
revision-id: 744c1c5de4dc63fc8291fd69c04a60b395f0fca4 (mariadb-5.5.47-7-g744c1c5)
parent(s): 0ed474484c037a32bea32abaecd3ff770f40bd49
committer: Oleksandr Byelkin
timestamp: 2015-12-14 19:16:29 +0100
message:
MDEV-9093 Persistent computed column is not updated when update query contains join
added lost virtual fields update call
—
Thanks for the report.