Unexpected behaviour from SQL Server SP4 in combination with Win2k
Ali
I am getting anomolies with certain combinations of SQL Server SP4 and
Windows 2003 where my query is grouping by two different columns, but the
resultset returns the first of the two grouped columns displayed in both
columns. If I reverse the order of the fields in the GROUP BY clause the
content of the two columns changes to what used to be the secind column but
the two columns are still identical.
The problem is present on :
Windows 2000 Standard Edition SP4 with SQL Server 2000 Standard Edition SP4
Windows 2003 Standard Edition with SQL Server 2000 Standard Edition
Windows 2003 Standard Edition with SQL Server 2000 Standard Edition SP4
Windows 2003 Standard Edition SP1 with SQL Server 2000 Standard Edition SP4
However I don't believe this to be a problem with my query or data, because
when I run the same query with the same database on the following platforms
it runs as expected, returning different data in each of the two grouping
rows :
Windows XP SP2 with SQL Server 2000 Desktop Edition SP4
Windows 2000 Standard Edition SP4 with SQL Server 2000 Standard Edition
I would appreciate any answers as to why I'm getting this inconsistency.
Further details of the problem follow.
Many thanks.
Details of Problem
I am having a problem with the Group By clause in the following SQL Statement:
DECLARE @datStartReport datetime,
@datEndReport datetime
SET @datStartReport = '20010315 00:00'
SET @datEndReport = '20010325 23:59'
SELECT
ogp1.Level1Code AS 'Group 1 Level 1 Code',
ogp1.Level2Code AS 'Group 1 Level 2 Code'
FROM fcr
LEFT OUTER JOIN ola
ON fcr._orig_line_alloc_id = ola._line_allocation_id
LEFT OUTER JOIN ogp1 ON ola._group1_code = ogp1._code
WHERE
(fcr._date_and_time_of_call between @datStartReport and @datEndReport)
AND fcr._record_status_code = 1
GROUP BY
ogp1.Level1Code,
ogp1.Level2Code
When I run this query under Windows XP SP2 with SQL Server 2000 Desktop
Edition SP4 or Windows 2000 Standard Edition SP4 with SQL Server 2000
Standard Edition SP3a I get the following results (which are correct):
NULL NULL
MK MK1
OP OP1
DD DD2
MK MK2
DD DD1
However, when I run this query on any of the following O/S & SQL combinations:
Windows 2000 Standard Edition SP4 with SQL Server 2000 Standard Edition SP4
Windows 2003 Standard Edition with SQL Server 2000 Standard Edition
Windows 2003 Standard Edition with SQL Server 2000 Standard Edition SP4
Windows 2003 Standard Edition SP1 with SQL Server 2000 Standard Edition SP4
I get a different set of results:
NULL NULL
MK MK
MK MK
OP OP
DD DD
DD DD
The second column displays the same as the first
If remove the Group By clause from the query, the results are as follows:
MK MK2
DD DD2
MK MK1
DD DD2
MK MK1
DD DD2
DD DD2
DD DD1
DD DD2
MK MK2
MK MK1
DD DD2
DD DD2
DD DD1
MK MK1
DD DD2
OP OP1
DD DD1
OP OP1
OP OP1
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
This clearly shows the values for the second column as being distinct from
the 1st column.
In terms of the database objects within the sql query...
fcr and ola are both tables
ogp1 is a view which uses a function to create the two columns which are
being selected in the final query...
function1([Field2],1) AS 'Level1Code',
function1([Field2],2) AS 'Level2Code',
function1 returns a varchar(20)
============================================================================================
Also, please speak to DJC because he has an even better cutdown version of
the query which does not use fcr. I suggest you rerun your results to make
sure that what you paste in the posting is what is actually output by the
query.
regards
Mart
-----Original Message-----
From: Alastair Mills
Sent: Monday, May 23, 2005 12:17 PM
To: Martin Rogers
Subject: Text for MSDN newsgroup
Hi Mart,
Does this look okay to put forward as the problem on a monitored MSDN
newsgroup?
Regards,
Ali
I am having a problem with the Group By clause in the following SQL Statement:
DECLARE @datStartReport datetime,
@datEndReport datetime
SET @datStartReport = '20010315 00:00'
SET @datEndReport = '20010325 23:59'
SELECT ogp1.Level1Code AS 'Group 1 Level 1 Code',
ogp1.Level2Code AS 'Group 1 Level 2 Code'
FROM fcr
LEFT OUTER JOIN ola
ON fcr._orig_line_alloc_id = ola._line_allocation_id
LEFT OUTER JOIN ogp1 ON ola._group1_code = ogp1._code
WHERE
(fcr._date_and_time_of_call between @datStartReport and @datEndReport)
AND fcr._record_status_code = 1
GROUP BY ogp1.Level1Code,ogp1.Level2Code
When I run this query under Windows XP SP2 with SQL Server 2000 Desktop
Edition SP4 or Windows 2000 Standard Edition SP4 with SQL Server 2000
Standard Edition SP3a I get the following results (which are correct):
NULL NULL
MK MK1
OP OP1
DD DD2
MK MK2
DD DD1
However, when I run this query on any of the following O/S & SQL combinations:
Windows 2000 Standard Edition SP4 with SQL Server 2000 Standard Edition SP4
Windows 2003 Standard Edition with SQL Server 2000 Standard Edition
Windows 2003 Standard Edition with SQL Server 2000 Standard Edition SP4
Windows 2003 Standard Edition SP1 with SQL Server 2000 Standard Edition SP4
I get a different set of results:
NULL NULL
MK MK
MK MK
OP OP
DD DD
DD DD
If I remove the Group By clause from the query, the results are as follows:
MK MK2
DD DD2
MK MK1
DD DD2
MK MK1
DD DD2
DD DD2
DD DD1
DD DD2
MK MK2
MK MK1
DD DD2
DD DD2
DD DD1
MK MK1
DD DD2
OP OP1
DD DD1
OP OP1
OP OP1
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
This clearly shows the values for the second column as being distinct from
the 1st column.
In terms of the database objects within the sql query...
fcr and ola are both tables
ogp1 is a view which uses a function to create the two columns which are
being selected in the final query...
function1([Field2],1) AS 'Level1Code',
function1([Field2],2) AS 'Level2Code',
function1 returns a varchar(20)
Steve Kass
It sounds like you are encountering this bug:
http://support.microsoft.com/?kbid=883415
It's an issue with UDF's that are called twice with different
second parameters, and it should have been corrected in
SP4, according to the KB article and the file version numbers.
I'll see if I can reproduce the problem in SP4 and report it to
Microsoft if I can. If it's not too much trouble, it could help if
you posted the CREATE TABLE statements and some
INSERT statements with data that reproduces the problem,
There are some easy workarounds that worked in the past
with this bug, such as making the UDF first parameters appear
different (using [field2] for one and [field2]+0 or [field2]+'' depending
on the type of the parameter).
Steve Kass
Drew University
"Ali" <ABM...@nospam.nospam> wrote in message
news:591798BB-21C4-4002...@microsoft.com...
Ali
Could I send you a backup of the database rather than posting the scripts?
Steve Kass
I was able to reproduce the problem, so there's no need. You
should be able to fix this by setting trace flag 9056 with
DBCC TRACEON (9056)
as mentioned in the Knowledge Base article, or else try one
of the workarounds suggested there.
If the trace flag doesn't work for you, let me know, and I might
want to try to create a repro closer to what you're doing.
I have notified Microsoft of the problem and asked why the
trace flag is still needed after SP4.
By the way, you mentioned that the bug doesn't show up in these two
configurations:
Windows XP SP2 with SQL Server 2000 Desktop Edition SP4
Windows 2000 Standard Edition SP4 with SQL Server 2000 Standard Edition
Can you run
SELECT @@version on both of these to see that SP4 was applied?
I'm not sure how the operating system would be a factor in whether or not
this bug occurred.
SK
"Ali" <ABM...@nospam.nospam> wrote in message
news:7FEDF58A-41DF-445C...@microsoft.com...
Ali
The machine with Windows XP SP2 & SQL Server 2000 Desktop Edition only had
SP3a on. I have upgraded to SP4 and the error occurs in the same manner.
The machine with Windows 2000 Standard Edition SP4 & SQL Server 2000
Standard Edition had SP4 on, but after a reboot, the error occurs in the same
manner.
I look foward to Microsoft's response.
Many thanks for your help.
Ali
Razvan Socol
> I have notified Microsoft of the problem and asked why the
> trace flag is still needed after SP4.
Good question! There are at least a dozen of trace flags introduced in
post-SP3 hotfixes; many of them should all be activated by default:
Trace flag 9256 described in KB 816883 FIX: SQL Server Optimizer May
Underestimate the Cardinality of Range Queries
Trace flag 9185 described in KB 833406 FIX: Cardinality estimates for
literals that are outside the histogram range are very low
Trace flag 3614 described in KB 834798 FIX: SQL Server 2000 may not
start if many users try to log in to SQL Server when SQL Server is
trying to start
Trace flag 9052 described in KB 840406 FIX: Queries that join a view
may run slowly if the view contains outer joins
Trace flag 9054 described in KB 838409 FIX: SQL Server 2000 Service
Pack 1 (SP1) and later builds may not generate an execution plan for a
query, and you receive error message 8623
Trace flag 9056 described in KB 883415 FIX: A user-defined function
returns results that are not correct for a query
Trace flag 9057 described in KB 885442 FIX: You receive a "Server: Msg
8624, Level 16, State 1, Line 3 Internal SQL Server error." error
message when you compile a delete query that contains sub-queries that
use "NOT IN" clauses in SQL Server 2000
I do not understand why Microsoft has created all those trace flags
instead of correcting the default behaviour of SQL Server. Of course,
there are times when there are significant drawbacks introduced by the
hotfix and the advantages gained by enabling them by default are less
significant. So I do understand why the trace flags described in KB
article 841776 or 838459 are not enabled by default. But allowing
incorrect results for a query (like those discussed here, described in
KB 885415) just for the sake of some performance improvement, is
unacceptable, in my opinion, because these incorrect results can go
undetected for a long time (unlike an error, which shows up imediately
and forces you to take actions) and may cause serious problems for the
customers.
Razvan
Razvan Socol
If you turn on the trace flag 9056 with the DBCC command, it remains
enabled only until SQL Server is restarted. To permanently enable this
trace flag, add -T9056 to the startup parameters (using Enterprise
Manager, select YourServer / Properties / Startup Parameters).
Razvan
Steve Kass
Thanks for digging up all these examples. I've got a support case opened
for the issue in this thread, and this will be useful information. I'll
post back
whatever I can about this issue, which I agree is problematic.
Steve