データベース

【SQL】H2DBの「単一値の原則」違反エラーを吐くタイミングが微妙でハマった話【GROUP BY】

2023年10月22日

LocalでH2DBに対しては動いていたSQL文が、SQLServerで動かすとエラーを吐くようになって、事象の理解にやや時間を要した時の話です。

結論、製品版DB(SQLServer)と、インメモリDB(H2DB)の単一値の原則違反エラーに対する挙動の違いが原因でした。

  • H2DB -> SQL文とテーブル内のデータにより単一値の原則エラーを吐くかどうか決まる
  • SQLServer -> テーブル内のデータによらず、SQL文のみを見て単一値の原則エラーを吐くかどうか決まる

ダメならダメでさっさとエラー吐いてもらった方がありがたいので、SQLServerの挙動の方が好みです。

ITエンジニア6年目の山根です。X(Twitter)やってます。自己紹介,お問い合わせはこちらまで！

伝えたいこと

dev環境とLocalで異なるDB製品使ってる場合は、Localでテスト通っててもdev環境で動かなくなる可能性あり。

そういった場合でも、「Localでテスト通ってるからOK！」じゃなく、ちゃんとSQLの知識つけて、SQL文そのものを読んでコードレビューしましょう！！

以上反省。

H2DBとは？？

www.h2database.com

Javaで作られたインメモリDB。

使い捨てがしやすいので、単体テストとか検証によく使っています。

何が起きたん？

idcolumn1column2
111
211
322
422
522

という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句の選択リストに列挙される全ての列は、行グループごとに単一の値の行でなければなりません。

とあります。

idcolumn1column2
111
212
321
423
523

というテーブルに

SELECT
    column1, column2
FROM
    sample
GROUP BY
    column1;

を実行すると、column2を表現する行グループが単一の値になっていないため、

column1column2
1{候補は1と2があるけど,何出せばいいんだ？？}
2{候補は1と3があるけど,何出せばいいんだ？？}

となって上記で述べたSQLServerのようなエラーを吐きます。

H2DBとSQLServerの挙動の比較

SQL文はおかしいが、データ上問題ない(一意になってる)場合

idcolumn1column2
111
211
322
422
522

という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製品の微妙な違いに気をつけないと、通ったと思っていたテストが機能してなかったりするので気をつけていきたいですね！

-データベース