Multiple SUM bug?!

Posted on 2004-09-01
Medium Priority
Last Modified: 2008-02-01
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.


Question by:JAMES
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 17

Expert Comment

ID: 11958518
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?

Author Comment

ID: 11958550
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

LVL 17

Expert Comment

ID: 11958594
??? 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Author Comment

ID: 11958639

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!

LVL 17

Expert Comment

ID: 11958698
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?

Author Comment

ID: 11958724
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.
LVL 17

Accepted Solution

BillAn1 earned 2000 total points
ID: 11958762
then I'm afraid I can't help you - the problem is not reproducable.
See if you can reproduce it on another server?

Author Comment

ID: 11958772
I can reproduce it ;-)

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

Thanks anyway.
LVL 18

Expert Comment

ID: 11958991
Please post the full code of your function.

Expert Comment

ID: 11959063
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).
LVL 12

Expert Comment

ID: 11959334
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'

Expert Comment

ID: 11960438
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


Author Comment

ID: 11960487

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.....

Author Comment

ID: 11961287
Mystery solved..........................

Use the Service Packs.....

Article http://support.microsoft.com/default.aspx?scid=kb;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.


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question