MariaDB の Application-Time Periods について - それが僕には楽しかったんです。

はじめに
Application-Time Periods
- INSERT
- DELETE
- UPDATE
- WITHOUT OVERLAPS
おわりに

はじめに

気がついたら 3 ヶ月もブログを更新していなかったので、色々あって見ていた MariaDB の Application-Time Periods について雑に書く。ほとんど日本語訳に近いがまぁそれもいいでしょう。
環境は以下のもので debug build したやつを make install で手元に突っ込んでやっている。

10.8.3-MariaDB-debug Source distribution commit: cbf9d8a

Application-Time Periods

まずそれが一体何なのかについて。公式ドキュメントは下記の通り。
mariadb.com

Application-Time Periods Table は 10.4.3 からサポートされている機能で、Time Periods と呼ばれる２つの temporal column によって範囲が決定される。
このテーブルにおいて Time Periods に相当するカラムは Date and Time Data Types*1 にあるデータ型のうち TIME と YEAR を除いたものであり、データ型が同じでないといけない。

ここまでざっと書いたが、何らかの時間的な範囲を何かするテーブルということしかわからないので実例について。まずは公式ドキュメントにあるやつをそのまま見ていく。
Application Time Periods は次の様に宣言することができる。*2

CREATE TABLE t1(
   name VARCHAR(50), 
   date_1 DATE,
   date_2 DATE,
   PERIOD FOR date_period(date_1, date_2));

name カラムは特に今関係無い。重要なのは PERIOD FOR *(date_1, date_2) の部分。これがまさに前述の Time Periods というやつ。

これによって date_1, date_2 を元にした範囲が定義されて、その範囲に対して INSERT, DELETE, UPDATE をすると何かが起こる。
ちなみにこの段階では特殊な何かの値が入っているというわけでもない。

MariaDB [test]> CREATE TABLE t1(
    ->    name VARCHAR(50), 
    ->    date_1 DATE,
    ->    date_2 DATE,
    ->    PERIOD FOR date_period(date_1, date_2));
Query OK, 0 rows affected (0.013 sec)

MariaDB [test]> select * from t1;
Empty set (0.002 sec)

これだけでもまだわからないので実際にテーブルのデータを追加したり、更新したりする。

INSERT

まずは INSERT から。

INSERT INTO t1 (name, date_1, date_2) VALUES
    ('a', '1999-01-01', '2000-01-01'),
    ('b', '1999-01-01', '2018-12-12'),
    ('c', '1999-01-01', '2017-01-01'),
    ('d', '2017-01-01', '2019-01-01');

余談だが date_period(date_1, date_2) という風に Time Periods を宣言した場合、それぞれのカラムに入る値は date_1 < date_2 となる必要がある。

MariaDB [test]> insert into t1(name, date_1, date_2) values ('e', '2022-01-01', '2021-01-01');
ERROR 4025 (23000): CONSTRAINT `date_period` failed for `test`.`t1`
MariaDB [test]> insert into t1(name, date_1, date_2) values ('e', '2022-01-01', '2022-01-01');
ERROR 4025 (23000): CONSTRAINT `date_period` failed for `test`.`t1`

DELETE

次に Time Periods に基づいて DELETE を飛ばしてみる。

MariaDB [test]> DELETE FROM t1
    -> FOR PORTION OF date_period
    ->     FROM '2001-01-01' TO '2018-01-01';
Query OK, 3 rows affected (0.008 sec)

MariaDB [test]> SELECT * FROM t1 ORDER BY name;
+------+------------+------------+
| name | date_1     | date_2     |
+------+------------+------------+
| a    | 1999-01-01 | 2000-01-01 |
| b    | 1999-01-01 | 2001-01-01 |
| b    | 2018-01-01 | 2018-12-12 |
| c    | 1999-01-01 | 2001-01-01 |
| d    | 2018-01-01 | 2019-01-01 |
+------+------------+------------+
5 rows in set (0.001 sec)

すると name = b の行が何故か２つになり、date_1, date_2 の値も変わっている。 name = c, name = d の列は増えてはないが date_1, date_2 の値が変わっている。
大体予想はつくが何が起こったかを図にするとこうなる。

application time periods before after — DELETE FOR PORTION OF

DELETE FOR PORTION OF は指定された Time Periods に基づいてその範囲を含むレコードの Time Periods を縮小・分割する。公式ドキュメントの例にはなかったが、DELETE で指定された Time Periods に収まりきる Time Periods を持つレコードは削除される。

MariaDB [test]> INSERT INTO t1(name, date_1, date_2) values('e', '2002-01-01', '2007-01-01');
Query OK, 1 row affected (0.007 sec)

MariaDB [test]> DELETE FROM t1 FOR PORTION OF date_period     FROM '2001-01-01' TO '2018-01-01';
Query OK, 1 row affected (0.007 sec)

MariaDB [test]> SELECT * FROM t1 ORDER BY name;
+------+------------+------------+
| name | date_1     | date_2     |
+------+------------+------------+
| a    | 1999-01-01 | 2000-01-01 |
| b    | 1999-01-01 | 2001-01-01 |
| b    | 2018-01-01 | 2018-12-12 |
| c    | 1999-01-01 | 2001-01-01 |
| d    | 2018-01-01 | 2019-01-01 |
+------+------------+------------+
5 rows in set (0.001 sec)

UPDATE

次に UPDATE について。まずはさっきと同じテーブルを作る。

