SQL Server Query - Weird Behaviour

Question

Consider below SQL.

   SELECT DISTINCT bvc_Order.ID,
            bvc_OrderItem.ProductID,
            bvc_OrderItem_BundleItem.ProductID
FROM dbo.bvc_OrderItem WITH (nolock)
RIGHT OUTER JOIN dbo.bvc_Order WITH (nolock)
LEFT OUTER JOIN dbo.bvc_User WITH (nolock) ON dbo.bvc_Order.UserID = dbo.bvc_User.ID
LEFT OUTER JOIN dbo.Amazon_Merchants WITH (nolock) ON dbo.bvc_Order.CompanyID = dbo.Amazon_Merchants.ID ON dbo.bvc_OrderItem.OrderID = dbo.bvc_Order.ID
LEFT OUTER JOIN dbo.bvc_OrderItem_BundleItem WITH (nolock) ON dbo.bvc_OrderItem.ID = dbo.bvc_OrderItem_BundleItem.OrderItemID
LEFT OUTER JOIN dbo.bvc_Product WITH (nolock) ON dbo.bvc_OrderItem.ProductID = dbo.bvc_Product.ID
WHERE 1=1
AND (bvc_Order.StatusCode <> 1
   AND bvc_Order.StatusCode <> 999)
AND ( bvc_OrderItem.ProductID IN ('28046_00')
   OR bvc_OrderItem_BundleItem.ProductID IN ('28046_00'))
AND bvc_Order.OrderSource = 56;

The query when I execute against my database, it returns 85 rows. Well, that is not correct. If I just remove the part "AND bvc_Order.OrderSource = 56" it returns back 5 rows which is really correct. Strange..... Another thing, if I remove the part

OR bvc_OrderItem_BundleItem.ProductID IN ('28046_00')

it will also return the 5 rows as expected even with bvc_Order.OrderSource filter. I am not sure why it is adding more rows while I am trying to reduce rows by using filters.

the table bvc_OrderItem_BundleItem doesn't contain any rows for the result order ids or OrderItemIDs

[edit]

Thanks guys, I tried to remove the LEFT/RIGHT Join Mix but Query manager doesn't allows only LEFT, it does add at least one RIGHT join. I updated the SQL to remove extra tables and now we have only three. But same result

SELECT DISTINCT dbo.bvc_Order.ID, dbo.bvc_OrderItem.ProductID, dbo.bvc_OrderItem_BundleItem.ProductID AS Expr1
FROM         dbo.bvc_OrderItem 
LEFT OUTER JOIN dbo.bvc_OrderItem_BundleItem ON dbo.bvc_OrderItem.ID = dbo.bvc_OrderItem_BundleItem.OrderItemId 
RIGHT OUTER JOIN dbo.bvc_Order ON dbo.bvc_OrderItem.OrderID = dbo.bvc_Order.ID

WHERE 1=1
AND (bvc_Order.StatusCode <> 1 AND bvc_Order.StatusCode <> 999)
AND ( 
    bvc_OrderItem.ProductID IN ('28046_00')
   OR bvc_OrderItem_BundleItem.ProductID IN ('28046_00')
  )
AND bvc_Order.OrderSource = 56;

[edit]So far, there is no solution for this. I previously pasted a link in my comment with example data outout for both valid/invalid results with queries. here it is again. http://sameers.me/SQLIssue.xlsx

One thing to remember here is that ALL left join is not possible. Let me explain further bvc_Order contains main order record bvc_ORderItem contains Order Items/Products bvc_ORderItem_BundleItem contains child products of the product which are available in bvC_OrderItem table.

Now NOT Every product has child products, so bvc_OrderItem_BundleItem may not have any record (and in current scenario, there is really no valid row for the orders in bvC_OrderItem_BundleItem). In short, in current scenario, there is NO matching row available in bvc_OrderItem_BundleItem table. If I remove that join for now, it is all okay, but in real world, I can't remove that BundleItem table join ofcourse.

thank you

Answer 1

When you say

WHERE bvc_Order.OrderSource = 56

that evaluates to false when bvc_Order.OrderSource is NULL. If the LEFT/RIGHT join failed then it will be NULL. This effectively turns the LEFT/RIGHT join into an inner join.

You probably should write the predicate into the ON clause. An alternative approach, which might not deliver the same results, is:

WHERE (bvc_Order.OrderSource IS NULL OR bvc_Order.OrderSource = 56)

The other predicates have the same problem:

Another thing, if I remove the part OR bvc_OrderItem_BundleItem.ProductID IN ('28046_00') it will also return the 5 rows as expected

When the join fails bvc_OrderItem_BundleItem.ProductID is NULL.

I also would recommend writing queries manually. If I understand you right this query comes from a designer. It's structure is quite confusing. I'm pulling up the most important comment:

Mixing left and right outer joins in a query is just confusing. You should start by rewriting the from clause to only use one type (and I strongly recommend left outer join). – Gordon Linoff

Answer 2

When you have eliminated the impossible, whatever remains, however improbable, must be the truth? S.H.

It is impossible that an extra AND condition appended to a WHERE clause can ever result in extra rows. That would imply a database engine defect, which I hope I can assume is "impossible". (If not, then I guess it's back to square one).

That fact makes it easier to concentrate on possible reasons:

1. When you comment out

   AND bvc_Order.OrderSource = 56;
   

   then you also comment out the semicolon terminator. Is it possible that there is text following this query that is affecting it? Try putting a semicolon at the end of the previous line to make sure.

2. Depending on the tool you are using to run queries, sometimes when a query fails to execute, the tool mistakenly shows an old result set. Make sure your query is executing correctly by adding a dummy column to the SELECT statement to absolutely prove you are seeing live results. Which tool are you using?