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のようです。