UNION する場合だけ、厳密な GROUP BY が要求される理由

Question

前提

➀と➁をSELECTする場合はIDだけにGROUP BYを付ければいいのですが、➂でUNIONする場合はSELECTするカラムすべてにGROUP BYを付けなければエラーになります。

➀：「コメントの本文」をSELECT
➁：「コメントについたタグ」をSELECT
➂： ➀と②をUNIONしてSELECT

知りたいこと

なぜ➂の場合だけ、SELECTするカラムすべてにGROUP BYを付けなければエラーになるのか？理由を知りたいです。

CREATE、INSERT

対象レコードです。

-- コメント
CREATE TABLE my_comments(
    `ID` int, `lang_id` int, `comment` varchar(10), count_likes int(10),
    PRIMARY KEY (`ID`),
    FULLTEXT full_my_comments (`comment`) WITH PARSER ngram );
INSERT INTO my_comments
    (`ID`, `lang_id`, `comment`, `count_likes`)
    VALUES
    (1,  1,  '眠らないでください',  5),
    (2,  1,  '静かにしてください',  1),
    (3,  1,  '席についてください',  6),
    (4,  1,  'たまに休んで下さい',  9);

-- タグ
CREATE TABLE my_tags(
    `ID` int, `is_official` int, `tag_kind_id` int, `tag_name` varchar(100), `detail` varchar(100),
    PRIMARY KEY (`ID`),
    UNIQUE unique_tags (`is_official`, `tag_kind_id`, `tag_name`, `detail`) );
INSERT INTO my_tags
    (`ID`, `is_official`, `tag_kind_id`, `tag_name`, `detail`)
    VALUES
    (1,  1,  1,  '鈴木', '良い人'),
    (2,  0,  1,  '佐藤', '悪い人'),
    (3,  1,  2,  '指導', '.');

-- コメントが持っているタグ
CREATE TABLE my_tag_holders(
    `comments_ID` int, `tags_ID` int,
    PRIMARY KEY (`comments_ID`,`tags_ID`) );
INSERT INTO my_tag_holders
    (`comments_ID`, `tags_ID`)
    VALUES
    (1, 1),(1, 2),(1, 3),
    (2, 4),
    (3, 3);

➀：「コメントの本文」をSELECT

まずこちらは、# ➀-1から# ➀-2行目の５つのカラムに対し、# ➀-3行目で「IDだけでまとめる」よう指定していますが、エラーは起こりません。

		-- ➀：「コメントの本文」をSELECT
		SELECT
			 comments.ID AS comment_id      # ➀-1
			,comments.lang_id
			,comments.comment
			,comments.count_likes
			,GROUP_CONCAT(
				tags.ID
				SEPARATOR '__SEPARATOR__'
			) AS tag_ids                    # ➀-2

		FROM
			my_comments comments
			LEFT JOIN my_tag_holders th
					ON th.comments_ID = comments.ID
			LEFT JOIN my_tags tags
					ON tags.ID = th.tags_ID
						  
		-- '指導'を持つコメントを指定          
		WHERE MATCH (comments.comment) AGAINST ('指導' IN BOOLEAN MODE)
		
		GROUP BY comments.ID                # ➀-3
		ORDER BY comments.count_likes DESC, comments.ID DESC
		LIMIT 0, 20

②：「コメントについたタグ」をSELECT

またこちらは、# ➁-1から# ➁-2行目の５つのカラムに対し、# ➁-3行目で「IDだけでまとめる」よう指定していますが、エラーは起こりません。

		-- ➁：「コメントについたタグ」をSELECT
		SELECT
			 comments.ID AS comment_id      # ➁-1
			,comments.lang_id
			,comments.comment
			,comments.count_likes
			,GROUP_CONCAT(
				tags.ID
				SEPARATOR '__SEPARATOR__'
			) AS tag_ids                    # ➁-2

		FROM
			my_comments comments
			LEFT JOIN my_tag_holders th
					ON th.comments_ID = comments.ID
			LEFT JOIN my_tags tags
					ON tags.ID = th.tags_ID

			-- タグ名を指定するためのJOIN
			LEFT JOIN my_tag_holders th2
					ON th2.comments_ID = comments.ID
			LEFT JOIN my_tags tags2
					ON tags2.ID = th2.tags_ID

		-- '指導'を持つタグを指定   
		WHERE tags2.tag_name LIKE '%指導%'
		
		GROUP BY comments.ID                # ➁-3
		ORDER BY comments.count_likes DESC, comments.ID DESC
		LIMIT 0, 20

