今日のバグ

↓みたいなテーブルがあって、

create table A ( 
  id BIGINT NOT NULL AUTO_INCREMENT, 
  name VARCHAR(20) NOT NULL DEFAULT '', 
  PRIMARY KEY(id)
);

create table B ( 
  id BIGINT NOT NULL AUTO_INCREMENT, 
  a_id BIGINT NOT NULL,
  name VARCHAR(20) NOT NULL DEFAULT '',
  FOREIGN KEY (a_id) REFERENCES A(id) ON DELETE CASCADE,
  PRIMARY KEY(id)
);

Aテーブルのデータ一覧を取得する機能を作った訳です。この一覧APIでは絞り込み条件が指定できて、

• Aの属性での絞り込み
• Bの属性での絞り込み(指定された属性を持つBと関連づけられたAのみを返す)
• および、条件のAND,ORでの結合

をサポートする。

ここで、Bの属性での検索を以下のように行なっていたのだけど、これではダメなことに今日気付いた・・・。orz

select distinct -- 重複行をはじく
  A.id,         -- 必要なAの属性を列挙
  A.name 
from A join B on B.a_id = A.id -- AテーブルとBテーブルをjoin
where B.name = 'aa';           -- 検索条件を並べる

テーブルに↓のようなデータが入っているとすると

mysql> select * from A;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from B;
+----+------+------+
| id | a_id | name |
+----+------+------+
|  1 |    1 | aa   |
|  2 |    2 | aa   |
|  3 |    2 | bb   |
+----+------+------+
3 rows in set (0.00 sec)

一見うまいこと動いているように見えるけど

mysql> select distinct A.id, A.name from A join B on B.a_id = A.id where B.name = 'aa'; 
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
+----+------+
2 rows in set (0.01 sec)

mysql> select distinct A.id, A.name from A join B on B.a_id = A.id where B.name = 'bb'; 
+----+------+
| id | name |
+----+------+
|  2 | bbb  |
+----+------+
1 row in set (0.00 sec)

検索条件にAの属性を追加すると期待と違う結果になる。

mysql> select distinct A.id, A.name from A join B on B.a_id = A.id where A.name = 'ccc' OR B.name = 'bb'; 
+----+------+
| id | name |
+----+------+
|  2 | bbb  |
+----+------+
1 row in set (0.00 sec)

# あれ? cccも含まれるハズなんだが!

というのも、AとBを内部結合しているのでBから参照されていないAの行がJOINした結果に含まれてないわけですよ。試しにdistinctと検索条件を外して内部結合だけ行なった結果は以下の通り。ここから、絞り込みと重複行の排除をするので当然'ccc'の行は返されない。

mysql> select * from A join B on B.a_id = A.id; 
+----+------+----+------+------+
| id | name | id | a_id | name |
+----+------+----+------+------+
|  1 | aaa  |  1 |    1 | aa   |
|  2 | bbb  |  2 |    2 | aa   |
|  2 | bbb  |  3 |    2 | bb   |
+----+------+----+------+------+
3 rows in set (0.00 sec)

まぁ、ANDやORで結合しなくてもAの属性で検索すればわかるはずなんですが、その場合そもそもJOINをしない実装にしていたので気付かなかったのです。むー。とりあえず、AとBの属性を混ぜての検索は今回は使わない(=急いで直さなくてもいい)ので、開発内試験期間にでもちょろっと直そうかな。

対策案

# その1
mysql> select distinct A.id, A.name from A left join B on B.a_id = A.id where A.name = 'ccc' or  B.name = 'bb'; 
+----+------+
| id | name |
+----+------+
|  2 | bbb  |
|  3 | ccc  |
+----+------+
2 rows in set (0.00 sec)

# その2 
mysql> select A.id, A.name from A where A.name = 'ccc' or  exists ( select id from B where B.a_id = A.id and B.name = 'bb'); 
+----+------+
| id | name |
+----+------+
|  2 | bbb  |
|  3 | ccc  |
+----+------+
2 rows in set (0.00 sec)

一般的にはどうするんだろう。