BUG: System Functions with INSERT Cause Error 221

• Microsoft SQL Server version 4.2 for OS/2

SYMPTOMS

If you INSERT into a table using a SELECT statement with a WHERE clause containing system functions, such as USER_NAME or OBJECT_NAME, to compare with a null, and if the argument in the system function is a table column that does not allow nulls, then the following error occurs:

   Column of type (varchar) does not allow nulls.
   It may not be compared with nulls.
   (Msg 221, Level 16, State 1)

   create table t1 (i int not null)
   create table t2 (c char(30) null)
   insert t1 values (1000)
   insert t2 (c)
      select user_name(i)
      from t1
      where user_name(i) !=null

However, the same SELECT statement

   select user_name(i) from t1 where user_name(i)!=null

In general, SQL Server allows you to compare the result of a system function and nulls. For example, the following query is valid

   select 1 where user_name(1000)=null

   select 1 where 1000=null

CAUSE

SQL Server incorrectly handles the comparison of the result of a system function with nulls if you use the comparison in a SELECT statement within an INSERT statement.

WORKAROUND

Use another system function, ISNULL, to force SQL Server to accept the null in the following way:

   insert t2 (c)
      select user_name(i)
      from t1
      where ISNULL(user_name(i),null) != null

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.