Multiple SUM bug?!

I think this is weird - try it and help me figure out whats going on before I go mad......(dont worry about the purpose of this function, it's for example only).

Create the following scaler function.

ALTER FUNCTION dbo.MyFunction(@Value1 float,@Value2 float)
        declare @NewValue float;
        set @NewValue = @Value2;
         RETURN @NewValue;

Now in SQL Analyzer perform this query :-

SUM(dbo.Function(1,2)) AS Result1,
SUM(dbo.Function(3,4)) AS Result2
FROM AnyTableThatContainsRows

You should receve the sum of the second input param and all should be fine... but now try this

SUM(dbo.Function(1,2)) AS Result1,
SUM(dbo.Function(1,4)) AS Result2
FROM AnyTableThatContainsRows

The value in the second SUM is ignored and the same result is returned in both columns....


I have had a few complicated posts along these lines (without resolutions) but seem to have narrowed it down to this fact that if the first input parameter is the same in both SUM's that messes subsequent calculations (or is simply caching the result and thinks it's the same calc being done - doh!).

If you change the first input param all is fine.

If I could award 1^10 points for this I would because I am going mad trying to solve it.

Many thanks.


Who is Participating?
BillAn1Connect With a Mentor Commented:
then I'm afraid I can't help you - the problem is not reproducable.
See if you can reproduce it on another server?
works fine here, except that  you define a function called dbo.MyFunction, but you call a function dbo.function
eiterh you are calling some other function called "function", or else this is not the code you ran.
can you post the exact code?
JAMESAuthor Commented:
Sorry I edited it when putting it into EE and didnt do a very good job - Apart from the function naming (it should be Function) that is the exact code...

Here are my results with MyTable containing 17 rows.

SUM(dbo.Function(1,2)) AS Premium,
SUM(dbo.Function(1,4)) AS Premium2
FROM MyTable

34.00         34.00

Here are results using :-

SUM(dbo.Function(1,2)) AS Premium,
SUM(dbo.Function(2,3)) AS Premium2

34.00        51.00

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

??? works fine , as I say.
what about if you do
SUM(dbo.Function(1,2)) AS Premium,
SUM(dbo.Function(1,4)) AS Premium2
i.e. no FROM clause, (should get 2,4)

or even

dbo.Function(1,2) AS Premium,
dbo.Function(1,4) AS Premium2
JAMESAuthor Commented:

If I do

SUM(dbo.Function(1,2)) AS Premium,
SUM(dbo.Function(1,3)) AS Premium2

I get


2.00             2.00

Life 'aint fair!

and what about if you just do
SELECT SUM(dbo.Function(1,3)) AS Premium2
now do you get 3?
are you 100% sure you are calling the correct function - there's no change of names getting crossed?
JAMESAuthor Commented:
I get 3...

Im 100% im calling the correct function.  Whats even weirder is if I do

(dbo.Function(1,2)) AS Premium,
(dbo.Function(1,3)) AS Premium2

without the SUM's I get 2 and 3 returned.

As soon as i put the SUM in i get 3 and 3..............

My install of SQL Server is straight from shrink-wrap so I dont think I have messed anything up in its config.
JAMESAuthor Commented:
I can reproduce it ;-)

I will try your suggestion and if you dont object i will leave this question open for now.

Thanks anyway.
Please post the full code of your function.
I've tried this on my server and it doesnt seem to have any issues.

Have you tried (just for the heck of it) dropping and creating the fuction (instead of altering it).
Ken SelviaRetiredCommented:
I can not duplicate the problem either.  Can you run each of these and post the results


sp_configure 'show advanced',1

exec master..xp_msver

sp_dboption 'yourdbname'
i 2 could not reproduce the problem

but try 2 more things...

1) drop the function and recreate it
try running the queries.

if this doesnt work, then

2) drop the function and recreate the function to return @value2 directly.

hope this solves ur problem

JAMESAuthor Commented:

Will try and report back.


Here are your details...

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

The command(s) completed successfully.
affinity mask      0      2147483647      0      0
allow updates      0      1      0      0
awe enabled      0      1      0      0
c2 audit mode      0      1      0      0
cost threshold for parallelism      0      32767      5      5
cursor threshold      -1      2147483647      -1      -1
default full-text language      0      2147483647      1033      1033
default language      0      9999      0      0
fill factor (%)      0      100      0      0
index create memory (KB)      704      2147483647      0      0
lightweight pooling      0      1      0      0
locks      5000      2147483647      0      0
max degree of parallelism      0      32      0      0
max server memory (MB)      4      2147483647      2147483647      2147483647
max text repl size (B)      0      2147483647      65536      65536
max worker threads      32      32767      255      255
media retention      0      365      0      0
min memory per query (KB)      512      2147483647      1024      1024
min server memory (MB)      0      2147483647      0      0
nested triggers      0      1      1      1
network packet size (B)      512      65536      4096      4096
open objects      0      2147483647      0      0
priority boost      0      1      0      0
query governor cost limit      0      2147483647      0      0
query wait (s)      -1      2147483647      -1      -1
recovery interval (min)      0      32767      0      0
remote access      0      1      1      1
remote login timeout (s)      0      2147483647      20      20
remote proc trans      0      1      0      0
remote query timeout (s)      0      2147483647      600      600
scan for startup procs      0      1      0      0
set working set size      0      1      0      0
show advanced options      0      1      1      1
two digit year cutoff      1753      9999      2049      2049
user connections      0      32767      0      0
user options      0      32767      0      0

1      ProductName      NULL      Microsoft SQL Server
2      ProductVersion      524288      8.00.194
3      Language      1033      English (United States)
4      Platform      NULL      NT INTEL X86
5      Comments      NULL      NT INTEL X86
6      CompanyName      NULL      Microsoft Corporation
7      FileDescription      NULL      SQL Server Windows NT
8      FileVersion      NULL      2000.080.0194.00
9      InternalName      NULL      SQLSERVR
10      LegalCopyright      NULL      © 1988-2000 Microsoft Corp. All rights reserved.
11      LegalTrademarks      NULL      Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12      OriginalFilename      NULL      SQLSERVR.EXE
13      PrivateBuild      NULL      NULL
14      SpecialBuild      65630      NULL
15      WindowsVersion      170393861      5.1 (2600)
16      ProcessorCount      1      1
17      ProcessorActiveMask      1      00000001
18      ProcessorType      586      PROCESSOR_INTEL_PENTIUM
19      PhysicalMemory      1023      1023 (1072611328)
20      Product ID      NULL      NULL

torn page detection
auto create statistics
auto update statistics

Thank you all very much for the feedback so far.....
JAMESAuthor Commented:
Mystery solved..........................

Use the Service Packs.....

Article;EN-US;288957 refers exactly to this promlem (under the following circumstances) and has been fixed in SP1.  We have only just deployed the SQL Server to this development system so hadnt (at that time) deployed the patched - BIG DOH!

SQL Server may return incorrect results for a query if all of the following conditions are true:
The query contains two or more references to the same aggregate function.
These aggregate functions take as their parameter the results from a call to the same user defined function (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.

The optimizer attempts to find duplicate aggregate functions in the query so that each unique aggregate is only calculated once. That code fails to take into account the secondary parameters that are being passed to a UDF and might incorrectly identify two aggregates as equivalent.

Many thanks for all those that suggested help.

Even though he didnt fix the problem I am going to award the points to BillAn1 for his continued help.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.