【SQL】IN句でヒットするはずのデータが0件だったら確認すること【暗黙型変換】

SQL

「同じ条件のはずなのに、なぜか取得結果が違う」という怪奇現象に出くわしたこと、ありませんか。

運用保守に関わる方なら誰しも、「DBからデータを探す」という業務を任された経験があると思います。
私も例に漏れず、ほぼ毎日SQLを叩いております。

そんな中で先日、まったく同じ条件を指定したはずのSQLが、複数列INでは0件、OR句では9件という結果を返してきました。
エラーは出ず、ただ静かに0件が返ってくるだけ。

犯人はSQLが行う「暗黙の型変換」でした。

今回は、見事SQLの仕様に翻弄された私の事例を紹介するので、日々業務に邁進する皆さまの一助になれば幸いです。

ことの発端

価格の変更依頼

あるとき、自分が担当しているWEBシステムのお客様から「該当する商品9件の価格を変更してほしい」という依頼をいただきました。
情報更新、運用保守ではよくある業務ですよね。

普段は更新用の商品マスタをもらって、それをバッチに流して自動処理…という流れだったのですが、件数が少ない＆普段とちょっと異なる扱いのデータだったこともあり、DBでUPDATE文を叩くことになりました。

価格をいじるというのは、システムの利用者にはかなりクリティカルな変更です。
UPDATE文を流す前に、まずは該当データの確認とバックアップを取っておきたいところ。

SELECT文を用意

業務ではもっと複雑な条件を付与していましたが、端的に言うとこんな感じでした。
カラム名やデータはダミーです。

SELECT * FROM items WHERE order_flag = 1 AND code = '111111A' AND area = 10;
SELECT * FROM items WHERE order_flag = 1 AND code = '222222B' AND area = 10;
SELECT * FROM items WHERE order_flag = 1 AND code = '333333C' AND area = 10;
SELECT * FROM items WHERE order_flag = 1 AND code = '111111A' AND area = 20;
SELECT * FROM items WHERE order_flag = 1 AND code = '444444D' AND area = 20;
SELECT * FROM items WHERE order_flag = 1 AND code = '111111A' AND area = 100;
SELECT * FROM items WHERE order_flag = 1 AND code = '111111A' AND area = 200;
SELECT * FROM items WHERE order_flag = 1 AND code = '111111A' AND area = 201;
SELECT * FROM items WHERE order_flag = 1 AND code = '444444D' AND area = 30;

商品テーブルから、発注可能な、該当する商品コード・販売エリアの商品をピックアップするという意味です。
ご覧のとおり、同じ商品コードが複数のエリアに登場したり、同じエリアに複数の商品コードがぶら下がったりしています。

データを見るだけならこれらのSQLを一個ずつ実行すればいいんですが…結果をエクスポート（バックアップ）するとなると、SQLは一文にまとめる必要があります。

SQLの実行結果＝エクスポート対象なので、一文ずつ実行すると一行ずつのデータを出力するはめになる。

IN句でひとまとめにできそうですが、「コードのIN」と「エリアのIN」を別々に書くと、意図しない組み合わせまで拾っちゃいそうです。
まとめるとしたらペア単位で指示してあげるしかない。

自分なりに考え、それぞれの条件を生かせるように書いたSELECT文がこちら。

SELECT *
FROM items
WHERE order_flag = 1
  AND (code, area) IN (
      ('111111A', 10),
      ('222222B', 10),
      ('333333C', 10),
      ('111111A', 20),
      ('444444D', 20),
      ('111111A', 100),
      ('111111A', 200),
      ('111111A', 201),
      ('444444D', 30)
  );

どうでしょう、スッキリしたんじゃないでしょうか。

(列A, 列B) IN ((値1, 値2), ...) という書き方は「複数列IN」「行コンストラクタ」などと呼ばれるもので、ペアの条件をまとめて書けるので可読性も高い気がします。

うん、我ながらいい感じ。
さっそく実行していきましょうね。

実行結果

0件やないかーーーーーーーーーーい！！！！！！！

余談：筆者はSQLのUIに「MySQL Workbench」を使用しています。

実行したSQLにエラーは出ていないのに、結果が0件なんです。

そもそも先方は、DBから商品情報（今回で言えば価格）を抽出し、変更箇所を提示してきます。
よって0件なわけないんです。

そして厄介なことにね、これ、OR句だと成功するんですよ。

SELECT *
FROM items
WHERE order_flag = 1
  AND (
       (code = '111111A' AND area = 10)
    OR (code = '222222B' AND area = 10)
    OR (code = '333333C' AND area = 10)
    OR (code = '111111A' AND area = 20)
    OR (code = '444444D' AND area = 20)
    OR (code = '111111A' AND area = 100)
    OR (code = '111111A' AND area = 200)
    OR (code = '111111A' AND area = 201)
    OR (code = '444444D' AND area = 30)
  );

こちらはきっちり9件返ってきます。
同じテーブル、同じ条件、同じ値。書き方を変えただけ。なのに0件と9件。

なんでだかわかりますか？

