Different behaviour in conditional clause (IF against WHERE)

Hi everyone. It's the first time I post here so forgive me if I chose the
wrong group, since this question is about a problem we found out when using
SQL Express 2005.
The problem is that a conditional clause behaves different when it's used
into a IF THEN block than when used in a WHERE clause.
It' something like ( exp1 = "A" or ( exp1 = "B" and bla bla) )
Assume that exp1 is neither "A" nor "B". That 'bla bla' part should never be
evaluated. It behaves as expected when that clause is in an IF (condition)
block, but if the clause appears in a WHERE then it behaves wrongly.
I mean, most of us rely on short-circuit evaluation. Is there something I
missed about SQL Express ?
Thanks in advance
Ignacio Burgueo> Is there something I missed about SQL Express ?
There's something you missed about SQL - conditions are not evaluated in
order, they are evaluated "all at once", or to put another way, all
conditions must evaluate to TRUE for a given record to be returned.
"Ignacio" wrote:

> Hi everyone. It's the first time I post here so forgive me if I chose the
> wrong group, since this question is about a problem we found out when usin
g
> SQL Express 2005.
> The problem is that a conditional clause behaves different when it's used
> into a IF THEN block than when used in a WHERE clause.
> It' something like ( exp1 = "A" or ( exp1 = "B" and bla bla) )
> Assume that exp1 is neither "A" nor "B". That 'bla bla' part should never
be
> evaluated. It behaves as expected when that clause is in an IF (condition)
> block, but if the clause appears in a WHERE then it behaves wrongly.
> I mean, most of us rely on short-circuit evaluation. Is there something I
> missed about SQL Express ?
> Thanks in advance
> Ignacio Burgue?o
>
>|||KH wrote:
> There's something you missed about SQL - conditions are not evaluated
> in order, they are evaluated "all at once", or to put another way, all
> conditions must evaluate to TRUE for a given record to be returned.
>
Ok, I kind of see what you mean. SQL will not necessarily evaluate in the
order I write. Instead it will do the way it thinks it's more optimal.
Take for instance this code:
select * From InstInte0245 A (nolock)
Where ( ( A.theValue = 'ENDED' and ( datediff(second,
FecFin0245, getdate()) > 0 )
)
or A.theValue = 'LOCKED'
or A.theValue = 'TAKED'
)
I've edited some parts of it, but I think you can get the idea of what I'm
trying to achieve. If 'theValue' is different from 'ENDED', you don't need
to calculate the datediff. SQL 2000 gets this right, SQL Express 2005 does
not.
It seems that the optimizer of SQL 2005 thinks wrong in this case. How could
you give hints to the optimizer in this case?
Regards,
Ignacio|||To answer simply: SQL Server does not support short-circuit in SQL
statements so you can not rely on it.
"Ignacio" <ignacio at emuunlim dot com> wrote in message
news:OH##IPD0FHA.596@.TK2MSFTNGP12.phx.gbl...
> KH wrote:
> Ok, I kind of see what you mean. SQL will not necessarily evaluate in the
> order I write. Instead it will do the way it thinks it's more optimal.
> Take for instance this code:
> select * From InstInte0245 A (nolock)
> Where ( ( A.theValue = 'ENDED' and ( datediff(second,
> FecFin0245, getdate()) > 0 )
> )
> or A.theValue = 'LOCKED'
> or A.theValue = 'TAKED'
> )
> I've edited some parts of it, but I think you can get the idea of what I'm
> trying to achieve. If 'theValue' is different from 'ENDED', you don't need
> to calculate the datediff. SQL 2000 gets this right, SQL Express 2005 does
> not.
> It seems that the optimizer of SQL 2005 thinks wrong in this case. How
could
> you give hints to the optimizer in this case?
> Regards,
> Ignacio
>|||David Frommer wrote:
> To answer simply: SQL Server does not support short-circuit in SQL
> statements so you can not rely on it.
>
Oh, thanks. I assumed that SQL behaved the same way as, say, C.
Thanks all for your replies.
Regards,
Ignacio Burgueo|||On Thu, 13 Oct 2005 18:22:12 -0300, "Ignacio" <ignacio at emuunlim dot
com> wrote:

>KH wrote:
>Ok, I kind of see what you mean. SQL will not necessarily evaluate in the
>order I write. Instead it will do the way it thinks it's more optimal.
>Take for instance this code:
>select * From InstInte0245 A (nolock)
> Where ( ( A.theValue = 'ENDED' and ( datediff(second,
>FecFin0245, getdate()) > 0 )
> )
> or A.theValue = 'LOCKED'
> or A.theValue = 'TAKED'
> )
>I've edited some parts of it, but I think you can get the idea of what I'm
>trying to achieve. If 'theValue' is different from 'ENDED', you don't need
>to calculate the datediff. SQL 2000 gets this right, SQL Express 2005 does
>not.
>It seems that the optimizer of SQL 2005 thinks wrong in this case. How coul
d
>you give hints to the optimizer in this case?
Hi Ignacio,
If you want to force an order of evaluation, you'll have to use a CASE
expression. For instance, the following simple example will never result
in division by 0 error:
SELECT a, b
FROM MyTable
WHERE CASE WHEN b > 0 THEN a / b ELSE NULL END > 20
Of course, this is a contrived example, as it's much easier to write
WHERE a / NULLIF(b, 0) > 20
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)