FIX: Query with OUTER JOINs May Neglect Built-in Function in WHERE Clause (323553)

SYMPTOMS

Under the following conditions, a query does not return the expected result. More precisely, SQL Server 2000 ignores the condition that contains a built-in function when it builds the query plan.

The query joins more than three tables.
At least one outer join is used in the query.
The inner table of a outer join is not at the end of the FROM clause.
The WHERE clause contains a condition with a built-in function (for example, POWER, ABS, DATEPART), which evaluates to FALSE.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How To Obtain the Latest SQL Server 2000 Service Pack

NOTE: The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.

The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

   Date      Time    Version   Size           File name
   -------------------------------------------------------

   17-May-02 09:44   8.00.629  7454804 bytes  Sqlservr.exe

NOTE: Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.

WORKAROUND

To work around this behavior, use any one of these methods:

Do not use a built-in function in the WHERE clause that evaluates to FALSE.
Wrap the built-in function inside a user-defined function.
Remove all outer joins.
Place the inner table of an outer join at the end of the FROM clause.
Do not use more than three tables in the FROM clause.

MORE INFORMATION

This problem can be clearly identified by looking at the estimated Execution Plan of a query in Query Analyzer. If there is no FILTER node that contains the built-in function in the WHERE clause, you are experiencing this problem.

For example, if your WHERE clause is

WHERE power(2,1) = 0

there must be a node in the query plan that is similar to

Filter(WHERE:(power(2, 1)=0))

-or-

Filter(WHERE:(STARTUP EXPR(power(2, 1)=0)))