Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22599

WITHOUT OVERLAPS does not work with prefix indexes

    XMLWordPrintable

    Details

      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'
      

        Attachments

          Activity

            People

            Assignee:
            nikitamalyavin Nikita Malyavin
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: