[MDEV-17094] ON UPDATE NOW on period fields could break constraints Created: 2018-08-29  Updated: 2019-02-04  Resolved: 2019-02-04

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.4
Fix Version/s: 10.4.0

Type: Bug Priority: Minor
Reporter: Nikita Malyavin Assignee: Nikita Malyavin
Resolution: Done Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-16974 Application-time periods: UPDATE Closed

 Description    

create or replace table t1(s timestamp on update now, e timestamp, period for apptime(s,e));

Expected:

Error cannot apply `on update now` on period field `s`

Current behavior:

MariaDB [test]> show create table t1;
 
| Table | Create Table 
| t1    | CREATE TABLE `t1` (
 
  `s` timestamp NOT NULL ON UPDATE current_timestamp(),
 
  `e` timestamp NOT NULL,
 
  PERIOD FOR `apptime` (`s`, `e`),
 
  CONSTRAINT `apptime` CHECK (`s` < `e`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

This could break constraints on update, because constraints are not checked for inserting fields

 Comments   
Comment by Sergei Golubchik [ 2018-08-30 ]

I think it's a not a bug and `ON UPDATE CURRENT_TIMESTAMP` should be added as everywhere else.

Comment by Nikita Malyavin [ 2018-09-21 ]

You see, when we UPDATE FOR PORTION..., the inserted row fields are generated/filled with default values. This means, that ON UPDATE NOW field will be also filled, with now()

Next, if period start field is ON UPDATE NOW, it will break the CHECK(start < end) constraint, which is always implicitly added with PERIOD. Same for the end field, but the IMO values are usually less then now()

The SQL standard guarantees, that no additional checks should be done on these inserts. For it they explicitly forbid period start/end columns to be generated.

So either should be done:

  • add constraint checks
  • forbid ON UPDATE NOW for period fields

I did not realize it, when was creating the task, but that is what it should be about.

Comment by Nikita Malyavin [ 2019-02-04 ]

Done in MDEV-17082

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