Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1irlZR-0006og-A3 for pgsql-bugs@arkaria.postgresql.org; Wed, 15 Jan 2020 16:31:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1irlZP-0005Dn-MV for pgsql-bugs@arkaria.postgresql.org; Wed, 15 Jan 2020 16:31:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1irlZP-0005Dg-An for pgsql-bugs@lists.postgresql.org; Wed, 15 Jan 2020 16:31:03 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1irlZJ-0006MG-2U for pgsql-bugs@lists.postgresql.org; Wed, 15 Jan 2020 16:31:01 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 00FGUsAt008017; Wed, 15 Jan 2020 11:30:54 -0500 From: Tom Lane To: im-scooter@yandex.ru cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16207: localization functions upper() and lower() does not work for text returned by convert_from() In-reply-to: <16207-5e69d1fb3d4035f2@postgresql.org> References: <16207-5e69d1fb3d4035f2@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Wed, 15 Jan 2020 15:41:41 +0000" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <8015.1579105854.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Wed, 15 Jan 2020 11:30:54 -0500 Message-ID: <8016.1579105854@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form writes: > Localization functions upper() and lower() does not work for text return= ed > by convert_from() for cyrillic symbols. > select upper(convert_from(convert_to('=D0=B0=D0=B1=D0=B2', 'utf8'), 'utf= 8')) =3D > upper('=D0=B0=D0=B1=D0=B2'); -- return false That happens because upper() depends on the collation that the parser determines for its input expression. When you write just upper('=D0=B0=D0= =B1=D0=B2') you get the database's default collation attached to the literal, since it's implicitly typed as text. However the convert_from() invocation ends up with collation "C", in which non-ASCII characters aren't going to be changed by upper(). You can fix it by assigning another collation explicitly, e.g. # select upper(convert_from(convert_to('=D0=B0=D0=B1=D0=B2', 'utf8'), 'utf= 8')); upper = -------- =D0=B0=D0=B1=D0=B2 (1 row) # select upper(convert_from(convert_to('=D0=B0=D0=B1=D0=B2', 'utf8'), 'utf= 8') collate "default"); upper = -------- =D0=90=D0=91=D0=92 (1 row) > On Postgresql 10.0 it works fine. This case sort of accidentally works in pre-v12 versions, because the convert_from call has no collatable inputs so it gets a default collation assignment anyway. However, the function's collation-name argument is of type name, and in v12 that type has collation "C" rather than no collation, so the convert_from result ends up with collation "C" not the database's default. I had not realized till just now that that change would have any side-effects on the behavior of convert_from (or more accurately, on the behavior of expressions involving convert_from as an input to a collation-sensitive function). I'm not sure whether to call it a bug or not --- it's not really obvious that convert_from() ought to be regarded as producing some particular collation rather than another one. In any case, there seems very little that we could do about it in the v12 release series, since those catalog entries are fixed now. Poking around, it seems that all of these functions would be similarly affected: # select oid::regprocedure from pg_proc where 'name'::regtype =3D any(proa= rgtypes) and prorettype =3D 'text'::regtype; oid = ----------------------------- text(name) obj_description(oid,name) shobj_description(oid,name) convert_from(bytea,name) to_ascii(text,name) (5 rows) regards, tom lane