BUG: SQL Internal Error Returned When UDF Value Is Null

• Microsoft FoxPro for Windows, version 2.6a
• Microsoft FoxPro for MS-DOS, version 2.6a
• Microsoft FoxPro for Macintosh, version 2.6a

SYMPTOMS

When an SQL SELECT statement calls a UDF that returns a null value, a SQL Internal Error occurs if the SELECT statement has a GROUP BY 1 clause.

RESOLUTION

To work around the problem:

• Change the GROUP BY a field name instead of 1:

  SELECT Myudf(Name) ;

        FROM Test ;
        GROUP BY Name
  

  -or-

• Return a space instead of a null from the UDF:

  RETURN IIF(Getname = "Sam", Getname," ")

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Problem

Build a database and run a SELECT statement. Type the following code into a program and run that program:

   CREATE TABLE Test (Name C(15))
   INSERT INTO Test (Name) VALUES ("Robert")
   INSERT INTO Test (Name) VALUES ("Sam")
   INSERT INTO Test (Name) VALUES ("James")

   SELECT Myudf(Name) ;
   FROM Test ;
   GROUP BY 1

   FUNCTION Myudf
   PARAMETER Getname
   RETURN IIF(Getname = "Sam", Getname,"")