[MDEV-22414] UPDATE or DELETE FOR PORTION OF increments the value of auto-increment column instead of copying the old one - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Not a Bug
Affects Version/s: 10.4(EOL), 10.5
Fix Version/s: 10.4.18, 10.5.9
Component/s: Versioned Tables
Labels:
None

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.

Attachments

Issue Links

relates to

MDEV-22562 Assertion `next_insert_id == 0' upon UPDATE on system-versioned table

Closed

MDEV-22564 Assertion `marked_for_write_or_computed()' failed in Field_long::store upon UPDATE on system- and app-versioned table

Closed

MDEV-19130 Assertion `next_insert_id >= auto_inc_interval_for_cur_row.minimum()' failed in handler::update_auto_increment after error 167

Closed

Activity

Ascending order - Click to sort in descending order

Nayuta Yanagisawa (Inactive) added a comment - 2020-11-19 16:47 - edited

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.

Nayuta Yanagisawa (Inactive) added a comment - 2020-11-19 16:47 - edited 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.

Nayuta Yanagisawa (Inactive) added a comment - 2020-11-19 17:00

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;

Nayuta Yanagisawa (Inactive) added a comment - 2020-11-19 17:00 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;

Nikita Malyavin added a comment - 2020-11-20 08:47

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, ....)

Nikita Malyavin added a comment - 2020-11-20 08:47 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, ....)

Nayuta Yanagisawa (Inactive) added a comment - 2020-11-20 10:36 - edited

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

Nayuta Yanagisawa (Inactive) added a comment - 2020-11-20 10:36 - edited > 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.

Nikita Malyavin added a comment - 2020-11-20 11:53

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

Nikita Malyavin added a comment - 2020-11-20 11:53 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

MariaDB Server

UPDATE or DELETE FOR PORTION OF increments the value of auto-increment column instead of copying the old one