DBMS は MariaDB 5.5.52。
最近、不思議な挙動に遭遇しました。
「データベースに登録されている企業の一覧を表示し、その企業の大分類を表示する」という機能の実装を行いました。
この企業データは以下のようなものです。
company テーブル
| id | name | f1 | f2 | f3 | f4 | f5 |
|---|---|---|---|---|---|---|
| 1 | 株式会社A | 1 | null | null | null | null |
| 2 | 株式会社B | null | 1 | null | null | null |
| 3 | 株式会社C | 1 | 1 | null | 1 | null |
分類には大分類と中分類があり、company テーブルではその企業がどの中分類が該当するのかというフラグだけをその中分類ごとにカラムを分けて f1～f5 で持ち、設定されている場合は 1、未設定の場合は null を設定するという構造になっていました。
大分類と中分類のマスタは別テーブルにあります。その大分類/中分類の名称は、そのマスタでコードから名称へ翻訳する必要がありました。
code テーブル
| code | name |
|---|---|
| fa | 大分類A |
| fb | 大分類B |
| f1 | 中分類A1 |
| f2 | 中分類A2 |
| f3 | 中分類B1 |
| f4 | 中分類B2 |
| f5 | 中分類B3 |
ここで、中分類 f1～3 は大分類A、f4 と f5 は大分類B です。
ですので、画面上の表示としては、以下を表示したいということになります。
株式会社A → 大分類A
株式会社B → 大分類A
株式会社C → 大分類Aと大分類B
そこで、以下のようなSQL文を考えました。
SELECT co.*
,CONCAT_WS(', '
,(CASE WHEN COALESCE(co.f1, co.f2) IS NOT NULL THEN (SELECT name FROM code WHERE code='fa') ELSE NULL END)
,(CASE WHEN COALESCE(co.f3, co.f4, co.f5) IS NOT NULL THEN (SELECT name FROM code WHERE code='fb') ELSE NULL END)
)
FROM company co
このSQL文では、以下を意図していました。
1.「カラム f1 または f2 が NULL でない」を COALESCE(co.f1, co.f2) IS NOT NULL で示す (大分類Bについても f3, f4, f5 を使って同様に)
2. 1が真(つまり f1 または f2 が NULL でない)なら SELECT name FROM code WHERE code=’fa’ を、そうでないなら NULL を取得する
3. その結果を CONCAT_WS でつなぐ (NULL は無視される)
これで正しく結果を取得できる想定だったのですが、どうにも結果が予期したものになりませんでした。
株式会社B は「大分類A」と取得したいのに、「大分類A, 大分類B」と取得されてしまうのです。
不思議なのは、
- WHERE句を指定すると結果が正常になる場合がある
- 取得するカラムに co.f3, co.f4, co.f5 を追加しても、idが2のレコードではそれぞれ null である
- COALESCE(co.f3, co.f4, co.f5) だけ取得しても、idが2のレコードでは null である
ということでした。
CONCAT_WS、CASE、COALESCE を組み合わせることによって何かツボを突いてしまったしまったかのようでした。
文法エラーにはなりませんでしたが、CONCAT_WS にサブクエリを指定したのが良くなかったのでしょうか。
この件は、以下のようにSQL文を書き直すことで対応することができました。
SELECT co.*
,CONCAT_WS(', '
,(SELECT name FROM code WHERE code='fa' AND COALESCE(co.f1, co.f2) IS NOT NULL)
,(SELECT name FROM code WHERE code='fb' AND COALESCE(co.f3, co.f4, co.f5) IS NOT NULL)
)
FROM company co