MySQLでSELECT句とGROUP BYの非集約カラムが揃ってなくても通るケース #MySQL

人に教えるとき「ここGROUP BY漏れてますよ」と言う機会が増えてきたので忘備録も兼ねてメモ。

TL;DR

MySQLの場合以下のようなケースにおいてSELECT句とGROUP BYの非集約カラムが揃っていなくてもクエリが通る

ONLY_FULL_GROUP_BYがoffになっている場合
関数従属性が存在する場合

1. 前提：SELECT句とGROUP BYの非集約カラムは揃えたい

例えばECサイトで以下のような購入テーブルpurchaseがあったとします。
(テーブル定義は最後に載せています)

+----+-------------+---------+--------+---------------------+
| id | customer_id | item_id | amount | create_at           |
+----+-------------+---------+--------+---------------------+
|  1 |           1 |   10001 |   1200 | 2019-03-26 18:00:00 |
|  2 |           2 |   10015 |    260 | 2019-03-26 19:00:00 |
|  3 |           3 |   10051 |   5400 | 2019-03-26 20:00:00 |
|  4 |           1 |   10051 |   5400 | 2019-03-26 21:00:00 |
|  5 |           4 |   10109 |    980 | 2019-03-26 22:00:00 |
|  6 |           2 |   10023 |  12000 | 2019-03-26 23:00:00 |
+----+-------------+---------+--------+---------------------+

ここでcustomer_idごとの購入金額合計を求める場合、通常は以下のようなクエリになるかと思います。

SELECT
  customer_id
  , SUM(amount)
FROM
  purchase
GROUP BY
  customer_id
;

基本的にSELECT句内の非集約カラムはこのようにGROUP BYにて指定されている非集約カラムに限定されます（そうしないとクエリが通らないと思われます）。
しかしながらそれが当てはまらないケースがいくつか存在しています。
今回はその当てはまらないケースのうち僕が遭遇した2つのケースを紹介します。

2. ONLY_FULL_GROUP_BYの設定

MySQLの場合sql_modeでONLY_FULL_GROUP_BYをoffにすることでSELECT句とGROUP BYの非集約カラムが一致していなくても結果が返ってきます。
ここではsql_modeの確認は以下の通り行います。

show variables like 'sql_mode';

> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

ここで一番最初に入っている¹ONLY_FULL_GROUP_BYを除いたものに変更します。

set sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

sql_modeからONLY_FULL_GROUP_BYを除外した状態で以下のクエリを実行
本来GROUP BYにcustomer_idが入っていないためエラーになるべきだが、クエリが返ってきてしまいます。


SELECT
  customer_id
  , SUM(amount) as customer_amount
FROM
  purchase
;

出力結果

+-------------+-----------------+
| customer_id | customer_amount |
+-------------+-----------------+
|           1 |           25240 |
+-------------+-----------------+

しかしながらこの出力結果は先程みたものと全く異なっています（出力されているのは単なるGROUP BYなしの全体SUMの結果）。
customer_idには適当な値が入っており²、結果を誤認させるものになってしまっています。

ONLY_FULL_GROUP_BYがデフォルトで入るようになったのはMySQL5.7からなのでそれ以前のバージョンを用いている場合は上記のような現象がデフォルトで発生します。
が、出力結果をみてもわかるようにこれ私たちが欲している値とはおそらく異なるので使用には注意が必要です。

3. 関数従属性が存在するケース

今度は以下のようなcustomerテーブルも用います。

+-------------+-------------------+---------------------+
| customer_id | customer_group_id | create_at           |
+-------------+-------------------+---------------------+
|           1 |                 1 | 2019-01-01 12:00:00 |
|           2 |                 1 | 2019-01-01 12:00:00 |
|           3 |                 2 | 2019-01-01 12:00:00 |
|           4 |                 3 | 2019-01-01 12:00:00 |
+-------------+-------------------+---------------------+

このcustomerテーブルはpurchaseテーブルから見て親テーブルになっています。
新しいテーブルが用意できたところでcustomerテーブルとpurchaseを用いた以下のようなクエリを叩きます。


SELECT
  t1.customer_id
  , t1.customer_group_id
  , SUM(t2.amount) as customer_amount

