From: Tom Lane <tgl@sss.pgh.pa.us>
To: depesz@depesz.com
Cc: ammmkilo@163.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18560: Inconsistent Behavior of PostgreSQL 'LIKE' Operator
Date: Wed, 31 Jul 2024 10:35:06 -0400
Message-ID: <3378620.1722436506@sss.pgh.pa.us> (raw)
In-Reply-To: <ZqoIBlVvEsntf9Eu@depesz.com>
References: <18560-c6c21c3b8d5c3f9b@postgresql.org>
<ZqoIBlVvEsntf9Eu@depesz.com>
hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Wed, Jul 31, 2024 at 04:47:23AM +0000, PG Bug reporting form wrote:
>> CREATE TABLE t0(c0 INT NOT NULL, c1 CHAR(1) UNIQUE);
> I bet the thing is simply one more edge case that proves that one
> shouldn't be using char(n) datatype:
> https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_char.28n.29
Yeah, exactly. Some surprising choices were made way-back-when about
which char(N) operations ought to ignore trailing blanks and which
shouldn't. In the case at hand, a closer look shows the problem:
=# explain verbose select c1,(c1 like c1) from t0;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on public.t0 (cost=0.00..43.00 rows=2200 width=6)
Output: c1, (c1 ~~ (c1)::text)
There is a "char ~~ text" operator but no "char ~~ char" operator,
so the parser coerces the right-hand "c1" to text --- which causes
stripping of its trailing blank --- and then applies ~~, which
treats the trailing blank in its left-hand argument as significant.
Yes, this is arguably inconsistent, but so are a lot of other char(N)
behaviors. Proposals to change anything about it have generally
failed on the grounds that (1) it's not very clear what would work
better and not just move the surprises around; (2) we risk breaking
applications that are expecting the current behaviors; and
(3) char(N) is a deprecated backwater that we shouldn't be putting
any effort into.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, depesz@depesz.com, ammmkilo@163.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18560: Inconsistent Behavior of PostgreSQL 'LIKE' Operator
In-Reply-To: <3378620.1722436506@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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