☹️
【MySQL8.0】PreparedStatementを利用した際の「invalid Date value」エラーの挙動
概要
MySQL8.0.16から、日付型に変換できない値と日付型を比較しようとするとエラーが出るようになった。
しかし、prepared statementを利用した際、後述のように通常のクエリ実行時と異なる挙動があった。
環境
MySQLバージョン：8.0.32
今回の現象に直接的には関係ないと思われるが、一応 NO_ZERO_IN_DATE,NO_ZERO_DATE の設定はともに有効。
現象
通常、 '' のような日付型に変換できない文字列を日付型カラムと比較しようとすると、下記のようにクエリエラーが発生する
mysql> select * from `some_table` where `created_at` >= '' order by `created_at` desc;
ERROR 1525 (HY000): Incorrect TIMESTAMP value: ''
mysql> show errors;
+-------+------+-------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------+
| Error | 1525 | Incorrect TIMESTAMP value: '' |
+-------+------+-------------------------------+
1 row in set (0.00 sec)
しかし、prepared statement を利用して同様のクエリを実行すると、エラーが出ない
mysql> prepare q1 from 'select * from `some_table` where `created_at` >= ? order by `created_at` desc;';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> set @v1 = '';
Query OK, 0 rows affected (0.00 sec)
mysql> execute q1 using @v1;
Empty set, 1 warning (0.00 sec)
mysql> show errors;
Empty set (0.00 sec)
原因
恐らくだが、prepared statementを利用して変数をバインドする時と、通常のSQL実行時でString型から日付型へのキャストの方法が異なると思われる。
例えば、 CAST() を利用して空文字列や、日付型に変換できない文字列をキャストしようとする場合、以下のように結果は null となる。
mysql> select CAST('' AS DATETIME);
+----------------------+
| CAST('' AS DATETIME) |
+----------------------+
| NULL |
+----------------------+
1 row in set, 1 warning (0.00 sec)
そして、以下のように null は日付型と比較してもエラーは出ない
mysql> select * from `chirps` where `created_at` >= null order by `created_at` desc;
Empty set (0.00 sec)
mysql> show errors;
Empty set (0.00 sec)
以上から、prepare によりパラメータをバインドする際は、 CAST() と同じようなロジックで型キャストが行われていると思われる。（ゆえに、空文字列を日付型のパラメータにバインドしてもクエリ実行時にエラーが発生しない）
Discussion