Ever since loading SP2, we've noticed that we are getting different results in some of our queries. For example, the following query:

SELECT *
FROM [dbo].[vwr_pr152w1]
WHERE ([dbo].[vwr_pr152w1].[ycurramt] <> 0.000 OR [dbo].[vwr_pr152w1].[ycurrhrs] <> 0.000)
AND [dbo].[vwr_pr152w1].[cemptype] <> 'I'
or ([dbo].[vwr_pr152w1].[ccalcflag] = 'X' OR [dbo].[vwr_pr152w1].[ccalcflag] = 'E')
AND [dbo].[vwr_pr152w1].[HR180ID] = 15

--The above query produces very different results than

SELECT *
FROM [dbo].[vwr_pr152w1]
WHERE ([dbo].[vwr_pr152w1].[ycurramt] <> 0.000 OR [dbo].[vwr_pr152w1].[ycurrhrs] <> 0.000)
AND [dbo].[vwr_pr152w1].[cemptype] <> 'I'
AND [dbo].[vwr_pr152w1].[HR180ID] = 15
or ([dbo].[vwr_pr152w1].[ccalcflag] = 'X' OR [dbo].[vwr_pr152w1].[ccalcflag] = 'E')


ALL I did was move the hr180id = 15 line BEFORE the ccalcflag line. The bottom produces the correct output of 1 employee.
There are no employees with ccalcflag x or e so the or should bring back anything anyway,
but if you run the first query, it brings back every employee in the database.

What changed in SP2 to create this behavior?

Thanks...

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

__________________
========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================

Due to logical operator order of operations, it would make sense that under certain conditions the two queries would yield different results. I am trying to come up with a simple repro to demostrate the difference on the same and different versions of SQL.

Adam Gossage
Lake Wylie, SC, USA

Without working with your database in both environments, I am not sure why you are seeing different results after upgrading to SP2.

The two queries your posted will yield different results for a given condition. This is due to logical operator order of operations. AND is computed before OR.

Operator Precedence (from http://msdn2.microsoft.com/en-us/library/aa276846(SQL.80).aspx)
When a complex expression has multiple operators, operator precedence determines the sequence in which the operations are performed. The order of execution can significantly affect the resulting value.

Operators have these precedence levels. An operator on higher levels is evaluated before an operator on a lower level:

+ (Positive), - (Negative), ~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
^ (Bitwise Exlusive OR), | (Bitwise OR)
NOT
AND
ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
= (Assignment)

Here is an example.

declare @table_flag table (flag1 char(1), flag2 char(1), flag3 char(1))
insert into @table_flag select 'A','A','A'
insert into @table_flag select 'B','B','B'

select * from @table_flag where flag1 = 'B' or flag2 = 'A' and flag3 = 'E'
'B' row is returned
select * from @table_flag where flag1 = 'B' and flag2 = 'E' or flag3 = 'A'
'A' row is returned

Hope this helps!!

Adam Gossage
Lake Wylie, SC, USA

Yeah, this is a stupid oversight on my part. I failed to look at it from the parenthesis standpoint. Proper use of parenthasis and remembering the order of execution would have helped here. Thanks Adam. Greatly appreciated.

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

SELECT *
FROM [dbo].[vwr_pr152w1]
WHERE 0.000 NOT IN ([dbo].[vwr_pr152w1].[ycurramt], [dbo].[vwr_pr152w1].[ycurrhrs])
AND [dbo].[vwr_pr152w1].[cemptype] <> 'I'
AND [dbo].[vwr_pr152w1].[HR180ID] = 15
AND [dbo].[vwr_pr152w1].[ccalcflag] IN ('X', 'E')