Microsoft KB Archive/310472

= FIX: Incorrect Results with Uncorrelated Subquery If You Use an Aggregate Function and NULL Values in the WHERE Clause =

Article ID: 310472

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q310472



BUG #: 355729 (SHILOH_BUGS)



SYMPTOMS
A SELECT query may return an incorrect result if an uncorrelated subquery in the WHERE clause contains the MIN or MAX function and more than one IS NULL in the WHERE clause of the subquery.



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



STATUS
Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.



MORE INFORMATION
For clarification, you can use the following script to demonstrate the problem: CREATE TABLE t (  ID INT IDENTITY CONSTRAINT PK PRIMARY KEY,      dt1 DATETIME NULL,   dt2 DATETIME NULL ) GO

INSERT INTO t (dt1, dt2) VALUES (null, null)

SELECT * FROM t    WHERE ID = (SELECT MAX(ID) FROM t WHERE dt1 IS NULL AND dt2 IS NULL) GO

DROP TABLE t GO The preceding SELECT query should return one row; however, it fails to return the row.

Keywords: kbbug kbfix KB310472

-

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

© Microsoft Corporation. All rights reserved.