JAMES
asked on
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.
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.
ASKER
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
?????????????????
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
?????????????????
??? 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
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
ASKER
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!
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!
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?
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I can reproduce it ;-)
I will try your suggestion and if you dont object i will leave this question open for now.
Thanks anyway.
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).
Have you tried (just for the heck of it) dropping and creating the fuction (instead of altering it).
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'
sp_helpsort
sp_configure 'show advanced',1
RECONFIGURE
sp_configure
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
cheers
Parag.
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.
ASKER
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.....
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.....
ASKER
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.
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.
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?