Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5
-
None
Description
create or replace table t1 (a varchar(6), s timestamp, e timestamp, period for p(s,e), unique(a(3), p without overlaps)); |
insert into t1 values ('foo','2012-01-01','2015-12-31'),('foobar','2013-01-01','2014-01-01'); |
10.5 69077dea25 |
MariaDB [test]> insert into t1 values ('foo','2012-01-01','2015-12-31'),('foobar','2013-01-01','2014-01-01'); |
Query OK, 2 rows affected (0.021 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> select * from t1; |
+--------+---------------------+---------------------+ |
| a | s | e |
|
+--------+---------------------+---------------------+ |
| foo | 2012-01-01 00:00:00 | 2015-12-31 00:00:00 |
|
| foobar | 2013-01-01 00:00:00 | 2014-01-01 00:00:00 |
|
+--------+---------------------+---------------------+ |
2 rows in set (0.001 sec) |
The prefix is non-unique, and there is an obvious overlap in the period, so an error is expected.
With "normal" unique key it works as expected of course:
MariaDB [test]> create or replace table t1 (a varchar(6), s timestamp, e timestamp, period for p(s,e), unique(a(3), s, e)); |
Query OK, 0 rows affected (0.235 sec) |
|
MariaDB [test]> insert into t1 values ('foo','2012-01-01','2015-12-31'),('foobar','2012-01-01','2015-12-31'); |
ERROR 1062 (23000): Duplicate entry 'foo-2012-01-01 00:00:00-2015-12-31 00:00:00' for key 'a' |