MySQL8.0 で地味に修正された DATE 型の奇妙な扱い OSC Niigata/Online 2020

⾃⼰紹介 ▌けんつ(@lrf141) ▌サイボウズ株式会社 n Slash/CyDE-C チーム ▌ミドルウェアが好き n MySQL が好き n Elasticsearch と格闘している

今⽇話すこと ▌DATE 型と⽂字列の評価が変わった話

今⽇話すこと ▌DATE 型と⽂字列の評価が変わった話 n MySQL 8.0.16 で地味に修正 n MySQL 8.0.15 以下の挙動と⽐較 n こいつが割とすごい挙動 n ⼀説によると MySQL 4.0 あたりから…︖

今⽇の流れ ▌経緯 ▌問題調査 ▌再現・検証する ▌まとめ

今⽇の流れ ▌経緯 ▌問題調査 ▌再現・検証する ▌まとめ

経緯 ▌配属後にドキュメント・仕様書の検証と更新を⾏う n 「ドキュメントは腐るもの」 n Mac ⽤開発環境の構築 ▌テストで⼿元の MySQL を利⽤している ▌MySQL8 にしてビルドをするとテストが落ちる n Incorrect DATE value: ʻ2020'

経緯 ▌配属後にドキュメント・仕様書の検証と更新を⾏う n 「ドキュメントは腐るもの」 n Mac ⽤開発環境の構築 ▌テストで⼿元の MySQL を利⽤している ▌MySQL8 にしてビルドをするとテストが落ちる n Incorrect DATE value: ʻ2020'

今⽇の流れ ▌経緯 ▌問題調査 ▌再現・検証する ▌まとめ

問題調査をする ▌Incorrect Date Value n sql_mode を疑ってみる ▌Release Note でそれっぽいやつを探す ▌Bug Report もみてみる

sql_mode を疑ってみる ▌Incorrect Date Value で調べてみた ▌Sql_mode なるものが関係している(っぽい?) n Sql_mode はより厳密にデータを扱う設定 n DATE 以外にも SQL そのもの、数値など

sql_mode を疑ってみる ▌NO_ZERO_IN_DATE n 0 ⽉, 0 ⽇を含む場合はエラー・警告 ▌NO_ZERO_DATE n '0000-00-00' という⼊⼒に対しエラー・警告 ▌これらが無効となっている場合は⾃動的に変換

Release Note でそれっぽいやつを探す ▌MySQL 8.0.0 より⼤きいバージョンが対象 n Bugs Fixed n Functionality Added or Changed ▌SQL 構⽂などは恐らく関係ない ▌内部的な修正、DATE 型の変換に関わる部分

MySQL 8.0.16 の Bugs Fixed 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) https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-16.html

Bugs Fixed 要点整理 ▌MySQL 8.0.15 以下の⽐較⼿順 1. ⽂字列を DATE 型に変換して⽐較 2. 変換に失敗した場合は DATE 型を⽂字列に変換して⽐較 ▌MySQL 8.0.16 以上の⽐較⼿順 1. ⽂字列を DATE 型に変換して⽐較 2. 変換に失敗した場合はエラーを返す

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. https://bugs.mysql.com/bug.php?id=93513

今⽇の流れ ▌経緯 ▌問題調査 ▌再現・検証する ▌まとめ

再現・検証する ▌事前準備 n MySQL 8.0.15 と MySQL 8.0.16 を⽤意する n テーブルを⽤意する

再現・検証する

再現・検証する Code が 1292 であることに注意。 1292 は ER_WRONG_VALUE ではなくて ER_TRUNCATED_WRONG_VALUE

再現・検証する

再現・検証する

再現・検証する ▌Cast では両⽅とも NULL

まとめ ▌DATE 型は元々すごい挙動をしていた ▌振る舞いをテストすることは⼤事 n テーブルにある通りに扱われるとは限らない n 将来的に変更される可能性もある ▌Warning もたまに⾒ておくと良いかも

と、⾔いたいところですが

MySQL 側の実装を少し⾒てみました

番外編: MySQL に何が起こっているか ▌Item_cmpfunc.cc の set_cmp_func がそれっぽい can_compare_as_dates -> get_date_from_const -> get_date_from_str -> get_mysql_time_from_str -> str_to_datetime ▌str_to_datetime で MYSQL_TIME_WARN_TRUNCATED ▌make_truncated_value_warning

番外編: MySQL に何が起こっているか ▌⽐較対象の DATE 型が STRING_RESULT を持っている ▌cmp_context が STRING_RESULT になっている ▌その後は⽂字列として⽐較されている(みたい)

MySQL をビルド&デバッグしないよ…

意外と簡単でおすすめです ▌mysql-server のコードをもってくる(github, wget) ▌WITH_DEBUG=1 を付与して cmake ▌make ▌bin/mysqld を gdb, lldb でデバッグ ▌VSCode でも OK n CLion でも⾏けることを確認

よい MySQL ライフを。

ご清聴ありがとうございました。