Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    9

    Unanswered: Absolutely mystifying bug

    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.

    TableA
    Id
    ParentId
    Approved
    ValueField

    TableB
    Id
    TableAId
    Approved
    OtherValueField

    I've also got 2 views, which just select the records where approved=1
    i.e.
    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

    It's completely blown my tiny little mind.

    Any ideas?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, could you post the execution plan for the problem version of the query?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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:
    Code:
    SELECT * 
    FROM ViewA 
           LEFT JOIN 
           ViewB 
             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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •