Обсуждение: The result of the pattern matching is incorrect when the patternstring is bpchar type
There are two tables with the type of column is char. when Using a 'like' predicate in a join condition will result in an incorrect result.
Because there is no 'like' operator that left operand and right operand are all bpchar.(bpchar ~~ bpchar), final the operator 'bpchar ~~ text' will be found form candidate set. so database do the cast from bpchar to text, The space at the end of the string was removed during the cast.
1、Following a example：
postgres=# create table t1(a char(6));
CREATE TABLE
postgres=# create table t2(a char(6));
CREATE TABLE
postgres=# insert into t1 values('aaa');
INSERT 0 1
postgres=# insert into t2 values('aaa');
INSERT 0 1
postgres=# select * from t1, t2 where t1.a=t2.a;
a | a
--------+--------
aaa | aaa
(1 row)
postgres=# select * from t1, t2 where t1.a like t2.a;
a | a
---+---
(0 rows)
postgres=#
2、The following example is a comparative:
postgres=# select 'aaa'::text like 'aaa'::text;
?column?
----------
t
(1 row)
postgres=# select 'aaa'::char(6) like 'aaa'::char(6);
?column?
----------
f
(1 row)
postgres=#
Re: The result of the pattern matching is incorrect when the patternstring is bpchar type
От
"David G. Johnston"
Дата:
There are two tables with the type of column is char. when Using a 'like' predicate in a join condition will result in an incorrect result.Because there is no 'like' operator that left operand and right operand are all bpchar.(bpchar ~~ bpchar), final the operator 'bpchar ~~ text' will be found form candidate set. so database do the cast from bpchar to text, The space at the end of the string was removed during the cast.
A similar complaint was made the other day; Tom's response succinctly sums up the prevailing opinion as to the character type.
In short, don't use character.
David J.
Re: The result of the pattern matching is incorrect when the pattern string is bpchar type
От
Andrew Gierth
Дата:
>>>>> "David" == David G Johnston <david.g.johnston@gmail.com> writes: >> There are two tables with the type of column is char. when Using a >> 'like' predicate in a join condition will result in an incorrect >> result. Because there is no 'like' operator that left operand and >> right operand are all bpchar.(bpchar ~~ bpchar), final the operator >> 'bpchar ~~ text' will be found form candidate set. so database do >> the cast from bpchar to text, The space at the end of the string was >> removed during the cast. David> A similar complaint was made the other day; Tom's response David> succinctly sums up the prevailing opinion as to the character David> type. It's also listed as WONTFIX here: https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard#Trailing_spaces_in_character.28n.29 though I guess adding the LIKE case as an example there might be good. -- Andrew (irc:RhodiumToad)