以下のようなSQLを書いてみると、OutSystems11では見慣れないエラーが出たので、原因を探ってみる。Task Entityは、Windowsでいうと、「C:\Program Files\OutSystems\ODC Studio\ODC Studio\TutorialResources」に配置されているTasks.xlsxをインポートして作ったもの。
SELECT [Id],[Description],[DueDate],[IsActive] FROM {Task}
WHERE {Task}.[Description] LIKE '%a%'
エラー内容は以下の通り
0A000: The query uses a pattern-matching operator (LIKE, ILIKE, SIMILAR, REPLACE, POSITION, REGEXP_MATCHES, REGEXP_REPLACE, REGEXP_SPLIT_TO_ARRAY, REGEXP_SPLIT_TO_TABLE) with nondeterministic collations, in which these operators are not supported.
Use the caseaccent_normalize function provided by OutSystems to overcome this limitation..
環境情報
ODC Studio(Version 1.3.15)
エラーメッセージで検索 → ドキュメントがヒット
エラーメッセージから抜き出した「The query uses a pattern-matching operator with nondeterministic collations, in which these operators are not supported.」というテキストで検索すると、以下のドキュメントが見つかった。
SQL queries compared to OutSystems 11
（日本語訳もあるドキュメントだが、一部文字化けがあったので英語版ページの方をリンク。英語版の右上にあるドロップダウンリストから日本語版として開き直すことも可能。以下で引用が日本語の場合、この方法で開いたページから引用している）
ドキュメントから：パターンマッチング演算子をつかうときはcaseaccent_normalize関数が必要
パターンマッチング演算子はそのままではサポートされない
パターンマッチング演算子（pattern-matching operator）は、最初のエラーメッセージによると、「LIKE, ILIKE, SIMILAR, REPLACE, POSITION, REGEXP_MATCHES, REGEXP_REPLACE, REGEXP_SPLIT_TO_ARRAY, REGEXP_SPLIT_TO_TABLE」らしい。
ドキュメントによると、
パターンマッチング演算子はサポートされていないため、実行時エラーになります。たとえば、「DataBaseException Error in advanced query SQL1 (...) with nondeterministic collations, in which these operators are not supported.」のようなエラーが発生します。
と書かれているので発生しているエラーメッセージと合致している。
パターンマッチング演算子を適用するText型にはcaseaccent_normalize関数を適用する
非決定論的な照合順序 (nondeterministic collations) を適用する列には「caseaccent_normalize(列名 collate "default")」、リテラル値には「caseaccent_normalize('%リテラル値')」のように指定する。
ODCでは全ての列（恐らくText型のみ）は非決定論的照合順序
ドキュメントに以下の通り記述されている。
ODCでは、デフォルトで非決定論的な照合順序が個々のレベルおよびODCが作成するすべての列に定義されています。
PostgreSQLの非決定論的照合順序
ODCで使っているバージョンを確認する
SQLを配置して、以下のクエリを「Test SQL」してみる。
SELECT version()
13.9だった。
PostgreSQLバージョン13のドキュメントを確認
PostgreSQL 13.1文書 > 第23章 多言語対応 > 23.2. 照合順序サポートの「23.2.2.4. 非決定論的な照合順序」より、
照合順序は決定論的もしくは非決定論的のどちらかです。 決定論的な照合順序は決定論的な比較を使用します。 つまり、同じバイト列で構成される場合に限り等しい文字列とみなします。 非決定論的な比較は、異なるバイト値で構成される文字列の場合でさえ文字列が等しいと判定するかもしれません。 一般的な状況では、大文字小文字を区別しない比較、アクセントを区別しない比較および異なるUnicode正規化形式による文字列の比較が含まれます。
ODCのドキュメントによるとデフォルトで非決定論的な照合順序が各属性に適用されるらしいので、大文字小文字を区別しない比較がされることになる。
別のドキュメント9.7. Pattern Matchingに、
The pattern matching operators of all three kinds do not support nondeterministic collations. If required, apply a different collation to the expression to work around this limitation.
と書かれており、
- パターンマッチング演算子は、非決定論的な照合順序をサポートしない
- パターンマッチング演算子が必要となるときは、異なる照合順序を適用して制限を回避する
とされている。よってこの問題はODCだけの問題ではなく、PostgreSQLに対して、非決定論的照合順序を適用するパターンマッチング演算子を使うときの一般的な問題ということだろう。
ただ、caseaccent_normalizeで検索すると、OutSystemsのページしか出てこないので、これ自体はODCで定義した関数なのかもしれない。
解決策
ODCのドキュメントにある通り、caseaccent_normalize関数を使えば良い。
SELECT [Id],[Description],[DueDate],[IsActive] FROM {Task}
WHERE {Task}.[Description] LIKE '%a%'
SELECT [Id],[Description],[DueDate],[IsActive] FROM {Task}
WHERE caseaccent_normalize({Task}.[Description] collate "default") LIKE caseaccent_normalize('%a%')