A LENGTHY REBOOT

By Richard Hsu
Latest | Archives | Contact | Others

Sybase: Where Clause in Derived Table

Sybase's processing of a where clause in a derived table confused me. Instead of applying it first, it seemed to move the where clause outside of the derived table. To recreate the behaviour, here is a table with two rows:

create table data(nums varchar(4))
insert into data values('255')
insert into data values('3000')

select nu
from
( select convert(tinyint, nums) nu
from data
where len(nums) < 4 -- filter out 3000
) inside
where nu = 255

What I expect: 255

What happens: Query throws Arithmetic overflow error during conversion of '3000' to tinyint(range is 0-255). I expected the filter in the derived table to be applied first eliminating '3000' from getting converted but that is not the case. It seems Sybase does below instead:

select nu
from
( select convert(tinyint, nums) nu, nums
from data
) inside
where nu = 255
and len(nums) < 4

I could not find out why so this remains a mystery but what makes it more interesting is below works fine:

select nu
from
( select convert(tinyint, nums) nu
from data
where nums != '3000'
) inside
where nu = 255

We used a temporary table to ensure filter happens.