【SQL】H2DBの「単一値の原則」違反エラーを吐くタイミングが微妙でハマった話【GROUP BY】
LocalでH2DBに対しては動いていたSQL文が、SQLServerで動かすとエラーを吐くようになって、事象の理解にやや時間を要した時の話です。
結論、製品版DB(SQLServer)と、インメモリDB(H2DB)の単一値の原則違反エラーに対する挙動の違いが原因でした。
- H2DB -> SQL文とテーブル内のデータにより単一値の原則エラーを吐くかどうか決まる
- SQLServer -> テーブル内のデータによらず、SQL文のみを見て単一値の原則エラーを吐くかどうか決まる
ダメならダメでさっさとエラー吐いてもらった方がありがたいので、SQLServerの挙動の方が好みです。
[st-myblock id=”447″]
伝えたいこと
dev環境とLocalで異なるDB製品使ってる場合は、Localでテスト通っててもdev環境で動かなくなる可能性あり。
そういった場合でも、「Localでテスト通ってるからOK！」じゃなく、ちゃんとSQLの知識つけて、SQL文そのものを読んでコードレビューしましょう！！
以上反省。
H2DBとは？？
Javaで作られたインメモリDB。
使い捨てがしやすいので、単体テストとか検証によく使っています。
何が起きたん？
| id | column1 | column2 |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 2 |
| 4 | 2 | 2 |
| 5 | 2 | 2 |
というsampleテーブルに対して、
SELECT
column1, column2
FROM
sample
GROUP BY
column1;というSQLを実行したところ、
- H2DB -> 実行成功
- SQLServer -> 以下エラー
Column 'column2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
となりました。
SQLServerだと動かないSQLがH2DBだとそのまま成功しちゃって不思議だな〜、というのが事象です。
そもそもこのエラー何？
「単一値の原則」エラーです。
オライリーの「SQLアンチパターン」によると、
SELECT句の選択リストに列挙される全ての列は、行グループごとに単一の値の行でなければなりません。
とあります。
| id | column1 | column2 |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 3 |
| 5 | 2 | 3 |
というテーブルに
SELECT
column1, column2
FROM
sample
GROUP BY
column1;を実行すると、column2を表現する行グループが単一の値になっていないため、
| column1 | column2 |
|---|---|
| 1 | {候補は1と2があるけど,何出せばいいんだ？？} |
| 2 | {候補は1と3があるけど,何出せばいいんだ？？} |
となって上記で述べたSQLServerのようなエラーを吐きます。
H2DBとSQLServerの挙動の比較
SQL文はおかしいが、データ上問題ない(一意になってる)場合
| id | column1 | column2 |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 2 |
| 4 | 2 | 2 |
| 5 | 2 | 2 |
というsampleテーブルに対して、
SELECT
column1, column2
FROM
sample
GROUP BY
column1;というSQLを実行したところ、
- H2DB -> 実行成功
- SQLServer -> 以下エラー
Column 'column2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SQL文がおかしいし、データも問題ある場合
| id | column1 | column2 |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 2 |
| 4 | 2 | 3 |
| 5 | 2 | 4 |
というsampleテーブルに対して、
SELECT
column1, column2
FROM
sample
GROUP BY
column1;というSQLを実行したところ、
- H2DB -> エラー
- SQLServer -> エラー
H2DBのエラー
Column "column2" must be in the GROUP BY list; SQL statement:
SQLServerのエラー
Column 'column2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
整理すると
- H2DB -> SQL文とテーブル内のデータにより単一値の原則エラーを吐くかどうか決まる
- SQLServer -> テーブル内のデータによらず、SQL文のみを見て単一値の原則エラーを吐くかどうか決まる
ダメならダメでさっさとエラー吐いてもらった方がありがたいので、SQLServerの挙動の方が好みです。
まとめ
DB製品の微妙な違いに気をつけないと、通ったと思っていたテストが機能してなかったりするので気をつけていきたいですね！