AR aggregate calculations always return BigDecimal

[yolk]

When calling sum or average on a AR-Model it always returns a BigDecimal, even if the used attribute is of type INT:

TimeEntry.sum(:minutes)
=> #<BigDecimal:105dade18,'0.10091662E8',9(18)>

TimeEntry.first.minutes
=> 123

With the mysql gem sum returns an integer:

TimeEntry.sum(:minutes)
=> 10091662

This also occurs when using average, but not maximum or minimum. Is this behaviour intended?

I testet with Rails 3.0.0 on ruby-1.9.2.p0

[brianmario]

This happens because mysql2 casts values down in C based on what type libmysql says the value is. For aggregate functions like this, libmysql says the type is a MYSQL_TYPE_DECIMAL (see http://github.com/brianmario/mysql2/blob/master/ext/mysql2/result.c#L150-157). As a result mysql2 creates a BigDecimal object for Ruby. The reason this doesn't happen for the mysql gem is because it does no type conversion in C, but instead lets ActiveRecord to the conversion in Ruby-land (this happens here: http://github.com/rails/rails/blob/master/activerecord/lib/active_record/relation/calculations.rb#L261-272). As you can see from that ActiveRecord code, when the value passes through that function it's already a BigDecimal object so it gets passed right through. Whereas for the mysql gem it attempts a type-cast based on the original column type you called the SUM() function on - in this case an INT field.
Although this may be the behavior ActiveRecord users expect when using aggregate functions, returning an Integer from an aggregate function using Mysql is wrong according to libmysql.

[yolk]

Thank you for the explanation! Would you suggest to patch AR then to handle this correct behaviour of libmysql/mysql2? Or to learn and live with it ;)?

[brianmario]

I'll have a chat with the Rails guys about this, but I suspect there's no one way to handle it that would be accurate across all of the individual database drivers.

Which is probably why they chose to do it the way it has been done up to this point.

[jduff]

Are you sure there isn't an issue here? When I connect to mysql from the command line and run the same query I get an integer result back, as expected.

[brianmario]

Yes, according to the docs at http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html:

The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE).

[grosser]

This now works on Rails 3.1 + mysql2, but not in Rails2 + mysql2, could you backport it to the 0.2 branch ?