I've discovered a bug in SQL Server 2012 where certain queries can give different - and incorrect - results at certain degrees of parallelism.
Essentially with the reproduction script provided the query will give 8 rows (correct) at MAXDOP 1; 16 rows (duplicates of each row) at MAXDOP 4; and 16 rows (duplicates with two incorrect rows) at MAXDOP 8.
I've attached a setup script and reproduction script. The setup script creates a database called ParallelismBugDB, creates a set of tables, populates those tables with data, and loads statistics for those tables from our production environment (so that the optimizer will choose the offending plan). The reproduction script runs the query that displays these inconsistencies with MAXDOP 1, MAXDOP 4 and MAXDOP 8 (freeing the plan cache between each run).
It would appear that there are two bugs here: the first being the differences between the number of rows returned for MAXDOP 1 and MAXDOP 4 cases; the second (and more concerning) being the logically incorrect results returned in the MAXDOP 8 case.

Tarakpatelon 5/14/2014 at 11:26 AMRecently we have migrated our database from SQL 2008 R2 SP2 to 20012 SP1. One of our stored procedure is calling another stored procedure and it executes many update statements, out of which 2 update statements which were working fine in SQL 2008R2 Standard edition (Prod - 8 processors) as well as developer edition (Dev and QA - 2 processors) are not working as expected in Prod environment (SQL 2012 standard edition -- 8 processors). It works fine in SQL 2012 developer edition (DEV and QA -- 2 processors).

Only two differences we have between SQL 2012 QA and Prod are:

1) SQL Edition is different.

2) Number of processors are different. Prod has 8 processors, while QA has 2 processors.

What I have found so far is, if I run 2 update statements without setting up Maxdop, I am not getting out put as expected. But if I apply maxdop 1 or maxdop 2 settings at query level than I am getting data as expected. It does not work with Maxdop 3 or 4 etc. (row counts are same for update with and without parallelism but data getting updated is different in both cases).

This is strange behavior. Looks like this is one of the bug with SQL 2012 parallelism.

Code:

DECLARE @Temp1 DECIMAL(18,6)

DECLARE @Temp2 DECIMAL(18,6)

DECLARE @Temp3 VARCHAR(20)

DECLARE @fsttime bit

DECLARE @Temp4 decimal(18,6)

DECLARE @CURRENT_YEARTIME VARCHAR(6)

SET @CURRENT_YEARTIME='201410'

set @Temp4=0

SET @Temp1 = 0

SET @Temp2 = 0

SET @fsttime = 1

set @Temp3 = ''

Update T

Set @fsttime = case when T.D1 = @Temp3 then 0 else 1 End

, @Temp2= Case when @fsttime=1 then IsNull(P.[ABC $(Rtl)],0) else @Temp1 End

, T.[A1 $(Rtl)] = IsNull(@Temp2,0)

, [A2 $ $ (Rtl)]=case when cast(T.Year as varchar) + Right(T.Time,2)<@CURRENT_YEARTIME then T.[A2 $ $ (Rtl)] else T.[Sell Thru %]/100 * isnull(nullif(@Temp2 + isnull(T.[A3 $ (Rtl)],0),0),T.[A6 $ (Rtl)]) end

, [Adj Store Total End Inv $ (Rtl)]=case when cast(T.Year as varchar) + Right(T.Time,2) <@CURRENT_YEARTIME then (Isnull(@Temp2,0) + IsNull(T.[Adj Gross Shipment $ (Rtl)],0) + IsNull(T.[Inv Adj $ (Rtl)],0) - IsNull(T.[Inv Liq $ (Rtl)],0) + IsNull(T.[A3 $ (Rtl)],0) + IsNull(T.[A4 $ (Rtl)],0) + IsNull(T.[A5 $ (Rtl)],0) + IsNull(T.[PCR $ (Rtl)],0) - IsNull(case when cast(T.Year as varchar) + Right(T.Time,2)<@CURRENT_YEARTIME then T.[A2 $ $ (Rtl)] else T.[Sell Thru %]/100 * isnull(nullif(@Temp2 + isnull(T.[A3 $ (Rtl)],0),0),T.[A6 $ (Rtl)]) end,0)) - (Isnull(nullif(T.[B1],0),T.[B2]) * T.C1) else 0 end

, @Temp1 = T.[Store Total End Inv $ (Rtl)] = IsNull(@Temp2,0) + IsNull(T.[Adj Gross Shipment $ (Rtl)],0) + IsNull(T.[Inv Adj $ (Rtl)],0) - IsNull(T.[Inv Liq $ (Rtl)],0) + IsNull(T.[A3 $ (Rtl)],0) + IsNull(T.[A4 $ (Rtl)],0) + IsNull(T.[A5 $ (Rtl)],0) + IsNull(T.[PCR $ (Rtl)],0) - IsNull(case when cast(T.Year as varchar) + Right(T.Time,2)<@CURRENT_YEARTIME then T.[A2 $ $ (Rtl)] else T.[Sell Thru %]/100 * isnull(nullif(@Temp2 + isnull(T.[A3 $ (Rtl)],0),0),T.[A6 $ (Rtl)]) end,0) - IsNull(case when cast(T.Year as varchar) + Right(T.Time,2) <@CURRENT_YEARTIME then (Isnull(@Temp2,0) + IsNull(T.[Adj Gross Shipment $ (Rtl)],0) + IsNull(T.[Inv Adj $ (Rtl)],0) - IsNull(T.[Inv Liq $ (Rtl)],0) + IsNull(T.[A3 $ (Rtl)],0) + IsNull(T.[A4 $ (Rtl)],0) + IsNull(T.[A5 $ (Rtl)],0) + IsNull(T.[PCR $ (Rtl)],0) - IsNull(case when cast(T.Year as varchar) + Right(T.Time,2)<@CURRENT_YEARTIME then T.[A2 $ $ (Rtl)] else T.[Sell Thru %]/100 * isnull(nullif(@Temp2 + isnull(T.[A3 $ (Rtl)],0),0),T.[A6 $ (Rtl)]) end,0)) - (Isnull(nullif(T.[B1],0),T.[B2]) * T.C1) else 0 end,0)

, @Temp3 = T.D1

From Test2.dbo.BOMEOMForUDDSuper_BOM_EOM T INNER Test2.dbo.BOMEOMForUDDTmppldata P

on T.D1 = P.D1

And T.D2 = P.D2

And T.D3 = P.D3