[MDEV-9093] Persistent computed column is not updated when update query contains join Created: 2015-11-06  Updated: 2016-02-05  Resolved: 2016-02-05

Status: Closed
Project: MariaDB Server
Component/s: Virtual Columns
Affects Version/s: 5.5.46, 10.0.19, 10.0, 10.1
Fix Version/s: 5.5.48, 10.0.24, 10.1.12

Type: Bug Priority: Major
Reporter: Aleksandr Vishnyakov Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None

Sprint: 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.

 Comments   
Comment by Elena Stepanova [ 2015-11-06 ]

Thanks for the report.

Comment by Oleksandr Byelkin [ 2015-12-14 ]

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;

Comment by Oleksandr Byelkin [ 2015-12-14 ]

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;

Comment by Oleksandr Byelkin [ 2015-12-14 ]

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

—

Comment by Sergei Golubchik [ 2016-02-04 ]

ok to push

Generated at Thu Feb 08 07:32:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.