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

The information in this article applies to:

Microsoft SQL Server 2000 (all editions)

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.

Modification Type:	Major	Last Reviewed:	10/17/2003
Keywords:	kbbug kbfix KB310472