職業プログラマの休日出勤

職業プログラマによる日曜自宅プログラミングや思考実験の成果たち。リアル休日出勤が発生すると更新が滞りがちになる。記事の内容は個人の意見であり、所属している(いた)組織の意見ではない。

暗黙のGroup By

この記事はMySQLのdisってる記事…のように見えるかもしれませんが、「長年継ぎ足して熟成されてきた秘伝のソース」をdisる記事です。
原理?は MySQLのGroup By - 職業プログラマの休日出勤distinct と order by - 職業プログラマの休日出勤 と同じです。

テスト用テーブルの定義と、テストデータ(PostgreSQLでもMySQLでも動作OK)

create table test20121105 (
	id int primary key,
	category int,
	val int
);

insert into test20121105 values(1, 1, 1);
insert into test20121105 values(2, 1, 3);
insert into test20121105 values(3, 2, 5);
insert into test20121105 values(4, 2, 7);

はじめに… Group By を明記しない集計処理

例えば

select count(*) from test20121105;

のようなSQLでは、group by 句を書かずに count() のような集計関数(aggregate functions)を使っています。この時、内部的にはもちろん集計処理が行われています。
この記事のタイトル「暗黙のGroup By」は、この集計処理のことを指しています。この言い方は一般的ではないようですが、他に適切な呼び名が見当たらなかったことから、利用しました。

困ったSQL

select category, sum(val)
from test20121105;

group by 句を書き忘れたのか、それとも select の列選択に誤って category を書いてしまったのか理由は不明ですが、「秘伝のソース」の中でこのようなSQL文を発見しました。

もちろんPostgreSQLで実行するとエラーになります。

postgres=# select category, sum(val)
postgres-# from test20121105;
ERROR:  column "test20121105.category" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select category, sum(val)
               ^

MySQLだと、例によって動作してしまいます。

mysql> select category, sum(val)
    -> from test20121105;
+----------+----------+
| category | sum(val) |
+----------+----------+
|        1 |       16 |
+----------+----------+
1 row in set (0.07 sec)

もちろんこんなSQLを書く人が悪いのですが、こういう誤りをさっと検知できるようにはしたいものです。