"BOKU"のITな日常

62歳・文系システムエンジニアの”BOKU”は日々勉強を楽しんでます

日付リテラルの比較で注意すべき暗黙の型変換がヤバイ/MariaDB(備忘)

プログラムで、日付文字列を組み立てて、それを条件にしてSQL文を生成する。

こういう処理はよくあります。

例えば。

システム日付が「2018/10/09」だった時に、同年同月の1日時点の情報を取得するために「2018/10/01」という日付文字列に変換してSQLを生成するみたいな感じ。

簡単な処理ですが、MariaDBMySQL)を使ってて、バグになりやすい部分です。

久々にはまって思い出したので、整理しときます。

f:id:arakan_no_boku:20190405004528j:plain

 

意外な結果に驚くケース

 

お題にするのは、超シンプルなこういうSQLです。

select case when '2018-09-01' > '2018-08-31' then 0 else 1 end;

「'2018-09-01'」が「'2018-08-31'」より大きければ「0」を表示し、でなければ「1」を表示するという処理です。

上記を実行すると、当然のように結果として「0」を表示します。

f:id:arakan_no_boku:20181009204627j:plain

では。

こうなるとどうか?

select case when '2018-09-01' > '2018/08/31' then 0 else 1 end;

違いは、 「'2018-08-31'」が「'2018/08/31'」に変わっただけです。

これも当たり前のように結果として「0」が表示される。

そう思います。

ところが、どっこい。

結果で表示されるのは「1」です。

f:id:arakan_no_boku:20181009204648j:plain

ちょっと、びっくりします。

でも、上記では「'2018/08/31'」は正しく日付として扱われず、暗黙の型変換で「文字列」として比較に使われているみたいなんですね。

これは、デリミタが同じで、月日のところで頭ゼロを省略しただけの以下のようなケースでも同じです。

select case when '2018-09-01' > '2018-8-31' then 0 else 1 end;

結果としては「1」が表示されます。

f:id:arakan_no_boku:20181009213116j:plain

 

前と後ろの形式が違うからなのかな

 

前と後ろの形式が違うからかな・・と思ってます。

つまり、前が「'2018-08-31'」、後ろが「'2018/08/31'」のように前後で違うと、後ろが文字列で扱われてしまうんだ・・ってな理屈です。

本当にそうなのか?と思って、こんなSQLにしてみると

select case when '2018-09-01' > '18/08/31' then 0 else 1 end; 

前後の形式が全く違いますが、でも、結果は正しく「0」を表示します。

f:id:arakan_no_boku:20181009205708j:plain

おお!この形式なら大丈夫なのか?と思ったら

select case when '2018-09-01' > '18/09/31' then 0 else 1 end;

みたいに、「1」が表示してほしい条件に変更しても、「0」が返ります。

f:id:arakan_no_boku:20181010190814j:plain

やっぱり日付として正しく処理されてはないみたいです。

 

何故、このようなことが発生するのか

 

 DATEリテラルについては、MariaDBのドキュメントに記述があります。

mariadb.com

引用します。

元が英語なので、下にGoogle翻訳してます。

A DATE string is a string in one of the following formats: 'YYYY-MM-DD' or 'YY-MM-DD'. Note that any punctuation character can be used as delimiter. All delimiters must consist of 1 character. Different delimiters can be used in the same string. Delimiters are optional (but if one delimiter is used, all delimiters must be used).A DATE literal can also be an integer, in one of the following formats: YYYYMMDD or YYMMDD.

DATE文字列は、 'YYYY-MM-DD'または 'YY-MM-DD'のいずれかの形式の文字列です。任意の句読点文字を区切り文字として使用できることに注意してください。すべての区切り文字は1文字で構成する必要があります。 同じ文字列で異なるデリミタを使用することができます。デリミタはオプションです(しかし、1つのデリミタを使用する場合は、すべてのデリミタを使用する必要があります)。DATEリテラルは、YYYYMMDDまたはYYMMDDのいずれかの形式の整数でもかまいません。

うーん。

but if one delimiter is used, all delimiters must be used

のあたりかなとも思いますが、「これだ!」というほどの説明はありません。

まあ、現象から「左側の形式を正として、右側がそれと異なる場合は日付リテラルとはみなさない・・」って感じですかね。

 

結論としては横着をするなということなのかな

 

はっきりしているのは、日付リテラルを使って日付の比較をするのは、十分な注意が必要だということです。

実際にあったパターンとして、表示用のフォーマットに「2018/09/01」を使っているのに、内部ログ用に「2018-09-01」を使っていて、保守している間にその辺が混在して生成されたSQLで、上記のような混在が発生したというのがありました。

嫌なのは。

いきなりエラーで落ちるんでもなく、何となく正しく動いているように見えていながら、ダメージのきついところで満を持して表面化したりすることです。

本当なら、横着しないで、ちゃんと日付型に返還してから比較するようにしたほうがいいんでしょうね。

以下みたいに。

select case when str_to_date('2018-09-01','%Y-%m-%d') > str_to_date('2018-08-31','%Y-%m-%d') then 0 else 1 end;

f:id:arakan_no_boku:20181009214800j:plain

そうでなければ、日付形式を徹底的に統一する。

例えば「'2018/08/31'」を表示に使うなら、すべてをそれに統一して、フォーマットが混在しないように、きちんとやる。

やっぱ、基本が大事ですよね。