It seems that casting varchar and bpchar does not work.

I create two tables.

create table main (id char(4), sub_id char(4), caption char(10));
create table sub (sub_id varchar(10), sub_caption varchar(10));

Then do a select.

select main.id, sub.sub_caption, main.caption from main, sub
	where main.sub_id=sub.sub_id;

I get an error(this is normal, I guess).

ERROR:  There is more than one possible operator '=' for types 'bpchar' and 'varchar'
	You will have to retype this query using an explicit cast

So I try some castings.

test=> select main.id, sub.sub_caption, main.caption from main, sub where main.sub_id::varchar =sub.sub_id;
test=> ERROR:  There is more than one possible operator '=' for types 'bpchar' and 'varchar'
	You will have to retype this query using an explicit cast
test=> select main.id, sub.sub_caption, main.caption from main, sub where main.sub_id=sub.sub_id::bpchar;
ERROR:  There is more than one possible operator '=' for types 'bpchar' and 'varchar'
	You will have to retype this query using an explicit cast
test=> select main.id, sub.sub_caption, main.caption from main, sub where cast(main.sub_id as varchar) =sub.sub_id;
ERROR:  There is more than one possible operator '=' for types 'bpchar' and 'varchar'
	You will have to retype this query using an explicit cast
test=> select main.id, sub.sub_caption, main.caption from main, sub where main.sub_id=cast(sub.sub_id as bpchar);
ERROR:  There is more than one possible operator '=' for types 'bpchar' and 'varchar'
	You will have to retype this query using an explicit cast

Do we have a problem with casting? BTW, this is 6.4.2. I have not
tried current yet.
--
Tatsuo Ishii

Wed, 10 Feb 1999 11:43:39 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
cannot cast bpchar and varchar
> It seems that casting varchar and bpchar does not work.

Hmmm. It seems to be a combination of two problems:

1) not knowing which type should be preferred (or how to compare varchar
and bpchar; should I strip the blank padding from a bpchar? Probably
so...).

2) swallowing the type coersions since bpchar, varchar, and text are
considered to be binary compatible. I may have seen another instance of
this being a problem, and perhaps should figure out how to propagate the
new coerced type into the query rather than dropping it.

Will look at this. Question: how *should* we compare bpchar and varchar?
It may be that we should have some explicit comparison or coersion
routines to make things work smoothly.

Thanks for the report.

                    - Tom

Wed, 10 Feb 1999 04:32:24 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] cannot cast bpchar and varchar
>Hmmm. It seems to be a combination of two problems:
>
>1) not knowing which type should be preferred (or how to compare varchar
>and bpchar; should I strip the blank padding from a bpchar? Probably
>so...).
>
>2) swallowing the type coersions since bpchar, varchar, and text are
>considered to be binary compatible. I may have seen another instance of
>this being a problem, and perhaps should figure out how to propagate the
>new coerced type into the query rather than dropping it.
>
>Will look at this. Question: how *should* we compare bpchar and varchar?
>It may be that we should have some explicit comparison or coersion
>routines to make things work smoothly.

Not sure. I will check some SQL books at home.
--
Tatsuo Ishii

Wed, 10 Feb 1999 17:59:01 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [HACKERS] cannot cast bpchar and varchar 
> >Will look at this. Question: how *should* we compare bpchar and varchar?
> >It may be that we should have some explicit comparison or coersion
> >routines to make things work smoothly.
> 
> Not sure. I will check some SQL books at home.

According to the standard, the result of comparison between a fixed
length char (bpchar) and a variable length char (varchar or text) may
vary depending on an attribute "PAD SPACE" or "NO PAD" of the
COLLATION. Since we do not have COLLATION (yet), we need to have
another way to decide which scheme (PAD SPACE or NO PAD) should be
employed. Possible solution might be:

o decide at compile time. always use one of them at runtime.

o decide at runtime. new set command or an environment variable might
  be used.

Comments?
---
Tatsuo Ishii

Thu, 11 Feb 1999 14:08:40 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [HACKERS] cannot cast bpchar and varchar 