SQLの単体テストケースって、どうやって考えるの？

## きっかけ
あるとき、○○ちゃんに、「SQLの単体テストケースってどうやって考えればよいの？」と問われ、「うっ」となりました。

---

## SQLの単体テストケースの考え方
そんなことも答えられないのに、「網羅的なテストぉー！」だとか、「カバレッジはどのくらいを狙っているの？」などと偉そうに言ってきましたが、なんとなくを整理して、改めて理解してみます。

---

## どこから手を付けるか？
テスト対象はDDLなのか、DMLなのか。日常よく使うSELECT文を単体テストケースの考え方の対象とします。SELECT文を<font color="#B61977">**概念的**</font>に(内部実装は置いといて)、「**<font color="#B61977">1.構成する要素(○○句)と実行順序</font>**」、「**<font color="#B61977">2.その要素の挙動</font>**」に整理、理解して、「**<font color="#B61977">3.単体テストケース</font>**」を考えます。

---
## 調査
書籍「プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに」をメインにSELECT文の実行順序と挙動を調査。下記黒三角をクリックすると折りたたまれた行が広がります。


<details><summary>SELECT文の挙動調査元ネタ</summary>

[Amazon:プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに](https://www.amazon.co.jp/dp/B00SF6JN4U/ref=dp-kindle-redirect?_encoding=UTF8&btkr=1 "https://www.amazon.co.jp/dp/B00SF6JN4U/ref=dp-kindle-redirect?_encoding=UTF8&btkr=1")
では、
1.WITH
2.FROM
3.WHERE
4.GROUP BY
5.HAVING
6.SELECT

ORDER BYはどこに行った？ググってみたがRDBMS公式サイトやANSIとかで見つけるのが難しい。

[Oracle:図でイメージするOracle DatabaseのSQL全集](https://www.oracle.com/technetwork/jp/articles/otnj-sql-image1-308625-ja.html "https://www.oracle.com/technetwork/jp/articles/otnj-sql-image1-308625-ja.html")
1.FROM
2.WHERE(結合条件)
3.START WITH
4.CONNECT BY
5.WHERE(行のフィルタ条件)
6.GROUP BY
7.HAVING
8.MODEL
9.SELECT
10.UNION､MINUS､INTERSECTなどの集合演算
11.ORDER BY

[Microsoft SQL Server:SELECT (Transact-SQL)](https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2012/ms189499(v=sql.110) "https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2012/ms189499(v=sql.110)")
1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP BY
6.WITH CUBE または WITH ROLLUP
7.HAVING
8.SELECT
9.DISTINCT
10.ORDER BY
11.TOP
ONとJOINが逆では？と思い、調べてみたら、ビンゴ。バインド順序がON->JOINの順ではあるが、概念的な挙動はJOIN→ON。
[jp.stackoverflow:ON句がJOIN句よりも先に評価されるのは本当ですか？](https://ja.stackoverflow.com/questions/33708/on%E5%8F%A5%E3%81%8Cjoin%E5%8F%A5%E3%82%88%E3%82%8A%E3%82%82%E5%85%88%E3%81%AB%E8%A9%95%E4%BE%A1%E3%81%95%E3%82%8C%E3%82%8B%E3%81%AE%E3%81%AF%E6%9C%AC%E5%BD%93%E3%81%A7%E3%81%99%E3%81%8B "https://ja.stackoverflow.com/questions/33708/on%E5%8F%A5%E3%81%8Cjoin%E5%8F%A5%E3%82%88%E3%82%8A%E3%82%82%E5%85%88%E3%81%AB%E8%A9%95%E4%BE%A1%E3%81%95%E3%82%8C%E3%82%8B%E3%81%AE%E3%81%AF%E6%9C%AC%E5%BD%93%E3%81%A7%E3%81%99%E3%81%8B")

[PostgreSQL:SELECT](https://www.postgresql.jp/document/11/html/sql-select.html "https://www.postgresql.jp/document/11/html/sql-select.html")
1.WITH
2.FROM
2.WHERE
4.GROUP BY
5.HAVING
6.SELECT
7.UNION,INTERSECT,EXCEPT
8.ORDER BY
9.LIMIT
10.FOR UPDATEなどのロック処理句

他、いくつか見つけたサイト。
[en.wikipedia:Select (SQL)](https://en.wikipedia.org/wiki/Select_(SQL)#Query_evaluation_ANSI "https://en.wikipedia.org/wiki/Select_(SQL)#Query_evaluation_ANSI")
[Qiita:SELECT文の評価順序の話](https://qiita.com/suzukito/items/edcd00e680186f2930a8 "https://qiita.com/suzukito/items/edcd00e680186f2930a8")

</details>

---

# **<font color="#B61977">SELECT文を構成する要素と<br>実行順序</font>**

---

## SELECT文を構成する要素(〇〇句)
SELECT文を概念的に構成する要素は10を超えるのですが、SELECT,FROM,WHERE,GROUP BY,HAVING,ORDER BYの6要素を理解の対象とします。

※FOR UPDATE句やWINDOW関数、UNION集合演算等々構成要素をあげたらきりがなく、整理が難しいので。。。

---

## SELECT文を構成する要素の実行順序

1.FROM→2.WHERE→3.GROUP BY→4.HAVING→5.SELECT→6.ORDER BY

この順で実行(RDBMS内部では違う模様)。

---

# **<font color="#B61977">SELECT文を構成する要素の<br>挙動</font>**

---

## FROM句の挙動1
FROM句に1つのテーブルだけ記述されていたら、そのテーブルからワークテーブルを作ります。
複数のテーブルが記述されていたら、それらを**<font color="#B61977">クロス結合[^FROM句の挙動1-1]</font>**して、**<font color="#B61977">1つのワークテーブル</font>**を作ります。このワークテーブルはSELECT文のスコープ内でのみ存在します。

[^FROM句の挙動1-1]: クロス結合すると巨大なワークテーブルになるため実際にはクロス結合を行う製品は存在しないです。クロス結合はあくまで説明の方便です。

---

## FROM句の挙動2
**<font color="#B61977">INNER JOIN</font>**:①上記ワークテーブルに対して、ON句結合条件に合致する行をワークテーブルに残し、合致しない行を削除します。
**<font color="#B61977">LEFT OUTER JOIN</font>**[^FROM句の挙動2-1]:まず、①で合致する行を残し、②次に左側テーブルの中で、右側テーブルには結合条件を満たす行が存在しなかった左側テーブルの行を残します。ここで残った行に右側テーブル由来の列はNULL値が設定されます。

[^FROM句の挙動2-1]: RIGHT OUTER JOINは、LEFTの逆。FULL OUTER JOINは、全ての結合行に加え、一致しなかった左側の行(右側由来列はNULL)、一致しなかった右側の行(左側由来列はNULL)を全て返します。

---

## WHERE句の挙動1
WHERE句に記述された検索条件[^WHERE句の挙動1-1]をFROM句で作られたワークテーブルの各行に適用します。そして、検索条件を評価した結果、TRUEの行だけがワークテーブルに残ります。
FALSEやUNKNOWNの行はワークテーブルから削除されます。

[^WHERE句の挙動1-1]: 単純な検索条件やサブクエリを入れ子にした複雑な条件も書けます。

---
## WHERE句の挙動2
WHERE句は次の2種類からなります[^WHERE句の挙動2-1]。
①ワークテーブルから除外する検索条件=>通常WHERE句に書く。
②2つ以上のテーブルの結合条件=>通常ON句に書く。
いずれにせよ、クロス結合した結果のワークテーブルから余計な行を除外するのがWHERE句(あとON句)の役割です。

[^WHERE句の挙動2-1]: ON句に書けるのはテーブルの結合条件だけで、WHERE句に書けるのは検索条件だけだと思いがちですが、そうではないです。結合条件と検索条件の記述場所を分けるのは、人間にとっての読みやすさ、理解のしやすさです。

---

## GROUP BY句の挙動
GROUP BY句に指定した列で、結果が同じ値になった行を1つの行に集約します。この時、古いワークテーブルを捨て、新しいワークテーブルが作られます。
NULLはそれで1つのグループを作ります。
一行に集約する際に、集約関数を実行します。

---

## HAVING句の挙動
HAVING句は、その時点のワークテーブルのグループ化された各行に対して適用されます。ここでTRUEに評価された行だけが残り、FALSEやUNKNOWNの行は削除されます。
GROUP BY句が無い場合[^HAVING句の挙動-1]、HAVING句はテーブル全体を1つのグループとして扱います。

[^HAVING句の挙動-1]: GROUP BY句が無くてもHAVING句は利用できます。SELECT句はグルーピングの列をまったく含まなくても、構文的には問題ないです(役に立つかどうかはアレですが)。

---

## SELECT句の挙動
SELECT 句に現れない列は最終的なワークテーブルから削除されます。ここに含まれるのは、列、定数、関数、スカラサブクエリ。
もしDISTINCTが有ればワークテーブルから余分な[^SELECT句の挙動-1]重複行が削除されます。
SELECT句がこんなに後で実行されるので、SELECT句のスカラサブクエリ、関数呼び出し、式は、これまでの句が実行された後に実行されます[^SELECT句の挙動-2]。

[^SELECT句の挙動-1]: この「余分な」の意味は、1つは残されるということ。もしSELECT句がALLオプションを持っていたり、あるいはALLもDISTINCTもなかった場合は、すべての重複行が最終的な結果テーブルに保存される。
[^SELECT句の挙動-2]: SELECT句の式に対するAS演算子は、列に別名を与えます。この新しい名前はすぐに存在しはじめますが、WHERE句もGROUP BY句もHAVING句も実行された後の話なので、この別名をその他の句で使うことはできません(PostgreSQLやMySQLはこの別名をGROUP BYで使えるが、標準外の独自実装)。

---

## ORDER BY句の挙動

その時点のワークテーブルをORDER BY句で指定した列 [^ORDER_BY句の挙動-1] でソート(昇順、降順) [^ORDER_BY句の挙動-2] します。
左の列から比較し、2つの行が同じ場合は、次の右側の列で比較します。その結果も同じなら、さらに次の右側の列に進みます。
指定した全ての列で同じと判断された場合は、実装に依存した順番で行が返されます [^ORDER_BY句の挙動-3] 。

[^ORDER_BY句の挙動-1]: 列だけでなく、式も書ける。複数指定可。SELECT出力リストに出現しない列を含むことも可。

[^ORDER_BY句の挙動-2]: ORDER BY中の任意の列の後に、ASC(昇順)、DESC(降順)を指定できます(省略可)。指定が無ければ、ASC(昇順)とします。

[^ORDER_BY句の挙動-3]: ORDER BY句が無かったら、実装依存の順番で行が返されます。

---

## <font color="#B61977">単体テストケースを考える</font>

---

## 単体テストケースの考え方1
ここまででSELECT文を構成する6つの要素を整理してきました。
テスト対象のSELECT文を6つの要素に分割して、それぞれの要素毎に単体テストするイメージで、テストケースの考え方を整理します。

---

## 単体テストケースの考え方2
長いSELECT文は単体テストしにくいので、実際に分割して、いくつかのSELECT文を作り、それぞれで単体テストします。
例えば、FROM句のテーブルの結合が複雑なら、FROM句だけは元のSQLからコピーし、他は余計なものをつけない(SELECT句は主キーだけか”*”とし、WHERE句以下は無しとか)SQLを作り、それでテストします。

---

## FROM句の単体テストケース
先のFROM句の挙動整理では、クロス結合して得られたワークテーブルからJOINタイプとON句で指定した条件で絞り込みますので、<font color="#B61977"> **テーブル同士の結合条件がテスト対象** </font>となります。
次のようなテストデータと期待値を考えます。

---

## FROM句の単体テストケース(INNER JOIN)で用意するテストデータ
①左テーブルONLY行、②右テーブルONLY行、
③左右を比較した結果**<font color="#B61977">TRUE</font>**になる行、
④左右を比較した結果**<font color="#B61977">FALSE</font>**になる行、
⑤左右の比較列のどちらかをNULLにして比較結果が<font color="#B61977">**UNKNOWN**</font>になる行を基本とし、
⑥関数や式の計算結果との比較がTRUE/FALSE/UNKNOWNになるテストデータを用意します。

---
## FROM句の単体テストケース(INNER JOIN)の期待値
左右比較結果がTRUEとなる行だけが返る事。

[#SQLの論理はTRUE/FALSE/UNKNOWNの3値論理](https://qiita.com/madilloar/items/df9c579caae3aa380b52##TRUE/FALSE/UNKNOWN%E3%81%AE3%E5%80%A4%E8%AB%96%E7%90%86)

---

## FROM句の単体テストケース(LEFT OUTER JOIN)
**用意するテストデータ**:INNER JOINの①~⑥と同じ考え方でテストデータを用意します。
**期待値**:
左ONLY行と左右比較結果がTRUEとなる行だけが返ってくる事。
左ONLY行で右テーブル由来の列はNULLである事。

---

## WHERE句の単体テストケース
FROM句後ワークテーブルを<font color="#B61977">**WHERE句の検索条件**</font>でふるいにかける事がテスト対象です。
**用意するテストデータ**:いっそFROM句後をイメージしたテーブルを用意します。そしてWHERE句でTRUEになるテストデータ、FALSE,UNKNOWNになるテストデータを用意します。
**期待値**:WHERE句の検索条件でTRUEとなる行だけが返ってくる事。

---

## GROUP BY句の単体テストケース
GROUP BY句に指定した列で<font color="#B61977">**①同じ値になった行が1つの行に集約されるか、②集約関数の集約結果**</font>がテスト対象になります。

---

## GROUP BY句の単体テストケース　用意するテストデータ
GROUP BY句に指定した列数を基にテストデータを用意します。1列なら3行用意し、1行と2行は集約され1行になり、残りは1行となるようなデータを用意します。集約関数に使う列は期待値を検証できる値を設定します。NULLはそれで1つのグループを作るので、GROUP BYで指定した列それぞれにNULLを設定した行も用意します。

---

## GROUP BY句の単体テストケースの期待値

①GROUP BYに指定した列が同一の行は集約される事。
②集約関数が意図した結果に集約される事。

---

## HAVING句の単体テストケース
HAVING句はあまり使ったことがないので、テストケースを考えるのが難しい。。。が、HAVING句はGROUP BY句が無くても動くので(テーブル全体を1つのグループとして扱う)、GROUP BY後をイメージしたテーブルを用意して、シンプルなSQL文を動かす単体テストケースを考えます。<font color="#B61977">**HAVING句の条件**</font>でふるいにかける事がテスト対象となります。

---

## HAVING句の単体テストケース 用意するデータと期待値
**用意するテストデータ**:いっそGROUP BY句後をイメージしたテーブルを用意します。そしてHAVING句でTRUEになるテストデータ、FALSE,UNKNOWNになるテストデータを用意します。
**期待値**:HAVING句の検索条件でTRUEとなる行だけが返ってくる事。

---

## SELECT句の単体テストケース
SELECT句の<font color="#B61977">**列(定数、関数、スカラサブクエリ含む)**</font>がテスト対象です。
**用意するデータ**:列の並び順がテストできるように、<font color="#B61977">**全ての列が違う値**</font>のテストデータを用意します。列が待値になるデータ、期待値とならないデータを用意。
**期待値**:①列の並び順が期待値通りである事。②列の結果が期待値通りである事。

---

## ORDER BY句の単体テストケース
ORDER BY句で指定した列で<font color="#B61977">**ソート(昇順、降順)されている事**</font>がテスト対象です。
**用意するテストデータ**:ORDER BY句で指定した列数を基にテストデータを用意します。1列なら2行、昇順、降順がわかるように用意します。2列指定なら4行用意します。
**期待値**:意図したソート順でソートされる事。

---

## TRUE/FALSE/UNKNOWNの3値論理
SQLでは3値論理を採用しているので、比較演算の結果、UNKNOWNとなるテストケースも考える。

## NOT

| X | not X |
|:-:|:-:|
|TRUE   |FALSE   |
|UNKNOWN   |UNKNOWN   |
|FALSE   |TRUE   |

## AND
|   | TRUE | UNKNOWN | FALSE |
|:-:|:-:|:-:|:-:|
| **TRUE**  | TRUE  | UNKNOWN  | FALSE  |
| **UNKNOWN**  | UNKNOWN  | UNKNOWN  | FALSE  |
| **FALSE**  | FALSE  | FALSE  | FALSE  |

## OR
|   | TRUE | UNKNOWN | FALSE |
|:-:|:-:|:-:|:-:|
| **TRUE**  | TRUE  | TRUE    | TRUE|
| **UNKNOWN**  | TRUE  | UNKNOWN  | UNKNOWN  |
| **FALSE**  | TRUE  | UNKNOWN    | FALSE  |