SQLiteでは、独特な方法で動的な型づけを行っている。CAST式の挙動を把握するために、いくつかのパターンで返される値を整理したいと思う。
SQLiteのCAST式の書き方
CAST式の書き方は、他のSQL標準のDBMSと変わらない。以下のようなものである。
sql
CAST(列名 AS 型名)
型名に指定できる値は、SQL準拠のDBMSがサポートしているものであればなんでも良い。中には良くないものもあるかもしれないが、SQLiteでは型アフィニティという仕組みにより、自動で型名を判断し、SQLiteで使われている4つのストレージクラス（≒データ型）に変換する。
INTEGER、REAL、TEXT、BLOBがそれで、変換できないデータはBLOBに分類されることになっている（分類の例は公式ドキュメントのここにある）。
データ型別に見るCAST式の返り値
CAST式の変換処理については、公式ドキュメントのここに記載がある。
ただ、自分の目で確かめた方が324929（ミニヨクツク）と思うので、以下のようなテーブルを用意してCAST式の挙動を確かめたい。
sql
.header ON
.nullvalue (NULL)
.mode table
CREATE TABLE cast_test(original_value, sample_value);
-- 整数
INSERT INTO cast_test VALUES("1",1);
-- 整数に変換できる小数
INSERT INTO cast_test VALUES("1.0",1.0);
-- 整数に変換できない小数
INSERT INTO cast_test VALUES("1.1",1.1);
-- 整数にも小数にも変換できる文字列
INSERT INTO cast_test VALUES("'1.0'",'1.0');
-- 整数に変換できず、小数に変換できる文字列
INSERT INTO cast_test VALUES("'1.1'",'1.1');
-- 整数にも小数にも変換できない文字列
INSERT INTO cast_test VALUES("'壱'",'壱');
-- 文字と数が混ざった文字列1
INSERT INTO cast_test VALUES("'壱1'",'壱1');
-- 文字と数が混ざった文字列2
INSERT INTO cast_test VALUES("'壱1'",'壱1.0');
-- 文字と数が混ざった文字列3
INSERT INTO cast_test VALUES("'1壱'",'1壱');
-- 文字と数が混ざった文字列4
INSERT INTO cast_test VALUES("'1.0壱'",'1.0壱');
-- バイナリ
INSERT INTO cast_test VALUES("X'53514C697465'",X'53514C697465');
-- 空文字
INSERT INTO cast_test VALUES("''",'');
-- NULL
INSERT INTO cast_test VALUES("NULL",NULL);
ほいでは、さっそく実験を始める。
INTEGERへのキャスト結果
sql
SELECT original_value,CAST(sample_value AS INTEGER) FROM cast_test;
+------------+-------------------------------+
| original_v | CAST(sample_value AS INTEGER) |
+------------+-------------------------------+
| 1 | 1 |
+------------+-------------------------------+
| 1.0 | 1 |
+------------+-------------------------------+
| 1.1 | 1 |
+------------+-------------------------------+
| '1.0' | 1 |
+------------+-------------------------------+
| '1.1' | 1 |
+------------+-------------------------------+
| '壱' | 0 |
+------------+-------------------------------+
| '壱1' | 0 |
+------------+-------------------------------+
| '壱1' | 0 |
+------------+-------------------------------+
| '1壱' | 1 |
+------------+-------------------------------+
| '1.0壱' | 1 |
+------------+-------------------------------+
| X'53514C69 | 0 |
| 7465' | |
+------------+-------------------------------+
| '' | 0 |
+------------+-------------------------------+
| NULL | (NULL) |
+------------+-------------------------------+
だいたい予想通りの結果である。先頭が数字で始まる文字はその数字が取得され、文字から始まるものは0が返される点に注意したい。
また、小数点以下は切り捨てられて整数になる。例えば1.5だろうが1.9999999だろうが1に丸められる。
REALへのキャスト結果
sql
SELECT original_value,CAST(sample_value AS REAL) FROM cast_test;
+------------+----------------------------+
| original_v | CAST(sample_value AS REAL) |
+------------+----------------------------+
| 1 | 1.0 |
+------------+----------------------------+
| 1.0 | 1.0 |
+------------+----------------------------+
| 1.1 | 1.1 |
+------------+----------------------------+
| '1.0' | 1.0 |
+------------+----------------------------+
| '1.1' | 1.1 |
+------------+----------------------------+
| '壱' | 0.0 |
+------------+----------------------------+
| '壱1' | 0.0 |
+------------+----------------------------+
| '壱1' | 0.0 |
+------------+----------------------------+
| '1壱' | 1.0 |
+------------+----------------------------+
| '1.0壱' | 1.0 |
+------------+----------------------------+
| X'53514C69 | 0.0 |
| 7465' | |
+------------+----------------------------+
| '' | 0.0 |
+------------+----------------------------+
| NULL | (NULL) |
+------------+----------------------------+
小数点以下が切り捨てられない以外は、INTEGERと同じ結果である。
NUMERICへのキャスト結果
sql
SELECT original_value,CAST(sample_value AS NUMERIC) FROM cast_test;
+------------+-------------------------------+
| original_v | CAST(sample_value AS NUMERIC) |
+------------+-------------------------------+
| 1 | 1 |
+------------+-------------------------------+
| 1.0 | 1.0 |
+------------+-------------------------------+
| 1.1 | 1.1 |
+------------+-------------------------------+
| '1.0' | 1 |
+------------+-------------------------------+
| '1.1' | 1.1 |
+------------+-------------------------------+
| '壱' | 0 |
+------------+-------------------------------+
| '壱1' | 0 |
+------------+-------------------------------+
| '壱1' | 0 |
+------------+-------------------------------+
| '1壱' | 1 |
+------------+-------------------------------+
| '1.0壱' | 1 |
+------------+-------------------------------+
| X'53514C69 | 0 |
| 7465' | |
+------------+-------------------------------+
| '' | 0 |
+------------+-------------------------------+
| NULL | (NULL) |
+------------+-------------------------------+
NUMERICは、SQLiteの型アフィニティの1つで、浮動小数点数に解釈できるものはREALに、整数に解釈できるものはINTEGERになる。はずだが、'1.0壱'が1になっている。
試しにSELECT CAST('1.1壱'AS NUMERIC);を打ってみると、1.1が返った。
イヤイヤながら、CAST式の説明をちゃんと読んでみた。NUMERICでキャストすると、「入力テキストが整数のように見え（小数点も指数もない）、値が64ビット符号付き整数に収まるほど小さい場合にはINTEGERと解釈される」ようである。
小数点はあるが、1.0は整数と等価として解釈されるらしい。
TEXTへのキャスト結果
sql
SELECT original_value,CAST(sample_value AS TEXT) FROM cast_test;
+------------+----------------------------+
| original_v | CAST(sample_value AS TEXT) |
+------------+----------------------------+
| 1 | 1 |
+------------+----------------------------+
| 1.0 | 1.0 |
+------------+----------------------------+
| 1.1 | 1.1 |
+------------+----------------------------+
| '1.0' | 1.0 |
+------------+----------------------------+
| '1.1' | 1.1 |
+------------+----------------------------+
| '壱' | 壱 |
+------------+----------------------------+
| '壱1' | 壱1 |
+------------+----------------------------+
| '壱1' | 壱1.0 |
+------------+----------------------------+
| '1壱' | 1壱 |
+------------+----------------------------+
| '1.0壱' | 1.0壱 |
+------------+----------------------------+
| X'53514C69 | SQLite |
| 7465' | |
+------------+----------------------------+
| '' | |
+------------+----------------------------+
| NULL | (NULL) |
+------------+----------------------------+
文字列になるだけなので、大体予想通りである。
ここに来て、ようやくBLOB値（X'53514C69）が置換された。この値は公式ドキュメントのリテラル値の項から拝借したものである。
BLOBからTEXTへのキャストに関しては、"BLOB値をTEXTにキャストするには、BLOBを構成するバイト列を、データベースのエンコーディングを使用してエンコードされたテキストとして解釈"とある。
また、対応するバイト列がエンコーディングにない場合は、空文字が返るようだ。
BLOBへのキャスト結果
sql
SELECT original_value,CAST(sample_value AS BLOB) FROM cast_test;
+------------+----------------------------+
| original_v | CAST(sample_value AS BLOB) |
+------------+----------------------------+
| 1 | 1 |
+------------+----------------------------+
| 1.0 | 1.0 |
+------------+----------------------------+
| 1.1 | 1.1 |
+------------+----------------------------+
| '1.0' | 1.0 |
+------------+----------------------------+
| '1.1' | 1.1 |
+------------+----------------------------+
| '壱' | 壱 |
+------------+----------------------------+
| '壱1' | 壱1 |
+------------+----------------------------+
| '壱1' | 壱1.0 |
+------------+----------------------------+
| '1壱' | 1壱 |
+------------+----------------------------+
| '1.0壱' | 1.0壱 |
+------------+----------------------------+
| X'53514C69 | SQLite |
| 7465' | |
+------------+----------------------------+
| '' | |
+------------+----------------------------+
| NULL | (NULL) |
+------------+----------------------------+
試しにBLOBにキャストしてみたら、TEXTと結果が同じである。エンコーディングによって違いが出るそうだが、そもそもエンコーディングの知識が曖昧なのでよくわからない。
NULLへのキャスト結果
sql
sqlite> SELECT original_value,CAST(sample_value AS NULL) FROM cast_test;
Parse error: near "NULL": syntax error
SELECT original_value,CAST(sample_value AS NULL) FROM cast_test;
error here ---^
NULLへのキャストはパースエラーが出た。
SOMETHINGへのキャスト結果
sql
SELECT original_value,CAST(sample_value AS SOMETHING) FROM cast_test;
+------------+---------------------------------+
| original_v | CAST(sample_value AS SOMETHING) |
+------------+---------------------------------+
| 1 | 1 |
+------------+---------------------------------+
| 1.0 | 1.0 |
+------------+---------------------------------+
| 1.1 | 1.1 |
+------------+---------------------------------+
| '1.0' | 1 |
+------------+---------------------------------+
| '1.1' | 1.1 |
+------------+---------------------------------+
| '壱' | 0 |
+------------+---------------------------------+
| '壱1' | 0 |
+------------+---------------------------------+
| '壱1' | 0 |
+------------+---------------------------------+
| '1壱' | 1 |
+------------+---------------------------------+
| '1.0壱' | 1 |
+------------+---------------------------------+
| X'53514C69 | 0 |
| 7465' | |
+------------+---------------------------------+
| '' | 0 |
+------------+---------------------------------+
| NULL | (NULL) |
+------------+---------------------------------+
謎の型へのキャストは、NUMERICへのキャストと同じ結果になった。この辺は型アフィニティに沿って解釈されるようだ。例えばINTもINTEGERと同じ結果になる。