From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
cc: =?UTF-8?Q?Juan_Jos=c3=a9_Santamar=c3=ada_Flecha?=
 <juanjo.santamaria@gmail.com>,
        Alvaro Herrera <alvherre@2ndquadrant.com>,
        Arthur Zakirov <zaartur@gmail.com>,
        Tomas Vondra <tomas.vondra@2ndquadrant.com>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Allow to_date() and to_timestamp() to accept localized names
In-reply-to: <aa5d8d96-0eb6-07b9-2caf-0910b313cb56@2ndquadrant.com>
References: <20200124161814.GA8989@alvherre.pgsql>
 <32487.1579882979@sss.pgh.pa.us>
 <2f83150e-a2c0-6318-3125-d9b86e421aa2@2ndquadrant.com>
 <964.1579884408@sss.pgh.pa.us>
 <CAC+AXB1+dHo_DhCTySrqoVaq9WUta9uA+2Ens-bEqDw8X4UbTA@mail.gmail.com>
 <ba83b5a7-e63e-5aaf-ac29-745a549f9d7d@2ndquadrant.com>
 <aa5d8d96-0eb6-07b9-2caf-0910b313cb56@2ndquadrant.com>
Comments: In-reply-to Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
	message dated "Sat, 25 Jan 2020 10:31:42 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <7211.1580177517.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 27 Jan 2020 21:11:57 -0500
Message-ID: <7212.1580177517@sss.pgh.pa.us>
Precedence: bulk

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> For the record, the correct form of that would appear to be
> select to_date('=CE=99=CE=B1=CE=BD=CE=BF=CF=85=CE=AC=CF=81=CE=B9=CE=BF=CF=
=82', 'TMMonth');
> with the accent.  I had tried different variations of that and they all =

> failed.

OK, so for anyone who is as confused as I was, the main point here
seems to be this: the upper case form of Greek sigma is '=CE=A3',
and the lower case form is '=CF=83' ... except as the final letter of
a word, where it is supposed to be written like '=CF=82'.

If I set lc_collate, lc_ctype, and lc_time to 'el_GR.utf8',
then (on a somewhat hoary glibc platform) I get

u8=3D# select to_char('2020-01-01'::timestamptz, 'TMMONTH'); =

       to_char        =

----------------------
 =CE=99=CE=91=CE=9D=CE=9F=CE=A5=CE=86=CE=A1=CE=99=CE=9F=CE=A3
(1 row)

u8=3D# select to_char('2020-01-01'::timestamptz, 'TMMonth');
       to_char        =

----------------------
 =CE=99=CE=B1=CE=BD=CE=BF=CF=85=CE=AC=CF=81=CE=B9=CE=BF=CF=82
(1 row)

u8=3D# select to_char('2020-01-01'::timestamptz, 'TMmonth');
       to_char        =

----------------------
 =CE=B9=CE=B1=CE=BD=CE=BF=CF=85=CE=AC=CF=81=CE=B9=CE=BF=CF=82
(1 row)

which is correct AFAICS ... but

u8=3D# select lower(to_char('2020-01-01'::timestamptz, 'TMMONTH'));
        lower         =

----------------------
 =CE=B9=CE=B1=CE=BD=CE=BF=CF=85=CE=AC=CF=81=CE=B9=CE=BF=CF=83
(1 row)

So what we actually have here, ISTM, is a bug in lower() not to_char().
The bug is unsurprising because str_tolower() simply applies towlower_l()
to each character independently, so there's no way for it to account for
the word-final rule.  I'm not aware that glibc provides any API whereby
that could be done correctly.  On the other hand, we get it right when
using an ICU collation for lower():

u8=3D# select lower(to_char('2020-01-01'::timestamptz, 'TMMONTH') collate =
"el-gr-x-icu");
        lower         =

----------------------
 =CE=B9=CE=B1=CE=BD=CE=BF=CF=85=CE=AC=CF=81=CE=B9=CE=BF=CF=82
(1 row)

because that code path passes the whole string to ICU at once, and
of course getting this right is ICU's entire job.

I haven't double-checked, but I imagine that the reason that to_char
gets the month name case-folding right is that what comes out of
strftime(..."%B"...) is "=CE=99=CE=B1=CE=BD=CE=BF=CF=85=CE=AC=CF=81=CE=B9=CE=
=BF=CF=82" which we are able to upcase
correctly, while the downcasing code paths don't affect '=CF=82'.

I thought for a little bit about trying to dodge this issue in the
patch by folding to upper case, not lower, before comparing month/day
names.  I fear that that would just shift the problem cases to some
other language(s).  However, it makes Greek better, and I think it
makes German better (does '=C3=9F' appear in any month/day names there?),
so maybe we should just roll with that.  In the end, it doesn't seem
right to reject this patch just because lower() is broken on some
platforms.

The other question your example raises is whether we should be trying
to de-accent before comparison, ie was it right for '=CE=99=CE=B1=CE=BD=CE=
=BF=CF=85=CE=AC=CF=81=CE=B9=CE=BF=CF=82' to
be treated differently from '=CE=99=CE=B1=CE=BD=CE=BF=CF=85=CE=B1=CF=81=CE=
=B9=CE=BF=CF=82'.  I don't know enough Greek
to say, but it kind of feels like that should be outside to_date's
purview.

			regards, tom lane