Re: [BUG REPORT] Unexpected cast while matching CHAR type - Mailing list pgsql-bugs

From Tom Lane
Subject Re: [BUG REPORT] Unexpected cast while matching CHAR type
Date
Msg-id 3307252.1691001346@sss.pgh.pa.us
Whole thread Raw
In response to [BUG REPORT] Unexpected cast while matching CHAR type  ("翁思扬" <52275903002@stu.ecnu.edu.cn>)
List pgsql-bugs
"=?utf-8?B?57+B5oCd5oms?=" <52275903002@stu.ecnu.edu.cn> writes:
> We find an unexpected behaviour about query optimizer in PostgreSQL.
> More specifically, char type will be transformed into text incorrectly which leads to an unexpected result.

This is not a bug.  There is no variant of the LIKE operator that accepts
char(n) as the pattern argument:

=# \do ~~
                                     List of operators
   Schema   | Name | Left arg type | Right arg type | Result type |       Description
------------+------+---------------+----------------+-------------+-------------------------
 pg_catalog | ~~   | bytea         | bytea          | boolean     | matches LIKE expression
 pg_catalog | ~~   | character     | text           | boolean     | matches LIKE expression
 pg_catalog | ~~   | name          | text           | boolean     | matches LIKE expression
 pg_catalog | ~~   | text          | text           | boolean     | matches LIKE expression
(4 rows)

Therefore, the parser (not the optimizer) has little choice but to
insert an implicit cast of the sub-select's output to text; the
only other alternative is to throw an error.

We could make this case behave as you expect by adding a variant
of ~~ that takes char(n) on the RHS, but I'm disinclined to do so
because of the high probability that it would cause unwanted
behavioral changes in other cases.  Moreover, given that char(n)'s
trailing spaces are considered insignificant, is preserving them in
a LIKE pattern even the right thing to do?  I don't think that's
exactly an open-and-shut question.  (Arguably, the right fix is
not to add another ~~ variant but to get rid of the char(n) versus
text one, so that both sides get space-stripped in this case.)

The short answer is that char(n) is a legacy type that you should
not use, precisely because it has such odd behavior around trailing
spaces.  We support it for pro-forma compliance with the SQL
standard, but consider it deprecated.  That being the case, there's
not a lot of interest in changing edge cases in its behavior.
We'd be more likely to get complaints than kudos for doing so.

            regards, tom lane



pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #17997: Assert failed in validatePartitionedIndex() when attaching partition index to child of valid index
Next
From: Jeff Davis
Date:
Subject: Re: search_path not recomputed when role name changes