SAP SQL Anywhere における LIKE 述語セマンティクスと空白埋めデータベース （過去のブログより）

このページは、以下の英語ページの抄訳です。最新の情報については、英語ページを参照してください。

https://blogs.sap.com/2014/05/14/like-predicate-semantics-with-blank-padded-databases/

この記事のオリジナルは、Glenn Paulley が sybase.com に 2009 年 8 月に掲載したものです。その中で、Glenn は SQL Anywhere における空白埋め文字列と、それらが LIKE 述語に関してどのように動作するのか解説しています。

標準 SQL:2008 では、固定長文字列の値は、空白埋めされます。空白埋めは、オリジナルのサイズが、宣言されたカラムの幅よりも少ない場合、固定長文字列値の格納時に起こります。また、空白埋めは、固定長文字列が文字列比較述語を使用して比較される場合にも起こります。

ディスクへの格納について、カラム T の V 値の格納の動作を定義するセクション 2 (ストアの割り当て) のゼネラルルール 2 (b) (iii) からの関連部分を ISO 文書より以下に引用します。

もし、宣言された T の型が固定長文字列で、数値 L が長さで、V の文字長 M が L よりも少ない場合、T の先頭 M 文字は、V に設定され、それ以降 L 文字目まではスペースに設定されます。

しかしながら、SQL Anywhere は固定長文字列を空白埋めしません。SQL Anywhere データベースでは、全ての文字列はカラムが VARCHAR 型であるかのように格納されます。これは、文字列値内の全ての空白が、重要な文字として扱われることを意味しています。そのため、値 ‘a ‘ (次が空白の文字 ‘a’ ) は、単一の文字列 ‘a’ とイコールにはなりません。不等の比較も空白を照合の中の他の文字として扱います。

SQL Anywhere では、ANSI SQL 文字列比較動作を模倣する機能を「空白埋め」オプションで提供しており、dbinit ユーティリティまたはCREATE DATABASE 文で設定することが可能です。空白埋めオプションを有効にすると、文字列内の後端空白は、比較された場合無視されます。後端空白を無視するのは、同等と不等(“!=”)のオペレーションにおいて空白埋めと同一のセマンティクスを持ちます。しかしながら、この動作は、より少ない (“<“) のような他の比較オペレーションのための空白埋め動作では同一ではありません。

空白埋めありと空白埋めなしでのLIKE句の動作

SQL Anywhere におけるLIKEパターンの動作は、パターンへの表現のマッチングが、一文字ずつ（または、UTF8 データベースの場合、コードポイントずつ） 比較を左から右への形で含めていくためにデータベースが空白埋めされていても変化しません。動作中、追加の空白埋め（または切り捨て）は、LIKEの表記またはパターンの値の上では実行されません。そのため、表記 “a1” は、パターン “a1” とマッチしますが、パターン “a1 ” (“a1” と空白) または “a1_”とはマッチしません。これらの動作の違いは、表記またはパターンに後端空白が含まれる場合には必ず、また、他のリレーショナル DBMS 製品との違いの説明について後ほど見られるように、すべてのケースで旧版との互換性を考慮した動作を保持していることにより発生します。

LIKE句例

これの説明にあたり、John Smirnios の以下の分析に感謝します。以下のように、4つの文字列カラムで構成されるテーブルT を定義します。

CREATE TABLE T( a CHAR(1), b CHAR(2), c CHAR(3), d VARCHAR(10) );

そして、テーブル T に2 つのローを挿入します。最初のローは、単一の文字 ‘a’ をそれぞれのカラムに、そして、2 番目のローは、それぞれのカラムに、値 ‘a ‘ ( ‘a’ の後に空白) を入れます。

INSERT INTO T VALUES( 'a',  'a',  'a',  'a' );
INSERT INTO T VALUES( 'a ', 'a ', 'a ', 'a ' );

様々なデータベースシステムにわたって12 種類のテストを行ったところ、以下のような結果になりました。

追記:

1. ASE と Microsoft SQL Server 2005 は常に全後端空白をVARCHAR値の終わりから切り捨てします。これら 2 つのシステムでは、VARCHAR 文字列末に空白を格納するのは不可能です。
2. IQ では、述語 “d LIKE ‘a ‘” がロー 1と 2 の両方を返しても、述語 “d LIKE ‘a[ ]’” は、ロー 2を返します。
3. ASE では、述語 “d LIKE ‘a ‘” がロー 1 と 2 の両方を返しても、“d LIKE ‘a[ ]’” は、ローは返しません。

考察

SQL Anywhere では、空白埋めされているデータベースでも、全ての文字列をVARCHARとして扱います。VARCHAR文字列では、SQL Anywhere の動作は DB2、Oracle、そして ANSI/ISO 標準 SQL (テストケース 4、8、12 ) とマッチします。

DB2 と Oracle は、同一の動作です。固定長文字カラムは、常に定義長に埋められ、文字列の長さは、常にカラムの幅と同一です。文字列末は、パターン末とマッチしなければなりません。VARCHAR フィールドは、ユーザーによって挿入されたあらゆる後端空白を保ちます。空白のVARCHARフィールドへの追加、VARCHARフィールドからの切り捨ては決してありません。

ASE では、パターン文字列から後端空白を切り捨てします。しかしながら、「空白に相当する」表現は切り捨てしません (追記 3を参照)。しかしながら、ASE は、1 つまたはそれ以上の空白が続く‘%’で終わるパターンの場合、単一の後端空白を維持します。この特殊な表現は、ASE のマニュアルのSQL ユーザーズガイドに記述されています。また、ASE は、マッチした値からの後端空白を効果的に切り捨てし、十分な空白でCHARカラムを再埋めし、パターンとマッチさせます　(しかし、カラムの幅を超えるには十分ではありません)。VARCHARとマッチする値には、空白は事前に切り捨てされ (追記 1参照) 、空白は決して追加されることはなく、マッチングも発生しません。空白と同等のもの (‘[ ]’) で終わるパターンは、VARCHAR文字列とマッチすることは決してありません。

Microsoft SQL Server 2005 は、後端空白をパターンから切り捨てしません。しかしながら、ASE のように、SQL Server はマッチした値から後端空白を切り捨てし、十分な空白 でCHARカラムを再度埋め、パターンにマッチさせます。しかし、カラムの幅を超えるには十分ではありません。空白は、決してVARCHARに付加されず、マッチすることがありません。

私の同僚である John Smirnios のSQL Anywhere とこれら他のデータベース管理システムにおける LIKEセマンティクスに関する綿密な分析に感謝します。