質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
87.20%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

phpMyAdmin

phpMyAdminはオープンソースで、PHPで書かれたウェブベースのMySQL管理ツールのことです。

受付中

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

nikuatsu
nikuatsu

総合スコア154

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

phpMyAdmin

phpMyAdminはオープンソースで、PHPで書かれたウェブベースのMySQL管理ツールのことです。

2回答

1評価

0クリップ

294閲覧

投稿2022/04/28 09:33

編集2022/05/02 17:17

前提

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

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

知りたいこと

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

CREATE、INSERT

対象レコードです。

SQL

-- コメント 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行目の5つのカラムに対し、# ➀-3行目で「IDだけでまとめる」よう指定していますが、エラーは起こりません。

SQL

-- ➀:「コメントの本文」を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行目の5つのカラムに対し、# ➁-3行目で「IDだけでまとめる」よう指定していますが、エラーは起こりません。

SQL

-- ➁:「コメントについたタグ」を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行目の5つのカラムに対し、同じように# ➂-3行目で「comment_idだけでまとめる」よう指定しているつもりなのですが、この場合にエラーになります。

SQL

-- ➂: ➀と➁を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

エラーメッセージ

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

SQL

#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を次のようにすればエラー解消できました。

SQL

# 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

良い質問の評価を上げる

以下のような質問は評価を上げましょう

  • 質問内容が明確
  • 自分も答えを知りたい
  • 質問者以外のユーザにも役立つ

評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

気になる質問をクリップする

クリップした質問は、後からいつでもマイページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

  • プログラミングに関係のない質問
  • やってほしいことだけを記載した丸投げの質問
  • 問題・課題が含まれていない質問
  • 意図的に内容が抹消された質問
  • 過去に投稿した質問と同じ内容の質問
  • 広告と受け取られるような投稿

評価を下げると、トップページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

まだ回答がついていません

会員登録して回答してみよう

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
87.20%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

同じタグがついた質問を見る

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

phpMyAdmin

phpMyAdminはオープンソースで、PHPで書かれたウェブベースのMySQL管理ツールのことです。