NH-2983 - Coalesce in projection doesn't work if there is more than 1 Coalesce

[nhibernate-bot]

phillip.haydon created an issue — 21st December 2011, 1:02:04:

Originally posted: http://groups.google.com/group/nhusers/browse_thread/thread/868177e01816a5fb

---

I'll write a test case tonight and add it.

---

I have a query here:

var netSales = uow.Session 
                 .QueryOver<TransactionSummary>(() => tsAlias) 
                 .Where(x => x.EntityId == entityId) 
                 .And(x => x.TransactionDate >= dayOpen) 
                 .And(x => x.TransactionDate < dayClose) 
                 .SelectList(x => x 
                     .SelectSum(xx => xx.SalesExclGstExcl).WithAlias(()  => tsAlias.SalesExclGstExcl) 
                     .SelectSum(xx => xx.TransactionCount).WithAlias(()  => tsAlias.TransactionCount)) 
                 .TransformUsing(Transformers.AliasToBean(typeof(TransactionSummary))) 
                 .Take(1) 
                 .FutureValue(); 

This generates the following SQL

SELECT TOP (1 /** @p0 **/) sum(this*.Sales_Excl_GST_EXCL) as y0*, 
                  sum(this*.TransactionCount)    as y1* 
 FROM   dbo.tbTransactionSummary this_ 
 WHERE  this_.EntityId = 678 /** @p1 **/ 
        and this_.TransactionDate >= '2010-10-06T05:00:00.00' /** @p2 **/ 
        and this_.TransactionDate < '2010-10-07T05:00:00.00' /** @p3 **/; 

Which works perfectly. No issues.

---

The problem is that if I'm looking at a date range that has no data, it returns NULL and breaks. So far so good...

If I add a Coalesce onto the Summed projections:

.SelectList(x => x 
     .SelectSum(xx => xx.SalesExclGstExcl.Coalesce(0m)).WithAlias(() =>  tsAlias.SalesExclGstExcl) 
     .SelectSum(xx => xx.TransactionCount.Coalesce(0)).WithAlias(() =>  tsAlias.TransactionCount)) 

This also generates the correct SQL:

SELECT TOP (1 /** @p0 */) sum(coalesce(this_.Sales_Excl_GST_EXCL, 0 /*  @p1 **/)) as y0_, 
                  sum(coalesce(this*.TransactionCount, 0 /* @p2 */))  as y2* 
 FROM   dbo.tbTransactionSummary this_ 
 WHERE  this_.EntityId = 678 /** @p3 **/ 
        and this_.TransactionDate >= '2010-10-06T05:00:00.00' /** @p4 **/ 
        and this_.TransactionDate < '2010-10-07T05:00:00.00' /** @p5 **/ 

Except if you look at the returned columns, the second one is named 'y2*' instead of 'y1*'

So when the result gets back to NH it throws an exception:

could not execute query 
[ SELECT TOP (@p0)  sum(coalesce(this_.Sales_Excl_GST_EXCL, @p1)) as
y0_, sum(coalesce(this_.TransactionCount, @p2)) as y2_ FROM
dbo.tbTransactionSummary this_ WHERE this_.EntityId = @p3 and
this_.TransactionDate >= @p4 and this_.TransactionDate < @p5 ]
 Name:cp0 - Value:0  Name:cp1 - Value:0  Name:cp2 - Value:678
Name:cp3 - Value:6/10/2010 5:00:00 AM  Name:cp4 - Value:7/10/2010
5:00:00 AM
[SQL: SELECT TOP (@p0)  sum(coalesce(this_.Sales_Excl_GST_EXCL, @p1))
as y0_, sum(coalesce(this_.TransactionCount, @p2)) as y2_ FROM
dbo.tbTransactionSummary this_ WHERE this_.EntityId = @p3 and
this_.TransactionDate >= @p4 and this_.TransactionDate < @p5]