oracle

暗黙の型変換には注意

今日暗黙の型変換に関する部分で、バグを見つけたので紹介します。数値の先頭桁を0埋めしてchar型やvarchar2型で保持しているシーンは案外多いと思いますが、この項目に対して数値で検索すると意外な結果になる場合があるので注意してください。

  • 注意点その1
    • 先頭の’0’埋めされた’0’は無いものとして検索されるようです。(テーブルに格納されている値のほうがnumber型に型変換されて比較されているように見えます)
  • 注意点その2
    • 暗黙の型変換がされた場合並び順も変わります。

※半角空白は’_’(アンダーバー)に置き換えています。

CHAR型のカラムに対しnumber型の値で条件指定した場合

create table ex007(
   c1 char(5)
  ,c2 varchar2(20)
);
create table ex007( c1 char(5) ,c2 varchar2(20));
insert into ex007(c1,c2) values ('00001','トマト');
insert into ex007(c1,c2) values ('1    ','キュウリ');
insert into ex007(c1,c2) values ('010  ','スイカ');
insert into ex007(c1,c2) values ('01   ','トウモロコシ');
insert into ex007(c1,c2) values ('    1','ナス');
select * from ex007 order by c1
;
C1C2
____1ナス
00001トマト
01___トウモロコシ
010__スイカ
1____キュウリ

当然ですがC1カラムはCHAR型の昇順で表示されます。次にこのC1カラムに対してnumber型で条件を指定し暗黙の型変換を発生させて検索します。

select * from ex007 t where t.C1 =1
order by c1;
C1C2
00001トマト
1____キュウリ
01___トウモロコシ
____1ナス

「トマト」や「トウモロコシ」レコードが検索対象になるとは意外ではなかったでしょうか。さらには暗黙の型変換が行われたカラムがorder byに指定されていると並び順も変わるようです。上記は対象カラムをCHAR型で検証していますが、VARCHAR2型でも同様な結果となりました。暗黙の型変換は便利ですが「本番に使われるようなプログラムには使わない」ようにする事が賢明だと思います。

VARCHAR2型のカラムに対しnumber型の値で条件指定した場合

create table ex008( 
  c1 varchar2(5)
   ,c2 varchar2(20)
);
insert into ex008(c1,c2) values ('00001','トマト');
insert into ex008(c1,c2) values ('1','キュウリ');
insert into ex008(c1,c2) values ('010','スイカ');
insert into ex008(c1,c2) values ('01','トウモロコシ');
insert into ex008(c1,c2) values ('    1','ナス');
select * from ex008 order by c1
;
C1C2
____1ナス
00001トマト
01トウモロコシ
010スイカ
1キュウリ

CHAR型の時と同様に、C1カラムはVARCHAR2型の昇順で表示されます。次にこのC1カラムに対してnumber型で条件を指定し暗黙の型変換を発生させて検索します。

select * from ex008 t where t.C1 =1
order by c1;
C1C2
00001トマト
1キュウリ
01トウモロコシ
____1ナス

CHAR型の時と同様に、先頭’0’埋めされたレコードのトマトやトウモロコシ、先頭半角空白が入ったナスも検索対象レコードとしてでてきて、こちらも同様に並び順は、テーブルに格納された値がnumber型に変換された状態での並び順になっているようです。

スポンサーリンク