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

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]