MySQL で date_add/sub で年が 0000 になると 0000-00-00 が返る

MySQL

MySQL の sql_mode で NO_ZERO_IN_DATE,NO_ZERO_DATE とかを指定すると 0000-00-00 や、月や日が 00 などの日時の挿入を禁止できます。

がしかし、年が 0000 の場合の動きが謎いです。

MySQL のバージョンは次の通り。

select version();
/*-----------+
| version()  |
+------------+
| 5.7.19-log |
+-----------*/

SQL モードを指定します。

set session sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO';
select @@sql_mode;
/*--------------------------------------------------------------------------+
| @@sql_mode                                                                |
+---------------------------------------------------------------------------+
| STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO |
+--------------------------------------------------------------------------*/

データを入れます。想定通りの動きだと思います。月や日の 00 は無効ですが年の 0000 は有効です。

create table t (d date);
insert into t values ('0000-00-00'); /* ERROR 1292 (22007): Incorrect date value */
insert into t values ('0001-01-00'); /* ERROR 1292 (22007): Incorrect date value */
insert into t values ('0001-00-01'); /* ERROR 1292 (22007): Incorrect date value: */
insert into t values ('0000-01-01'); /* Query OK */
insert into t values ('0000-01-02'); /* Query OK */
insert into t values ('9999-12-30'); /* Query OK */
insert into t values ('9999-12-31'); /* Query OK */

なんということでしょう。

select d, date_add(d, interval 1 day) as `d+1` from t;
/*-----------+------------+
| d          | d+1        |
+------------+------------+
| 0000-01-01 | 0000-00-00 |
| 0000-01-02 | 0000-00-00 |
| 9999-12-30 | 9999-12-31 |
| 9999-12-31 | NULL       |
+------------+-----------*/

select d, date_sub(d, interval 1 day) as `d-1` from t;
/*-----------+------------+
| d          | d-1        |
+------------+------------+
| 0000-01-01 | 0000-00-00 |
| 0000-01-02 | 0000-00-00 |
| 9999-12-30 | 9999-12-29 |
| 9999-12-31 | 9999-12-30 |
+------------+-----------*/

9999-12-31 + 1 day が NULL なのは良いとして 0000-01-01 や 0000-01-02 に date_add や date_sub すると 0000-00-00 なりました。

下記の結果を見るに、比較はうまく動いているっぽい。

select d, d < '0000-01-02', d > '0000-01-01' from t;
/*-----------+------------------+------------------+
| d          | d < '0000-01-02' | d > '0000-01-01' |
+------------+------------------+------------------+
| 0000-01-01 |                1 |                0 |
| 0000-01-02 |                0 |                1 |
| 9999-12-30 |                0 |                1 |
| 9999-12-31 |                0 |                1 |
+------------+------------------+-----------------*/

どうやら date_add や date_sub による演算の結果、年が 0000 になると 0000-00-00 になってしまうっぽい。

select date_add('0000-12-30', interval 1 day) as `0000-12-30 + 1`,
       date_add('0000-12-31', interval 1 day) as `0000-12-31 + 1`,
       date_sub('0001-01-01', interval 1 day) as `0001-01-01 - 1`,
       date_sub('0001-01-02', interval 1 day) as `0001-01-02 - 1`;
/*---------------+----------------+----------------+----------------+
| 0000-12-30 + 1 | 0000-12-31 + 1 | 0001-01-01 - 1 | 0001-01-02 - 1 |
+----------------+----------------+----------------+----------------+
| 0000-00-00     | 0001-01-01     | 0000-00-00     | 0001-01-01     |
+----------------+----------------+----------------+---------------*/

西暦1年の前年は0年ではなく紀元前1年？なのなら 0000 という年は無効な日付になるので結果が 0000-00-00 とかになるのも判らなくもないけど・・それなら date_add や date_sub の入力に与えられたときも結果は 0000-00-00 になるべきな気がするし NO_ZERO_IN_DATE で弾かれてほしい気もする。

あとこれ、テーブルへの格納ではなくて date_add や date_sub による演算に対して発生しているので sql_mode は関係なく発生するようです。