Why does changing the sort order return a different number of results? Is it a bug? [closed]

Question

I have a query that contains COALESCE in the select list. If I change the order by clause of the query I get a different number of results.

-- 225 rows - with order by  desc
select coalesce(tdo.orderId, -1) , o.* 
FROM dbo.[Order] o
       JOIN dbo.TradeDeskOrder tdo  ON o.orderId = tdo.orderId
       join dbo.CodeValues cv1  ON o.[type] = cv1.iCodeValue 
       AND cv1.vchCodeType = 'Order.Type' AND cv1.vchSource = 'Trading.UI'
       JOIN dbo.TradeCentralAccounts am  ON o.iCfnAccountId = am.id
       JOIN dbo.TradeCentralProducts pm  ON o.iProductID = pm.iProductID  
order by o.orderid desc  

-- 233 rows - order by
select coalesce(tdo.orderId, -1) , o.* 
FROM dbo.[Order] o
       JOIN dbo.TradeDeskOrder tdo  ON o.orderId = tdo.orderId
       join dbo.CodeValues cv1  ON o.[type] = cv1.iCodeValue 
       AND cv1.vchCodeType = 'Order.Type' AND cv1.vchSource = 'Trading.UI'
       JOIN dbo.TradeCentralAccounts am  ON o.iCfnAccountId = am.id
       JOIN dbo.TradeCentralProducts pm  ON o.iProductID = pm.iProductID  
order by o.orderid   

If I remove the coalesce from the select list I get 233 rows regardless of sort order. Is this a bug? I'm using SQL 2014 SP2-CU13.

Answer 1

The two should be equivalent since they only differ in the ORDER BY, and this is last in the logical processing order of the clauses you use.

The physical processing order, however, can be anything the optimizer chooses. In this case I would guess (and it can only be a guess without execution plans for both queries) the DESC causes the optimizer to evaluate the COALESCE later in the plan than in the other query. This causes physical join operators to compare on NULL instead of -1. That comparison fails and so fewer rows are returned.

Anyhoo, you shouldn't get different row counts either way.