FROM
  customer as t1
LEFT JOIN
  purchase as t2
ON
  t1.customer_id = t2.customer_id

GROUP BY
  t1.customer_id
;

先程までの例に則ればSELECT句内にあるcustomer_group_idはGROUP BYに列挙されていないカラムであるため、このクエリはエラーになると思われます（ONLY_FULL_GROUP_BYがonのとき）。
しかしながらこのクエリエラーになりません。
実際以下のように出力されます。

+-------------+-------------------+-----------------+
| customer_id | customer_group_id | customer_amount |
+-------------+-------------------+-----------------+
|           1 |                 1 |            6600 |
|           2 |                 1 |           12260 |
|           3 |                 2 |            5400 |
|           4 |                 3 |             980 |
+-------------+-------------------+-----------------+

このことは関数従属性（functional dependency）という用語を用いて説明されます。

あるカラムxの値を決めた場合自動的に値が決まってしまうようなカラムyが存在するときに、それらのカラムには関数従属性が存在するといいます。
カラムyの値がxの値に依存している場合、カラムyをGROUP BYに含めないことが可能になることがあります。
MySQLの場合この性質はONLY_FULL_GROUP_BYの設定に関わらず適用されます。

今回の場合customerテーブルにおいてcustomer_idの値が決まってしまえばcustomer_group_idの値も一意に決まってしまいます。
つまりcustomer_group_idはcustomer_idも依存して決まっている³ため、GROUP BYに列挙されていなくともSELECT句に含めることができます。

4. 個人的な意見

ONLY_FULL_GROUP_BYについていうと僕個人は基本的にonにしてます。
基本的に出力があれば見てわかることが多いのですが、注意力が散漫になってしまうとき誤ったクエリが通ってしまいそのミスに気づけないのが怖いからというのが理由です。

関数従属性については通れば正直どっちでも良いのかなと感じています。
ただ個人的にSELECT句とGROUP BYの非集約カラムが揃ってないのは気持ち悪く感じるので、関数従属性があったとしてもなるべく全部書くようにしています⁴。

以上のことはあくまで僕が「きちんとした出力がでればある程度のクエリは許容する」というスタンスに基づいて書いています。
異なるスタンスの方から見たら違う見解がでるかもしれませんのでその点はご容赦下さい。

現場からは以上です。

参考リンク

12.20.3 MySQL Handling of GROUP BY - MySQL 5.7 Reference Manual
12.20.4 Detection of Functional Dependence - MySQL 5.7 Reference Manual

使用クエリ

-- customer

CREATE TABLE customer (
  customer_id int NOT NULL AUTO_INCREMENT,
  customer_group_id int,
  create_at timestamp DEFAULT '2019-01-01 12:00:00',

  PRIMARY KEY(customer_id)
)
;

INSERT INTO customer VALUES
(1,1,DEFAULT),
(2,1,DEFAULT),
(3,2,DEFAULT),
(4,3,DEFAULT)
;

-- purchase

CREATE TABLE purchase (
  id int NOT NULL AUTO_INCREMENT,
  customer_id int,
  item_id int,
  amount int,
  create_at timestamp,

  PRIMARY KEY(id),
  CONSTRAINT FOREIGN KEY(customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE
)
;

INSERT INTO purchase VALUES
(DEFAULT, 1,10001, 1200, '2019-03-01 18:00:00'),
(DEFAULT, 2, 10015, 260, '2019-03-01 19:00:00'),
(DEFAULT, 3, 10051, 5400, '2019-03-01 20:00:00'),
(DEFAULT, 1, 10051, 5400, '2019-03-01 21:00:00'),
(DEFAULT, 4, 10109, 980, '2019-03-01 22:00:00'),
(DEFAULT, 2, 10023, 12000, '2019-03-01 23:00:00')
;

ここでの出力結果は環境によって異なり得ます。 ↩
公式でも"In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want."と書かれています。 ↩
今回customerテーブルにおいてcustomer_idがPKなのでcustomer_idさえ決まってしまえばcustomer_group_idも一意に特定できます。 ↩
きちんと確認できてないですが古いRDBMSとかだと関数従属性に対応してないものもあるようです。そういうものに今後触るかはわかりませんが。 ↩