This project is archived and is in readonly mode.
average() returns integer values instead of decimals
Reported by Andreas Mayer | December 2nd, 2010 @ 05:05 PM
I have a table "comments" with column "rating".
To get the average:
> t.comments.calculate(:average, 'rating')
=> 4
> t.comments.calculate(:average, 'rating').class
=> Fixnum
which is WRONG!
Expected result:
4.6667
When I have a look at the logs, the generated command is:
SQL (0.1ms) SELECT
AVG(comments
.rating
) AS avg_id FROM
comments
WHERE (comments
.commentable_id =
74 AND comments
.commentable_type = 'Tutorial')
When I run this command manually:
mysql> SELECT AVG(`comments`.`rating`) AS avg_id FROM `comments` WHERE (`comments`.commentable_id = 74 AND `comments`.commentable_type = 'Tutorial');
+--------+
| avg_id |
+--------+
| 4.6667 |
+--------+
which is correct. So, the generated SQL command is correct, but the result is converted to a decimal somehow.
Maybe related to #4514?
Comments and changes to this ticket
-
Andreas Mayer December 3rd, 2010 @ 09:22 PM
- Tag changed from rails 3.0.3, aggregations, arel to rails 3.0.3, activerecord, aggregations
-
Andreas Mayer December 3rd, 2010 @ 09:23 PM
- Tag changed from rails 3.0.3, activerecord, aggregations to rails 3.0.3, activerecord, aggregations, rails3
-
2kan December 18th, 2010 @ 03:50 PM
It works for me in edge for postgres.
ree-1.8.7-2010.02 > Post.calculate(:average, 'rating') => #<BigDecimal:102740628,'0.125E2',18(27)> ree-1.8.7-2010.02 >
-
2kan December 18th, 2010 @ 03:52 PM
It works for me in edge for postgres.
ree-1.8.7-2010.02 > Post.calculate(:average, 'rating') => #<BigDecimal:102740628,'0.125E2',18(27)> ree-1.8.7-2010.02 >
-
Andreas Mayer December 18th, 2010 @ 06:20 PM
Maybe a MySQL driver-related issue? Or could this have been fixed in edge, shall I try?
-
Tiago Fernandes December 19th, 2010 @ 01:10 AM
Think this bug is related with the type cast of the calculated value. the activerecord tries to cast the value based on the type of the column.
If the rating column is of type integer it should calculate integer/integer = integer. If raging is of type float, it calculates float/integer = float.
What is the type of your rating column ?
-
Tiago Fernandes December 19th, 2010 @ 01:11 AM
Did a quick test with a fresh rails install and sqlite3, and the calculated value is different for columns of type integer and float.
-
Andreas Mayer December 19th, 2010 @ 12:34 PM
My :ratings are integer. Maybe this is the problem because the avg() of integer values still can be float - the avg() of 1 and 2 is not 1 but 1.5
-
Jarrett Meyer December 19th, 2010 @ 10:18 PM
In ticket 6158 ( https://rails.lighthouseapp.com/projects/8994/tickets/6158-count-on... ), performing a COUNT() operation on a decimal column is returning a decimal, event though a count should always be a whole integer. I've already added a unit test and patch for that particular patch. I suppose this is the inverse of that issue.
There's a method in /lib/active_record/relation/calculations.rb that coerces the type returned to be the same as the column type.
-
Tiago Fernandes December 19th, 2010 @ 11:52 PM
Think that the bug is related with type_cast method in the schema_definitions.rb
If you create a second field rating2 of type float, and test Post.average(:rating) vs Post.average(:rating2), you will notice that the value class in on both Float, but the class of type is integer and float respectively.
From the comment in the code i think that value is supposed to be String, but as you can test, it can be also a float. A small fix can be: "return value if !value.is_a?(String)" in the beginning of the type_cast method.
Casts value (which is a String) to an appropriate instance.
def type_cast(value)
{mkd-extraction-02893f2974f2d821de0529f9bcc610de}
return nil if value.nil? puts "Class: #{type.class}" puts "Type: #{type}" puts "Value class: #{value.class}" puts "Value: #{value}" case typeend end
-
Tiago Fernandes December 19th, 2010 @ 11:53 PM
# Casts value (which is a String) to an appropriate instance. def type_cast(value) return nil if value.nil? puts "Class: #{type.class}" puts "Type: #{type}" puts "Value class: #{value.class}" puts "Value: #{value}" case type when :string then value when :text then value when :integer then value.to_i rescue value ? 1 : 0 when :float then value.to_f when :decimal then self.class.value_to_decimal(value) when :datetime then self.class.string_to_time(value) when :timestamp then self.class.string_to_time(value) when :time then self.class.string_to_dummy_time(value) when :date then self.class.string_to_date(value) when :binary then self.class.binary_to_string(value) when :boolean then self.class.value_to_boolean(value) else value end end
-
Janis Vitums January 10th, 2011 @ 02:01 PM
This bug comes form commit - we should always cast the value based on the column October 11, 2010 when calculations type casts was changed.
in module Calculations method:
def type_cast_calculated_value(value, column, operation = nil) if value.is_a?(String) || value.nil? case operation when 'count' then value.to_i when 'sum' then type_cast_using_column(value || '0', column) when 'average' then value.try(:to_d) else type_cast_using_column(value, column) end else value end end
was changed to:
def type_cast_calculated_value(value, column, operation = nil)
if value.is_a?(String) || value.nil? case operation when 'count' then value.to_i when 'sum' then type_cast_using_column(value || '0', column) when 'average' then value.try(:to_d) else type_cast_using_column(value, column) end else type_cast_using_column(value, column) end end
new method is not working any more as described in documentation for Calculate method (AVG should return Float)
-
Josh Kalderimis February 9th, 2011 @ 11:12 PM
- Assigned user set to Santiago Pastorino
This issue can be closed as it is already in master
-
Santiago Pastorino February 10th, 2011 @ 12:21 PM
- State changed from new to resolved
- Importance changed from to Low
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
<h2 style="font-size: 14px">Tickets have moved to Github</h2>
The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>
People watching this ticket
Referenced by
- 4514 [PATCH] Check and immediately return value from an average if it's already a BigDecimal May this be related to ticket #6103 ?
- 6158 #count on decimal column returns BigDecimal It looks like a different patch was applied to fix a simi...