Rounding float values in aggregate functions with group by statements (MySQL).

Suppose, we have a table to store some statistical data:

CREATE TABLE IF NOT EXISTS `stats` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`client_id` int(11) NOT NULL,
`clicks_count` int(11) NOT NULL,
`cost` decimal(9,2) NOT NULL DEFAULT '0.00',
`create_date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

which is mapped to:

public class StatsItem
{
public virtual int ID { get; set; }

public virtual int ClientID { get; set; }

public virtual int Count { get; set; }

public virtual decimal Cost { get; set; }

public virtual DateTime CreateDate { get; set; }
}

Then we try to query data and group by date:

var results = session.Query<StatsItem>()
.Where(x => x.ClientID == someID)
.GroupBy(x => x.CreateDate)
.Select(g => {
Date = g.Key,
Clicks = g.Sum(x => x.Count),
Cost = g.Sum(x => x.Cost),
}).ToList();

The generated SQL is something like:

select stats0_.create_date as col_0_0_,
cast(sum(stats0_.hits_count) as SIGNED) as col_1_0_,
cast(sum(stats0_.cost) as DECIMAL) as col_2_0_
from stats stats0_
where stats0_.client_id=?
group by stats0_.create_date

The problem is that we obtain rounded values, if the values of Cost field are floats.
It's gone, if we override MySQL5Dialect class as follows:

protected override void RegisterCastTypes()
{
base.RegisterCastTypes();

// fix bug with losing scale
RegisterCastType(DbType.Decimal, "DECIMAL(19, 5)");
RegisterCastType(DbType.Double, "DECIMAL(19, 5)");
RegisterCastType(DbType.Single, "DECIMAL(19, 5)");
}

But it seems a little bit hacky.

Used MySQL 5.5.22, .NET 4.0