技術めも

MySQLのUNION

magentadesk 0.9.0 のリリースでも触れましたが、

MySQLのUNIONに非常に苦しめられました。

具体的には、完全外部結合をする過程の中で、使っているUNIONの箇所で、

ERROR 1030 (HY000): Got error 124 - 'Wrong index given to function' from storage engine

というエラーが発生します。

この原因がどうもMySQL 8.0 から導入されたTempTableエンジンという一時テーブル用のエンジンの不具合のようです。。

そもそも完全外部結合で何をしたいかというと、以下のようなイメージとなります。

要は、同じ構成の2テーブルのレコード差を出す形です。

以下のテーブルとデータを用意してます。

create table base (
  id int not null primary key,
  name text not null
);
insert into base values (1, 'A'),(2, 'B'),(3, 'C');

create table compare (
  id int not null primary key,
  name text not null
);
insert into compare values (1, 'A'),(2, 'BB'),(4, 'D');

外部結合がサポートされているPostgreSQLだと簡単にできます。

(PostgreSQL 18.1)

select
  base.id as base_id, 
  base.name as base_name, 
  compare.id as compare_id, 
  compare.name as compare_name 
from
  base full outer join compare on base.id = compare.id
where
  base.id is null or compare.id is null
  or
  base.id != compare.id or base.name != compare.name
;

 base_id | base_name | compare_id | compare_name 
---------+-----------+------------+-------------
       2 | B         |          2 | BB
       3 | C         |       NULL | NULL
    NULL | NULL      |          4 | D

magentadesk での以前の実装は以下のとおりでした。

これはMariaDBでは問題ありませんでした。当然PostgreSQLでも問題なかったです。

ただし、MySQL 8.0系だと、正しくない結果でした。（結果的にはwhere句を追加して対応）

with
only_base as (
  select * from base
  except
  select * from compare
),
only_compare as (
  select * from compare
  except
  select * from base
),
merge_left as (
  select 
    only_base.id as base_id, 
    only_base.name as base_name, 
    only_compare.id as compare_id, 
    only_compare.name as compare_name
  from only_base left outer join only_compare on only_base.id = only_compare.id
),
merge_right as (
  select 
    only_base.id as base_id, 
    only_base.name as base_name, 
    only_compare.id as compare_id, 
    only_compare.name as compare_name 
  from only_base right outer join only_compare on only_base.id = only_compare.id
),
merge_full as (
  select * from merge_left
  union
  select * from merge_right
)
select
  *
from
  merge_full
;

(MariaDB 12.1)
+---------+-----------+------------+-------------+
| base_id | base_name | compare_id | compare_name |
+---------+-----------+------------+-------------+
|       2 | B         |          2 | BB          |
|       3 | C         |       NULL | NULL        |
|    NULL | NULL      |          4 | D           |
+---------+-----------+------------+-------------+

(PostgreSQL 18.1)
 base_id | base_name | compare_id | compare_name 
---------+-----------+------------+-------------
       3 | C         |       NULL | NULL
       2 | B         |          2 | BB
    NULL | NULL      |          4 | D

(MySQL 8.0.44)
+---------+-----------+------------+-------------+
| base_id | base_name | compare_id | compare_name |
+---------+-----------+------------+-------------+
|       2 | B         |          2 | BB          |
|       3 | C         |       NULL | NULL        |
|       1 | A         |          1 | A           |
|    NULL | NULL      |          4 | D           |
+---------+-----------+------------+-------------+

このクエリーはMySQL 8.4 だと動きません。

そのため、次の形に変更しました。

with
only_base as (
  select id, MD5(IFNULL(name, '')) as row_hash from base
  except
  select id, MD5(IFNULL(name, '')) as row_hash from compare
),
only_compare as (
  select id, MD5(IFNULL(name, '')) as row_hash from compare
  except
  select id, MD5(IFNULL(name, '')) as row_hash from base
),
diff_keys as (
  select id from only_base
  union
  select id from only_compare
),
merge_full as (
  select
    base.id as base_id, 
    base.name as base_name, 
    compare.id as compare_id, 
    compare.name as compare_name 
  from
    diff_keys
    left outer join base on diff_keys.id = base.id
    left outer join compare on diff_keys.id = compare.id
)
select
  *
from
  merge_full
;

ポイントは、

EXCEPTの比較時はハッシュ化しておく
UNIONでは主キーのみとする
最後の外部結合で情報を取得する

といったところで、ハッシュ化する負荷はあるもののトータルで見れば軽くなった形となります。

これで、MariaDBも、MySQL 8.0および8.4 でも同じ結果を得ることができました。

さすがにわからかなったので、Geminiにも聞きながらやりました。

とはいえ、結構Geminiも間違えていたので、なかなか厄介な問題なのかなと思っています。

magentadesk での以前の実装のクエリーの問題点は、

merge_full as (
  select * from merge_left
  union
  select * from merge_right
)

の部分なのですが、文字列カラム（char/varchar/text）があると、エラーを引き起こすようです。

バイナリは試していないのですが、同じかもしれません。

もちろん、通常のテーブルのUNIONは問題ないのですが、仮想表でのUNIONには気をつけたほうがいいのかな、という所感です。

UNIONだけなの問題なのか、EXCEPTやINTERSECTまで及ぶのかはわからないのですが、

TempTableにはまだ不具合ありそうなので、テンポラリーに落ちるとき（Using temporary）はご注意ください。

以上

投稿日時：2026年01月30日 17:12 カテゴリー：mariadb, mysql, postgresql

MySQLのUNION

コメントがあればどうぞ

カテゴリー

アーカイブ