読者です 読者をやめる 読者になる 読者になる

MySQLでgroup byなしでcountとorder byを同時に使うとエラー

MySQLとLaravelの話です。
MySQLSQL_MODEONLY_FULL_GROUP_BYが指定されていると、group by無しでcountとorder byを同時に使うと以下のエラーが発生します。

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

MySQLのバージョン

Server version: 5.6.36 MySQL Community Server (GPL)
  • 5.6.36以前は確認していません
  • 5.7.17では発生しませんでした

再現

-- サンプルのテーブル
mysql> desc foo;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)


-- sql_modeを一旦未指定にする
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

-- count と order byを同時に使っても問題なし
mysql> select count(*) from foo order by id;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)


-- sql_modeにonly_full_group_byを指定
mysql> set session sql_mode='only_full_group_by';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)


-- count と order byを同時に使うとエラー
mysql> select count(*) from foo order by id;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

通常はそんなSQL書かないでしょうが、今回Laravelを使っていてちょっとハマりました。

クエリオブジェクトを使いまわしたかった

以下のような感じで検索条件を構築したクエリオブジェクトに対して、countと実際の検索を別々に発行したいケースです。

<?php
public function something()
{
    // \DB::table('foo')->where()...的な感じでクエリオブジェクトを作成
    $query = $this->createQuery();

    // 総件数を取得
    $count = $query->count();

    // 実際のレコードを取得
    $records = $query->get();
}

実際のレコードを取得するために$queryにはすでにorder_byの指定が入っています。
そのため上記のエラーが発生してしまいました。

ハマったのはログに出力されたクエリを直接MySQLに実行しても通るのに、PHP経由だとエラーになったためです。
(MySQL側ではsql_modeにonly_full_group_byを付けていなかったのでエラーにならない)

config/database.phpのstrict => true

config/database.phpにはstrictという項目があり、Laravel5.3以降ではtrueがデフォルト値のようです。

<?php

    'mysql' => [
        // (略)
        'strict' => true,
        // (略)
    ],

これがtrueだと、MySQL接続時に以下のSQLが走ってsql_modeが設定されます。 https://github.com/laravel/framework/blob/5.4/src/Illuminate/Database/Connectors/MySqlConnector.php#L178

set 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'

ということで、MySQL自体のsql_modeは上書きされます。

対応策

同じようなIssueではstrict => falseにすることが提案されています。
5.3 Query Builder count() Sql error - PR proposal

それが嫌な場合はcountを実行してからorder byを指定するとかが回避策になると思います。