きっかけ

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

SQLの単体テストケースの考え方

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

どこから手を付けるか？

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

調査

書籍「プログラマのためのSQL 第4版すべてを知り尽くしたいあなたに」をメインにSELECT文の実行順序と挙動を調査。

SELECT文の挙動調査元ネタ

Amazon:プログラマのためのSQL 第4版すべてを知り尽くしたいあなたに
では、
1.WITH
2.FROM
3.WHERE
4.GROUP BY
5.HAVING
6.SELECT

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

Oracle:図でイメージするOracle DatabaseのSQL全集
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)
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句よりも先に評価されるのは本当ですか？

PostgreSQL:SELECT
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)
Qiita:SELECT文の評価順序の話

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

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内部では違う模様)。

SELECT文を構成する要素の
挙動

FROM句の挙動1

FROM句に1つのテーブルだけ記述されていたら、そのテーブルからワークテーブルを作ります。
複数のテーブルが記述されていたら、それらをクロス結合¹して、1つのワークテーブルを作ります。このワークテーブルはSELECT文のスコープ内でのみ存在します。

FROM句の挙動2

INNER JOIN:①上記ワークテーブルに対して、ON句結合条件に合致する行をワークテーブルに残し、合致しない行を削除します。
LEFT OUTER JOIN²:まず、①で合致する行を残し、②次に左側テーブルの中で、右側テーブルには結合条件を満たす行が存在しなかった左側テーブルの行を残します。ここで残った行に右側テーブル由来の列はNULL値が設定されます。

WHERE句の挙動1

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

WHERE句の挙動2

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

GROUP BY句の挙動

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

HAVING句の挙動

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

SELECT句の挙動

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

ORDER BY句の挙動

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

単体テストケースを考える

単体テストケースの考え方1

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

単体テストケースの考え方2

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

FROM句の単体テストケース

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

FROM句の単体テストケース(INNER JOIN)で用意するテストデータ

①左テーブルONLY行、②右テーブルONLY行、
③左右を比較した結果TRUEになる行、
④左右を比較した結果FALSEになる行、
⑤左右の比較列のどちらかをNULLにして比較結果がUNKNOWNになる行を基本とし、
⑥関数や式の計算結果との比較がTRUE/FALSE/UNKNOWNになるテストデータを用意します。

FROM句の単体テストケース(INNER JOIN)の期待値

左右比較結果がTRUEとなる行だけが返る事。

#SQLの論理はTRUE/FALSE/UNKNOWNの3値論理

FROM句の単体テストケース(LEFT OUTER JOIN)

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

WHERE句の単体テストケース

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

GROUP BY句の単体テストケース

GROUP BY句に指定した列で①同じ値になった行が1つの行に集約されるか、②集約関数の集約結果がテスト対象になります。

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文を動かす単体テストケースを考えます。HAVING句の条件でふるいにかける事がテスト対象となります。

HAVING句の単体テストケース用意するデータと期待値

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

SELECT句の単体テストケース

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

ORDER BY句の単体テストケース

ORDER BY句で指定した列でソート(昇順、降順)されている事がテスト対象です。
用意するテストデータ: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

クロス結合すると巨大なワークテーブルになるため実際にはクロス結合を行う製品は存在しないです。クロス結合はあくまで説明の方便です。 ↩
RIGHT OUTER JOINは、LEFTの逆。FULL OUTER JOINは、全ての結合行に加え、一致しなかった左側の行(右側由来列はNULL)、一致しなかった右側の行(左側由来列はNULL)を全て返します。 ↩
単純な検索条件やサブクエリを入れ子にした複雑な条件も書けます。 ↩
ON句に書けるのはテーブルの結合条件だけで、WHERE句に書けるのは検索条件だけだと思いがちですが、そうではないです。結合条件と検索条件の記述場所を分けるのは、人間にとっての読みやすさ、理解のしやすさです。 ↩
GROUP BY句が無くてもHAVING句は利用できます。SELECT句はグルーピングの列をまったく含まなくても、構文的には問題ないです(役に立つかどうかはアレですが)。 ↩
この「余分な」の意味は、1つは残されるということ。もしSELECT句がALLオプションを持っていたり、あるいはALLもDISTINCTもなかった場合は、すべての重複行が最終的な結果テーブルに保存される。 ↩
SELECT句の式に対するAS演算子は、列に別名を与えます。この新しい名前はすぐに存在しはじめますが、WHERE句もGROUP BY句もHAVING句も実行された後の話なので、この別名をその他の句で使うことはできません(PostgreSQLやMySQLはこの別名をGROUP BYで使えるが、標準外の独自実装)。 ↩
列だけでなく、式も書ける。複数指定可。SELECT出力リストに出現しない列を含むことも可。 ↩
ORDER BY中の任意の列の後に、ASC(昇順)、DESC(降順)を指定できます(省略可)。指定が無ければ、ASC(昇順)とします。 ↩
ORDER BY句が無かったら、実装依存の順番で行が返されます。 ↩

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