前文
きっかけは以下のような単純なクエリ。
mydb=# SELECT 1 WHERE 'AA ' = 'AA';
?column?
----------
(0 rows)
なん…だと…？
末尾スペースは無視してくれない、だと？ これは 標準SQL に反する動作。
INF_ SQL Server が末尾のスペースで文字列を比較する方法
INF: SQL Server が末尾のスペースで文字列を比較する方法
概要
SQL Server は、ANSI/ISO SQL-92 仕様 (セクション8.2、<比較述語>、一般的なルール #3)に従って、文字列をスペースで比較します。 ANSI 標準では、比較で使用される文字文字列を比較する前に、その長さが一致するようにパディングが必要になります。 パディングは、WHERE 句の述語とその他の Transact-sql 文字列の比較のセマンティクスに直接影響します。 たとえば、Transact-sql では、ほとんどの比較操作で、文字列 'abc' と 'abc ' が等価であると見なされます。このルールの唯一の例外は LIKE 述語です。 LIKE 述語式の右側の値に末尾のスペースが含まれる場合、SQL Server は、比較が行われる前に2つの値を同じ長さにパディングしません。 LIKE 述語の目的は、単純な文字列の等価テストではなく、パターンの検索を容易にすることです。これは、前に説明した ANSI SQL 92 仕様のセクションに違反することはありません。
「標準SQL なんて知らねぇし」「実務でもそこまでの知識なんて必要ないっしょ」なんて声が聞こえてきそうだけど。
例えばSQL Serverを学び初めてまず困惑するのが型変換にCAST、CONVERTと二つの関数が用意されていること。
CAST および CONVERT (Transact-SQL)
違いは？ どちらを使うのが普通なの？ という初歩的な疑問。
CASTは 標準SQL に則った実装であり、CONVERTはSQL Server独自の実装（拡張機能）。
それが分かれば「CONVERTの方が機能が豊富なのかな？（CASTと同等かそれ以下ならわざわざ用意される筈がない）」「移植性を考慮した場合はCASTを使用した方が無難か？（大抵の DB は標準SQL に準拠している）」といった判断が成り立つ。
流石に積極的に 標準SQL を学ぶ必要は無いとは思うが。（標準SQL仕様書は有料だしね）
それでも多少なりとも頭の隅にでもあれば、DB、クエリに対する理解は深まる。今回のテーマであるPostgreSQLでもドキュメントに目を通せば、「標準SQLの仕様です」といった文言はそこかしこで出てくる。あぁ、標準SQL はそうなんだな、ぐらいの認識は持ちたい。
環境
| DB | バージョン |
|---|---|
| PosgreSQL | 13.5 |
| SQL Server | 2017 |
| Oracle | 19c |
| 設定はデフォルト。 | |
本題は PostgreSQLだが、SQL Server、Oracleも手持ちの環境に入っていたので比較として引用した。 |
末尾スペースの調査と考察
PostgreSQLは真偽値を直接扱うことができる。それを踏まえ改めて各文字列の型を明示して確認したのが以下。
mydb=# SELECT
mydb-# 'AA '::char(10) = 'AA' AS char型
mydb-# , 'AA '::varchar(10) = 'AA' AS varchar型
mydb-# , 'AA '::text = 'AA' AS text型
mydb-# ;
char型 | varchar型 | text型
--------+-----------+--------
t | f | f
(1 row)
t=true=真。
f=false=偽。
char型しか末尾スペースは無視しないらしい。
SQL Serverは以下。
1> SELECT IIF(CAST('AA ' AS char(10)) = 'AA', '一致', '不一致');
2> SELECT IIF(CAST('AA ' AS varchar(10)) = 'AA', '一致', '不一致');
3> SELECT IIF(CAST('AA ' AS nchar(10)) = 'AA', '一致', '不一致');
4> SELECT IIF(CAST('AA ' AS nvarchar(10)) = 'AA', '一致', '不一致');
5> GO
----
一致
(1 行処理されました)
----
一致
(1 行処理されました)
----
一致
(1 行処理されました)
----
一致
(1 行処理されました)
冒頭の公式の記載にもある通り、末尾スペースは無視してくれる。因みにtext型は将来廃止予定、現在非推奨であり、そもそも比較演算子=で判定できないので却下。
Oracleは以下。因みにOracle固有関数DECODEを使用した場合は結果が異なるので注意（それはそれで興味深いが）。ここではあくまでも比較演算子=の挙動確認が目的。
SQL> SELECT CASE WHEN CAST('AA ' AS char(10)) = 'AA' THEN '一致' ELSE '不一致' END FROM DUAL;
CASEWHENC
---------
一致
SQL> SELECT CASE WHEN CAST('AA ' AS varchar2(10)) = 'AA' THEN '一致' ELSE '不一致' END FROM DUAL;
CASEWHENC
---------
不一致
SQL> SELECT CASE WHEN CAST('AA ' AS nchar(10)) = 'AA' THEN '一致' ELSE '不一致' END FROM DUAL;
CASEWHENC
---------
一致
SQL> SELECT CASE WHEN CAST('AA ' AS nvarchar2(10)) = 'AA' THEN '一致' ELSE '不一致' END FROM DUAL;
CASEWHENC
---------
不一致
ふーん、Oracleでも可変長文字列型では後ろスペースは無視しないのか。それにしてもこんな単純な確認クエリでも、真偽値を直接扱うことができるのはPostgreSQLだけ、条件演算子的なIIF関数が用意されているのはSQL Serverだけ、Oracleにはそれらがないので標準SQL での記述になるという。クエリの方言って面倒だよな。
余談はさておき、まとめると以下。〇：末尾スペース無視、×：無視しない。
| DB | 型 | 末尾スペース |
|---|---|---|
| PosgreSQL | char | 〇 |
| varchar | × | |
| text | × | |
| SQL Server | char | 〇 |
| varchar | 〇 | |
| nchar | 〇 | |
| nvarchar | 〇 | |
| Oracle | char | 〇 |
| varchar2 | × | |
| nchar | 〇 | |
| nvarchar2 | × |
可変長文字列に関しては、末尾スペースを無視するのはSQL Serverだけ、PostgreSQL、Oracleでは無視しないという結果。
まずPostgreSQLに関して。
そもそもtext型は 標準SQL ではない。
text型は標準SQLにはありませんが、
そう考えると、末尾スペースの扱いも 標準SQL には則っていないことも、そういうものなんだなと納得。
また、varchar型とtext型は上記ドキュメントによれば、どちらも可変長であり違いは上限付きか制限なしかだけっぽい。（厳密には上限約 1 GB）
一貫性を保持する為、varchar型の仕様をtext型に寄せていると考えられる。
しかし、Oracleの可変長文字列型が 標準SQL に沿っていないのは謎。
それで思い出すのが、Oracleの文字列型はNULLの扱いが厳密ではなく、空白（長さ 0 の文字列）と同じ扱いという仕様。
その為、Oracleでは複数の文字列を連結する際、NULLが混じっていると連結結果がNULLになってしまうという「事故」（これはこれで標準SQL仕様みたいだけど）が発生しない。
mydb=# SELECT 'PostgreSQL' || null;
?column?
----------
(null)
(1 row)
1> SELECT 'SQL Server' + null;
2> GO
-----------
NULL
(1 行処理されました)
SQL> SELECT 'Oracle' || null from DUAL;
'ORACL
------
Oracle
Oracleって 標準SQL より、実際の運用での利便性を優先している節がある。文字列の比較でも末尾スペースを無視する仕様は思わぬ落とし穴があるとよく指摘されている。
SQL Server - 後ろにスペースがある場合の文字列の比較について
末尾スペースの件も無視しない方が合理的とOracleは判断したのかも。
最後にchar型について。
これは特殊で末尾スペースが列サイズに合わせて自動で埋められ、取得時も末尾スペース込みとなる。
mydb=# SELECT 'AA'::char(10);
bpchar
------------
AA
(1 row)
mydb=# SELECT 'AA'::char(20);
bpchar
----------------------
AA
(1 row)
それでいて文字列長の取得では末尾スペースを除いた実質的な文字列の長さが返ってくる。
mydb=# SELECT length('AA'::char(20));
length
--------
2
(1 row)
つまり末尾スペースは「空気」扱いなので、文字列比較でも無視される、という理解で良いと思う。
因みにこのイマイチ気持ち悪い固定長文字列という仕様は、今となっては見ることも少なくなった古典的固定長データファイルに対応している、という理解で良いのかな。
固定長 (fixed length)とは
固定長データファイルとは
text型の上限についての調査と考察
この件に関してぐぐると出てくるのが「1GB」という値。
これは以下の公式ドキュメントが出自と思われる。
いずれの場合にあっても保存できる最長の文字列は約1ギガバイトです。
公式に書いてあるのだからそうだろ、で結論かもしれないが……。
まず「いずれの場合」とは、この段落の文脈から「短い文字列（126バイトまで）の保存には、～」と「より長い文字列では～」の両者を指していると解釈できる。
この段落は型に限定した話ではなく、あくまでも文字列が内部的にどのように格納されるかについて言及している。
つまり「文字列は約1ギガバイトです」の「文字列」の型については明言していない。
しかし、text型は「文字列」であり、結果としてtext型の上限も「約1ギガバイト」と解釈できるが、イマイチ釈然としない。
ならば上記公式の冒頭「表8.4 文字型」表内textの説明にある制限なし可変長とは何なのか。
更に公式ドキュメントを確認すると以下の情報がある。
付録K PostgreSQLの制限
フィールドの大きさの上限は1 GBとある。結局こちらの制約に引っかかるということか。
同様の疑問として、bytea型の上限がある。ぐぐるとまず出てくるのが以下。
PostgreSQL_ Documentation_ 7.4_ Storing Binary Data
While a column of type bytea can hold up to 1 GB of binary data,
やはりこちらも 1GB なのかと、というのは早合点でこれはJDBCドライバの制限について言及している情報。
最新の公式を確認したがbytea型の上限について明言した情報は見つからず。次に見つけたのが以下。
Thread_ bytea size limit_ _ Postgres Professional
こちらのやり取りがとても合点がいくものだった。要点は以下。
-
bytea型は内部的にTOASTと呼ばれる技術が使われている -
TOASTで扱うことができるデータサイズの上限は 1GB
TOASTは以下の公式参照。
68.2. TOAST
そのため、すべてのTOAST可能なデータ型の値の論理サイズは1ギガバイト（230 - 1バイト）までになります。
text型も同様。列の上限が 1GB というのもこの技術仕様に依存しているっぽい。
更にPostgreSQLのソースコードを確認。オープンソースはこんな時に便利だね。
text型データを内部的に格納している箇所を追ってみたら以下に辿り着いた。
/*
* We require C99, hence the compiler should understand flexible array
* members. However, for documentation purposes we still consider it to be
* project style to write "field[FLEXIBLE_ARRAY_MEMBER]" not just "field[]".
* When computing the size of such an object, use "offsetof(struct s, f)"
* for portability. Don't use "offsetof(struct s, f[0])", as this doesn't
* work with MSVC and with C++ compilers.
*/
#define FLEXIBLE_ARRAY_MEMBER /* empty */
(中略)
struct varlena
{
char vl_len_[4]; /* Do not touch this field directly! */
char vl_dat[FLEXIBLE_ARRAY_MEMBER]; /* Data content is here */
};
#define VARHDRSZ ((int32) sizeof(int32))
/*
* These widely-used datatypes are just a varlena header and the data bytes.
* There is no terminating null or anything like that --- the data length is
* always VARSIZE_ANY_EXHDR(ptr).
*/
typedef struct varlena bytea;
typedef struct varlena text;
typedef struct varlena BpChar; /* blank-padded char, ie SQL char(n) */
typedef struct varlena VarChar; /* var-length char, ie SQL varchar(n) */
Data content is here（データ内容はこちら）のコメントがある通り、varlena構造体内のvl_dat[FLEXIBLE_ARRAY_MEMBER]がtext型データ（及び他の可変長データ）を格納する入れ物、実態らしい。
公式にも以下の解説があり、varlena、FLEXIBLE_ARRAY_MEMBERがキーワードの模様。
35.9. C言語関数
例えば、text型を定義するには、下記のように行えます。
typedef struct {
int32 length;
char data[FLEXIBLE_ARRAY_MEMBER];
} text;
[FLEXIBLE_ARRAY_MEMBER]表記は、データ部分の実際の長さはこの宣言では指定されないことを意味します。
TOASTをサポートするためには、データ型は可変長（varlena）表現を持たなければなりません。
しかし、改めて前述のコードを確認すると、#define FLEXIBLE_ARRAY_MEMBER /* empty */とある。
局所的に見ればchar vl_dat[FLEXIBLE_ARRAY_MEMBER];はchar vl_dat[];と展開されることになる。
あれ？ int buff[] = {0, 1, 2};のような初期値を伴う配列定義ならいざ知らず、サイズを指定しない定義なんて Native C（ANSI C）で通ったっけ？
更に調べてみるとこれはC99 の拡張とのこと。（というか上記ソースコードのコメントにもC99の文言があるし）
C言語の最新事情を知る： C99の仕様
C99では構造体の最後の要素が配列の場合に限って、サイズを省略できる（仕様では、「不完全配列（incomplete array）」とか、「長さ指定省略（unknown size）」と呼ばれている）。
C
typedef struct {
size_t size;
char data[];
} Packet;
リスト5 不完全配列を最後のメンバーに持った構造体
この場合最後のメンバーである「data」の大きさは「0」となる
へー。更に以下のサイトが参考になった。
PostgreSQL のコーディングスタイル
3.6 可変長構造体
構造体の最後のメンバーを配列として、構造体本体に続くメモリ領域にアクセスする手法がある。 この手法は PostgreSQL でも利用されている。
C99 は最後のメンバー変数が構造体の場合、要素数を省略した記法 int16 tail[]; を認めているが、C89 では要素数が 0 個の配列は許されていない。
そこで PostgreSQL では要素が 1 個だけ持つ配列を配置する。 この最後のメンバー配列の後には /* VARIABLE LENGTH ARRAY / というコメントを残すこと。 構造体定義の最後にも / VARIABLE LENGTH STRUCT */ というコメントを残すこと。
typedef struct T
{
/* other member */
int16 tail[1]; /* VARIABLE LENGTH ARRAY */
} T; /* VARIABLE LENGTH STRUCT */
別の方法として FLEXIBLE_ARRAY_MEMBER を使う方法がある。
typedef struct T
{
/* other member */
int16 tail[FLEXIBLE_ARRAY_MEMBER];
} T;
FLEXIBLE_ARRAY_MEMBER はその環境で適切な要素数が入る。 それは tail[1] かもしれないし、tail[0] かもしれないし、tail[] かもしれない。
へーへーへー。
だから公式にもtext型の説明で制限なし可変長とあるんだね。つまりこれは暗にC99の不完全配列を指していると。
実質的な上限は環境に依存すると。だからバックグラウンドテーブルに格納されます。 いずれの場合にあっても保存できる最長の文字列は約1ギガバイトです。であると。バックグラウンドテーブルはTOAST機能を指していると。
当初、公式のtext型の説明は釈然としなかったが、内部仕様が分かると合点がいく。
そうそう以下のサイトも有用だった。
PostgreSQLの文字列型の最大長は？
text型
同じくPostgreSQL 10で試したところ
1GiB – 53 byteまで
保存できました。以前のマニュアルには1GiBまで保存可能との記載があったように思います。現在のマニュアルにはtext型を含め、最大値の記載が無い（？）ようです。
ただし、E’abc\nxyz’のようにエスケープシークエンスを使った文字列を使う場合、保存できる最大文字列は1GiB – 53byteより短くなります。入力文字列のエスケープシークエンスを含み1GiB – 53byte以下でなければなりません。
実際に試す。これ大事。
今回の件に限った話でもないが、ネット情報って出典や根拠も示さず、単なる他所からの受け売りが多すぎる。
PostgreSQL 文字列連結演算子||の問題点
前述の通り、文字列連結演算子||で複数の文字列を連結する際、nullが含まれていると結果もnullになってしまう。
その回避策はconcat関数を使うこと。
mydb=# SELECT concat('PostgreSQL', null);
concat
------------
PostgreSQL
(1 row)
という解説はネット上でよく見かける。しかし実はそれ以外にも文字列連結演算子||とconcat関数に挙動の違いがある。
まずtext型で確認。
mydb=# SELECT 'Postgre '::text || 'SQL'::text, concat('Postgre '::text, 'SQL'::text);
?column? | concat
--------------+--------------
Postgre SQL | Postgre SQL
(1 row)
両者、後ろスペースを保持して連結で同じ結果。
次にvarchar型で確認。
mydb=# SELECT 'Postgre '::varchar(10) || 'SQL'::varchar(10), concat('Postgre '::varchar(10), 'SQL'::varchar(10));
?column? | concat
--------------+--------------
Postgre SQL | Postgre SQL
(1 row)
こちらも同様。
次にchar型で確認。
mydb=# SELECT 'Postgre '::char(10) || 'SQL'::char(10), concat('Postgre '::char(10), 'SQL'::char(10));
?column? | concat
------------+----------------------
PostgreSQL | Postgre SQL
(1 row)
あれれ、結果が異なるね。SQL Server、Oracleでの文字列連結演算子の挙動を確認。
1> SELECT CAST('SQL ' AS char(10)) + CAST('Server' AS char(10));
2> GO
--------------------
SQL Server
(1 行処理されました)
SQL> SELECT CAST('Ora ' AS char(10)) || CAST('cle' AS char(10)) FROM DUAL;
CAST('ORA'ASCHAR(10)
--------------------
Ora cle
どちらも後ろスペースを保持して連結される。
そもそも前述の通り、char型は後ろスペースも含めて取得される仕様なので、それを連結すればそうなるわな、というだけの話。
事実、PostgreSQLのconcat関数はそうなる。しかし何故||演算子はそうならないのか？
PostgreSQL 文字列連結演算子||の仕様
答えは公式にある。
10.2. 演算子
以下は型の指定がない2つの値の連結です。
SELECT 'abc' || 'def' AS "unspecified";
unspecified
-------------
abcdef
(1 row)
この場合、問い合わせ内に型が指定されていませんので、どの型を使用すべきかについての初期の指針がありません。 ですから、パーサは全ての演算子候補を検索し、文字列カテゴリとビット列カテゴリ入力を受け付ける候補を見つけます。 使用できる場合は文字列カテゴリが優先されますので、文字列カテゴリが選択され、それから文字列に対して優先される型であるtextが、不明のリテラルを解決する型として使用されます。
text || text → text
character値を他の文字列型に変換する際は、文字列の終わりの空白は除去されます。
まとめると以下。
-
||演算子で文字列はtextにキャストされる -
char型をtextにキャストすると後ろスペースは除去される。
つまり前述のクエリは以下と同等。
mydb=# SELECT 'Postgre '::char(10)::text || 'SQL'::char(10)::text;
?column?
------------
PostgreSQL
(1 row)
うーん、しかしこれは期待に反した動作では。char型の原則に反しているし。というか仕様上のバグと言ってもいいレベル。
そんな意味でも文字列の連結にはconcat関数を使用した方が無難ってことか。
暗黙の型変換の考察
冒頭のクエリSELECT 1 WHERE 'AA ' = 'AA';で、そもそも固定リテラル'AA 'の型は何として解釈されるのか？
という初歩的な疑問点が出発点だった。
そもそも一般的なコンピュータ言語の仕様として、異なる型の値の比較はできないし、DB でも同様。
しかし、現実問題として以下のクエリでエラーは発生せず正常に処理される。
mydb=# SELECT '1' = 1;
?column?
----------
t
(1 row)
よく実務でも、数値の0、1が格納される列に対してWHERE del_flag = 0と記述して、よくよく確認してみるとdel_flag列の定義は何故かchar(1)だったりして、数値しか格納されない仕様なのに何故文字列型で定義されているんだよと困惑することがあるよね。しかも上記の通りエラーにもならず、こんなんでも動いてしまうんだと妙に感心したりしてね。
勿論、厳密な型定義を導入し、型がちげーし、と弾くことは簡単だけど、DBエンジンさんは親切なので、可能な限り「多分こういうことだよね」と調整してくれる。それが暗黙の型変換、キャスト。
と前置きが長くなったけど上記クエリ、'1' = 1は、'1'が数値型に暗黙の型変換されているのか、1が文字列型'1'に暗黙の型変換されるのか？ あなたは答えられますか？
文字列を数値型に変換すると失敗する可能性がある。例えば'zz'は数値型に変換できない。
しかし、数値型は原理的に必ず文字列型に変換できる。前後を'（シングルクォーテーション）で囲めば文字列型だよね。
という理屈で言えば、数値を文字列型に変換する方が合理的に思える。実際のところはどうなのか。
まず、それぞれが何の型として解釈されるのか、pg_typeof関数で確認。
mydb=# SELECT pg_typeof('1'), pg_typeof(1);
pg_typeof | pg_typeof
-----------+-----------
unknown | integer
(1 row)
'1'はunknown（不明）、1はintegerと解釈されることが分かる。
比較演算子の左右で、一方の型が確定し、もう一方の型が不明の場合、確定している方に型変換される。
つまり、'1'がinteger型に型変換される。これは公式の解説にも明記されている。
10.2. 演算子
a. 二項演算子の1つの引数がunknown型であった場合、この検査のもう片方の引数と同一の型であると仮定します。
以下の単純なクエリでもそれは確認できる。
mydb=# SELECT '0.1' = 1;
ERROR: invalid input syntax for type integer: "0.1"
LINE 1: SELECT '0.1' = 1;
文字列を'0.1'をinteger型への暗黙の型変換を試みて失敗している。
前述の通り、数値を文字列型へ型変換する場合は失敗しない筈であり、明示的にそうすれば正常に動作する。
mydb=# SELECT '0.1' = 1::text;
?column?
----------
f
(1 row)
しかし暗黙の型変換ではそうはしてくれないと。単純にこれは仕様だから仕方がない。
次の素朴な疑問は何故'1'がunknownなのか。'（シングルクォーテーション）で囲まれている以上、文字列であることは自明ではないのか。それは以下のような記述もあり得るからだと考えられる。
'\xDEADBEEF''1999-01-08 04:05:06'
前者はバイナリ列データ型の書式であり、後者はタイムスタンプ型の書式。（公式のそれぞれの型の説明サンプルから引用したもの）
つまり前者は単なる文字列ではなくバイナリ列データ型であることが示唆されているし、後者はタイムスタンプ型であることが示唆されている。
しかし、pg_typeof関数で確認するとやはりunknown。
mydb=# SELECT pg_typeof('\xDEADBEEF'), pg_typeof('1999-01-08 04:05:06');
pg_typeof | pg_typeof
-----------+-----------
unknown | unknown
(1 row)
これはどこまでいっても単独ではどの型が断定できないから。単なる文字列の可能性も捨てきれない。
前述の通り、等号演算子で使用されれば、もう一方の型に合わせられる。
-- 左辺はタイムスタンプ型なので右辺もタイムスタンプ型に暗黙変換される
mydb=# SELECT now() = '1999-01-08 04:05:06';
?column?
----------
f
(1 row)
-- 左辺はtext型なので右辺もtext型に暗黙変換される
mydb=# SELECT '1999-01-08 04:05:06'::text = '1999-01-08 04:05:06';
?column?
----------
t
(1 row)
INSERT文やUPDATE文の代入値に使用される場合なら、対応する列の型として解釈され型変換される。
暗黙の型変換に関してはこの程度の認識があれば事足りるかな？ これ以上の詳細は公式参照ということで。
（何か思いついたら追記予定）