Thread

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

   myzhen <zhenmingyang@yeah.net> — 2019-07-11T10:48:11Z

   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=#

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

   David G. Johnston <david.g.johnston@gmail.com> — 2019-07-11T14:36:08Z

   On Thu, Jul 11, 2019 at 3:48 AM 甄明洋 <zhenmingyang@yeah.net> 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.
   
   https://www.postgresql.org/message-id/20122.1562548227%40sss.pgh.pa.us
   
   In short, don't use character.
   
   David J.
   

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

   Andrew Gierth <andrew@tao11.riddles.org.uk> — 2019-07-11T17:26:52Z

   >>>>> "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)