Microsoft KB Archive/308810

= FIX: Select Query That Compares a Tinyint to a Converted Int in the Where Clause Fails to Find Valid Row =

Article ID: 308810

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q308810



BUG #: 354990 (SHILOH_BUGS)



SYMPTOMS
A query that meets all the following criteria may fail to return the correct result:
 * The query contains an equality comparison between a tinyint column and an explicit conversion of the constant value zero (0).
 * The tinyint column has a unique constraint.
 * The tinyint column has a check constraint with zero (0) defined as the lower bound.

For example: USE pubs GO

CREATE TABLE T1(c1 TINYINT)

ALTER TABLE T1 ADD UNIQUE (c1)

ALTER TABLE T1 ADD CHECK(c1 BETWEEN 0 AND 100)

INSERT INTO T1 VALUES(0) GO

SELECT * FROM T1 WHERE c1 = CAST(0 AS INT) GO

DROP TABLE T1 GO The preceding SELECT query should return one row, but it fails to return the row.



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



WORKAROUND
To work around this problem, set the database compatibility level to 70.

NOTE: If you set the compatibility level to 70, you cannot take advantage of many of the new features included in SQL Server 2000.



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.

Keywords: kbbug kbfix KB308810

-

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

© Microsoft Corporation. All rights reserved.