This project is archived and is in readonly mode.

#6103 ✓resolved
Andreas Mayer

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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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)
    return nil if value.nil? puts "Class: #{type.class}" puts "Type: #{type}" puts "Value class: #{value.class}" puts "Value: #{value}" case type

    {mkd-extraction-02893f2974f2d821de0529f9bcc610de}

    end end

  • Tiago Fernandes

    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

    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)

  • Raimonds Simanovskis
  • Josh Kalderimis

    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

    Santiago Pastorino February 10th, 2011 @ 12:21 PM

    • State changed from “new” to “resolved”
    • Importance changed from “” to “Low”
  • klkk

    klkk May 23rd, 2011 @ 02:53 AM

    louisvuittonwarehouse.com

  • klkk

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>

Referenced by

Pages