[MDEV-19657] CONSTRAINT `time_period` failed for `sbtest`.`sbtest1` unclear error while adding time_period Created: 2019-05-31  Updated: 2023-12-15

Status: Open
Project: MariaDB Server
Component/s: Documentation, Versioned Tables
Affects Version/s: 10.4.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Shahriyar Rzayev (Inactive) Assignee: Joe Cotellese
Resolution: Unresolved Votes: 0
Labels: versioned-table

Issue Links:
PartOf

 Description   

Reading DOC:
https://mariadb.com/kb/en/library/temporal-data-tables/#adding-and-removing-time-periods

Adding timestamp columns to the table:

MariaDB [sbtest]> alter table sbtest1 add column time_1 timestamp(6);
Query OK, 0 rows affected (0.046 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
MariaDB [sbtest]> alter table sbtest1 add column time_2 timestamp(6);
Query OK, 0 rows affected (0.041 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
MariaDB [sbtest]> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT 0,
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `time_1` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
  `time_2` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=220753785 DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
1 row in set (0.001 sec)

Trying to add time_period:

MariaDB [sbtest]> alter table sbtest1 add period for time_period(time_1, time_2);
ERROR 4025 (23000): CONSTRAINT `time_period` failed for `sbtest`.`sbtest1`

Why failed? What is time_period constraint?



 Comments   
Comment by Elena Stepanova [ 2019-05-31 ]

The documentation says:

It also adds a constraint to check whether the first value is less than the second value.

In your case, they are equal.

Comment by Shahriyar Rzayev (Inactive) [ 2019-05-31 ]

Yes, now it is clear that both time_period and date_period requires the first value to be less than the second value.
So this features, in fact, require some prior work to get ready.

How we can improve it?
By adding more realistic cases to documentation - currently, we have just empty tables with alter statements.

Comment by Julien Fritsch [ 2021-09-22 ]

jacob.moorman from our discussion, I decided to reassign Kenneth's ticket to you?

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