• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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)
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
JAMES
Asked:
JAMES
1 Solution
 
BillAn1Commented:
???
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
 
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.

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
 
BillAn1Commented:
??? 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
JAMESAuthor Commented:
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
 
BillAn1Commented:
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
 
JAMESAuthor Commented:
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
 
BillAn1Commented:
then I'm afraid I can't help you - the problem is not reproducable.
See if you can reproduce it on another server?
0
 
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.
0
 
SjoerdVerweijCommented:
Please post the full code of your function.
0
 
ChrisFretwellCommented:
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
 
Ken SelviaRetiredCommented:
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
 
Parag_GujarathiCommented:
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
 
JAMESAuthor Commented:
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
 
JAMESAuthor Commented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now