4

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.

7
  • 4
    Looks like a bug. Can you add the execution plans of the 2 queries and the exact version? (select @@version;) Dec 7, 2018 at 21:36
  • 6
    Is this reproducible? I.e. can you add an mcve
    – Hannah Vernon
    Dec 7, 2018 at 21:42
  • 7
    Are all of the objects involved tables or are there views involved (that perhaps are using non deterministic constructs)? Dec 7, 2018 at 22:20
  • 4
    To get better answers, start stripping out parts of the query until you can get down to the bare minimum to reproduce it. Start by taking out the joins one at a time - remove TraceCentralProducts from the query, and see if it still happens. Then take out TradeCentalAccounts, see if it still happens. Take out the coalesce and see if it still happens. When you're down to the bare minimum of things in the query where it still happens, post the actual (not estimated) plans at PasteThePlan.com and include that link in your question.
    – Brent Ozar
    Dec 10, 2018 at 12:18
  • 2
    I'm voting to close this question as "unclear what you're asking" since the required details have not been added to the question, and as such the question cannot be reliably answered.
    – Hannah Vernon
    Dec 14, 2018 at 15:19

1 Answer 1

2

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.