Thread: The result of the pattern matching is incorrect when the patternstring is bpchar type
The result of the pattern matching is incorrect when the patternstring is bpchar type
From
甄明洋
Date:
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
From
"David G. Johnston"
Date:
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
From
Andrew Gierth
Date:
>>>>> "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)