➂： ➀と➁をUNIONしてSELECT

疑問なのがこちらで、➀と➁と同じように# ➂-1から# ➂-2行目の５つのカラムに対し、同じように# ➂-3行目で「comment_idだけでまとめる」よう指定しているつもりなのですが、この場合にエラーになります。

-- ➂： ➀と➁をUNIONしてSELECT
SELECT 
	 x.comment_id                          # ➂-1
	,x.lang_id
	,x.comment
	,x.count_likes
	,x.tag_ids                             # ➂-2
FROM (
	(
	
		-- ➀：「コメントの本文」をSELECT
		-- 上と同じなので割愛
		
	) UNION (
	
		-- ➁：「コメントについたタグ」をSELECT
		-- 上と同じなので割愛
	)
) AS x 

GROUP BY x.comment_id                       # ➂-3
ORDER BY x.count_likes DESC, x.comment_id DESC
LIMIT 0, 20		

エラーメッセージ

③の場合のエラーがこちらです。（改行は質問者が追加）

#1055 - Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'x.lang_id'
which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by

試したこと

まず③の場合はGROUP BYを次のようにすればエラー解消できました。

# GROUP BY x.comment_id

# ↓SELECTされるカラム名をすべてGROUP BY

GROUP BY x.comment_id, x.lang_id, x.comment, x.count_likes, x.tag_ids

さらに、SELECT @@global.sql_mode;で確認されるONLY_FULL_GROUP_BYを解除すると、GROUP BY x.comment_idのままでもエラー解消できました。

これらのことから、➀➁で平気で、③がエラーになるその違いはGROUP BYに由来するものだと考えられます。

でも私は➀➁③どれもが、『同じカラムに対して同じようにGROUP BYしている認識』なのですが、UNIONした場合は何が異なるのでしょうか？

言い換えるなら、③でSELECTするカラムすべてにGROUP BYを付けなければエラーになるのならば、➀➁だってGROUP BY comments.IDだけではエラーになるハズではないか？という疑問になります。

環境

サーバー：ConoHa WING
phpMyAdmin：5.1.3-2.el7.remi
MySQL：5.7.27-log

Answer 1

ONLY_FULL_GROUP_BY SQL モードを有効にし、MySQL GROUP BY の拡張を無効にして、標準SQLとなるようにして下さい。
12.19.3 MySQL での GROUP BY の処理

拡張に慣れるより、標準SQLに慣れる方が有用だと思いますので。

Comments

[nikuatsu]

ありがとうございます。すでに有効です。有効の状態での質問です。

[sazi]

本当にそうなら①でエラーになるはずです。
リンク先の「標準 SQL では、GROUP BY 句を含むクエリーは、GROUP BY 句で名前が指定されていない選択リスト内の非集約カラムを参照できません。」に従っていませんから。

標準SQLでなくとも
select * from ～ group by xxx
のように group by の項目指定して、select *　なんて記述しようとするのは改めましょう。

[nikuatsu]

>本当にそうなら①でエラーになるはずです。
あなたのご回答は度々目にしておりそのご見識には十分な信頼を置いているのですが、今回ばかりは質問の通りで間違いないと思います。私がONLY_FULL_GROUP_BYを知ったのは「試したこと」が初めてであり、それ以前である➀➁➂の過程において解除できるとは思えないためです。

>なんて記述しようとするのは改めましょう
これは…つまりどう記述すべきということでしょうか？何か正当な記述を省略してしまっているということでしょうか？

[nikuatsu]

>本当にそうなら①でエラーになるはずです。

例えばpaizaのSQL↓を使い、質問の CREATE、INSERT、SELECT➀ をお試しください。エラーにはなりません。
https://paiza.io/ja/projects/new

そして SELECT @@global.sql_mode; を実行すると ONLY_FULL_GROUP_BY と表示され有効だと分かります。

これらのことから（paizaが質問のバージョンと同一かは不明ですが）「有効なのにエラーにならないという状況が起こりうる」という事実はご確認いただけるかと思います。