冒頭でもお話したとおり、「暗黙の型変換」が行われているんです。

型変換とは

概要

型変換とは、有り体に言うと「データ型が違ってもいい感じに解釈するよ」というSQLの機能です。

前提として、数値（intなど）の 10 と、文字列（VARCHAR）の '0010'は、まったく異なる値としてDBで扱われます。

  • '0010' → 0 0 1 0 という4文字の並び
  • 10 → 十という「量」

紙に「0010」という文字を書くのと、物体を10個数えることって別物じゃないですか。
で、この異なる型でもどちらか一方に勝手に揃えて（重要）比較してくれるのが暗黙の型変換になるんです。

この例で言うなら、10 = ‘0010’ とされる。

そんでもって、型変換は値どうしのみならず、WHERE句でも発生します。
例えば code が VARCHAR 型だったとして、

SELECT *
FROM items
WHERE code = 100;

と書いた場合、

code（文字列） = 100（数値）

と、型は異なるものの、SQLが状況に応じて変換＝条件にヒットするようになるんです。

では、今回のケースはどうだったでしょうか。

WHERE句のテーブル定義

SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'データベース名'
  AND TABLE_NAME   = 'items'
  AND COLUMN_NAME IN ('code', 'area', 'order_flag');

結果はこうでした。

列名型文字セット
codevarchar(50)utf8
order_flagtinyint(4)—
areavarchar(100)utf8

area がVARCHAR。
思えば、area って0010みたいなゼロ埋めの値だった気がするな（今更）。

これは自分のよくないところなんですが、簡単な更新作業であればそこまで型って意識しなかったんですよね。
なんとなくこれは01だったな～とか、ここはintだったな～とか。

今回の一件で改めてテーブル定義を眺めましたが、「0か1しかないしbool値だと思ってたら、tinyintだった」なんてこともありました（さっきのorder_flagがそう）。

codearea
111111A0010
222222B0010
111111A0100
111111A0201

area に格納されているのは 10 という数値ではなく、'0010' という4文字の文字列でした。
それに対して、私は area = 10 と数値で条件を書いちゃってた。

でも、暗黙の型変換のお陰でこれまで条件にヒットしてた…ってわけね。

…。

じゃぁIN句だって9件ヒットしてもよくない？？？

OR句と差がでる理由にはなってなくない！！！？？？？

ここが今回のややこしいところで、実は同じ「文字列の列に数値を渡す」書き方でも、書き方によって結果が変わるんです。

OR句だけ動いた理由

OR句の場合：数値として比較された

area = 10

比べているのは「VARCHAR列」と「数値」の1対1。
このルールでは、文字列のほうが数値に変換されます。

「0010」という文字を数字として読み直したら10だった、というイメージです。
だから9件取得できていました。

IN句の場合：文字列として比較された

(code, area) IN (('111111A', 10), ...)

こちらは ( , ) で2つをセットにして比べています。

セットの相方である code は '111111A' という、どう頑張っても数値にできない文字列です。
条件文全体が文字列と解釈されるため、数値への変換が行われません。

「0010」と「10」を文字の並びとして見比べたので、別物と判定されたわけです。

正しい書き方

原因はややこしいですが、正しくデータを取得するのは簡単です。
テーブル定義どおりの形式、つまりゼロ埋めした文字列で指定すればOK。

SELECT *
FROM items
WHERE order_flag = 1
  AND (code, area) IN (
      ('111111A', '0010'),
      ('222222B', '0010'),
      ('333333C', '0010'),
      ('111111A', '0020'),
      ('444444D', '0020'),
      ('111111A', '0100'),
      ('111111A', '0200'),
      ('111111A', '0201'),
      ('444444D', '0030')
  );

ポイント①：桁数を揃えてゼロ埋めする（10 → 0010）
ポイント②：必ずクォートで囲む（囲まないと 0010 は数値の10と解釈され、元の木阿弥）

これで無事に9件返ってきました。

補足

「毎回ゼロ埋めするのが面倒」という場合、LPAD で加工する方法もあります。
この関数は、指定した桁数になるように、足りない部分を指定した文字で埋めてくれるものです。

AND (code, LPAD(area, 4, '0')) IN (...)

ただし、列に関数をかけるとインデックスが効かなくなるんですって。
全行に対して関数を実行することになるため、件数の多いテーブルでは重くなっちゃいそう。

今回のように条件が数件〜数十件程度なら、素直に '0010' と書くほうが早そうですね。

まとめ

このエピソードの何が怖いって、「エラーが出なかったこと」なんですよね…。

依頼内容から「データが存在する」と知っていたおかげで、「0件になるのはおかしい」という違和感に気付けました。
でも運用保守の業務では、データの有無なんて調べてみないとわからないことの方が多いじゃないですか。

考えなしに「該当データはありませんでした」なんて返信したら大事になったかも…。

とはいえ、この暗黙の型変換という仕組み自体は、知らないと絶対に気づけません。
同じところでハマった方の助けになればいいな～と思います。

UPDATE文の前には必ず件数確認しようね。

ではまた次回！

タイトルとURLをコピーしました