Help us understand the problem. What is going on with this article?

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

以下のような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)
suzuki_sh
Windowsでコンピュータの世界が広がります
https://www.s2terminal.com
finergy-a-tm
大阪府大阪市北区角田町8番1号 梅田阪急ビル オフィスタワー35F
https://finergy.a-tm.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした