Microsoft KB Archive/323553

= FIX: Query with OUTER JOINs May Neglect Built-in Function in WHERE Clause =

Article ID: 323553

Article Last Modified on 10/9/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q323553



BUG #: 357101 (SHILOH_BUGS)



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.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.



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)))

Keywords: kbbug kbfix kbsqlserv2000presp3fix kbsqlserv2000sp3fix KB323553

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.