Posted by Dan Guzman on 03/08/07 12:41

> The odd thing is that there is no "a.type = 'A' " entry, thus the
> query wouldn't return anything. In 2000, it seems as though the
> engine is evaluating the type = 'A' and short-circuiting and in 2005,
> it is trying to evaluate the entire query OR is there an implicit
> conversion occuring in 2000 and not in 2005?

SQL is a descriptive language rather than a procedural one. You have no
control over the order in which WHERE clause predicates are evaluated. The
SQL Server optimizer is cost-based and may evaluate the conditions in
different query plan operators. The differences between SQL 2000 and 2005
you are simply the result different query plans, most likely because of the
different optimizers. You can verify this by looking at the execution
plans.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<Luke.Schollmeyer@gmail.com> wrote in message
news:1173327825.026405.123790@c51g2000cwc.googlegroups.com...
>I found an unusual problem between 2000 and 2005 I haven't been able
> to decipher from any documentation.
>
> The query structure is as follows:
>
> select *
> from
> tableA a
> join
> tableB b ON a.somekey = b.somekey
> where
> a.type = 'A'
> and datediff(yyyy, b.someDateField, getdate()) between
> a.lowboundary and a.highboundary
>
> Some basic facts about the elements and data. The low and high-
> boundary fields are varchar datatypes. In 2005 (regardless of
> compatibility type I run the database under), the query evaluates the
> BETWEEN and errors out due to the fact that it is evaluating the
> DATEDIFF as an integer and finds a non-integer entry in either
> lowboundary or highboundary. I understand and expect this behavior.
> Obviously, changing the result of the DATEDIFF function to varchar
> allows the operation to go forth.
>
> The odd thing is that there is no "a.type = 'A' " entry, thus the
> query wouldn't return anything. In 2000, it seems as though the
> engine is evaluating the type = 'A' and short-circuiting and in 2005,
> it is trying to evaluate the entire query OR is there an implicit
> conversion occuring in 2000 and not in 2005?
>
> As I mentioned, the compatibility mode doesn't change how this reacts,
> but running this on a native 2000 server allows this to happen. This
> particular code isn't the problem, it's what we might have to contend
> with when we migrate this through. Sure, we're going to perform
> regression testing, but I'm concerned about what we would miss.
>
> Thanks for any replies.
>

[Back to original message]