The result of the pattern matching is incorrect when the pattern string is bpchar type

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

The result of the pattern matching is incorrect when the pattern string 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=#
Reply | Threaded
Open this post in threaded view
|

Re: The result of the pattern matching is incorrect when the pattern string is bpchar type

David G Johnston
On Thu, Jul 11, 2019 at 3:48 AM 甄明洋 <[hidden email]> wrote:
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.
Reply | Threaded
Open this post in threaded view
|

Re: The result of the pattern matching is incorrect when the pattern string is bpchar type

Andrew Gierth
>>>>> "David" == David G Johnston <[hidden email]> 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)