Sybase: Where Clause in Derived Table
Feb 28, 2014
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.