[MDEV-22414] UPDATE or DELETE FOR PORTION OF increments the value of auto-increment column instead of copying the old one Created: 2020-04-29  Updated: 2020-11-20  Resolved: 2020-11-20

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.4, 10.5
Fix Version/s: 10.4.18, 10.5.9

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Nikita Malyavin
Resolution: Not a Bug Votes: 1
Labels: None

Issue Links:
Blocks
Relates
relates to MDEV-22562 Assertion `next_insert_id == 0' upon ... Closed
relates to MDEV-22564 Assertion `marked_for_write_or_comput... Closed
relates to MDEV-19130 Assertion `next_insert_id >= auto_inc... Closed

 Description    

create table t1 (a int auto_increment, b char(16), s date, e date, period for se(s,e), primary key(a,s));
 
insert into t1 values (1,'new','2019-01-01','2020-01-01');
 
update t1 for portion of se from '2019-07-01' to '2020-07-01' set b = 'updated' where a = 1;
 
select * from t1;
 
delete from t1 for portion of se from '2019-03-01' to '2019-10-01' where a = 1;
 
select * from t1;
 
 
 
drop table t1;

Actual result, 10.4 503fd211

 
update t1 for portion of se from '2019-07-01' to '2020-07-01' set b = 'updated' where a = 1;
 
select * from t1;
 
a	b	s	e
 
1	updated	2019-07-01	2020-01-01
 
2	new	2019-01-01	2019-07-01
 
delete from t1 for portion of se from '2019-03-01' to '2019-10-01' where a = 1;
 
select * from t1;
 
a	b	s	e
 
3	updated	2019-10-01	2020-01-01
 
2	new	2019-01-01	2019-07-01

The behavior is not specified in the documentation or (as far as I can tell) in the standard, but it seems obviously wrong, although understandable from the implementation point of view.

 Comments   
Comment by Nayuta Yanagisawa (Inactive) [ 2020-11-19 ]

After a brief investigation, on b2029c0300bf8c311ff5d9fdc4b73a9e48bf6930, I found that the weird update of the auto-increment column is caused by TABLE::update_generated_fields() in sql/table.cc. Removing update_auto_increment() from update_generated_fields() at least fixes the bug, while I'm not yet so sure about the side effect of the remove.

Comment by Nayuta Yanagisawa (Inactive) [ 2020-11-19 ]

Just to make sure, the expected result is the below, right?

create table t1 (a int auto_increment, b char(16), s date, e date, period for se(s,e), primary key(a,s));
 
insert into t1 values (1,'new','2019-01-01','2020-01-01');
 
update t1 for portion of se from '2019-07-01' to '2020-07-01' set b = 'updated' where a = 1;
 
select * from t1;
 
a       b       s       e
 
1       updated 2019-07-01      2020-01-01
 
1       new     2019-01-01      2019-07-01
 
delete from t1 for portion of se from '2019-03-01' to '2019-10-01' where a = 1;
 
select * from t1;
 
a       b       s       e
 
1       updated 2019-10-01      2020-01-01
 
1       new     2019-01-01      2019-03-01
 
drop table t1;

Comment by Nikita Malyavin [ 2020-11-20 ]

nayuta-yanagisawa I think the expected result should be "update auto_increment counter when we insert a new record". According to standard, "new" should be inserted, and "updated" should be updated/deleted:

create table t1 (a int auto_increment, b char(16), s date, e date, period for se(s,e), primary key(a,s));
 
insert into t1 values (1,'new','2019-01-01','2020-01-01');
 
update t1 for portion of se from '2019-07-01' to '2020-07-01' set b = 'updated' where a = 1;
 
select * from t1;
 
a       b       s       e
 
1       updated 2019-07-01      2020-01-01
 
2       new     2019-01-01      2019-07-01
 
delete from t1 for portion of se from '2019-03-01' to '2019-10-01' where a = 1;
 
select * from t1;
 
a       b       s       e
 
3       updated 2019-10-01      2020-01-01
 
2       new     2019-01-01      2019-03-01
 
drop table t1;

As far as I can see, this is the same as the actual result.

elenst Please note, how the algorithm works:

UPDATE t SET x = 'new' FOR PORTION OF p FROM a TO b

transforms to 

UPDATE t SET x = '"updated", s=a, e=b
 
# if row.s < a:
 
INSERT t(x, s, e) values ("new", ....)
 
# if row.e > b:
 
INSERT t(x, s, e) values ("new", ....)

DELETE FROM t FOR PORTION OF p FROM a TO b

transforms to

DELETE FROM t;
 
# if row.s < a:
 
INSERT t(x, s, e) values (row.x, ....)
 
# if row.e > b:
 
INSERT t(x, s, e) values (row.x, ....)

Comment by Nayuta Yanagisawa (Inactive) [ 2020-11-20 ]

> I think the expected result should be "update auto_increment counter when we insert a new record".

This sounds a bit counterintuitive to me but it is reasonable when we interpret insert operations, triggered by UPDATE or DELETE on an application time-period table, as just usual insert operations.

Comment by Nikita Malyavin [ 2020-11-20 ]

Maybe that's counterintuitive on the one hand, but on the contrary, the INSERT triggers are called as well. It'd be controversial if the INSERT trigger is called, but no autoincrement update happened. And think about what if `a` would be PRIMARY KEY AUTO_INCREMENT – then updates/deletes would fail with duplicate error

Generated at Thu Feb 08 09:14:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.