Incorrect Results from Aggregate Query with UDF

Question

Incorrect Results from Aggregate Query with UDF

Terence Siganakis

Valued Member

Points: 71
More actions
February 12, 2006 at 6:51 pm

#365080

Howdy,
I just stumbled accross something very interesting with the way SQL Server handles Aggregates over user defined functions. It seems that if I do a SUM() on a user defined function, and then another SUM() on the same user defined function with different parameters and where the first parameter of both functions is the same, it will return the value of the first function as the value of the second function (even though they my be different).
Here is some code to duplicate the issue:
CREATE FUNCTION fSimple
(
@Blah INT,
@Number INT
)
RETURNS INT
AS
BEGIN
  RETURN @Number
END
GO
CREATE TABLE #TMP (IntColumn INT)
INSERT INTO #TMP (IntColumn) SELECT 1
INSERT INTO #TMP (IntColumn) SELECT 2
INSERT INTO #TMP (IntColumn) SELECT 3
SELECT
  SUM(dbo.fSimple(1, 1)),
  SUM(dbo.fSimple(1, IntColumn))  -- Wrong!
FROM #TMP
GROUP BY IntColumn

SELECT
  (dbo.fSimple(1, 1)),
  (dbo.fSimple(1, IntColumn)) -- Correct
FROM #TMP
DROP TABLE #TMP
GO
DROP FUNCTION fSimple
GO
It seems however, that this issue has been resolved in SQL Server 2005...

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47797 More actions · Answer 1

Hmm ... I may be missing something here but I don't see where there is an issue with the code sample you've provided. For both the select statements I get

----------- ----------- 
1           1
1           2
1           3

Which is what I would expect seeing as you've got a GROUP BY clause in the first SELECT statement.

--------------------
Colt 45 - the original point and click interface

Terence Siganakis Valued Member Points: 71 More actions · Answer 2

Well that is very very odd...

My first result set is:

1 1

with the second result set being:

1 1

1 2

1 3

What version / service pack are you running? Im running SQL 2000 Ent (although as imentioned before, running it on SQL 2005 provides the correct output (the same result set as you got, and the same as my second result set).

philcart SSC-Forever Points: 47797 More actions · Answer 3

We are running SQL 2000 Sp4

--------------------
Colt 45 - the original point and click interface

Terence Siganakis Valued Member Points: 71 More actions · Answer 4

Well, it turns out I wasnt running SP4... So somewhere along the line this little issue must have been fixed.

That'll teach me not to patch my servers!