oracle のバージョン:11g
調査の時にその場で作るSQLでは暗黙の型変換に頼ることがよくあるが、
カラムの型等が成功するものと変わらないのに何故か失敗するテーブルがいくつかあり、困惑することがあった。
暗黙の型変換を含むSQLは、検索対象に含まれるデータによってエラーになる恐れがある。
create table m_item{
item_cd int,
--省略
};
select * from m_item
Where item_cd = '2121032';
原因
検索対象のカラムにキャスト不可能な値が入っている。
解説
SQL上で型が違う2つの要素を比較するとき、暗黙の型変換が行われる
例えば以下のようなSQLの場合、
Where item_cd = '2121032'
内部的に以下の変換が行われている
Where item_cd = to_number('2121032')
変換が行われるのは必ず定数側とは限らない。
公式のSQLリファレンスによると
http://docs.oracle.com/cd/E16338_01/server.112/b56299/sql_elements002.htm#i46862
数値と文字値を比較する場合、Oracleは文字データを数値に変換します。
とある。ので列側の方がcharの場合
Where to_number(item_cd) = 2121032
のような変換が起こる。
※ちなみに列側を演算しているので、item_cdのインデックスが使われないため、パフォーマンス低下を引き起こす。
この時列側の値の中に、1件でもキャスト不可能な値、
数値型へのキャストであれば、空白や数字以外の文字等があれば、暗黙のキャストは失敗する。
調査方法
正規表現を利用した条件指定で該当データを探す。
数値型へのキャスト失敗の場合
where not (regexp_like(item_cd, '[0-9]'))
で条件指定したSELECT文を発行することにより、
数字以外の項目を含む行を検索可能。
・大抵手作業でinsertした不正な項目を持つ行
・謎の空白を含む行
等がヒットするかと思う。そのデータが原因。
データによって処理が失敗してしまうので、暗黙の型変換には気を付けよう。