MySQLの拡張仕様、GROUP BYでカラム指定を省略してSELECTする場合について

GROUP BY節で、選択したすべてのカラムの名前を列挙する必要はありません。これにより、ごく一部ではありますが、きわめて一般的なクエリのパフォーマンスが向上します。項11.11. 「GROUP BY 句との関数および修飾子の使用」 を参照してください。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 1.7.1 標準 SQL に対する MySQL 拡張機能

SELECT foo_id, bar_id FROM examples GROUP BY foo_id;

MySQLでは、例えばこんな風に、GROUP BYにfoo_idだけを指定して、foo_id, bar_idを取得出来ます。上記の通り、実はこれはMySQLの拡張仕様らしいです。

SELECT foo_id, bar_id FROM examples GROUP BY foo_id, bar_id;

標準SQL規格では、この様にfoo_id, bar_idを取得したい場合は、GROUP BY foo_id, bar_idとする必要があります。

注意

GROUP BY 部から省略したカラムがグループ内で一定していない場合は、この機能を 使用しないで ください。サーバはいかなる値もグループから自由に戻すことができ、すべての値が同じでない限り、結果は不確定です。

http://dev.mysql.com/doc/refman/5.1/ja/group-by-hidden-fields.html

機械翻訳っぽくて分かりにくいですが、前述の例でGROUP BY foo_idでグルーピングした際、bar_idに何が入るか分からないという、落とし穴があるみたいです。これを知らなかったんですが、GROUP BYをあまり使った事が無く不安だったので聞いてみたら、教えてもらえました。

例

foo_id	bar_id
1	1
1	2
1	3
2	1
2	1
2	1

分かりにくいし、後で忘れるかもしれないので、例を書いておきます。
こんなテーブルから、GROUP BYを使ってSELECTした場合の、SQL標準とMySQL拡張仕様の2パターンの想定動作*1を書いてみます。

SQL標準

SELECT foo_id, bar_id, count(foo_id) AS count FROM examples GROUP BY foo_id, bar_id;

foo_id	bar_id	count
1	1	1
1	2	1
1	3	1
2	1	3

SQL標準どおりちゃんと書けば、GROUP BYに指定するカラムはそもそも省略出来ません。foo_idとbar_idの両方が同じ値のレコードをグループにするので、この様になります。

MySQL拡張仕様

SELECT foo_id, bar_id, count(foo_id) AS count FROM examples GROUP BY foo_id;

foo_id	bar_id	count
1	?	3
2	1	3

MySQL拡張仕様で、GROUP BYへのカラムを省略してGROUP BY foo_idとすると、foo_idだけ同じ値なら、グルーピングします。この場合、?になっているbar_idには、1, 2, 3の3通りの可能性があります。そして、MySQLの拡張仕様では、実行毎にどれかを入れるし、どれが入るかは分からないという話です。

例2

tech-memo » MySQLのGROUP BYは、寛容すぎて気持ちが悪い。

例を書いておいてなんですが、こっちのエントリーのほうが例がわかりやすいかもしれません。

書いた時期

2011-10-06

*1:実行してません。