MySQLでGROUP BYしたカラムの値が変わる #MySQL

以下のようなmembersというテーブルがあります。

SELECT * FROM members;

+----+--------+------+-------+
| id | name   | age  | adult |
+----+--------+------+-------+
|  1 | ichiro |   11 |  NULL |
|  2 | jiro   |   22 |     1 |
|  3 | saburo |   33 |     1 |
+----+--------+------+-------+
3 rows in set (0.00 sec)

年齢と、成人(age>=20)なら1になるカラムがあるだけの、とても単純なテーブルのようです。

この中に成人が何人いるかGROUP BYで集計してみましょう。

SELECT adult, COUNT(*) FROM members GROUP BY adult;

+-------+----------+
| adult | COUNT(*) |
+-------+----------+
|  NULL |      129 |
|     0 |        2 |
+-------+----------+
2 rows in set (0.00 sec)

...のように、

NULLは1人なのに、129人もいる事になっている
1が2人のはずなのに、0が2人という事になっている

という意味不明な結果が得られてしまいました。

詳細

どうやら下記の条件を満たすと再現するようです。

MySQL5.6
- MySQL5.7では発生せず
照合順序が明示されたDBをまたいでViewを貼る
数値とCAST(NULL AS UNSIGNED)を混同したカラムをGROUP BYで集計する
- 文字列との混同や、単なるNULLでは起きない
- GROUP BYを使わなくても起きない

再現手順

dockerを使います。
下記のようなdocker-compose.ymlを作成します。

docker-compose.yml

version: "3"
services:
  db:
    image: mysql:5.6
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: 1

ビルドして中に入ります。

$ docker-compose up -d
$ docker-compose run --rm db mysql -u root -h db

MySQLに入ったら、まず参照元のデータベースを作成します。

CREATE DATABASE my_source_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE my_source_db;

CREATE TABLE source_members (
  id   INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20),
  age INT,
  INDEX(id)
);

INSERT into source_members (name, age) VALUES ('ichiro', 11);
INSERT into source_members (name, age) VALUES ('jiro', 22);
INSERT into source_members (name, age) VALUES ('saburo', 33);

次に、問題のViewを作成します。

CREATE DATABASE my_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE my_db;

CREATE OR REPLACE VIEW members AS (
  SELECT
    id,
    name,
    age,
    CASE WHEN age >= 20 THEN 1 ELSE CAST(NULL AS UNSIGNED) END AS 'adult'
  FROM my_source_db.source_members
);

この状態で、冒頭のSELECT adult, COUNT(*) FROM members GROUP BY adultを実行すると発生します。

対策案

原因は不明なのですが、CAST(NULL AS UNSIGNED)を単なるNULL等に変更すること等で解消するようです。

CREATE OR REPLACE VIEW members AS (
  SELECT
    id,
    name,
    age,
    CASE WHEN age >= 20 THEN 1 ELSE NULL END AS 'adult'
  FROM my_source_db.source_members
);

SELECT adult, COUNT(*) FROM members GROUP BY adult;

+-------+----------+
| adult | COUNT(*) |
+-------+----------+
|  NULL |        1 |
|     1 |        2 |
+-------+----------+
2 rows in set (0.00 sec)