Re: Order of execution in logical expressions

Wayne,

The answer is: The optimizer decides and it can change its mind at a later
time, depending on statistics, indexes, et cetera, when to evaluate a clause
in T-SQL. If you hide things in scalar or multi-statement table-valued
functions you can also force evaluation order. (But not for in-line table
valued functions.)

Your TOP 1 fix could have also been fixed by MIN or MAX, for the same
reasons. It tells the optimizer that only one value will return.

RLF

"Wayne Erfling" <wayne_erfling@xxxxxxxxxxxxxx> wrote in message
news:eNEgrGspHHA.3524@xxxxxxxxxxxxxxxxxxxxxxx
GlacierI'm a little confused about order of execution in logical
expressions.

In a statement like this:

SELECT * FROM myTable
WHERE PK_ID > 0 OR
(PK_ID <=0 AND 1/0 > 5)

apparently statement compilation jumps in and detects the divide by zero
error, even though that clause would never be executed, because all PK_IDs
in myTable are in fact > 0.

On the other hand, if I "hide" the divide by zero, then Transact-SQL seems
to use what the .NET world calls "partial statement evaluation" (we called
it something else in the old COBOL through Pascal days):

SELECT * FROM myTable
WHERE PK_ID > 0 OR
(PK_ID <=0 AND 1/myFunction(5) > 5)

In this case, myFunction(5) would return zero if called, but as this is
apparently out of reach of the statement compiler, the query runs fine and
returns all rows.

This question arose because of a sub-query in an UPDATE TRIGGER:

....

IF NOT UPDATE(fieldname) OR
(SELECT DISTINCT fieldname FROM INSERTED) IS NULL)

For a multiple-row update, at execution time this statement returned an
error about multiple rows being returned from a sub-query.

However, if the statement is being executed using "partial statement
evaluation", then the only time the second clause should be executed is if
UPDATE(fieldname) is TRUE.

When UPDATE(fieldname) is TRUE there can only be one value for fieldname,
the value supplied in the UPDATE statement.

Therefore the sub-query error is inappropriate.

(Incidentally, I "fixed" it by replacing DISTINCT with TOP 1, even though
DISTINCT could not have returned more than one value in this case).

I'm wondering if somebody can help me understand better when Transact-SQL
will and will not execute logical clauses using AND and OR, as it seems
unpredictable to me, and in the context of a WHERE clause, I don't have the
luxury of using nested IF statements as one has to do in at least some
members of the VB family (VBScript, VBA, VB, VB.Net):

Thanks!

---Wayne



.

Relevant Pages

  • Re: Evaluating Exceptions, Try Except and Try Finally
    ... error occurs during execution. ... statementList2 (the finally clause) is executed. ... exception is raised during execution of statementList1, ... THE IF THEN ELSE STATEMENT DOCUMENTATION IS JUST AS FUCKED:)' ...
    (alt.comp.lang.borland-delphi)
  • Re: Sorting Problem
    ... "nomi" wrote in message ... Only by running SELECT clause and specify ORDER BY clause you can ... >>> I presume that it has something to do with collations, ... >>> Here is the output of the query after different execution of the sp. ...
    (microsoft.public.sqlserver.programming)
  • Re: SET NOLOGGING in JDBC url?
    ... It's right it does not have a where clause, the query *should* run ... execution at a time ... isolation level could be the key in this case. ...
    (comp.databases.ingres)
  • Re: [python] using try: finally: except
    ... [Tim Peters] ... was it that the order of execution was fixed, ... a 'finally' clause had to be the last clause in a ... most common confusion was over whether the code in the 'finally' ...
    (comp.lang.python)
  • Re: CLUSTERD INDEXES
    ... ORDER BY clause and the clustered index both specify the same order. ... > optimizer to include a Sort operator in the execution plan, ...
    (microsoft.public.sqlserver.programming)