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:
- Multiply the first parameter of the first UDF by one (1).
- 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:
- Concatenate the first parameter of the first UDF with an empty string ().
- 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 "More Information" 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 "3,3" when the correct result is "3, 4". 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 "3, 4", 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
