Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Done
-
10.4(EOL)
-
None
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
Attachments
Issue Links
- relates to
-
MDEV-16974
Application-time periods: UPDATE
-
- Closed
-
Activity
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.
I think it's a not a bug and `ON UPDATE CURRENT_TIMESTAMP` should be added as everywhere else.