MariaDB [test]> TRUNCATE t1;
MariaDB [test]> INSERT INTO t1 (name, date_1, date_2) VALUES
    ->     ('a', '1999-01-01', '2000-01-01'),
    ->     ('b', '1999-01-01', '2018-12-12'),
    ->     ('c', '1999-01-01', '2017-01-01'),
    ->     ('d', '2017-01-01', '2019-01-01');
Query OK, 4 rows affected (0.001 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM t1;
+------+------------+------------+
| name | date_1     | date_2     |
+------+------------+------------+
| a    | 1999-01-01 | 2000-01-01 |
| b    | 1999-01-01 | 2018-12-12 |
| c    | 1999-01-01 | 2017-01-01 |
| d    | 2017-01-01 | 2019-01-01 |
+------+------------+------------+
4 rows in set (0.001 sec)

その上で次のように UPDATE する。

MariaDB [test]> UPDATE t1 FOR PORTION OF date_period
    ->   FROM '2000-01-01' TO '2018-01-01' 
    -> SET name = CONCAT(name,'_original');
Query OK, 3 rows affected (0.009 sec)
Rows matched: 3  Changed: 3  Inserted: 4  Warnings: 0

MariaDB [test]> SELECT * FROM t1 ORDER BY name;
+------------+------------+------------+
| name       | date_1     | date_2     |
+------------+------------+------------+
| a          | 1999-01-01 | 2000-01-01 |
| b          | 1999-01-01 | 2000-01-01 |
| b          | 2018-01-01 | 2018-12-12 |
| b_original | 2000-01-01 | 2018-01-01 |
| c          | 1999-01-01 | 2000-01-01 |
| c_original | 2000-01-01 | 2017-01-01 |
| d          | 2018-01-01 | 2019-01-01 |
| d_original | 2017-01-01 | 2018-01-01 |
+------------+------------+------------+
8 rows in set (0.001 sec)

これもまた図にするとこうなる。

name = a は例によって範囲外なので影響を受けない。name = b, name = c, name = d は範囲内が更新され範囲外の Time Periods は分割・縮小される。
ここで気になるのが、やはり範囲内に収まりきる Time Periods の挙動について。UPDATE により前述の *_original も影響を受け、とても見づらいが範囲内に収まる Time Periods については DELETE 時と同様に UPDATE の影響を受け、Time Periods が分割・縮小されない。

MariaDB [test]> INSERT INTO t1(name, date_1, date_2) values('e', '2002-01-01', '2007-01-01');
Query OK, 1 row affected (0.007 sec)

MariaDB [test]> UPDATE t1 FOR PORTION OF date_period   FROM '2000-01-01' TO '2018-01-01'  SET name = CONCAT(name,'_original');
Query OK, 4 rows affected (0.009 sec)
Rows matched: 4  Changed: 4  Inserted: 0  Warnings: 0

MariaDB [test]> SELECT * FROM t1 ORDER BY name;
+---------------------+------------+------------+
| name                | date_1     | date_2     |
+---------------------+------------+------------+
| a                   | 1999-01-01 | 2000-01-01 |
| b                   | 1999-01-01 | 2000-01-01 |
| b                   | 2018-01-01 | 2018-12-12 |
| b_original_original | 2000-01-01 | 2018-01-01 |
| c                   | 1999-01-01 | 2000-01-01 |
| c_original_original | 2000-01-01 | 2017-01-01 |
| d                   | 2018-01-01 | 2019-01-01 |
| d_original_original | 2017-01-01 | 2018-01-01 |
| e_original          | 2002-01-01 | 2007-01-01 |
+---------------------+------------+------------+
9 rows in set (0.001 sec)

WITHOUT OVERLAPS

ここまで書いた上で WITHOUT OVERLAPS なんて名前のものが出てきたら察しがつくが MariaDB 10.5.3 から重複不可にも利用できる。

MariaDB [test]> CREATE OR REPLACE TABLE rooms (
    ->  room_number INT,
    ->  guest_name VARCHAR(255),
    ->  checkin DATE,
    ->  checkout DATE,
    ->  PERIOD FOR p(checkin,checkout),
    ->  UNIQUE (room_number, p WITHOUT OVERLAPS)
    ->  );
Query OK, 0 rows affected (0.012 sec)

MariaDB [test]> INSERT INTO rooms VALUES 
    ->  (1, 'Regina', '2020-10-01', '2020-10-03'),
    ->  (2, 'Cochise', '2020-10-02', '2020-10-05'),
    ->  (1, 'Nowell', '2020-10-03', '2020-10-07'),
    ->  (2, 'Eusebius', '2020-10-04', '2020-10-06');
ERROR 1062 (23000): Duplicate entry '2-2020-10-06-2020-10-04' for key 'room_number'

UNIQUE と組み合わせる必要があるが、こういった利用方法もありらしい。

おわりに

これと partitioning が絡んだらバグるパターンが存在していてそれを直そうとするなかで面白い機能だったのでまとめたが、イマイチこれの使いどころが想像つかない。
普段は MySQL ばかりなので、この手の勝手に何か行が増えたり書き換わったりするやつは見たことがなくデバッグしていても中々に面白かった。唐突に write_row が出てきたり。
で、書いていて思ったがその辺りや諸々気になったことがあるのでまた余裕があるときにまとめる。

訂正: 3ヶ月じゃなくて約 7 ヶ月だった…

*1:Date and Time Data Types - MariaDB Knowledge Base

*2: ALTER TABLE でも Time Periods の追加、削除ができるが面倒なので今回はそこについて言及しない。