Using an equi-join clause between two different length columns

[CR #401336] if you have a equi-join clause between two different length char() and char()/varchar() type columns and also use char_length() on one of the columns, the result of the char_length() is indeterminate. For example:

create table t1 (sid char(8), styp char(5)) 
go 
create table t2 (sid char(16)) 
go 
select char_length(a.sid) 
from t1 a, t2 b 
where a.sid=b.sid and styp = 'R1' 
go

Workaround: Use convert() in char_length() to state explicitly which type you need for the char_length.

For example, in the above query, use:

select char_length(convert(char(8), a.sid)) 
from t1 a, t2 b 
where a.sid=b.sid and styp = 'R1'

The same is true for binary() and varbinary() with data_length(), unichar() and univarchar() with char_length().

Copyright © 2005. Sybase Inc. All rights reserved.

View this release bulletin as PDF