CODEWITHSUNDEEP
sqlsql-servert-sql
Black Dynamite
Black Dynamite

Reputation: 4147

T-SQL: Where clause appears to be ignored

I'm working with SQL Server, and it appears to be returning rows that are filtered AGAINST in my predicate. I've narrowed my query down to something that is small but returns the bad data.

DECLARE @ConnectMagicNumber BIGINT;
SET @ConnectMagicNumber = 4294967296;

SELECT DISTINCT
FLOOR(pe.ID_PE/@ConnectMagicNumber) as 'PE-StaNo',
pe.FName, pe.VName
FROM [dbo].[CRM_CPPE] pe 
WHERE 
-- Predicate below is not being applied
(FLOOR(pe.ID_PE/@ConnectMagicNumber)) > 5000
--
AND pe.FName = 'Augendoppler' OR (pe.FName = 'Batinic' AND pe.VName ='Drasco')

Why on earth is that row with a PE-StaNo of 1 coming back when I explicitly filtered for only ones where with Greater Than 5000.

Any help on what I'm missing would be appreciated. I'm shaken to the core that I can't perform filtering correctly.

Bad SQL Query

Upvotes: 2

Views: 2536

Answers (3)

Jamiec
Jamiec

Reputation: 136074

Because, in essence, without explicit parentheses your query is

...
FROM [dbo].[CRM_CPPE] pe 
WHERE 
((FLOOR(pe.ID_PE/@ConnectMagicNumber)) > 5000 AND pe.FName = 'Augendoppler') 
OR (pe.FName = 'Batinic' AND pe.VName ='Drasco')

Upvotes: 0

S3S
S3S

Reputation: 25112

This is the part that's wrong:

AND pe.FName = 'Augendoppler' OR (pe.FName = 'Batinic' AND pe.VName ='Drasco')

Since Batinic is in the FName it is returning that row.

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33571

You are missing some parenthesis here. You need to keep in mind the order of operations. 

WHERE 
-- Predicate below is not being applied
(FLOOR(pe.ID_PE/@ConnectMagicNumber)) > 5000
--
AND (pe.FName = 'Augendoppler' OR (pe.FName = 'Batinic' AND pe.VName ='Drasco'))

Upvotes: 8

Related Questions