If anyone can explain this to me I'd really appreciate it. I've wasted a day and a bit tracking it down but I cant make any logical explanation for why its happening.
SQL Server 2008 R2, running on windows server 2008 standard. Nothing special about the setup.
2 tables, call them tableA and tableB, one contains optional records linked to the other and both contain a bit column called approved which just marks the record as having been checked by an administrator for publication.
I've also got 2 views, which just select the records where approved=1
ViewA is "SELECT Id, ParentId, Approved, ValueField FROM TableA Where Approved=1"
If I execute the following SQL;
SELECT * FROM ViewA LEFT JOIN ViewB ON ViewA.Id=ViewB.TableAId WHERE ViewA.ParentId=1 AND (ViewA.ValueField='x' OR ViewB.OtherValueField='x')
where I know perfectly well that there are no records that satisfy the requirements then I get the correct empty resultset, BUT if I run exactly the same query with a particular ParentId value (881 if it matters) then the rest of the where clause is ignored and I get all the records for that parentid returned whether they have 'x' in a value field or not.
If I take out the views and do it directly from the tables, then it works ok.
If I use any parentId value but 881 then it works ok.
If I take out the parentid=881 clause and just leave in the valuefield='x' parts then it also works ok.
I've tried replacing everything after the AND with variations of "ViewB.OtherValueField is null" etc and it still screws up.
It's as if the combination of "parentid=881" and anything that references viewB means everything after the AND should be ignored
Are you able to mock all of this up with tables, views and sample data that we can run and confirm all this?
It often happens that being that focussed on replicating the environment makes you spot something you missed up until this point.
The only other thing that jumps out is this is R2 and I doubt many people will have this in prod as yet.
PaulMolloy, I am not sure if this applies in your situation or not but I have seen instances where the combination of an Outer Join and the Where predicate produces some 'head-scratching' results. Try moving the ParentID filter from the Where clause to the On clause and see what happends:
ON ViewA.Id = ViewB.TableAId
AND ViewA.ParentId = 881
WHERE ViewA.ValueField = 'x' OR ViewB.OtherValueField = 'x'
If nothing else, this could help performance as the ParentId filter will be applied before joining is done. Otherwise all rows from ViewA will be joined with all rows from ViewB before the Where clause is processed.