Strange: is null doesn't work when multiple CPUs are used.

30 Jun 2005 5:35 PM

James Ma

Hi,

This is a simple SQL:
select orders_id,keycode,validpayment,moneyreceived,prospect_id
from orders
where Keycode='ABCDEF123' and validpayment='valid' and moneyreceived>0 and
prospect_id is NULL
option (maxdop 1)

If I run with option (maxdop 1), it returns correct lines. But if I run
without that option, it returns all those lines even including prospect_id is
not null. That is incorrect. I used DBCC CHECKTABLE and found nothing wrong.
What can cause this strange problem?

Can anyone here shed some light on this?

Thanks in advance.
James

30 Jun 2005 5:58 PM

Razvan Socol

Hi, James

What version of SQL Server are you using? Try to install SP4 (+the AWE
hotfix), as there are a couple of bugs related to parallel execution
plans, that were fixed in SP4 (KB Articles 822746, 822033, 322871).

Razvan

30 Jun 2005 7:42 PM

David Gugick

James Ma wrote:
Show quote

> Hi,
>
> This is a simple SQL:
> select orders_id,keycode,validpayment,moneyreceived,prospect_id
> from orders
> where Keycode='ABCDEF123' and validpayment='valid' and
> moneyreceived>0 and prospect_id is NULL
> option (maxdop 1)
>
> If I run with option (maxdop 1), it returns correct lines. But if I
> run without that option, it returns all those lines even including
> prospect_id is not null. That is incorrect. I used DBCC CHECKTABLE
> and found nothing wrong. What can cause this strange problem?
>
> Can anyone here shed some light on this?
>
> Thanks in advance.
> James

That's a scary one and hopefully one that is corrected in SP4. Most of
the parallelism bugs prior to SP4 were related to COUNT().

What Service Pack are you currently running?

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

30 Jun 2005 8:25 PM

James Ma

8.00.760(SP3). Now I am trying to persuade admin to apply SP4 on a test
server to look.

Show quote

"David Gugick" wrote:

> James Ma wrote:
> > Hi,
> >
> > This is a simple SQL:
> > select orders_id,keycode,validpayment,moneyreceived,prospect_id
> > from orders
> > where Keycode='ABCDEF123' and validpayment='valid' and
> > moneyreceived>0 and prospect_id is NULL
> > option (maxdop 1)
> >
> > If I run with option (maxdop 1), it returns correct lines. But if I
> > run without that option, it returns all those lines even including
> > prospect_id is not null. That is incorrect. I used DBCC CHECKTABLE
> > and found nothing wrong. What can cause this strange problem?
> >
> > Can anyone here shed some light on this?
> >
> > Thanks in advance.
> > James
>
> That's a scary one and hopefully one that is corrected in SP4. Most of
> the parallelism bugs prior to SP4 were related to COUNT().
>
> What Service Pack are you currently running?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
>