pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedDocumenting more pitfalls of non-default collations?
3+ messages / 2 participants
[nested] [flat]
* Documenting more pitfalls of non-default collations?
@ 2024-06-11 06:55 Will Mortensen <will@extrahop.com>
0 siblings, 1 reply; 3+ messages in thread
From: Will Mortensen @ 2024-06-11 06:55 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org, Jeremy Schneider <schneider@ardentperf.com>; +Cc: Daniel Verite <daniel@manitou-mail.org>; Jeff Davis <pgsql@j-davis.com>
Hi there,
I mentioned to Jeremy at pgConf.dev that using non-default collations
in some SQL idioms can produce undesired results, and he asked me to
send an email. An example idiom is the way Django implements
case-insensitive comparisons using "upper(x) = upper(y)" [1][2][3] ,
which returns false if x = y but they have different collations that
produce different uppercase.
For example, assuming the default collation performs standard Unicode
case mapping:
# select upper('i') = upper('i' collate "tr-x-icu");
f
# select upper('é') = upper('é' collate "C");
f
Or with collations in DDL instead:
# create table t (
tr text collate "tr-x-icu",
c text collate "C"
);
CREATE TABLE
# insert into t values ('i', 'é');
INSERT 0 1
# select count(*) from t where upper('i') = upper(tr);
0
# select count(*) from t where upper('é') = upper(c);
0
This is expected, given a careful reading of the collation docs, but
it's not really highlighted in any of the examples--in each example
that doesn't produce an error, all of the collation-sensitive
functions/operators end up applying the same collation. Maybe there
should be an example that applies different collations in different
subexpressions, and/or a warning against constructions like "upper(x)
= upper(y)"?
[1] https://github.com/django/django/blame/stable/5.1.x/django/db/backends/postgresql/operations.py#L175
[2] https://github.com/django/django/blame/stable/5.1.x/django/db/backends/postgresql/base.py#L155
[3] https://code.djangoproject.com/ticket/32485
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Documenting more pitfalls of non-default collations?
@ 2024-06-11 17:58 Jeff Davis <pgsql@j-davis.com>
parent: Will Mortensen <will@extrahop.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jeff Davis @ 2024-06-11 17:58 UTC (permalink / raw)
To: Will Mortensen <will@extrahop.com>; pgsql-docs@lists.postgresql.org, Jeremy Schneider <schneider@ardentperf.com>; +Cc: Daniel Verite <daniel@manitou-mail.org>
On Mon, 2024-06-10 at 23:55 -0700, Will Mortensen wrote:
> I mentioned to Jeremy at pgConf.dev that using non-default collations
> in some SQL idioms can produce undesired results, and he asked me to
> send an email. An example idiom is the way Django implements
> case-insensitive comparisons using "upper(x) = upper(y)" [1][2][3] ,
> which returns false if x = y but they have different collations that
> produce different uppercase.
Hi,
Thank you for the examples.
There are quite a few subtleties to getting case-insensitive
comparisons right, and neither LOWER() nor UPPER() get everything quite
right even if the collation is the same.
For instance (for almost any locale other than "C"):
UPPER(LOWER(U&'\1E9E')) != UPPER(U&'\1E9E')
And:
LOWER(UPPER(U&'\03C2')) != LOWER(U&'\03C2')
The results of UPPER() and LOWER() can also change if some language
adds a new case variant in the future, which could be a problem if the
results are stored somewhere.
How should we document all of that? If we include too many caveats,
it's just frustrating.
Instead, I propose that we implement Unicode "case folding" in PG18,
which solves these issues by transforming the string to a canonical
form suitable for case-insensitive comparison. (In most cases, the
results are the same as LOWER(), but there are exceptions specifically
to avoid the problems above.)
Then, we can just have a section in the docs on "case folding" to
describe the right way to use it. That still leaves one caveat: the
handling of dotted- and dotless-i. But one caveat is a lot easier to
keep track of.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Documenting more pitfalls of non-default collations?
@ 2024-06-12 01:20 Will Mortensen <will@extrahop.com>
parent: Jeff Davis <pgsql@j-davis.com>
0 siblings, 0 replies; 3+ messages in thread
From: Will Mortensen @ 2024-06-12 01:20 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: pgsql-docs@lists.postgresql.org, Jeremy Schneider <schneider@ardentperf.com>; Daniel Verite <daniel@manitou-mail.org>
Hi Jeff,
Unicode case folding would be great!
I wonder if there might still be situations where multiple collations
are applied within the same expression/statement and this produces a
surprising result. So far I'm mostly coming up with examples that seem
more contrived or less surprising, but maybe one would still be
informative?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-06-12 01:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-11 06:55 Documenting more pitfalls of non-default collations? Will Mortensen <will@extrahop.com>
2024-06-11 17:58 ` Jeff Davis <pgsql@j-davis.com>
2024-06-12 01:20 ` Will Mortensen <will@extrahop.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox