"=?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