NH-3224 - The aggregate function on Nhibernate does not use User type of underlying column

[nhibernate-bot]

Pranav K created an issue — 17th July 2012, 20:40:41:

Hi,
Use case:

Stack: .Net 4.0, Oracle, ODP.Net 11.2, Nhibernate 3.3.1

Problem:

Hibernate mapping:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Accounting.JournalLineItem" assembly="DAS.BusinessEntities">
<class name="JournalLineItem" table="JOURNAL*LINE*ITEM">
<id name="Id" column="Id" />
<property name="Amount" column="AMOUNT" type="DAS.BusinessEntities.UserTypes.BigDecimalUserType, DAS.BusinessEntities" />
<property name="BusinessDate" column="BUSINESS_DATE" />
</class>
</hibernate-mapping>

Class:

public class JournalLineItem
{
	public virtual int Id {get; set;}
	public virtual decimal Amount {get; set;}
	public virtual DateTime BusinessDate {get;set;}

}

Query:

var query = (from journal in session.Query<JournalLineItem>()
                             group journal by new
			     {
				journal.BusinessDate
			     }
			        into groupedJournal
                                 select new
                                        {
						groupedJournal.Key,
						Amount = groupedJournal.Sum(j=> j.Amount)
					});
	
	var result = query.ToList();

When the trace is generated, it shows , something like this:

	SELECT journallin0.Id as col0, journallin0.BusinessDate, cast(sum(journallin0.Amount) as NUMBER(19,5)
	FROM JOURNAL_LINE_ITEM journallin0
	GROUP BY journallin0.Id, journallin0.BusinessDate

The default casting of NUMBER(19,5) can be changed to some higher precision, but .Net still has limitation in consuming numbers.
And we have a huge transactions/numbers to be consumed. That's why in normal query without aggregation it uses User Type.
but in case of aggregation, it simply returns the number in decimal format. There is a possibility of arithmatic overflow.

Please create a way , we can map the output in user type so that we will be able to consume the number in better way.

---

Pranav K added a comment — 26th July 2012, 10:42:27:

Could you please pick the following issue as it is affecting our large number reports.

---

Pranav K added a comment — 8th August 2012, 9:00:37:

Hi, do I need to add any information so that this issue will picked up. I believe I gave most of the information needed to investigate the issue. Thanks.

---

Alexander Zaytsev added a comment — 11th August 2012, 5:45:41:

Pranav K could you provide source for BigDecimalUserType?