BUG: Subquery for Char Non-null Col Returns Less Rows |
Q102802
The information in this article applies to:
- Microsoft SQL Server version 4.2x
BUG# NT: 812 (4.2)
SYMPTOMS
When executing a correlated subquery with aggregate functions MAX or MIN, the char non-null datatype column of a table, and the actual data entered in the column is less than the total column width (for example, entering a string of length 1 into char(2) column), you may get less rows on you may than expected.
This does not happen with int, varchar, or char null datatypes columns.
For example, consider the table test. Then the query below does not return any rows.
create table test (col1 char(2), col2 char(2)) go insert test values ("1","2") insert test values ("1","3") insert test values ("4","3") go select * from test a where col1=(select max(col1) from test b where a.col2=b.col2)
It will return the following two rows:
col1 col2 ---- ---- (0 rows affected)
However, the rows that should be returned are:
col1 col2 ---- ---- 1 2 4 3 (2 rows affected)
WORKAROUND
To work around this problem, please use the following query:
select * from test a where col1=(select max(convert(varchar(2),col1)) from test b where a.col1=b.col2)
Optionally, you may also change the datatype to char null or varchar in the table.
STATUS
Microsoft has confirmed this to be a problem in SQL Server version 4.21.
Additional query words: aggregate
Keywords : kbprogramming
Issue type :
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2
Last Reviewed: November 9, 1999 |