MySQLでdatetimeに10000年を指定すると残念な事になる。

MySQLのBETWEENに'10000-01-01 00:00:00'のように9999年以上の値を入れると予期せぬ結果が返ってくる

MySQL ver : 5.6.20

こんなテーブルに対して

mysql> select * from date_test_table;
+---------------------+
| date                |
+---------------------+
| 2015-01-01 00:00:00 |
| 9999-01-01 00:00:00 |
| 0000-01-01 00:00:00 |
| 1015-01-01 00:00:00 |
| 3015-01-01 00:00:00 |
+---------------------+

BETWEENのminのほうに1000年と間違えて10000年を指定してしまった。
普通はエラーになるだろうと思ったらなぜかレコードが返ってきた。
謎い。

mysql> select * from date_test_table where date between "10000-01-01" AND "2050-01-01";
+---------------------+
| date                |
+---------------------+
| 2015-01-01 00:00:00 |
| 0000-01-01 00:00:00 |
| 1015-01-01 00:00:00 |
+---------------------+

どうやら挙動的には0000年になっているっぽい

DateTimeは0年から9999年まで

そもそもMySQLのDateTime自体が9999年までしか対応していない。

更新はエラーだけど参照はwarning

実際に10000年に関してのクエリを打ってみると、更新はエラーになるが参照はwarningという不思議。

mysql> insert date_test_table set date = '10000-01-01 00:00:00';
ERROR 1292 (22007): Incorrect datetime value: '10000-01-01 00:00:00' for column 'date' at row 1
 
mysql> select * from date_test_table where date = "10000-01-01 00:00:00";
Empty set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------+
| Level   | Code | Message                                                            |
+---------+------+--------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '10000-01-01' for column 'date' at row 1 |
+---------+------+--------------------------------------------------------------------+

BETWEENは空文字でも受け付ける

一方で、BETWEENは空文字でもクエリを受け付ける仕様のようだ。しかも自動的に最小の値になっているっぽい。

mysql> select * from date_test_table where date between "" AND "2050-01-01";
+---------------------+
| date                |
+---------------------+
| 2015-01-01 00:00:00 |
| 0000-01-01 00:00:00 |
| 1015-01-01 00:00:00 |
+---------------------+