エンジニアのsassaです。

MySQLからPostgreSQLに移行する際、またはMySQL 5.7 以降へのバージョンアップする際にONLY_FULL_GROUP_BYの設定によって、クエリを修正しなければならない場合があります。

この記事では、ONLY_FULL_GROUP_BY を満たしていないクエリを自動で洗い出す方法を紹介します。スムーズにMySQLからPostgreSQLへの移行を進めるために、ぜひ参考にしてください。

この記事の対象

• MySQL 5.7より前のバージョンを使用している方
• MySQLのSQLモードの ONLY_FULL_GROUP_BY を有効にしていない方
• ONLY_FULL_GROUP_BY に反しているクエリを洗い出したい方
• Railsで自動テストを構築している方

ONLY_FULL_GROUP_BY の説明

ONLY_FULL_GROUP_BYは、SQLモードの一つで、SQLの GROUP BY 句に関する制約を強化する設定です。この設定が有効になると、SELECT 句で使用するすべてのカラムは GROUP BY 句で指定される必要があります。もしくは、集約関数 (例: COUNT, MAX, MIN) を使用する必要があります。これにより、GROUP BY 句を使用したクエリで予期せぬ結果が出るのを防ぐことができます。

例えば、GROUP BY に含まれていないカラムを SELECT 句で参照するクエリが、ONLY_FULL_GROUP_BY が無効な環境では動作しますが、有効な環境ではエラーが発生します。これは、集約されていないカラムが曖昧なまま参照されるのを防ぐためです。

ONLY_FULL_GROUP_BYが無効な場合

以下のクエリでは、name カラムが GROUP BY に含まれていませんが、エラーは発生せず、正常に実行されます。

SELECT id, name, COUNT(*) 
FROM users 
GROUP BY id;

ONLY_FULL_GROUP_BYが有効な場合

上記のクエリを実行すると、name カラムが GROUP BY に含まれていないため、以下のようなエラーが発生します。

ERROR:  column "users.name" must appear in the GROUP BY clause or be used in an aggregate function

検証環境

• MariaDB: 10.4.17 (MySQL 5.7相当)
  • MariaDBでは、すべてのバージョンでONLY_FULL_GROUP_BYがデフォルトで無効
  • 参考：https://mariadb.com/kb/en/sql-mode/
• Rails: 7.0.8

ONLY_FULL_GROUP_BYに反するクエリの洗い出し手順

今回紹介する方法は、Railsの自動テストを使用して、ONLY_FULL_GROUP_BY を満たしていないクエリを洗い出す手順です。ActiveRecordを介したDB接続時に、セッション単位で ONLY_FULL_GROUP_BY を有効にします。

以下の3つの手順で進めます。

• デフォルトのSQLモードの確認
• database.yml の test 環境に ONLY_FULL_GROUP_BY を追加
• 自動テストを実行

これにより、テスト環境のみでONLY_FULL_GROUP_BYを有効にすることで、 MySQLの設定を変更せず、効率的にクエリを洗い出せます。

デフォルトのSQLモードの確認

まず、現在のSQLモードは以下のコマンドで確認します。

SELECT @@sql_mode;

バージョンやRDBMSによりデフォルトのSQLモードが異なります。MySQL 5.6以前やMariaDBでは、デフォルトで ONLY_FULL_GROUP_BY が無効ですが、MySQL 5.7以降やPostgreSQLでは、デフォルトで有効です。

例: MariaDB 10.4.17のデフォルトのSQLモード

+-------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+

database.yml に ONLY_FULL_GROUP_BY を追加

database.yml ファイルで sql_mode を設定できます。今回はテスト環境でのみ ONLY_FULL_GROUP_BY を有効にするため、test 環境の variables にSQLモードを設定します。この際、sql_mode の設定は上書きされるため、デフォルトのSQLモードも含めて設定する必要があります。

test:
  <<: *default
  host: 127.0.0.1
  database: your_database
  username: your_username
  password: your_password
  variables:
    sql_mode: 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

自動テストの実行

設定が完了したら、自動テストを実行します。ONLY_FULL_GROUP_BY のルールに違反するクエリを含むテストが失敗するため、そのクエリを修正できます。

ONLY_FULL_GROUP_BYが有効でも動作するクエリに変換する方法

ONLY_FULL_GROUP_BY に反するクエリを見つけた後は、以下のいずれかの方法で修正します。

• SELECT 句で使用するカラムを GROUP BY に追加する
• 集約関数（MAX, MIN など）を使用する
• ANY_VALUE 関数を使用する

SELECT句で使用しているカラムをGROUP BYに追加

GROUP BY に該当のカラムを追加する方法です。

SELECT id, name, COUNT(*) 
FROM users 
GROUP BY id, name;

ただし、SELECT 句が長いと GROUP BY も冗長になりやすいので注意が必要です。

集約関数を使う

MAX や MIN などの集約関数を使用して GROUP BY でカラムを指定しない方法です。

SELECT id, MAX(name), COUNT(*) 
FROM users 
GROUP BY id;

これにより GROUP BY の記述が簡潔になりますが、元々の集約関数としての用途ではないため、可読性が低下する可能性があります。

ANY_VALUE関数を使う

ANY_VALUE 関数を使うことで、GROUP BY にカラムを指定しなくてもエラーが発生しないようにできます。ANY_VALUE 関数は集約関数ではないですが、カラム単位でONLY_FULL_GROUP_BYを無効にすることができる関数です。

SELECT id, ANY_VALUE(name), COUNT(*) 
FROM users 
GROUP BY id;

この方法は、クエリの意図がわかりやすく、GROUP BY の冗長さを回避できるため、推奨されます。ただし、MySQL 5.7以降およびPostgreSQL 16.0以降でしか使用できないため、注意が必要です。

まとめ

ONLY_FULL_GROUP_BY の設定を有効にした際、Railsの自動テストを使って効率的にクエリを洗い出す方法を紹介しました。PostgreSQLへの移行やMySQLのバージョンアップの前に、この手法でクエリを事前にチェックして、移行作業をスムーズに進めてください。