MySQL 8.0.15 の前後で変わった文字列と DATE 型の比較について

はじめに

そういえば、最近この手の記事を書いてないし何ならインプットもしてない事を思い出し、今日雑にテストしてたらたまたまハマった面白い挙動があったのでまとめる。
久々に MySQL と格闘した。

前提条件

こんなテーブルを作っておく。

create table hoge(t DATE);
insert into hoge(t) values("2020-01-01");

mysql> desc hoge;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| t     | date | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> select * from hoge;
+------------+
| t          |
+------------+
| 2020-01-01 |
+------------+
1 row in set (0.01 sec)

こうなればおっけー。

謎現象を再現する

8.0.15 以下

mysql> select * from hoge where t > "2020";     
+------------+                                                                                
| t          |                                                                                
+------------+                                                                                
| 2020-01-01 |                                                                                
+------------+                                                                                
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '2020' for column 't' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)                                

これも個人的には？？という感じだったが、まぁとりあえず放置。

8.0.16 以上

mysql> select * from hoge where t > "2020";
ERROR 1525 (HY000): Incorrect DATE value: '2020'

エラーになる。

原理

まず、MySQL 8.0.15 以下では文字列と DATE 型を比較する場合、以下の手順を踏む。
1. 文字列を DATE (or DATETIME) に変換して比較する
2. 1.の変換ができない場合は逆に DATE を文字列に変換して比較する

これが MySQL 8.0.16 以上では次のような手順に変更された。
1. 文字列を DATE (or DATETIME) に変換して比較する
2. 1.が失敗した段階でエラーとなる

これ、MySQL 8.0.16 以上を触っている人からすると普通にやべぇなって思うかもしれないが
MySQL 5.7.x から MySQL 8.0.16 以上にアップグレードしてからこれがわかるとまぁびっくりすることこの上ない。
しかも、ガチで文字列になっているみたいで DATE 型の値を文字列に直して評価したときと同じ結果になる。
断じて内部的に 2020 という文字列が "2020-01-01" みたいに修正されているわけではない。
ガチで DATE が文字列として評価されているみたい。

文献

MySQL 8.0.16 の Release Note に書いてあった。

When comparing DATE values with constant strings, MySQL first tries to convert the string to a DATE and then to perform the comparison. When the conversion failed, MySQL executed the comparison treating the DATE as a string, which could lead to unpredictable behavior. Now in such cases, if the conversion of the string to a DATE fails, the comparison fails with ER_WRONG_VALUE. (Bug #29025656)
(DATE値を定数文字列と比較する場合、MySQLは最初に文字列をDATEに変換してから比較を実行しようとします。変換に失敗した場合、MySQLはDATEを文字列として扱って比較を実行するため、予測できない動作が発生する可能性がありました。このような場合、文字列のDATEへの変換に失敗すると、ER_WRONG_VALUEで比較が失敗するようになりました。(バグ#29025656))

MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.16 (2019-04-25, General Availability)

いや、これ Bug 扱いかよとは思ったが一応そういうことらしい。

ちなみに 8.0.15 で Bug Report が出されていて、コメントがついていた。

Documented fix as follows in the MySQL 8.0.16 changelog:

When comparing DATE values with constant strings, MySQL first
tries to convert the string to a DATE and then to perform the
comparison. When the conversion failed, MySQL executed the
comparison treating the DATE as a string. Now in such cases, if
the conversion of the string to a DATE fails, the comparison
fails with ER_WRONG_VALUE.

Closed.

(一部抜粋)
( DATE値を定数文字列と比較する際に、MySQLが文字列をDATEに変換してから比較を実行しようとする。変換に失敗した場合、MySQL は DATE を文字列として扱い比較を実行していました。このような場合、文字列のDATEへの変換に失敗すると、ER_WRONG_VALUEで比較に失敗します。)

MySQL Bugs: #93513: Unexpected behaviour for date comparison with constant strings

おわりに

今日はこれに4時間溶かしました。