Sybase 15 substring error in a case statement on update

Question

Running on Adaptive Server Enterprise/15.0.3/EBF 17157 ESD#3/P/x86_64/Enterprise Linux/ase1503/2726/64-bit/FBO/

The below code should never enter the substring case, however I am presented with a Sybase Error 536.

Is this a form of optimization where it evaluates all paths regardless of the actual value??

We can work around this but wish to know why?

declare @test float
declare @test1 char(10) 

create table #TestTable
(
    Dno int,
    Code varchar(10)    
)

Insert into #TestTable values (1,'code')
set @test1 = 'ddd'
print 'test'
select  @test = case
        when (1=1) then 2
        when (1=0) then (select Dno FROM  #TestTable  WHERE Code = substring('abc',1,charindex(@test1,'a')-1) AND Dno = 1)
        else 10
       end

       select @test

drop table #TestTable

Answer 1

Yes.

It appears that you may be misunderstanding the nature of, and what is required of, optimising the query. Apparently you think there is some "code path" that should be executed sometimes, and not others. It is not possible to exclude a coded "code path" from the gaze of the optimiser. Although it optimises queries, in terms of handling SQL, it is just a compiler, not an optimiser.

When the query is optimised, the entire query path are determined (setting aside the fact that many possibilities are evaluated before one is chosen), evaluated, checked, and compiled. The CASE is irrelevant. In order for ANY subquery to run that subquery has to be evaluated and compiled. The fact the your particular subquery will never execute is irrelevant to the code.

For many reasons, it is a good idea to place code that will never execute, outside the program. Whether the branch in the query tree will be executed or not, is a determination at runtime. Humans can see that it will never be activated, but the optimiser does not yet have that level of AI (it will be a grand day when an optimiser can exclude such code).