今日のバグ
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)
一般的にはどうするんだろう。