MySQL 5.7 と 8.0 で日時型の挙動が微妙に違っていた件

こんにちは。 ATOM事業部の田村です。

最近 MySQL 5.7 から 8.0 へのバージョンアップを行う機会があったのですが、その際に MySQL の日時データ型でちょっと面白い挙動に遭遇しました。「WHERE 句で日時型カラムに条件を入れて期間で絞り込む」というよくある SQL なのですが、右辺の値に '' （空文字）を指定した場合に、5.7 ではエラーにならず 8.0 では SQL エラーになるというものです。

mysql> SELECT * FROM table WHERE date_column = '';

ERROR 1525 (HY000): Incorrect DATETIME value: ''

DATETIME 型カラムと比較される文字列 '' が、内部的に同じ DATETIME 型にキャストされる時に「そんな値は正しくない」ということでエラーが出ているのかと思い、明示的にキャストしてみました。

mysql> SELECT CAST('' AS DATETIME);

+----------------------+
| CAST('' AS DATETIME) |
+----------------------+
| NULL                 |
+----------------------+

？？？
あくまで右辺に値として '' が渡された際にしかエラーにならないようです。

ドキュメントを見てもこれに関する記述は見つからず、意図したものなのかはわかりませんが、とにかく 8.0 になって挙動が変わったのは確かです。ただし右辺の値に '' を指定することなど通常はない事で、挙動が変わったからといって受ける影響は少なそうではあったので、さらっと軽く検証してみることにしました。

環境

検証環境の mysql サーバは docker で MySQL 公式イメージを使って立てます。 https://hub.docker.com/_/mysql

バージョンは 5.7.41, 8.0.36, latest ← (2024/02/26 時点では 8.3.0) の3つで行います。

検証

MySQL の日時データ型には DATE, TIME, DATETIME, TIMESTAMP, YEAR と 5 種類あるので、それぞれで動作を比較します。 CAST した場合と STR_TO_DATE() 関数で日時型に変換した場合の違いも見てみます。

それと先にちょっとネタバレというか、検証結果に影響するパラメータがあるのでこの違いも検証に入れています。

dev.mysql.com

SQL モードは、変換操作の結果に影響します (セクション5.1.11「サーバー SQL モード」を参照)。例:

「「ゼロ」」の日付文字列を日付に変換する場合、CONVERT() および CAST() は NULL を返し、NO_ZERO_DATE SQL モードが有効なときに警告を生成します。

公式 docker イメージで起動した際のデフォルト sql_mode は以下の通り最初から NO_ZERO_DATE が指定されているので、デフォルト状態に加えこれを外したパターンも検証します。

5.7.41
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

8.0.36
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

8.3.0
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

結果

赤文字は結果が返らずエラーが出たことを表します。

	5.7 default	5.7 NO_ZERO_DATEなし	8.0 default	8.0 NO_ZERO_DATEなし	8.3 default	8.3 NO_ZERO_DATEなし
SELECT CAST('' AS DATE)	NULL	NULL	NULL	NULL	NULL	NULL
SELECT CAST('' AS DATE) = ''	NULL	NULL	ERROR 1525 (HY000): Incorrect DATE value: ''	ERROR 1525 (HY000): Incorrect DATE value: ''	ERROR 1525 (HY000): Incorrect DATE value: ''	ERROR 1525 (HY000): Incorrect DATE value: ''
SELECT CAST('' AS DATETIME)	NULL	NULL	NULL	NULL	NULL	NULL
SELECT CAST('' AS DATETIME) = ''	NULL	NULL	ERROR 1525 (HY000): Incorrect DATETIME value: ''	ERROR 1525 (HY000): Incorrect DATETIME value: ''	ERROR 1525 (HY000): Incorrect DATETIME value: ''	ERROR 1525 (HY000): Incorrect DATETIME value: ''
SELECT CAST('' AS TIME)	NULL	NULL	NULL	NULL	NULL	NULL
SELECT CAST('' AS TIME) = ''	NULL	NULL	NULL	NULL	NULL	NULL
SELECT CAST('' AS YEAR)	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'YEAR)' at line 1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'YEAR)' at line 1	NULL	NULL	NULL	NULL
SELECT CAST('' AS YEAR) = ''	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'YEAR)' at line 1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'YEAR)' at line 1	NULL	NULL	NULL	NULL
SELECT CAST('' AS TIMESTAMP)	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMP)' at line 1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMP)' at line 1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMP)' at line 1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMP)' at line 1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMP)' at line 1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMP)' at line 1
SELECT CAST('' AS TIMESTAMP) = ''	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMP)' at line 1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMP)' at line 1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMP)' at line 1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMP)' at line 1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMP)' at line 1	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMP)' at line 1
SELECT STR_TO_DATE('','%Y-%m-%d')	NULL	0000-00-00	NULL	0000-00-00	NULL	0000-00-00
SELECT STR_TO_DATE('','%Y-%m-%d') = ''	NULL	0	ERROR 1525 (HY000): Incorrect DATE value: ''	ERROR 1525 (HY000): Incorrect DATE value: ''	ERROR 1525 (HY000): Incorrect DATE value: ''	ERROR 1525 (HY000): Incorrect DATE value: ''
SELECT STR_TO_DATE('','%Y-%m-%d %H:%i')	NULL	0000-00-00 00:00:00	NULL	0000-00-00 00:00:00	NULL	0000-00-00 00:00:00
SELECT STR_TO_DATE('','%Y-%m-%d %H:%i') = ''	NULL	0	ERROR 1525 (HY000): Incorrect DATETIME value: ''	ERROR 1525 (HY000): Incorrect DATETIME value: ''	ERROR 1525 (HY000): Incorrect DATETIME value: ''	ERROR 1525 (HY000): Incorrect DATETIME value: ''
SELECT STR_TO_DATE('','%H:%i')	NULL	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00
SELECT STR_TO_DATE('','%H:%i') = ''	NULL	0	0	0	0	0
SELECT STR_TO_DATE('','%Y')	NULL	0000-00-00	NULL	0000-00-00	NULL	0000-00-00
SELECT STR_TO_DATE('','%Y') = ''	NULL	0	ERROR 1525 (HY000): Incorrect DATE value: ''	ERROR 1525 (HY000): Incorrect DATE value: ''	ERROR 1525 (HY000): Incorrect DATE value: ''	ERROR 1525 (HY000): Incorrect DATE value: ''

気になったポイント

TIMESTAMP 型への CAST は、どのバージョンでもエラーになりました。 Incorrect **** value ではなく Syntax error なので、値の問題ではなくそもそも機能として用意されていないようですね。 5.7 で YEAR への CAST が同様のエラーになるのも意外でした。

STR_TO_DATE() は書式によって結果が DATE だったり DATETIME だったりして、挙動が読みづらいです。そして TIME になりそうな %H:%i のパターンは、5.7 で NO_ZERO_DATE が指定されている場合のみ NULL が出るが、8.0, 8.3 では NO_ZERO_DATE がまったく影響しないという、ちょっと意外な結果でした。そして %Y を指定した場合に YEAR 型にはならず DATE という扱いだったり、このあたり色々事情がありそうな。わざわざ YEAR() 関数が用意されているのだからそっちを使えという事ですかね。

最後に

そもそも日時として '' なんて値を渡すべきではないので、どうでもいい部分と言ってしまえばそれまでかもしれません。今回この挙動に気がついたきっかけは、プリペアドステートメントでパラメータに値をバインドする変数が、偶然間違って NULL になっていたことから始まっているので、そういった場合のエラー箇所の判定ぐらいには役立つかもしれません。という小ネタでした。