Q102802

• 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) 

    col1 col2
    ---- ----
   (0 rows affected) 

    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) 

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