MySQL の非標準 group by と ANY_VALUE

MySQLを使っている人には有名なことかもしれない。でも長く使っているけど、あまり group by しない世界線で生きてきたから詳しくなかったので調べた。

MySQLのgroup byはSQL標準ではない(なかった)

MySQL5.6のドキュメントには以下のように書かれています。

標準 SQL では、GROUP BY 句を含むクエリーは、GROUP BY 句で名前が指定されていない選択リスト内の非集約カラムを参照できません。 MySQL では、選択リストが GROUP BY 句で名前が指定されていない非集約カラムを参照できないように、GROUP BY の使用が拡張されています。

quoted from: 12.19.3 MySQL での GROUP BY の処理

これはつまり、標準SQLでは、あるカラムで group by したときに、一意にならないカラムが group by で指定されていない場合、不正であるということです。しかし、MySQLにおいては、そのような、あるカラムでgroup byされたとき、一意にならないカラム(非集約カラム)をMySQLが自由に選択する(何が選ばれるか不確定とする)ことで一意とし、そのような非集約カラムを含む group by SQLを許容するようになっています。(ドキュメントでは「拡張」と言っている)

非集約カラム

非集約カラムを一意にするためには、通常group by向けの関数群(集約関数)を利用します。

GROUP BY 句を含まないステートメントでグループ関数を使用する場合は、すべての行をグループ化することと同等になります。

quoted from: 12.19.1 GROUP BY (集約) 関数

標準SQLにおいては、group by で指定されないカラムを選択するときは、この集約関数によってグループ化されないといけないわけですね。でも、MySQLだとそれがグループ化されてなくても MySQL側で適当に選択してくれて問題なく動いてしまう。

SQL Mode : ONLY_FULL_GROUP_BY

非集約カラムを許容する拡張を利用したくない場合は、MySQL の sql_mode で ONLY_FULL_GROUP_BY を有効にします。

この非集約カラムが許容されない ONLY_FULL_GROUP_BY Mode は MySQL 5.7 からデフォルトで有効になっています。

非集約カラムを使っている場合の対応

MySQL5.6 から MySQL5.7 にあげたときに、SQL標準ではない非集約カラムを使っていると、sql_modeによりエラーが吐かれるようになります。

SELECT XXX is not in GROUP BY clause and contains nonaggregated column 'XXX' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

ここで sql_mode を緩い方向に変更する手段もありますが、SQL標準に対応する方が、将来的に長く互換性が維持できる可能性が高いでしょう。

非集約カラムの問題の場合、group byにカラムが明示されてないので明示するようにすればいいかと思いますが、気軽にやるとクエリの結果が変わります。なぜなら、もともとは MySQLが適当に、自由に、不確定にgroup byされた後に一意に集約していたのに、group by してしまうと、グループの数だけ選択されるようになるので、総じて抽出されるレコードが増えます。

このような場合は、group by のカラムは変更しないで、選択されるカラムに ANY_VALUE() 関数を利用します。これはMySQL5.6までの非集約カラムに対して MySQLが行っていた自由な選択を模してくれるグループ関数で、MySQL5.7から利用できます。

MySQL5.7のsql_modeのドキュメントで言及 されています。