Re: [HACKERS] SELECT BUG

José Soares <jose@sferacarta.com> writes:
> Here an example...
> create table master(mcode char(11), mcode1 char(16));
> create table detail(dcode char(16));
> insert into master values ('a','a');
> insert into master values ('a1','a1');
> insert into master values ('a13','a13');
> insert into detail values ('a13');
> insert into detail values ('a1');
> insert into detail values ('a13');

> --in the following example mcode is long 11 and mcode1 is long 16
> --but mcode=mcode1 is true:

> select  * from master where mcode=mcode1;
> mcode      |mcode1
> -----------+----------------
> a          |a
> a1         |a1
> a13        |a13
> (3 rows)

On looking at the bpchar (ie, fixed-length char) comparison functions,
I see that they *do* strip trailing blanks before comparing.  varchar
and text do not do this --- they assume trailing blanks are real data.

This inconsistency bothers me: I've always thought that char(),
varchar(), and text() are functionally interchangeable, but it seems
that's not so.  Is this behavior mandated by SQL92?

> --in the following example mcode is long 11 and dcode1 is long 16
> --but mcode=dcode1 is false:

> select  mcode, dcode from master m, detail d where mcode=dcode;
> mcode|dcode
> -----+-----
> (0 rows)

Oh my, that's interesting.  Executing your query with current sources
gives me:

regression=> select  mcode, dcode from master m, detail d where mcode=dcode;
mcode      |dcode
-----------+----------------
a1         |a1
a13        |a13
a13        |a13
(3 rows)

When I "explain" this, I see that I am getting a mergejoin plan.
Are you getting a hash join, perhaps?

bpchareq is marked hashjoinable in pg_operator, but if its behavior
includes blank-stripping then that is WRONG.  Hashjoin is only safe
for operators that represent bitwise equality...
        regards, tom lane