[sazi]

> それ以前である➀➁➂の過程において解除できるとは思えないためです。
　ＳＱＬ毎にモードを指定するというような使い方はしませんよ。

> つまりどう記述すべきということでしょうか？何か正当な記述を省略してしまっているということでしょうか？

モードがどうであれ、標準SQLでの記述をした方が良いという事です。
「標準 SQL では、GROUP BY 句を含むクエリーは、GROUP BY 句で名前が指定されていない選択リスト内の非集約カラムを参照できません。」
以上に従い、GROUP BYを使用するときはSELECT では明確に項目を指定して下さい。

[sazi]

> 例えばpaizaのSQL↓を使い、質問の CREATE、INSERT、SELECT➀ をお試しください。エラーにはなりません。
> SELECT @@global.sql_mode; を実行すると ONLY_FULL_GROUP_BY と表示され有効だと分かります。
使い方がいまいち分かりませんでしたが、MYSQLを選択して実行したら、結果は表示されました。

paizaがどのような環境を提供しているのか分かりませんけど、ONLY_FULL_GROUP_BYモードに関してMYSQL公式リファレンスにある動作では無いように思います。

[sazi]

試しに以下の様にしてモードを確認しました。
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
SELECT @@SESSION.sql_mode;

モードは反映されているような動作はしていますね。

[sazi]

ただ、「CREATE、INSERT」に続けて①を追記して実行しても、エラーにはなりませんが、結果は表示されませんでした。
①の前に、
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
を実行しても結果は表示されません。
条件などを省き、標準SQLの記述に直すと結果表示はされましたが。

[nikuatsu]

>GROUP BYを使用するときはSELECT では明確に項目を指定して下さい。
ありがとうございます。質問の SELECT➂ を修正致しました。以後気を付けます。

[nikuatsu]

> SET SESSION sql_mode =
こうやってモードが解除、指定できるのですね。ありがとうございます。（質問の「試したこと」のモード解除はConoHaサーバーで解除したわけではなく、元からモード解除されているXサーバーで別途実行したものでした。）

確かにpaizaでも私のConoHaサーバーでもそれの後に続けて CREATE、INSERT、SELECT➂ を書いたら、ONLY_FULL_GROUP_BY モードが解除されエラーはなくなりました。まぁ解除しない方がいいとのことなので実用はしませんが、後学のために覚えておきます。

[nikuatsu]

>ONLY_FULL_GROUP_BYモードに関してMYSQL公式リファレンスにある動作では無い
するとご回答としては、「質問者のサーバー（とpaiza）の ONLY_FULL_GROUP_BY の設定がおかしいせいだが、どうおかしいかはわからない」ということになりますでしょうか。

これは契約先である conoHaサーバー さんに問い合わせてみます。「なぜ ONLY_FULL_GROUP_BY が有効なのに、➀がエラーにならないのか？」と。返答あり次第こちらにシェアさせて頂きます。

その他もし今後お気づきの点ございましたら、またいつでもコメント頂けましたら幸いです。

[nikuatsu]

もし余裕がございましたら下記リンクの「 GROUP_CONCAT 多すぎ問題 」にも知見をご教示賜われれば、幸甚の至に存じまする。（CREATE、INSERT は今回の質問と同様です。）
https://teratail.com/questions/aml7ouvbga2v94

[sazi]

>「なぜ ONLY_FULL_GROUP_BY が有効なのに、➀がエラーにならないのか？」
それによって、対応が変わる訳では無い（結局標準SQLの記述になるだけ）ですし、拡張モードでエラーにならない方法を探るのが本筋では無いのですから、モードに依らず標準SQLでの記述を心掛けるという事で。

そもそもMYSQLの拡張は、理解している人にとって手抜きができるだけであって、初心者や理解できていない人にとって、エラーにならない事によって意図しないものになったりしますから、質が悪いとしか思えません。

ニーズがあるならデフォルトをONLY_FULL_GROUP_BYに変更したりしないでしょうし。

[nikuatsu]

>モードがどうであれ、標準SQLでの記述をした方が良い
>モードに依らず標準SQLでの記述を心掛ける
とりあえずこれに尽きますね。基本的で大事な心がけをありがとうございます。