Help us understand the problem. What is going on with this article?

SQLトラップ(ゲームブック風)

この記事は、All About Group(株式会社オールアバウト) Advent Calendar 2019 7日目の記事です。

初めての方ははじめまして、そうでない方はお久しぶりです。
NACKと申します。

今年も「毎月7日は罠(わな=07)の日」……と言い張ってみます。
※記念日認定されているわけではありませんので、ご注意ください。

というわけで、今年も罠の話を、昔のゲームブック風に書いてみます。
今年のお題はSQL(主な対象DBはMySQL)です。
「では、幸運を祈っています(1へ進む)」

「そういうお遊びは興味無いから!」という方は、直接 罠の解説まで飛んでください。
罠の話が列挙されています。


SQLトラップ(ゲームブック風)


1

「本日は、アトラクション『SQLトラップ』のご利用、誠にありがとうございます。
 当アトラクションでは、SQLに関する謎解きをお楽しみいただけます。

 なお、当アトラクションには時間制限がございます。
 営業時間内にクリアできない場合、各部屋に設けられております非常口よりお帰りください。
 それでは、良いゲームライフを!」

扉を開けて、最初の問題に挑戦しよう。(2へ


2

係員がいる。
「いらっしゃいませ。
 さっそくですが、あちらの窓口で、この先の『有効な部屋』に入るために必要な合鍵を受け取ってください。
 ただし、一部『有効な合言葉』が必要になる部屋がございますので、そちらのヒントも一緒に受け取っていただく必要があります」
どうやら、必要なものが過不足なく揃わないと、次の部屋への扉を開けてもらえないようだ。

roomsテーブル

id spare_key valid_flag
1 鉄の鍵 1
2 銅の鍵 1
3 銀の鍵 1
4 金の鍵 0

passwordsテーブル

id room_id hint valid_flag
1 2 折りたたまれたメモ 1
2 3 カセットテープ 0

君は窓口にたどり着いた。
さて、どうやって受け取ろうか?

  • roomsテーブル、passwordsテーブルともに、検索条件として「有効なもの」を指定するなら16へ進め。
  • roomsテーブルは検索条件、passwordsテーブルは結合条件として「有効なもの」を指定するなら11へ進め。
-- [16へ]のクエリ
SELECT rooms.spare_key, passwords.hint FROM rooms
LEFT JOIN passwords
ON rooms.id = passwords.room_id
WHERE rooms.valid_flag = 1 AND passwords.valid_flag = 1;

-- [11へ]のクエリ
SELECT rooms.spare_key, passwords.hint FROM rooms
LEFT JOIN passwords
ON rooms.id = passwords.room_id AND passwords.valid_flag = 1
WHERE rooms.valid_flag = 1;

3

君は全ての道具を取得した。
係員が走ってきて「黒真珠は『持出禁止』と書いてあるでしょう!」と叱られる。
この説教は、いったいいつになったら終わるのだろうか……(14へ)。


4

扉はあっさりと開いた(15へ)。


5

ハスの花と魔女のカツラを取得できた。
君は「なんでこんなものを……?」と思うかもしれないが、ともかく、取得したものを机の上に置いて、次の部屋へ進もう(12へ)。


6

ファンファーレが鳴り響く。

「制限時間内のゴール、おめでとうございます!
 こちらがクリア認定証となります」

挑戦してくれてありがとう。
非常口ではなく、正規の出口から颯爽と帰宅しよう。


7

さわべさんのレコードはロックできた。

が、おおはなさんのレコードロックが、いつまで経っても終わらない。
おおはなさんは しんぱいそうに こちらをみている……(14へ)。
解説


8

二人のレコードは無事ロックできた。
二人は君にとても感謝し、お礼に扉を開けてくれた(6へ)。


9

ハスの花だけが取得できた。
良い匂いがしてきて、君はだんだん眠くなってくる……(14へ)。
解説


10

扉は音を立てて開いた。が、少しずつしか開かない。
果たして、通れるようになるまでどれだけかかるのか……(14へ)。
解説


11

「はい、たしかに。では、行ってらっしゃいませ」
係員が、次の部屋への扉を開けてくれた。13へ進め。


12

看板が立っている。
「この部屋の扉を開けるためには合言葉が必要です」

最初の窓口でもらったメモを開いてみると
「合言葉は、『1時間天気』のテーブルから、2019/12/07の天気を取得するクエリです。
 テーブルは hourly_weather 、カラムは target_datetime(datetime型) 、weather(string型)の2つです。
 なお、target_datetime にはINDEXがはられているものとします。」
と書かれている。

  • 期間指定(BETWEEN)で検索するなら4へ進め。
  • 日時カラムの値を日付に変換し、完全一致検索するなら10へ進め。
-- [4へ]のクエリ
SELECT * FROM hourly_weather WHERE target_datetime BETWEEN '2019-12-07 00:00:00' AND '2019-12-07 23:59:59';

-- [10へ]のクエリ
SELECT * FROM hourly_weather WHERE DATE_FORMAT(target_datetime, '%Y-%m-%d') = '2019-12-07';

13

次の部屋への扉には、以下の文章が書かれている。
「メモに『持出禁止』と書かれていないと思われる道具(=黒真珠以外)をすべて取得し、机の上に置いてください。
 なお、この機械に搭載されているデータベースは、標準SQLに準拠しています」

扉の横に自動販売機が置いてあり、中には黒真珠と魔女のカツラとハスの花が入っている。
また、それぞれの道具にメモもついている。

itemsテーブル

id name memo
1 黒真珠 持出禁止
2 魔女のカツラ NULL
3 ハスの花 良いにおいがする

自動販売機には赤・青・黄色のボタンが付いており、それぞれのボタンの下にクエリが書いてある。
どうやら、そのクエリで取得可能な道具が出てくるようだ。

-- 「赤いボタン」のクエリ
SELECT * FROM items;

-- 「青いボタン」のクエリ
SELECT * FROM items WHERE memo <> '持出禁止';

-- 「黄色いボタン」のクエリ
SELECT * FROM items WHERE memo <> '持出禁止' OR memo IS NULL;

君は、どのボタンを押しても良い。

  • 赤いボタンを押すなら3へ進め。
  • 青いボタンを押すなら9へ進め。
  • 黄色いボタンを押すなら5へ進め。

14

……音楽とともに、館内放送が聞こえてくる。

「当施設はただいまをもちまして、本日の営業を終了させていただきます。
 本日のご来訪、誠にありがとうございました。
 またのご利用を心よりお待ちいたしております」

今日のところは、非常口から帰宅したまえ。
そして、いつの日か再び挑戦してほしい。


15

部屋に入ると、二人の係員が悩んでいる。

話を聞いてみると、二人とも、急いでusersテーブルの、自分のレコードを更新したいらしい。
ただし、更新作業中、自分自身のレコードを、名前(nameカラム)でロックしておきたいとのこと(二人の名前は「さわべ」と「おおはな」というそうだ)。
なお、usersテーブルのプライマリキーはidで、INDEXは何もはられていないものとする。

さて、どうしようか?

  • 各自の端末で、レコードをロックするなら7へ
-- さわべさんの端末で実行するクエリ
BEGIN;
SELECT * FROM users WHERE name = 'さわべ' FOR UPDATE;

-- おおはなさんの端末で実行するクエリ
BEGIN;
SELECT * FROM users WHERE name = 'おおはな' FOR UPDATE;
  • nameにINDEXをはってから、各自の端末でレコードをロックするなら8へ
-- nameにINDEXをはる
ALTER TABLE users ADD INDEX name(name);

-- さわべさんの端末で実行するクエリ
BEGIN;
SELECT * FROM users WHERE name = 'さわべ' FOR UPDATE;

-- おおはなさんの端末で実行するクエリ
BEGIN;
SELECT * FROM users WHERE name = 'おおはな' FOR UPDATE;

16

「鍵が足りませんね……。もう一度受け取りに行ってください」

再度受け取りに行こうとしたが、窓口には長い行列ができている。
君は並んで待つことにする……(14へ)。
解説


罠の解説

外部結合が内部結合に?

テーブルを外部結合する場合、結合対象のテーブルにレコードが無い場合、そのテーブルのレコードはすべてNULLになります。
※確認用に、取得項目として各テーブルの「有効フラグ」を追加しています。

全ての部屋の合鍵&有効フラグ、それに紐付く合い言葉のヒント&有効フラグを取得するクエリ
SELECT rooms.spare_key, rooms.valid_flag AS room_valid_flag, passwords.hint, passwords.valid_flag AS password_valid_flag FROM rooms
LEFT JOIN passwords
ON rooms.id = passwords.room_id;
spare_key room_valid_flag hint password_valid_flag
鉄の鍵 1 NULL NULL
銅の鍵 1 折りたたまれたメモ 1
銀の鍵 1 カセットテープ 0
金の鍵 0 NULL NULL

そのため、「外部結合したテーブルのカラム」をWHERE句で使用し、かつ、NULL以外の値を指定すると、条件に合致しない→取得対象外になります。
つまり、外部結合したはずなのに、内部結合したような結果が返ってきてしまうのです。

上記のクエリにWHERE句で「有効かどうか」を追加したクエリ
SELECT rooms.spare_key, rooms.valid_flag AS room_valid_flag, passwords.hint, passwords.valid_flag AS password_valid_flag FROM rooms
LEFT JOIN passwords
ON rooms.id = passwords.room_id
WHERE rooms.valid_flag = 1 AND passwords.valid_flag = 1;
spare_key room_valid_flag hint password_valid_flag
銅の鍵 1 折りたたまれたメモ 1

これを避け、意図した外部結合をさせるためには、外部結合したテーブルのカラムを条件に追加する場合、WHERE句ではなくON句に追加すればOKです。

SELECT rooms.spare_key, rooms.valid_flag AS room_valid_flag, passwords.hint, passwords.valid_flag AS password_valid_flag FROM rooms
LEFT JOIN passwords
ON rooms.id = passwords.room_id AND passwords.valid_flag = 1
WHERE rooms.valid_flag = 1;
spare_key room_valid_flag hint password_valid_flag
鉄の鍵 1 NULL NULL
銅の鍵 1 折りたたまれたメモ 1
銀の鍵 1 NULL NULL

NULLの扱い

標準SQLにおいて、NULLは決まった値ではありません。NULLは「値が不定、不明」ものとして扱われます。
※一般的なプログラミング言語の「NULL」とは位置づけが異なります(一般的なプログラミング言語は2値論理、SQLは3値論理を使用しています)。
※これはあくまで「標準SQLにおいて」の位置付けです。PostgreSQL等、一部のデータベースでは、設定次第で別の挙動をすることもありますのでご注意ください。

値が不定、不明ということは、「〇〇と一致しないこと」も確約できないため

SELECT * FROM items WHERE memo <> '持出禁止';

と書いても、memoがNULLのデータは取得対象外になります。

なお、同じ理由で

SELECT * FROM items WHERE memo = NULL;

と書いても、itemsがNULLのデータは取得できません。

NULLのデータも扱うためには、専用の構文 hoge IS NULL を使う必要があります。

memoが'持出禁止'ではない(と思われる)全ての道具を取得するクエリ
SELECT * FROM items WHERE memo <> '持出禁止' OR memo IS NULL;

詳しいことが知りたい方は

をどうぞ。

INDEXと関数と

普通のINDEXは、あくまで「今、そのカラムに格納されている値」に対して目次を作ります。
そして、その目次を使って検索等を行うため、実行速度が早くなるのです。
INDEXが張られたカラムに関数や式を適用してしまうと、「関数が適用された値」の目次は作られていないためINDEXが使えません。
そのため、実行速度が遅くなります。

なお、関数や式に対してもINDEXが使いたい場合は式インデックスを使いましょう。
ただ、式インデックスは、どのDBでもサポートしているわけではありません。
MySQLの場合は、8.0.13からサポートされたそうです。

行ロック?表ロック?

MySQLの場合、行ロックをする場合、ロック条件のカラムにINDEXが張られている必要があります。

なお、ロックについては、他にも罠がたくさんあります……。
実は今年のゲームブック、「ロックトラップ」と「SQLトラップ」のどちらにしようかと悩みました。

罠の解説(Extra Stage)

ゲームブックには入れませんでしたが、Extra StageとしてDB違いによるトラップを掲載しておきます。

ISNULLという関数

こちら、同じ関数名なのに、「MySQL」と「SQLServer」で挙動が全く違います。

MySQLの場合
SELECT ISNULL(NULL);      -- TRUE
SELECT ISNULL(9);         -- FALSE
SELECT ISNULL(NULL, 9);   -- 構文エラー
SQLServerの場合
SELECT ISNULL(NULL);      -- 構文エラー
SELECT ISNULL(9);         -- 構文エラー
SELECT ISNULL(NULL, 9);   -- 9
  • MySQLのISNULLは「引数がNULLかどうか判定し、判定結果(boolean)を返すもの」です。
  • SQLServerのISNULLは「1つめの引数がNULLの場合に、2つめの引数の値を返すもの」です。

なお、「SQLServerのISNULLに該当する関数」は、DBによって異なります。

Nullを9に置換するクエリ(DB別)
-- SQL Server
SELECT ISNULL(NULL, 9);
-- MySQL・SQLite
SELECT IFNULL(NULL, 9);
-- MySQL・PostgreSQL
SELECT COALESCE(NULL, 9);
-- Oracle
SELECT NVL(NULL, 9);

なお、上の例でIFNULLという関数をご紹介していますが、これとは別にNULLIFという関数もあります(名前が似ているだけで、全く別の関数です)。
ISNULLIFNULLNULLIFがあって、しかもISNULLはDBによって挙動が違っていて……なかなかのトラップだと思います。

(ゲームブック部分の)参考文献

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした