Possible SQL Bug with clustered indexes and DOP > 1

Hi there,
I think I have identified a possible bug in MS-SQL 2K....
The information below has been obfuscated for provacy reasons, however
the principal is the same...
I have a table which has several fields (nothing interesting). The
table has approx 1M rows. One of the fields [HR_POSITION] is of
datatype varchar(2).
I create a clustered index using several of the fields with
[HR_POSITION] at the top of the clustered index. Lets say the possible
values of [HR_POSITION] are 'L1', 'L2', 'L3', 'L4', 'L5'.
I issue a simple SELECT query as follows:-
SELECT [HR_POSITION],
[FIELD1],
[FIELD2],
[FIELD3],
[FIELD4],
[FIELD5],
[FIELD6],
[FIELD7],
[FIELD8],
[FIELD9],
[FIELD10]
from TABLE_BROKEN
WHERE [FIELD1] = 'Hello'
and [HR_POSITION] != 'L1'
ORDER BY [HR_POSITION]
Believe it or not I get L1 values in the field [HR_POSITION] in the
result set. If include the OPTION (MAXDOP 1) at the end I get a
different result set which appears to be accurate.
Has nay one seen this before, is it documented, am I the first, do I
get a free T-Shirt?
I have replicated the bug on three different machines and have
discussed it with our team here, and no one can seem to come with an
answer.
We are running MS-SQL 2K Enterprise Sp3a, on Win2K Sp4<justin.parry-okeden@.cba.com.au> wrote in message
news:1137382255.509303.42040@.g14g2000cwa.googlegroups.com...
> Hi there,
> I think I have identified a possible bug in MS-SQL 2K....
> The information below has been obfuscated for provacy reasons, however
> the principal is the same...
> I have a table which has several fields (nothing interesting). The
> table has approx 1M rows. One of the fields [HR_POSITION] is of
> datatype varchar(2).
> I create a clustered index using several of the fields with
> [HR_POSITION] at the top of the clustered index. Lets say the possible
> values of [HR_POSITION] are 'L1', 'L2', 'L3', 'L4', 'L5'.
> I issue a simple SELECT query as follows:-
> SELECT [HR_POSITION],
> [FIELD1],
> [FIELD2],
> [FIELD3],
> [FIELD4],
> [FIELD5],
> [FIELD6],
> [FIELD7],
> [FIELD8],
> [FIELD9],
> [FIELD10]
> from TABLE_BROKEN
> WHERE [FIELD1] = 'Hello'
> and [HR_POSITION] != 'L1'
> ORDER BY [HR_POSITION]
> Believe it or not I get L1 values in the field [HR_POSITION] in the
> result set. If include the OPTION (MAXDOP 1) at the end I get a
> different result set which appears to be accurate.
> Has nay one seen this before, is it documented, am I the first, do I
> get a free T-Shirt?
> I have replicated the bug on three different machines and have
> discussed it with our team here, and no one can seem to come with an
> answer.
> We are running MS-SQL 2K Enterprise Sp3a, on Win2K Sp4
>
Is it this issue?
http://support.microsoft.com/kb/814509/en-us
Can you replicate the issue on SQL 2000 SP4?
David|||Hi there David,
After extensive testing last night I came to the following
conclusions...
(*) If I change the datatype of the first member in the list of fields
for the index to a char from a varchar the bug goes away
(*) If I leave the datatype of the first member in the list of fields
for the index as varchar but set the last member of the list to not
nullable the bug goes away.
(*) If I don't change anything and apply sp4 the bug goes away.
I think there is at least two VERY NASTY bugs going on here which have
been addressed in some capacity in SP4.
JPO