MySQL
エラー1055：ONLY_FULL_GROUP_BY：何かがGROUP BY句にない...

前書き

備考

SET  sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

GROUP BYの使用と誤用

 SELECT item.item_id, item.name,     /* not SQL-92 */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

itemと呼ばれるテーブルに行が表示され、 usesというテーブルに関連する行の数が表示されuses 。これは正常に動作しますが、残念ながら標準のSQL-92ではありません。

何故なの？ GROUP BY問合せのSELECT句（およびORDER BY句）には、次のような列が含まれている必要があるためです。

1. GROUP BY句に記載されている
2. COUNT() 、 MIN()などの集合関数。

この例のSELECT句には、いずれかの条件を満たす列ではないitem.nameています。 MySQL 5.6以前では、SQLモードにONLY_FULL_GROUP_BYが含まれている場合、このクエリは拒否されONLY_FULL_GROUP_BY 。

この例のクエリは、このようにGROUP BY句を変更することによってSQL-92標準に準拠するようにすることができます。

SELECT item.item_id, item.name, 
       COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id, item.name

後のSQL-99標準では、DBMSがグループキー列との間の機能的依存関係を証明できる場合、 SELECT文で集約されていない列をグループキーから省略することができます。のでitem.name上の機能的に依存しているitem.item_id 、最初の例では、有効なSQL-99です。 MySQLはバージョン5.7で機能依存プローバを取得しました。元の例は、 ONLY_FULL_GROUP_BY下でONLY_FULL_GROUP_BYます。

GROUP BYを誤って予測できない結果を返す：Murphy's Law

SELECT item.item_id, uses.category,   /* nonstandard */ 
       COUNT(*) number_of_uses 
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

itemと呼ばれるテーブルに行が表示され、usesというテーブルに関連する行の数が表示されます。また、 uses.categoryという列の値も表示されuses.category 。

このクエリは、 ONLY_FULL_GROUP_BYフラグが現れる前にMySQLで動作します。これは、 MySQLの非標準的な拡張機能をGROUP BYに使用します 。

しかし、問合せには問題があります。 uses表の複数の行がJOIN句のON条件と一致すると、MySQLはその列の1つからcategory列を戻します。どの列？クエリの作成者とアプリケーションのユーザーは、事前にそのことを知ることはできません。形式的に言えば、 予測できないことです.MySQLは必要な値を返すことができます。

予測不能とは、 ランダムなものと同様ですが、大きな違いが1つあります。 ランダムな選択が時々変わると予想されるかもしれません。したがって、選択肢がランダムであった場合、デバッグまたはテスト中にその選択肢を検出する可能性があります。 予期しない結果がより悪化します。MySQLはクエリを使用するたびに同じ結果を返します。時には、それは別の結果を引き起こすMySQLサーバの新しいバージョンです。ときには問題を引き起こすテーブルが増えています。間違って行くことができます、間違って行くと、あなたがそれを期待していないとき。それはマーフィーの法則です。

MySQLチームは、開発者がこの間違いを犯すのをより困難にするよう努めてきました。 5.7シーケンスの新しいバージョンのMySQLには、 sql_modeというsql_modeフラグがONLY_FULL_GROUP_BYます。このフラグが設定されると、MySQLサーバは1055エラーを返し、この種のクエリの実行を拒否します。

SELECT *を使用してGROUP BYを誤解し、それを修正する方法。

時には、 SELECT句に*を付けて照会すると、このようになります。

 SELECT item.*,     /* nonstandard */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

このようなクエリは、 ONLY_FULL_GROUP_BY標準に準拠するようにリファクタリングする必要があります。

これを行うには、 item_id number_of_uses値を正しく返すためにGROUP BY正しく使用するサブクエリが必要item_id 。このサブクエリは、 usesテーブルを調べるだけで済み、短くて甘いです。

                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id

次に、そのサブクエリをitemテーブルと結合することができます。

 SELECT item.*, usecount.number_of_uses
   FROM item
   JOIN (
                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id
        ) usecount ON item.item_id = usecount.item_id

これにより、 GROUP BY句を単純で正確にすることができます。また、 *指定子を使用することもできます。

注意：それにもかかわらず、賢明な開発者はいずれの場合でも*指定子の使用を避けます。クエリに必要な列を一覧表示する方が一般的です。

ANY_VALUE（）

 SELECT item.item_id, ANY_VALUE(uses.tag) tag,   
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

itemというテーブルの行、関連する行の数、およびusesという関連テーブルの値の1つを表示します。

このANY_VALUE()関数は、奇妙な一種の集合関数と考えることができます。 count、sum、またはmaximumを返す代わりに、MySQLサーバに、問題のグループから1つの値を任意に選択するように指示します。エラー1055を回避する方法です。

プロダクションアプリケーションのクエリでANY_VALUE()を使用する場合は注意が必要です。

それは本当にSURPRISE_ME()と呼ばれるべきです。 GROUP BYグループの一部の行の値を返します。それが返す行は不確定です。つまり、それはMySQLサーバーまでです。正式には、予測不可能な値を返します。

サーバーはランダムな値を選択しません。それよりも悪いです。クエリが実行されるたびに同じ値が返されます。テーブルが大きくなったり小さくなったり、サーバのRAMが多かったり、サーバのバージョンが変わったり、逆に（逆に）何かがあったとしても、まったく理由なしに変更することができます。

あなたは警告されています。