Solved

Multiple SUM bug?!

Posted on 2004-09-01
14
279 Views
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)
RETURNS float
AS
      BEGIN
        declare @NewValue float;
        set @NewValue = @Value2;
         RETURN @NewValue;
      END


Now in SQL Analyzer perform this query :-

SELECT
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

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

WHY??????????????????

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.

James.

0
Comment
Question by:JAMES
14 Comments
 
LVL 17

Expert Comment

by:BillAn1
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?
0
 

Author Comment

by:JAMES
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.

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

PREMIUM    PREMIUM2
34.00         34.00

Here are results using :-

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

PREMUIM   PREMIUM2
34.00        51.00

?????????????????
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11958594
??? works fine , as I say.
what about if you do
SELECT
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

SELECT
dbo.Function(1,2) AS Premium,
dbo.Function(1,4) AS Premium2
0
 

Author Comment

by:JAMES
ID: 11958639
Ahhhhh.....

If I do

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

I get

PREMIUM      PREMIUM2

2.00             2.00

Life 'aint fair!

0
 
LVL 17

Expert Comment

by:BillAn1
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?
0
 

Author Comment

by:JAMES
ID: 11958724
I get 3...

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

SELECT
(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.
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 500 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?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:JAMES
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.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11958991
Please post the full code of your function.
0
 
LVL 7

Expert Comment

by:ChrisFretwell
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).
0
 
LVL 12

Expert Comment

by:kselvia
ID: 11959334
I can not duplicate the problem either.  Can you run each of these and post the results

sp_helpsort

sp_configure 'show advanced',1
RECONFIGURE
sp_configure

exec master..xp_msver

sp_dboption 'yourdbname'
0
 
LVL 1

Expert Comment

by:Parag_Gujarathi
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

cheers
Parag.
0
 

Author Comment

by:JAMES
ID: 11960487
Parag,

Will try and report back.

KSelvia,

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

Author Comment

by:JAMES
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!

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

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

James.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now