BUG: Subquery for Char Non-null Col Returns Less Rows

• Microsoft SQL Server version 4.2 for OS/2
• Microsoft SQL Server, version 4.21

       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.2 for OS/2 and Microsoft SQL Server version 4.21. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.