Re: [HACKERS] SELECT BUG

=?iso-8859-1?Q?Jos=E9?= Soares <jose(at)sferacarta(dot)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