kawasin73のブログ

技術記事とかいろんなことをかくブログです

SQLite のおもしろ仕様 (1) : データ型

Database 他人が作ったものの使い方の解説

型は型、どうもかわしんです。SQLite では型は絶対ではなく、あくまでも尊重です。信用しすぎると裏切られます。

最近 Rust で SQLite をフルスクラッチで再実装しています。

github.com

なるべく本家の SQLite と compatible にするために SQLite のドキュメントやコードを読んで挙動を理解しながら作っています。これを作ることになった経緯はこの記事で紹介していますが、その過程でいろいろ知らなかった面白い仕様や実装があったので紹介していきたいと思います。今回はその第一弾です。

kawasin73.hatenablog.com

データ型と Type Affinity

SQLite のドキュメントの中で、今の所一番面白いのがこれです。

www.sqlite.org

まず、SQLite の内部的には 5 つのデータ型しかありません。

  • NULL
  • INTEGER
  • REAL
  • TEXT
  • BLOB

一方で我々は SQL の CREATE TABLE 文でテーブルを作るときに以下のようにそれぞれのカラムの型を指定します。

CREATE TABLE t1(a INT, b VARCHAR(10));

ここでいう INT や VARCHAR(10) というカラムの型指定は以下の 5 種類の Type Affinity に解釈されます。

  • TEXT
  • NUMERIC
  • INTEGER
  • REAL
  • BLOB

ここで最初に列挙したデータ型と比べると NULL がなくて NUMERIC があることに気づくかと思います。そうです、SQLite で扱われるデータ型と我々が指定する Type Affinity は別物です。

Type affinity はデータをカラムに格納するときの型の変換方法を指定します。（正確には CAST でも使われますがそれは後で）

INTEGER のデータを TEXT affinity の付いたカラムに格納して保存する場合は TEXT に変換されます。

TEXT のデータを INTEGER affinity の付いたカラムに格納して保存する場合は、INTEGER に変換できる時は変換し、変換できない場合 ('1234a' など変換すると一部の情報が欠ける時) は TEXT データのまま保存します。そうです、INTEGER affinity の付いたカラムであっても TEXT データが格納されている時があります。これを知らなかったので読んだ時びっくりしました。

sqlite> CREATE TABLE example1(col INTEGER);
sqlite> INSERT INTO example1(col) VALUES (123), ('123'), ('123a');
sqlite> SELECT typeof(col), col FROM example1;
integer|123
integer|123
text|123a

一方で、CAST(<value> AS <type name>) の構文でもデータ型の変換が同じように行われますが、CAST の場合は一部の情報が欠損する場合でも強制的に型を変換します。

sqlite> SELECT CAST('123a' AS INTEGER);
123

型名を Type Affinity に解釈する

さて、上の例での INT や INTEGER は INTEGER affinity に、VARCHAR(10) は TEXT affinity に解釈されます。実は SQLite は型名を文字列の部分一致で解釈します。

3.1. Determination Of Column Affinity を日本語にすると

  1. INT が含まれていた場合は、INTEGER affinity
  2. CHAR, CLOB, TEXT が含まれていた場合は TEXT affinity
  3. BLOB が含まれていた場合は、BLOB affinity
  4. REAL, FLOA, DOUB が含まれていた場合は REAL affinity
  5. それ以外の場合は全て NUMERIC affinity

です。上の方が優先順位が高いので、CHARINT は INTEGER affinity になります。

なお、VARCHAR(10) の (10) の数字の中身は SQL の文法としては正しいですが、ガン無視されます。わお。

Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any length restrictions (other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values.

さらにおもしろポイントとしてドキュメントでも紹介されていますが、 FLOATING POINT は INT が含まれているので INTEGER affinity です。わお。

データの比較

インデックスを実装するために目的の値を B tree 上で探す時に値の比較が必要になったのでデータの比較についてもドキュメントを読みました。SQLite は異なるデータ型でも比較でき、以下のような順序になります。

NULL < INTEGER | REAL < TEXT < BLOB

INTEGER と REAL 同士の場合は数値的に比較します。

データの比較の前の変換

データ同士の比較の際には前述の Type Affinity に基づいてデータ型の変換が行われます。

4.2. Type Conversions Prior To Comparison によると

  • 片方が INTEGER, REAL または NUMERIC affinity で、もう片方が TEXT, BLOB affinity か Type Affinity がついていない場合、後者に NUMERIC への変換が行われます。
  • 片方が TEXT affinity で、もう片方には Type Affinity がついていない場合、後者に TEXT への変換が行われます。
  • それ以外の場合は変換は行われません。

ここでの、Type Affinity がついていないとは比較文での文字列や数字リテラルなどです。

データ型の変換ではカラムへの格納の際と同じく一部の情報が欠けてしまう場合は変換されずそのままの型として比較されます。

SELECT col, col == '123', col > '456'  FROM example1;
123|1|0
123|1|0
123a|0|1

JavaScript についてのこんなツイートを見かけましたが、こうなる気持ちがすごくわかりました。

TEXT の比較

文字列の比較はデフォルトでは memcmp() と使ってバイト列として比較されますが、COLLATE を使うことで文字列の比較の方法を指定することができます。デフォルトでは BINARY, NOCASE, RTRIM の 3 種類が定義されていて、ユーザーが sqlite3_create_collation() によって独自に定義することもできます。(7. Collating Sequences)

こいつのせいで純粋な Value 同士の比較ができず、僕の prsqlite の実装では ValueCmp という型で wrap して使うことになり、既存のテストコードの変更がすごくめんどくさかったです。

Support Collating sequence for columns · kawasin73/prsqlite@afa31a2 · GitHub

さて、TEXT の比較自体は別にいいのですが、本題はその COLLATE の情報をどこまで引き回すかです。前述の Type Affinity は一度 expression の演算子の計算をするとその結果には Type Affinity は引き継がれません。

sqlite> SELECT CAST('123' AS TEXT) || 456 == 123456;
0

この例でいうと CAST によって '123' には TEXT affinity が付きますが、その TEXT affinity は || での文字列の結合の結果である '123456' には引き継がれません。そのため、'123456' (no type affinity) == 123456 (no type affinity) となり、左右のオペランドに Type Affinity がないので直接比較してデータ型が異なるので答えは 0 になります。

これは、CAST 由来の Type Affinity でも CREATE TABLE のカラム定義由来でも同じです。

しかし、COLLATE は違います。

CAST の結果についてはその内部の collation は引き継がれます。

他の expr 演算子 の演算結果については、その collation が CREATE TABLE のカラム定義由来の場合は引き継がれませんが、COLLATE expression 由来の場合は引き継がれます。わお。

このようにどのようにその collation が指定されたかによって挙動が違います。おそらく仕様としては決まっていなくて、実装した結果こういう挙動になったのだと思いますが、完全に再現するのはめんどくさかったです。

同様に比較などで左右のオペランドからどちらの collation を使うかは、COLLATE expression 由来のものが優先度が高く、同じ優先度の場合は左のオペランドの collation を比較に使い、選ばれたのがCOLLATE expression 由来の場合のみ引き継がれます。

最後に

このような感じで真面目に実装しようとすると面白い発見はありますが、めんどくさいです。