SQLでザックリとした日付を指定したはずなのに、何故か全件取れてしまう。皆さんは、そんな恐ろしい経験はありませんでしょうか？
何故か目をつぶって書き直すと、ちゃんと絞り込みが効いて全件取れなってしまい、いったい何が良くなかったのか、釈然としない状態になることもあると思います。まぁ、なんとなく気持ちが悪いなと思いつつ、欲しいデータ取れたし良いかなと進めてしまうことに、たびたびなりがちです。
今回は、そんな真夏にふさわしい？ 怪談みたいなSQLで起こった怖いお話を紹介していきたいと思います。
検証環境
今回はDockerで建てたMySQLを使用します。以下のコマンドでDockerを建てて、ローカルからアクセスをします。
% docker run --platform linux/x86_64 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest % % mysql -uroot -pmy-secret-pw
執筆時点では、以下の通りMySQL 8.
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.28 | +-----------+ 1 row in set (0.01 sec)
今回のテスト用に、testデータベースとテーブルを以下のクエリで作っておきます。
mysql> create database test; mysql> use test mysql> create table date_test(id SERIAL, inserted_at DATETIME);
以下のように、2022年と2021年の七夕の日付をそれぞれinsertしておきましょう。
mysql> INSERT INTO date_test(inserted_at) VALUES ('2021/07/07'),('2022/07/07');
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM date_test;
+----+---------------------+
| id | inserted_at |
+----+---------------------+
| 1 | 2021-07-07 00:00:00 |
| 2 | 2022-07-07 00:00:00 |
+----+---------------------+
2 rows in set (0.01 sec)
これで準備は完了です。
問題となったSQL
さて、そんな何故か絞り込みが効かない困ったクエリに登場してもらいましょう。意図としては2022年の七夕以降のデータを取得するためのクエリになります。
mysql> SELECT * FROM date_test WHERE inserted_at >= 2022-07-07;
パッと見て気がついた方、なかなか良い目をしていると思います。気が付かなかった方は、私と一緒です。じっくりと眺めていきましょう。
実際にこのクエリを実行してみると、どうなるか見てみましょう。
mysql> SELECT * FROM date_test WHERE inserted_at >= 2022-07-07; +----+---------------------+ | id | inserted_at | +----+---------------------+ | 1 | 2021-07-07 00:00:00 | | 2 | 2022-07-07 00:00:00 | +----+---------------------+ 2 rows in set, 1 warning (0.01 sec)
何故か2件取れてしまってます。2022年の七夕のデータは1件だけなはずなのに…いったい何が良くなかったのでしょうか。カラム名が間違っていて、条件式が必ずtrueになるようになっているのでしょうか？
そう思ったときは試してみるに限ります。inserted_
mysql> SELECT * FROM date_test WHERE insert_at >= 2022-07-07; ERROR 1054 (42S22): Unknown column 'insert_at' in 'where clause'
やはりカラム名を間違えていたら、さすがに止まります。本当の原因はいったいなんなのか気になると思います。
しかし、何が起こっているのかが掴めず、早々に手詰まりになってしまいました。こういう時は、落ち着いて最初からやりたい意図通りのクエリを書いてみましょう。
mysql> SELECT * FROM date_test WHERE inserted_at >= '2022/07/07';
このようなクエリが浮かんできたのではないかと思います。これを実際に実行してみましょう。
mysql> SELECT * FROM date_test WHERE inserted_at >= '2022/07/07'; +----+---------------------+ | id | inserted_at | +----+---------------------+ | 2 | 2022-07-07 00:00:00 | +----+---------------------+ 1 row in set (0.01 sec)
今度は1行だけになっていると思います。何が違ったか見比べてみましょう。
mysql> SELECT * FROM date_test WHERE inserted_at >= 2022-07-07; mysql> SELECT * FROM date_test WHERE inserted_at >= '2022/07/07';
もう気がついた方が多そうですが、日付が文字列になっていない事に気がついたと思います。というところで、ネタバラシになりますが、間違った方のクエリでは、2022 - 07 - 07 が計算されて2008以上のものが取ってこれることになります。
さて、ここで気になるのは、2008以上って何が取れるの？
mysql> SELECT CAST(inserted_at AS SIGNED) FROM date_test; +-----------------------------+ | CAST(inserted_at AS SIGNED) | +-----------------------------+ | 20210707000000 | | 20220707000000 | +-----------------------------+ 2 rows in set (0.01 sec)
このように、日付を全部繋ぎ合わせた数字になっているようです。というところで、もとのSQLの構文のままで意図通り動かしたい場合は以下のようになります。
mysql> SELECT * FROM date_test WHERE inserted_at >= 20220707000000; +----+---------------------+ | id | inserted_at | +----+---------------------+ | 2 | 2022-07-07 00:00:00 | +----+---------------------+ 1 row in set (0.01 sec)
ちなみに、末尾に1を追加すると取得できなくなるため、そういう比較がされていることがわかります。
mysql> SELECT * FROM date_test WHERE inserted_at >= 20220707000001; Empty set (0.01 sec)
仕組み的に防ぐ方法を考えてみる
ということで、クエリの書き方のミスのせいだったのですが、これを防ぐ方法はあるのか？
ひとつは、ORMなどでクエリビルダを使っている場合に、文字列結合を使わないことです。これはユーザの入力値が入るとSQLインジェクションに繋がりますし、良いことはありません。各言語にマッチした記法が使えると思いますのでそちらを利用しましょう。
もうひとつは、これは数式として処理されてしまったのが良くないので、数式として処理できない区切り文字を使ってみましょう。
SELECT * FROM date_test WHERE inserted_at >= 2022~07~07; 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 '~07~07' at line 1 m
こんな形で~にしてみるとエラーになります。それ文字列でも通るんですか？
mysql> SELECT * FROM date_test WHERE inserted_at >= '2022~07~07'; +----+---------------------+ | id | inserted_at | +----+---------------------+ | 2 | 2022-07-07 00:00:00 | +----+---------------------+ 1 row in set (0.01 sec)
このように正しく評価されていることがわかります。
このことはMySQLの公式のドキュメントにも書かれていて
MySQL は、文字列として指定された値に、
「緩やかな」 形式を使用でき、この形式では、どの句読点文字でも日付部分と時間部分の区切り文字として使用できます。 場合によっては、この構文は偽りになることがあります。 たとえば、'10:11:12'などの値は、:が原因で時間値のように見えますが、日付コンテキストで使用されている場合は'2010-11-12'年として解釈されます。 値 '10:45:15' は、'45' が有効な月ではないので、'0000-00-00' に変換されます。
ということで、それっぽい文字列を与えると、何か上手いこと解釈してくれるといった仕様になります。
というわけで、ORMを使っているなら言語に合わせた仕様を使うのがおすすめです。手で書いている場合などには、日付の区切り文字を変えてみるのもなくはないですが、一度落ち着いて書き直しするのが手軽でいいんじゃないかと思います。
まとめ
今回は、夏場に相応しいちょっと肝が冷える恐ろしいお話を紹介させていただきました。
絞り込みを指定したはずなのに、絞り込みが効かないとなると、なんとも言えない恐ろしさがあると思います。
しかし、幽霊の正体見たり枯尾花とも言ったもので、MySQLの立場になって考えてみるとごくごく自然な事だとわかります。こういった事象が起きた場合には、MySQLのバグを疑いたくなる気持ちもわかりますが、一旦落ち着いて見ましょう。いったん何もかも忘れて、正しいクエリをもう一度書いてみて比較してみましょう。
そうすると、今回のように答えが見つかるかもしれません。