Microsoft KB Archive/323504

= PRB: User Defined Function May Return Incorrect Query Result When It Is Used in a Group By Clause =

PSS ID Number: 323504

Article Last Modified on 10/3/2003

-

The information in this article applies to:


 * Microsoft SQL Server 2000 (all editions)

-



This article was previously published under Q323504





SYMPTOMS
SQL Server may mistakenly overwrite a column of a result set that is returned by a query with the value of another column from the query if all of the following conditions are true:


 * The query contains a GROUP BY clause.
 * The GROUP BY clause executes a user defined function (UDF) two times, or more.
 * The UDF takes two (2), or more, parameters.
 * Each call to the UDF uses the same first parameter.
 * The return value of the UDF is dependent on the second parameter and any remaining parameters that follow.

There is also a second issue (that is not related to the first issue) that you might experience. A query may also return incorrect results when all of these conditions are true:
 * The query contains two or more references to the same aggregate function.
 * The aggregate functions take as their parameter the results from a call to the same UDF.
 * The calls to the UDF pass the same first parameter.
 * The UDF takes two or more parameters, and the results that it returns are dependent on the value of those secondary parameters.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

288957 FIX: Multiple Calls To a User Defined Function Within an Aggregate Function May Return Incorrect Results



WORKAROUND
To work around this problem, in the GROUP BY clause, modify the name of the first parameter for each UDF so that the value of each first parameter remains unchanged, but the name of each first parameter is different.

For example:

If the first parameter is numeric, then:
 * 1) Multiply the first parameter of the first UDF by one (1).
 * 2) Multiply the first parameter of the second UDF by two (2), and then divide it by two (2), and so on.

If the first parameter is a string, then:
 * 1) Concatenate the first parameter of the first UDF with an empty string ('').
 * 2) Concatenate the first parameter of the second UDF with two empty strings ( + ), and so on.

Notes:


 * For the first parameter of each UDF that you change in the GROUP BY clause, you must also change the first parameter in the SELECT clause of the corresponding UDF. See the &quot;More Information&quot; section of this article for an example.
 * An empty string is represented by two single quotation mark characters, with no space in between.



MORE INFORMATION
The following script reproduces the behavior of this issue. The following SELECT statement will return &quot;3,3&quot; when the correct result is &quot;3, 4&quot;. The result of the second column is overwritten by the data in the first column.

USE tempdb GO

CREATE TABLE T1 ( c1 int NOT NULL, c2 int NOT NULL, c3 int NOT NULL ) GO

INSERT INTO T1 VALUES ( 1, 2, 3 ) GO

CREATE FUNCTION dbo.udf1 ( @c1 int, @c2 int ) RETURNS int AS BEGIN RETURN ( @c1 + @c2 ) END GO

SELECT dbo.udf1( c1, c2 ), dbo.udf1( c1, c3 ) FROM T1 GROUP BY dbo.udf1( c1, c2 ), dbo.udf1( c1, c3 ) GO To obtain the expected result of &quot;3, 4&quot;, you can modify the SELECT statement, as follows, so that the name of the first parameter in each of the UDFs in the GROUP BY clause are different in appearance from the others:

SELECT dbo.udf1( c1 * 1, c2 ), dbo.udf1( c1, c3 ) FROM T1 GROUP BY dbo.udf1( c1 * 1, c2 ), dbo.udf1( c1, c3 ) GO

Keywords: kbBug kbpending kbprb KB323504

Technology: kbAudDeveloper kbSQLServ2000 kbSQLServ2000Search kbSQLServSearch

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© 2004 Microsoft Corporation. All rights reserved.