【MYSQL】SELECT時のDuplicate entryエラー

12593 ワード
MySQL MySQL テキストリンク

※現象としては再現可能で回避方法もわかっていますが、なぜエラーになるのかは理解できていません。
MySQL5.6.4では発生しなかったため、MySQL5.7か8起因（5.7では未検証）かと考えられます。

実行環境

  • Amazon RDS for MySQL(バージョン8.0.21)
  • インスタンスタイプ:db.r6g.large
  • ストレージ：汎用100GB
  • クライアント：Sequel Nightly Build(5446)

データ準備

例えば次のようなテーブルを準備します。

CREATE TABLE `orders` (
  `user_id` varchar(10) DEFAULT NULL,
  `order_id` int DEFAULT NULL,
  `date` date DEFAULT NULL,
  `money` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `orders` (`user_id`, `order_id`, `date`, `money`)
VALUES
    ('AAA',1111,'2021-03-01',1000),
    ('AAA',2222,'2021-03-02',2000),
    ('AAA',3333,'2021-03-03',3000),
    ('BBB',4444,'2021-03-01',2000),
    ('BBB',5555,'2021-03-02',4000),
    ('BBB',6666,'2021-03-03',8000);

CREATE TABLE `order_detail` (
  `order_id` int DEFAULT NULL,
  `unit_price` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `order_detail` (`order_id`, `unit_price`)
VALUES
    (1111,500),
    (2222,1000),
    (3333,3000),
    (4444,2000),
    (5555,2000),
    (6666,8000),
    (1111,500),
    (2222,1000),
    (5555,2000);

CREATE VIEW order_detail_view as select 
 order_id,
 unit_price,
 "不明" as item_category
FROM order_detail
;

エラーになるときの書き方

目的としては、似たようなデータ（例えばECの売上など）で、
こちらのデータにはAという項目があるけどこちらには無い、
というようなデータ間での差を構造として共通化することで、同じSQLで様々なデータの分析をできるようにするためです。
構造をあわせるにあたり、VIEWを使用すればテーブルをいじらなくて済むので良いかなと考え、
存在しない列をVIEW内でダミーとして作成しています。
ただ、次に示す通り、VIEWかどうかは関係が無いようです。

エラー

SELECT 
 item_category,COUNT(*)
FROM 
 orders a
LEFT JOIN 
 order_detail_view b 
ON 
 a.order_id = b.order_id
GROUP BY 
 b.item_category;


#このようにVIEWにしなくてもエラーになる
SELECT 
 item_category,COUNT(*)
FROM 
 orders a
LEFT JOIN 
 (SELECT 
   *,"不明" AS item_category #ここが問題
  FROM 
   order_detail) b 
ON 
 a.order_id = b.order_id
GROUP BY 
 b.item_category;

>Duplicate entry '' for key '/rdsdbdata/tmp/*****.<group_key>'
#*****の部分にはなにかIDのようなものが入っていますが、何が載ってるか不明なのでマスクしています。

このような書き方をするとエラーが発生します。
ダミーとして作った列をGROUP BYで指定し、かつカウントを取ろうとしていました。
これがダミーでなければitem_categoryごとの件数を取得するものになります。

回避方法

1.文字列ではなくNULLや空文字にする

"不明"のような文字列ではなく""やNULLを指定すると集計できます。

2.ダミー列を先に内部でJOINする

このように一度括ってしまえばエラーになりません。

SELECT 
 item_category,COUNT(*)
FROM 
 orders a
LEFT JOIN 
 (SELECT 
   * 
  FROM 
   order_detail
  JOIN 
   (SELECT "不明" AS item_category)q1 #この部分
  ) b 
ON 
 a.order_id = b.order_id
GROUP BY 
 b.item_category;

3.LEFT JOINじゃなくINNER JOINにする

1のLEFT JOINをINNER JOINにすると動きます。

実行計画

実際に動かすとエラーにはなりますが、実行計画は見ることができます。
ここで見る限りおかしな点が見当たりません。

EXPLAIN
SELECT 
 item_category,COUNT(*)
FROM 
 orders a
LEFT JOIN 
 (SELECT 
   *,"不明" AS item_category
  FROM 
   order_detail
  ) b 
ON 
 a.order_id = b.order_id
GROUP BY 
 b.item_category;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE a NULL ALL NULL NULL NULL NULL 6 100.00 Using temporary
1 SIMPLE order_detail NULL ref order_id order_id 5 テスト用.a.order_id 1 100.00 Using index
速学scala(1)——関数式プログラミング
Spark RDDの要素順序(ordering)テスト