現象

データベースのバージョンアップ後、アプリケーションから次のようなエラーが頻発しました。

Expression #1 of ORDER BY clause is not in SELECT list, references column 'xxxx' which is not in SELECT list; this is incompatible with DISTINCT

原因

MySQL 5.7.5以降、sql_modeのONLY_FULL_GROUP_BYモードがデフォルトで有効になっていることが原因です。このモードにより、いくつかのクエリでエラーが発生します。

• GROUP BYを使用するクエリでは、SELECT句に指定できるのはGROUP BYで指定したグループ化フィールド、または集約関数で囲まれたフィールドのみです。それ以外のフィールドを指定すると、次のようなエラーが発生します。

  Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.table.column' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

• ORDER BYを使用するクエリで、SELECT DISTINCTと併用すると、上記のエラーが発生します。

確認方法

現在のMySQLのバージョンとsql_modeを確認します。

SELECT version(); -- バージョン確認

SELECT @@global.sql_mode; -- グローバルsql_mode確認

出力例:

+-----------+
| version() |
+-----------+
| 5.7.24    |
+-----------+
1 row in set (0.00 sec)

+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

対策

一時的な修正

ONLY_FULL_GROUP_BYを無効にします。

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

ただし、この方法はMySQLサービスを再起動すると設定が失効します。テスト中に問題が解決しない場合、セッションレベルのsql_modeも確認する必要があります。

SELECT @@SESSION.sql_mode;

出力例:

+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

セッションレベルのsql_modeも変更します。

SET @@SESSION.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

永続的な修正

MySQLの設定ファイルを編集して、ONLY_FULL_GROUP_BY SQLモードを無効にします。

sudo vim /etc/mysql/my.cnf

ファイルの末尾に以下を追加します。

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

保存してMySQLを再起動します。

sudo service mysql restart

なぜこのような設計なのか

この仕様にはいくつかの理由があります。

• DISTINCTとORDER BYはどちらもデータのソート処理を行うため、併用すると競合が発生します。MySQL 5.7では、この構文がより厳格になり、エラー3065が発生することがあります。

• GROUP BY句で指定されたフィールドのみがSELECT句に含まれることを強制することで、クエリの意図を明確にし、予期しない結果を防ぐためです。これは、機能的依存性の検出を含む、より複雑なGROUP BY処理を可能にします。

• 既存のプロジェクトでこのエラーを修正するには、ANY_VALUE()関数を使用して、GROUP BY句にないフィールドをSELECT句に含める方法があります。しかし、既存のすべてのSQLを修正する必要があるため、大規模プロジェクトでは実用的ではありません。

具体的なクエリ例

問題のあるクエリ:

-- employeesテーブルから部署IDを重複排除して取得し、入社日でソートします。
-- このクエリはエラーを引き起こします。
SELECT DISTINCT department_id FROM employees ORDER BY hire_date;

修正案1: DISTINCTを削除する

-- 重複が許容される場合、DISTINCTを削除します。
SELECT department_id FROM employees ORDER BY hire_date;

修正案2: ORDER BYの列をSELECTリストに追加する

-- ORDER BYで使用する列をSELECTリストに含めます。
SELECT DISTINCT department_id, hire_date FROM employees ORDER BY hire_date;