照合順序の制限と動作の違い - Amazon Aurora

照合順序の制限と動作の違い

Babelfish は ICU ライブラリを使用して照合をサポートします。PostgreSQL は特定のバージョンの ICU で構築されており、照合の 1 つのバージョンにだけ一致させることができます。バージョン間のバリエーションは避けられません。言語は時間と共に進化するため、マイナーなバリエーションも同様です。次のセクションでは、Babelfish 照合順序の既知制限と動作のバリエーションをいくつか示しています。

  • インデックスおよび照合順序タイプ依存関係 – International Components for Unicode (ICU) 照合ライブラリ (Babelfish が使用するライブラリ) に依存するユーザー定義型のインデックスは、ライブラリのバージョンを変更しても無効になりません。

  • 照合関数 – 照合プロパティは、サポートされている Babelfish BBF 照合に対してのみ実装されます。詳細については、「Babelfish supported collations table」を参照してください。

  • Unicode ソートルールの違い — SQL Server の SQL 照合順序は、Unicode エンコードされたデータ (ncharそしてnvarchar) を、Unicode でエンコードされていないデータ (charそしてvarchar) とは異なるやり方でソートします。Babelfish データベースは常に UTF-8 でエンコードされ、データ型に関係なく Unicode ソートルールを常に適用します。したがって、char または varchar のソート順は、nchar または nvarchar と同じです。

  • 二次等照合順序およびソート動作 - デフォルトの ICU Unicode 二次等 (CI_AS) 照合では、句読点やその他の英数字以外の文字を数字の前にソートし、英字の前に数字をソートします。ただし、句読点やその他の特殊文字の順序は異なります。

  • 三次等照合順序、ORDER BY の回避策 - SQL_Latin1_General_Pref_CP1_CI_AS などのSQL 照合順序は TERTIARY_WEIGHTS 関数 をサポートし、CI_AS 照合で等しく比較される文字列が初期に大文字でソートされる機能をサポートします: ABC、ABc、AbC、Abc、aBC、aBc、abC、そして最後に abc。よって、DENSE_RANK OVER (ORDER BY column) 分析関数は、これらの文字列を同じランクとして評価しますが、パーティション内でまず大文字で並べ替えます。

    @colCaseFirst=upper を指定する三次 CS_AS 照合 を明記した ORDER BY 句に COLLATE 句を追加することで、Babelfish でも同様の結果が得られます。ただし、colCaseFirst 修飾子は 3 次と等しい (CI_AS 照合順序のような二次等照合順序ではなく) 文字列にのみ適用されます。したがって、1 つの ICU 照合を使用して 3 次 SQL 照合をエミュレートすることはできません。

    回避策として、BBF_SQL_Latin1_General_CP1_CI_AS 照合順序を使う前に SQL_Latin1_General_Pref_CP1_CI_AS 照合を使用するアプリケーションを変更することをお勧めします。その後、このカラムの ORDER BY 句に COLLATE BBF_SQL_Latin1_General_Pref_CP1_CS_AS を追加します。

  • 文字拡張 - 文字拡張では、1 文字を 1 次レベルの文字シーケンスと等しく扱います。SQL Server のデフォルト CI_AS 照合では、文字拡張がサポートされています。ICU 照合では、アクセントが区別されない照合順序に対してのみ文字拡張がサポートされます。

    文字拡張が必要な場合は、AI 照合を使って比較してください。ただし、このような照合は LIKE 演算子では現在サポートされていません。

  • char および varchar エンコード - SQL で始まる照合を char または varchar データ型に使用する場合、ASCII 127 より前の文字のソート順は、その SQL 照合の特定のコードページによって決まります。SQL 照合では、char または varchar として宣言された文字列は、nchar または nvarchar として宣言された文字列とは異なるソートされることがあります。

    PostgreSQL はすべての文字列をデータベースエンコーディングでエンコードするため、すべての文字を UTF-8 に変換し、Unicode ルールを使用してソートします。

    SQL 照合では Unicode ルールを使用して nchar データ型と nvarchar データ型がソートされるため、Babelfish はサーバー上のすべての文字列を UTF-8 を使用してエンコードします。Babelfish は、Unicode ルールを使用して char 文字列と varchar 文字列をソートするのと同じ方法で、 nchar 文字列と nvarchar 文字列をソートします。

  • 補足文字 - SQL Server 関数 NCHAR、UNICODE、および LEN は Unicode 基本多言語平面 (BMP) 外のコードポイントの文字をサポートします。対照的に、SC 以外の照合では、サロゲートペア文字を使用して補足文字を処理します。Unicode データ型の場合、SQL Server は UCS-2 を使用して最大 65,535 文字を表すことができます。補足文字を使用する場合は Unicode の全範囲 (1,114,114 文字) を表すことができます。

  • かなを区別する (KS) 照合順序 — かなを区別する (KS) 照合とは、Hiragana と Katakana 日本語のかな文字を異なるやり方で処理する照合です。ICU は日本語の照合スタンダードJIS X 4061 をサポートしています。現在非推奨される colhiraganaQ [on | off] ロケール修飾子は KS 照合と同じ機能を提供する場合があります。ただし、SQL Server と同じ名前の KS 照合は現在 Babelfish でサポートされていません。

  • 幅を区別する (WS) 照合順序 - 同じ文字が 半角文字 (半角) と 全角文字 (全角) で別扱いされる場合、照合は幅を区別する (WS) と呼ばれます。SQL Server と同じ名前の WS 照合は、現在 Babelfish でサポートされていません。

  • バリエーションセレクタを区別する (VSS) 照合 — バリエーションセレクタを区別する（VSS）照合では、日本語の照合順序 Japanese_Bushu_Kakusu_140 と Japanese_XJIS_140 における表意文字バリエーションセレクタを区別します。バリエーションシーケンスは、基本文字と追加のバリエーションセレクタで構成されます。_VSS オプションを選択しない場合、バリエーションセレクタは比較には考慮されません。

    VSS 照合は、現在 Babelfish はサポートされていません。

  • BIN および BIN2 照合 – BIN2 照合では、コードポイントの順序に従って文字がソートされます。UTF-8 のバイト単位のバイナリ順序は Unicode コードポイントの順序を保持するため、これが最もパフォーマンスの高い照合になる可能性もあります。Unicode コードポイントの順序がアプリケーションで機能する場合は、BIN2 照合の使用を検討してください。ただし、BIN2 照合を使用すると、文化的に予期しない順序でデータがクライアントに表示されることがあります。小文字への新しいマッピングは時間の経過とともに Unicode に追加されるため、ICU のバージョンによって、LOWER 関数の動作が異なる場合があります。これは BIN2 照合に固有なケースではなく、より一般的な照合バージョニング管理の特殊な不具合ケースです。

    Babelfish は Unicode コードポイント順に照合すため、 Babelfish ディストリビューションで BBF_Latin1_General_BIN2 照合を提供します。BIN 照合では、初期の文字だけが wchar としてソートされます。残りの文字はバイト単位でソートされ、エンコーディングに従ってコードポイント順に効率的にソートされます。このアプローチは Unicode 照合ルールに従わず、Babelfish ではサポートされていません。

  • 非決定的照合順序と CHARINDEX の制限 — バージョン 2.1.0 より古いバージョンの Babelfish では、非決定的照合で CHARINDEX を使用することはできません。デフォルトでは、Babelfish は大文字と小文字を区別しない (非決定的) 照合を使用します。古いバージョンの Babelfish で CHARINDEX を使用すると、次のランタイムエラーが発生します。

    nondeterministic collations are not supported for substring searches
    注記

    この制限と回避策は、Babelfish バージョン 1.x (Aurora PostgreSQL 13.x バージョン) にのみ適用されます。Babelfish 2.1.0 以降のリリースでは、この問題は発生しません。

    この問題は、次のいずれかの方法で回避できます。

    • 式を大文字と小文字を区別する照合に明示的に変換し、LOWER または UPPER を適用して両方の引数で大文字と小文字を区別します。例えば、SELECT charindex('x', a) FROM t1 は次のようになります。

      SELECT charindex(LOWER('x'), LOWER(a COLLATE sql_latin1_general_cp1_cs_as)) FROM t1

    • SQL 関数 f_charindex を作成し、CHARINDEX 呼び出しを次の関数の呼び出しに置き換えます。

      CREATE function f_charindex(@s1 varchar(max), @s2 varchar(max)) RETURNS int
AS
BEGIN
declare @i int = 1
WHILE len(@s2) >= len(@s1)
BEGIN
  if LOWER(@s1) = LOWER(substring(@s2,1,len(@s1))) return @i
  set @i += 1
  set @s2 = substring(@s2,2,999999999)
END
return 0
END
go