FIX: Query Analyzer: Empty Row is Returned with COMPUTE on SMALLINT Data (274353)

SYMPTOMS

When you use the Microsoft SQL Server 7.0 Query Analyzer client tool with a smallint data type and a COMPUTE clause, unexpected results may be returned for the computed summary result. This behavior occurs only with negative numbers between -10000 and
-32768.

WORKAROUND

To work around this problem, use either of the following:

Use one of the following client tools instead:
- OSQL or ISQL command line utilities to run the query.
  
  -or-
- Use Microsoft SQL Server 6.5 ISQL_W or Microsoft SQL Server 2000 Query Analyzer to run the query.
Use the INT data type

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

Steps to Reproduce Behavior

Run this code:

if object_id('t1') is not null
DROP TABLE t1
go
CREATE TABLE t1 ( my_int smallint)
go
INSERT INTO t1 VALUES(-10000)
INSERT INTO t1 VALUES (10000)
go
SELECT my_int FROM t1 COMPUTE MIN(my_int)
go

Result:

my_int   
------ 
-10000
10000

min
=====
(unexpected result)

(3 row(s) affected)

When you use OSQL to run the same query, this is the result:

C:\>osql -Usa -P
1> USE mytest
2> go
1> SELECT my_int FROM t1 COMPUTE MIN(my_int)
2> go

 my_int
 ------
 -10000
  10000

 min
 ======
 -10000