DB2の文字列→数値変換

だいぶ前に徳丸さんが、文字列から数値への暗黙の型変換についてまとめています。

数値リテラルをシングルクォートで囲むことの是非 - ockeghem's blog

徳丸さんの日記には、Oracle, MS SQL Server, MySQL, PostgreSQLの4つのDBMSを対象に、暗黙の型変換が起こるかを実際に調べた結果が書かれています。

私が思うところ、割とメジャーなDBMSで網羅されていないのはDB2です。

というわけで、今日の日記ではDB2の挙動について書きます。

下は、DB2 V9.5での動作です。

[db2inst1@localhost ~]$ db2 "SELECT * FROM staff WHERE id=10"

ID     NAME      DEPT   JOB   YEARS  SALARY    COMM
------ --------- ------ ----- ------ --------- ---------
    10 Sanders       20 Mgr        7  98357.50         -

  1 レコードが選択されました。

[db2inst1@localhost ~]$ db2 "SELECT * FROM staff WHERE id='10'"
SQL0401N  演算 "="
のオペランドのデータ・タイプに互換性がありません。
SQLSTATE=42818

DB2では、文字列から数値への「暗黙の型変換」は行なわれないようです。数値をシングルクォートで囲んだ文字列と、数値とを比較するなどすると、SQLエラーになってしまいます。

SQLzooというサイトにDB2 V8.2の環境がありますが、そこで試しても同じような結果になります。

話はそれますが、SQLZooには、MySQL5、PostgreSQL8、SQL Server 2005、Oracle10g、DB2 V8の環境があり非常に便利です。本当はデータベース初心者のためのSQL文の学習サイトなのですが、全然違う用途で愛用しています。

****

徳丸さんの表に勝手に追加させてもらうと、以下のようになります(赤字の部分は私が追加したところです)。



  Oracle MS SQL MySQL PostgreSQL DB2
insert 1
1.0
1.6 四捨五入 切捨て 四捨五入 四捨五入 切捨て
'1' エラー(*7)
'1.0' エラー(*3) エラー(*6) エラー(*7)
'1.6' 四捨五入 エラー(*3) 四捨五入 エラー(*6) エラー(*7)
'1a' エラー(*1) エラー(*3) エラー(*4) エラー(*6) エラー(*7)
where 1
1.0
1.6 ○(*2) ○(*2) ○(*2) ○(*2) ○(*2)
'1' エラー(*8)
'1.0' エラー(*3) エラー(*6) エラー(*8)
'1.6' ○(*2) エラー(*3) ○(*2) エラー(*6) エラー(*8)
'1a' エラー(*1) エラー(*3) ○(*5) エラー(*6) エラー(*8)

*1 ORA-01722: 数値が無効です。
*2 1.6と一致する項目がない
*3 varchar の値 '1.0' をデータ型 int に変換できませんでした。
*4 ERROR 1265 (01000): Data truncated for column 'num1' at row 1
*5 数値1と解釈される
*6 invalid input syntax for integer: "1.0"
*7 SQL0408N 値には、その割り当てターゲットのデータ・タイプとの互換性がありません。 ターゲット名は "**" です。SQLSTATE=42821
*8 SQL0401N 演算 "="のオペランドのデータ・タイプに互換性がありません。SQLSTATE=42818


この機会にsqliteも調べてみましたが、非常に奇妙な結果になりました。例えば、Integerで定義したカラムに「1a」という値をINSERT文で突っ込むと、「1a」のまま値が保存されてしまいます。

(参考)http://www.tuyudaku.net/sqlite/datatype.html

sqliteは、どうも型があるような無いような… 風変わりなDBのようです。