事象 : Group Byをしたらエラーになった
- 環境
- MySQL 5.7.12
- クライアントツール : A5:SQL Mk-2 version 2.15.0
エラーになったSQL
select * from (
select distinct pon.pon_id,vin,pon_cho_vin.code,flg
from pon_cho_vin
left join pon_cho on pon_cho.pon_cho_id=pon_cho_vin.pon_cho_id
left join pon_vin on pon_vin.pon_vin_id=pon_cho.pon_vin_id
left join pon on pon.pon_id=pon_vin.pon_id
where pon_cho_vin.code in ('001','003')
) a
group by pon_id,vin,code
having count(*) > 1;
エラーメッセージ
SQL : #42000Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.flg' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
原因 : Group By句に指定したカラムをSELECT句に指定しないから
エラーを訳してみると
「SELECTリストの式はGROUP BY句になく、GROUP BY句の列に機能的に依存しない非集約列'a.flg'を含んでいます。これは、sql_mode=only_full_group_byと互換性がありません。」
となり、わかるようなわからないような・・・。
sql_modeとは？
sql_mode システム変数の値に応じて異なるクライアントにこれらの異なるモードを適用できます。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.11 サーバー SQL モード
わかるようなわからないような・・・。
-- sql_modeを確認してみる(A5M2を使っている場合は[設定]>[オプション]>[SQLタブ]>[パラメータ利用モード]を「”:”のみパラメータとして利用する」にしてから実行すると「@」がパラメータ扱いされずに実行できます)
SELECT @@GLOBAL.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
SELECT @@SESSION.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.11 サーバー SQL モードでデフォルトのsql_modeの意味を見てみる。
| sql_mode | 意味 |
|---|---|
| ONLY_FULL_GROUP_BY | 選択リスト、HAVING 条件または ORDER BY リストが、GROUP BY 句で指定されておらず、機能的に GROUP BY カラムに依存しない (一意に決定される) 非集計カラムを参照するクエリーを拒否します。 |
| STRICT_TRANS_TABLES | トランザクションストレージエンジンに対して、および可能な場合は非トランザクションストレージエンジンに対して、厳密な SQL モードを有効にします。 |
| NO_ZERO_IN_DATE | 年の部分は非ゼロであるが月または日の部分が 0 である日付をサーバーが許可するかどうかに影響します。 |
| NO_ZERO_DATE | サーバーが '0000-00-00' を有効な日付として許可するかどうかに影響します。 |
| ERROR_FOR_DIVISION_BY_ZERO | MOD(N,0) を含むゼロ除算の処理に影響します。 |
| NO_ENGINE_SUBSTITUTION | CREATE TABLE または ALTER TABLE などのステートメントが無効またはコンパイルされていないストレージエンジンを指定したとき、デフォルトのストレージエンジンの自動置換を制御します。 |
sql_modeは、SQLの解釈を制御しているようだ。
「sql_mode=only_full_group_byと互換性がありません。」は「SELECT句にGROUP BY句で指定したカラムを指定していないから拒否します。」ということで、
面倒くさがって「select * from」としないで、ちゃんと「select pon_id,vin,code from」と書かねばならないということだ。
「SELECT *」としているためGROUP BYで指定されていない「flg」が含まれてしまっているからエラーになっているということだ。
select * from (
select distinct pon.pon_id,vin,pon_cho_vin.code,flg
-- ...省略...
group by pon_id,vin,code
-- ...省略...
対応 : Group By句に指定したカラムをSELECT句に指定する
select pon_id,vin,code from (
select distinct pon.pon_id,vin,pon_cho_vin.code,flg
from pon_cho_vin
left join pon_cho on pon_cho.pon_cho_id=pon_cho_vin.pon_cho_id
left join pon_vin on pon_vin.pon_vin_id=pon_cho.pon_vin_id
left join pon on pon.pon_id=pon_vin.pon_id
where pon_cho_vin.code in ('001','003')
) a
group by pon_id,vin,code
having count(*) > 1;