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

ID: 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 : kbprg kbbug4.20 kbbug4.21 SSrvServer SSrvWinNT
Version : winnt:4.2x
Platform : winnt
Issue type :