ORACLEのCHAR型とPostgreSQLのchar型は似て非なるもの
定義
指定サイズより短い文字列を挿入すると末尾に空白が埋められて、指定されたサイズになる。 なお、ORACLEのCHAR型サイズはバイト数だが、PostgreSQLのCHAR型サイズは文字数なので、PostgreSQLのCHAR型はORACLEのNCHAR型に近い。
データ更新
指定されたサイズより長い文字列を挿入しようとすると、ORACLEはエラーになり、PostgreSQLは指定サイズで切り捨てる。
比較演算
PostgreSQL
マニュアルには次のように記載されている。
最後の空白は、重要ではないものとして扱われ、2つのcharacter型の値を比べる際には無視されます。 空白が重要な照合順序では、この挙動は予期しない結果を返す可能性があります。例えば、SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2)はCロケールでスペースが改行よりも大きいにも関わらず真を返します。character値を他の文字列型に変換する際は、文字列の終わりの空白は除去されます。例がちょっとわかりにくいが、
'a'::CHAR(1) = 'a '::CHAR(2)は真と判定されるし、'a'::varchar = 'a '::CHAR(2)も真と判定される。
ORACLE
ORACLEには空白埋め比較セマンティクスと非空白埋め比較セマンティクスがある。 CHAR型同士の比較などでは空白埋め比較セマンティクスが使われるが、比較する片方または両方の値が(N)VARCHAR2データ型の場合、非空白埋め比較セマンティクスが使われる。
非空白埋め比較セマンティクスはマニュアルに次のように記載されている。
2つの値を、最初に異なる文字まで1文字ずつ比較します。最初に異なる文字の位置で、大きい方の文字を持つ値の方が大きいとみなされます。長さが異なる2つの値を短い方の値の最後まで比較して、すべて同じ文字だった場合、長い方の値が大きいとみなされます。同じ長さの2つの値に異なる文字がない場合、その2つの値は等しいとみなされます。よって、
'a' = 'a 'は真と判定されるが、TO_CHAR('a') = 'a 'は偽と判定される。
(ORACLEでは、テキストリテラルはCHAR型と同じ扱いであるが、TO_CHARはVARCHAR2型を返すので違いが出る)
結合
比較演算の違いは、結合にも影響する。 次のような二つのテーブルがあるとする。
FIXED| KEY as char(3) |
|---|
'A ' |
VARIABLE
| KEY as varchar2(3) |
|---|
'A' |
'A ' |
'A ' |
(※PostgreSQLの場合は、varchar2ではなくvarchar)
この時、SELECT * FROM FIXED JOIN VARIABLE ON FIXED.KEY = VARIABLE.KEYの結果は次のようになる。
| FIXED.KEY | VARIABLE.KEY |
|---|---|
'A ' | 'A ' |
PostgreSQL
| FIXED.KEY | VARIABLE.KEY |
|---|---|
'A ' | 'A' |
'A ' | 'A ' |
'A ' | 'A ' |
インデックス
上記の結合の例でKEY列にインデックスがあっても、PostgreSQLではインデックスが使われない。 これは、PostgreSQLのCHAR型とVARCHAR型の等値比較演算子が一意な結果を返さないことや、ソート順が異なることが理由と思われる。
結合だけでなく、WHERE句での検索条件指定でも、PostgreSQLでは型が異なるとインデックスが使われない。
例えば、SELECT * FROM VARIABLE WHERE KEY = $1のようなパラメータクエリで、$1の方としてCHAR型を指定してしまうと、インデックスが使われなくなる。
こういうバグを仕込んでしまうと、SQLの実行計画を取得するとインデックスを使うことになっているが、アプリからの実行時にはインデックスが使われないという分かりにくい現象が発生する。
コメント
コメントを投稿