Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Ticket #5489 (closed defect: duplicate)

Opened 3 years ago

Last modified 1 year ago

[PATCH] wrong interpretation of :limit in Mysql and PostgreSQL

Reported by: pedrobelo@gmail.com <pedrobelo@gmail.com> Assigned to: rails@bitsweat.net
Priority: normal Milestone: 1.2.7
Component: ActiveRecord Version: 1.1.1
Severity: normal Keywords: integer limit
Cc: bitsweat, tarmo

Description

Ticket #2900 fixed PostgreSQL adapter to use the :limit constraint and generate smallint or bigint columns according to it. In this adapter, :limit is the number of bytes used to store the number.

But in the MySQL adapter :limit is used to output a column name in the format of INT(x), what means "display width of x", not "storage size of x bytes". So, :limit, in MySQL, is the display width of an integer.

Also, the current PostgreSQL adapter does not comply with PostgreSQL storage sizes. For instance, when :limit is 3, it generates a smallint column (that uses 2 bytes for storage).

Attachments

change_mysql_limit_translation.diff (1.7 kB) - added by pedrobelo@gmail.com <pedrobelo@gmail.com> on 06/23/06 18:01:50.
change MySQL adapter to consider limit as the number of bytes, not the display width
change_pg_limit_translation.diff (0.7 kB) - added by pedrobelo@gmail.com <pedrobelo@gmail.com> on 06/23/06 18:03:40.
changes PostgreSQL adapter rules to translate limit according to the oficial docs
test_new_limit_translation.diff (2.5 kB) - added by pedrobelo@gmail.com <pedrobelo@gmail.com> on 06/23/06 18:04:12.
tests
unified.diff (6.2 kB) - added by pedrobelo on 11/06/06 18:13:52.
fixed both mysql and postgres connection adapters, plus tests
change_limit_translation.diff (8.3 kB) - added by tarmo on 08/07/07 02:43:53.
Added sqlite, sqlite3 support; added the test_int_limits to *.drop.sql files for all relevant adapters
change_limit_translation_v2.diff (7.4 kB) - added by pedrobelo on 12/05/07 07:09:54.
updated changes, now against r8279

Change History

06/23/06 18:01:50 changed by pedrobelo@gmail.com <pedrobelo@gmail.com>

  • attachment change_mysql_limit_translation.diff added.

change MySQL adapter to consider limit as the number of bytes, not the display width

06/23/06 18:03:40 changed by pedrobelo@gmail.com <pedrobelo@gmail.com>

  • attachment change_pg_limit_translation.diff added.

changes PostgreSQL adapter rules to translate limit according to the oficial docs

06/23/06 18:04:12 changed by pedrobelo@gmail.com <pedrobelo@gmail.com>

  • attachment test_new_limit_translation.diff added.

tests

07/05/06 01:53:54 changed by david

  • owner changed from David to rails@bitsweat.net.

08/24/06 10:41:06 changed by bitsweat

You can use svn diff to produce a single unified diff. See the directions at the Trac main page.

08/24/06 10:46:18 changed by bitsweat

  • cc set to bitsweat.
  • keywords set to integer limit.
  • status changed from new to closed.
  • resolution set to wontfix.
  • milestone set to 1.2.

Please reopen with an updated patch against svn trunk, since numeric/decimal support has changed some of the affected methods. Roll in #5484 as well. Thanks!

11/06/06 18:13:52 changed by pedrobelo

  • attachment unified.diff added.

fixed both mysql and postgres connection adapters, plus tests

11/06/06 18:16:18 changed by pedrobelo

  • status changed from closed to reopened.
  • resolution deleted.

k! added a new and unified diff (now against r5439), including changes from #5484.

07/07/07 00:05:08 changed by nzkoz

This breaks sqlite, and probably the commercial dbs too. If you can incorporate the same logic for sqlite, the commercial dbs will follow suit themselves.

08/05/07 02:17:45 changed by nzkoz

Jeremy, any thoughts?

08/07/07 02:43:53 changed by tarmo

  • attachment change_limit_translation.diff added.

Added sqlite, sqlite3 support; added the test_int_limits to *.drop.sql files for all relevant adapters

08/07/07 02:50:06 changed by tarmo

Tests pass on mysql 5.0.45, postgresql 8.2.4, sqlite-2.8.16, sqlite-3.4.1

08/07/07 02:50:19 changed by tarmo

  • cc changed from bitsweat to bitsweat, tarmo.

12/05/07 07:09:54 changed by pedrobelo

  • attachment change_limit_translation_v2.diff added.

updated changes, now against r8279

06/12/08 22:43:49 changed by pedrobelo

Gee. Can anyone do something about this?

Let me explain what's going on in this ticket because there are 3 different bugs being fixed, since I was asked to merge with #5484.

To understand the bugs, let's begin with a single migration that creates a table with one column: 

  t.column :limit3, :integer, :limit => 3

1) When you run this migration under postgresql, the column is dumped to schema.rb without the limit. Like this: 

  t.column "limit3", :integer

The patch in this ticket fixes the postgresql adapter to dump columns with limits.

2) Also in postgresql, the above migration will generate a smallint column, which uses 2 bytes for storage. I think it should generate an integer column, so it can handle 3 bytes without overflow -- and this is also covered by the patch attached.

3) Finally, in mysql the migration will generate a INT(3) column, what means a column with display size of 3, and not a column with 3 bytes for storage. The patch changes mysql adapter to use the limit constraint to generate smallint, mediumint, int and bigint columns accordingly.

06/16/08 11:20:43 changed by bitsweat

  • status changed from reopened to closed.
  • resolution set to duplicate.

Moved to Lighthouse: http://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/420-interpret-limit-as-number-of-bytes