This project is archived and is in readonly mode.

#3554 ✓stale

Decimal columns with a scale of 0 are broken using ruby schemas

Reported by Nick | December 9th, 2009 @ 12:47 PM

I'm using columns with an sql_type (MySQL) of DECIMAL(40,0) to store very large numbers (128-bit - IPv6 addresses, if you're curious).

The column definition in the migration I wrote is:
t.decimal :ip_number, :null => false, :default => 0, :precision => 40, :scale => 0

When I run rake db:schema:dump I get this:
t.column "ip_number", :integer, :limit => 40, :precision => 40, :scale => 0, :default => 0, :null => false

(In both 2.1.2 and git master)

I looked through the code and the issue is the simplified_type method of ActiveRecord::ConnectionAdapters::Column class:

[...] def simplified_type(field_type)
case field_type [...]

when /decimal|numeric|number/i
  extract_scale(field_type) == 0 ? :integer : :decimal

[...] end end

If the decimal(n,0) column is storing numbers larger than the maximum allowable value for the integer type, this code is broken.

I hesitate to suggest a particular patch as I don't know a great deal about SQL data types - but I'd suggest always preserving the 'decimal' type regardless of the scale.

Comments and changes to this ticket

  • Nick

    Nick December 9th, 2009 @ 04:48 PM

    Having played with this some more, I've got a better idea of the conceptual nature of the problem.

    A column's simplified_type seems to be there to specify the Ruby type that best represents the data in a particular sql_type - so specifying DECIMAL(n,0) as an integer actually makes a lot of sense in that context, and makes a lot of things easier when working with these numbers in ruby (since you're passing around Fixnum or Bignum objects, not BigDecimal ones).

    The issue is that a column's type is conflated with it's simplified_type - it's assumed that the two are equivalent, when in fact they are not all the time (although they are almost all the time. Maybe this is the only case where the logic is wrong?)

    I think SchemaDumper should probably use sql_type directly, rather than attempting to reconstruct it from the simplified_type and the variously parsed options. Alternatively, there should be a canonical_type (or just type) and a simplified_type (containing what type has currently). Then everything that cares about the best Ruby representation of the object can use simplified_type, and SchemaDumper and other things that interface with the database can use canonical_type/type.


  • Nick

    Nick December 9th, 2009 @ 05:09 PM

    Current monkey-patch. This gets almost everything right (although I wouldn't want to see it inside activerecord!) with one exception: the schema_dumper sets the default to 0.0 (it should be 0).

    @@@ruby module ActiveRecord module ConnectionAdapters

    class Column
      alias_method :base_simplified_type, :simplified_type
      def simplified_type(field_type)
        if field_type.match(/decimal|numeric|number/) &&
           extract_scale(field_type) == 0 &&
  {|c| c.match(/schema_dumper/) }.nitems > 0

    end end

  • Marius Nuennerich

    Marius Nuennerich May 7th, 2010 @ 02:18 PM

    Isn't this fixed with commit 532219fd091837a9312a301c74e0fbf06abab3a8 ?

  • Nick

    Nick May 7th, 2010 @ 02:31 PM

    That does indeed fix the issue - excellent stuff! :)


  • Santiago Pastorino

    Santiago Pastorino February 2nd, 2011 @ 05:02 PM

    • State changed from “new” to “open”

    This issue has been automatically marked as stale because it has not been commented on for at least three months.

    The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.

    Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.

  • Santiago Pastorino

    Santiago Pastorino February 2nd, 2011 @ 05:02 PM

    • State changed from “open” to “stale”

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=""></a>

People watching this ticket

Referenced by