[MDEV-22599] WITHOUT OVERLAPS does not work with prefix indexes - Jira

XML

Word

Printable

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'