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

From: 甄明洋 <zhenmingyang(at)yeah(dot)net>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: The result of the pattern matching is incorrect when the pattern string is bpchar type
Date: 2019-07-11 10:48:11
Message-ID: 68c3aa88.331d9.16be0a5d184.Coremail.zhenmingyang@yeah.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Magnus Hagander 2019-07-11 11:21:39 Re: BUG #15888: Bogus "idle in transaction" state for logical decoding client after creating a slot
Previous Message Michael Paquier 2019-07-11 08:15:53 Re: BUG #15896: pg_upgrade from 10-or-earlier: TRAP: FailedAssertion(»!(metad->btm_version >= 3)«