Hiya, TOMs
I stumbled on a thing I think is a bit weird. I made this test script over at LiveSQL:
create table ch ( ch char(10) );
create table vc ( vc varchar2(10) );
insert into ch values ('MICKEY');
insert into ch values ('MICKEY ');
insert into ch values ('MICKEY ');
insert into vc values ('MICKEY');
insert into vc values ('MICKEY ');
insert into vc values ('MICKEY ');
commit;
select ch, dump(ch) as ch_dump
from ch;
select vc, dump(vc) as vc_dump
from vc;
select ch, dump(ch) as ch_dump
, vc, dump(vc) as vc_dump
from ch
join vc
on vc.vc = ch.ch;
When I select the dumps of the CH columns, I get the expected output that all are 10 characters long (MICKEY plus 4 spaces.)
CH CH_DUMP
MICKEY Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32
MICKEY Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32
MICKEY Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32
When I select the dumps of the VC columns, I also get the expected output of 6, 8 and 10 characters respectively.
VC VC_DUMP
MICKEY Typ=1 Len=6: 77,73,67,75,69,89
MICKEY Typ=1 Len=8: 77,73,67,75,69,89,32,32
MICKEY Typ=1 Len=10: 77,73,67,75,69,89,32,32,32,32
The join returns unexpected output (at least unexpected to me ;-)
CH CH_DUMP VC VC_DUMP
MICKEY Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32 MICKEY Typ=1 Len=10: 77,73,67,75,69,89,32,32,32,32
MICKEY Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32 MICKEY Typ=1 Len=10: 77,73,67,75,69,89,32,32,32,32
MICKEY Typ=96 Len=10: 77,73,67,75,69,89,32,32,32,32 MICKEY Typ=1 Len=10: 77,73,67,75,69,89,32,32,32,32
Firstly the join comparison is performed using blank-padded comparison semantics. The documentation (
http://docs.oracle.com/database/122/SQLRF/Data-Type-Comparison-Rules.htm#GUID-A114F1F4-A08D-4107-B679-323DC7FEA31C ) in Data Type Comparison Rules state:
"Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function."
"Oracle uses nonpadded comparison semantics whenever one or both values in the comparison have the data type VARCHAR2 or NVARCHAR2."
Since one of my values is VARCHAR2, I was expecting nonpadded comparison semantics, so only the third row of each table would be joined. But it is using blank-padded comparison semantics joining all three rows.
That's as maybe - it could be a documentation bug. (Or that I've misread the docs and somewhere else it states how this join is performed.)
So for comparison purposes it is appending spaces to the VC values in order to compare them with the CH values. But when I then SELECT the VC values, I do not get the actual data stored in the VC column, I get the "comparison value" - the VC value with appended spaces.
Is this expected behaviour?
Thanks
Cheerio
/Kim
I think you're over thinking this Kim ;)
So for comparison purposes it is appending spaces to the VC values in order to compare them with the CH values. But when I then SELECT the VC values, I do not get the actual data stored in the VC column, I get the "comparison value" - the VC value with appended spaces.No!
The last insert stores a value appended with spaces in the VC column. You've said "This string has spaces at the end". So its ten characters long. The database appends these spaces to the end of all the char values automatically.
So for the last VC column, you
are comparing the stored values
(VC) "MICKEY " to (CH) "MICKEY "
Adding an ID column to the tables may make this easier to see:
create table ch ( chid int, ch char(10) );
create table vc ( vcid int, vc varchar2(10) );
insert into ch values (1, 'MICKEY');
insert into ch values (2, 'MICKEY ');
insert into ch values (3, 'MICKEY ');
insert into vc values (4, 'MICKEY');
insert into vc values (5, 'MICKEY ');
insert into vc values (6, 'MICKEY ');
commit;
select chid, vcid, ch, vc
from ch
join vc
on vc.vc = ch.ch;
CHID VCID CH VC
3 6 MICKEY MICKEY
2 6 MICKEY MICKEY
1 6 MICKEY MICKEY
If you cast the char to a shorter varchar2 in the join, it'll trim the spaces off the end. And it only matches with the rows you stored in VC that are that length:
select chid, vcid, ch, vc
from ch
join vc
on vc.vc = cast(ch.ch as varchar2(8));
CHID VCID CH VC
3 5 MICKEY MICKEY
2 5 MICKEY MICKEY
1 5 MICKEY MICKEY
select chid, vcid, ch, vc
from ch
join vc
on vc.vc = cast(ch.ch as varchar2(7));
no rows selected
And if you cast the VC to char, you get nine rows:
select chid, vcid, ch, vc
from ch
join vc
on cast(vc.vc as char(10)) = ch.ch;
CHID VCID CH VC
3 4 MICKEY MICKEY
2 4 MICKEY MICKEY
1 4 MICKEY MICKEY
3 5 MICKEY MICKEY
2 5 MICKEY MICKEY
1 5 MICKEY MICKEY
3 6 MICKEY MICKEY
2 6 MICKEY MICKEY
1 6 MICKEY MICKEY