Does the POSITION() function takes into account the COLLATION... or not ?!?

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: pageorge(at)unice(dot)fr
Subject: Does the POSITION() function takes into account the COLLATION... or not ?!?
Date: 2022-02-15 16:17:53
Message-ID: 164494187300.23318.373331246819207718@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/14/functions-string.html
Description:

The doc doesn't say anything about the impact of the COLLATION on the
results of any of the string functions, so I tried some interesting tests
with POSITION():

SELECT POSITION(('ß' COLLATE "de_DE.utf8") IN 'DASS'); -- should return 3
instead of 0 !?!
SELECT POSITION(('ss' COLLATE "de_DE.utf8") IN 'daß'); -- should return 3
instead of 0 !?!

SELECT POSITION(('oe' COLLATE "fr_FR.utf8") IN 'bœuf'); -- should return 2
instead of 0 !?!
SELECT POSITION(('œ' COLLATE "fr_FR.utf8") IN 'boeuf'); -- should return 2
instead of 0 !?!

SELECT POSITION(('å' COLLATE "en_US.utf8") IN 'yeah'); -- should return 3
instead of 0 !?!
SELECT POSITION(('o' COLLATE "en_US.utf8") IN 'ångström'); -- should return
7 instead of 0 !?!

==> up to here, this seems pretty enough to conclude that POSITION() doesn't
care at all about COLLATION and always perform a byte search.

Now the great surprise comes !...

CREATE COLLATION public.case_and_accent_insensitive (
provider = icu,
locale = 'und-u-ks-level1',
deterministic = false
);
SELECT POSITION(('o' COLLATE public.case_and_accent_insensitive) IN
'ångström');

=> gives ERROR: "nondeterministic collations are not supported for substring
searches"

Does the POSITION() function pretends taking into account the COLLATION ??
or not ??

- If not, then why the hell is there this error message about
nondeterministic collations while the POSITION() doesn't care at all about
the COLLATION...
- If yes, then the first 6 lines of SQL above are returning the wrong
value... (are there any specific technical limitations here ?)

I would like to have something in the doc about that... i.e. either some
examples showing how the COLLATION is impacting the results of the
POSITION() function ; or a statement which confirms that the POSITION()
function doesn't care at all about the COLLATION (+ the advice to
systematically add ...COLLATE "C"... on one of the POSITION()'s arguments in
order to avoid the dumb "nondeterministic collations are not supported"
error, while in fine no collations at all are supported)

Thanks.

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Troy Frericks 2022-02-15 18:50:05 Re: Data Type Size Calculation
Previous Message David G. Johnston 2022-02-15 15:10:00 Re: Doc says nothing about wether CREATE OR REPLACE VIEW can change the COLLATION of an existing field