Thread: cannot cast bpchar and varchar

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, subwhere 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
usingan 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
thisquery 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
usingan 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
usingan 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
usingan explicit cast
 

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

> 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

>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

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