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, 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
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
Import Notes
Reply to msg id not found: YourmessageofWed10Feb1999043224GMT.36C10BD8.A99D0F86@alumni.caltech.edu | Resolved by subject fallback
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
Import Notes
Reply to msg id not found: YourmessageofWed10Feb1999175901+0900.199902100859.RAA28146@srapc451.sra.co.jp | Resolved by subject fallback