#5316 :limit + :offset incompatibility between SQLite and Postgres - Ruby on Rails

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

This project is archived and is in readonly mode.

#5316 ✓resolved

:limit + :offset incompatibility between SQLite and Postgres

Reported by Sean | August 6th, 2010 @ 01:49 PM | in 3.0.2

SQLlite requires that a SQL statement with an OFFSET command include a LIMIT statement. The way to use an offset to return all remaining records with no limit, is to put a limit of -1. Postgres on the other hand disallows negative limits (since version 8.4), but allows the use of the OFFSET command without the LIMIT to achieve the same effect. (I believe MySQL also prevents negative limits.) That means the same activerecord statement, containing either a negative limit, or no limit, with an offset, won't work in both SQLite and Postgres.

Example:

This will work in Postgres, fail in SQLite:

scope :archives, :offset => 10

This will fail in Postgres, work in SQLite:

scope :archives, :offset => 10, :limit => -1

I'm not sure if this is something that activerecord can account for, or if it's just something developers need to be aware of (and change the code when switching databases). But since AR is supposed to be DB agnostic, it seems the point is that you should be able to swap out the DB without changing the code.

Comments and changes to this ticket

Rohit Arondekar October 7th, 2010 @ 03:33 AM
- State changed from “new” to “open”
- Assigned user set to “Santiago Pastorino”
- Tag set to “activerecord”
- Importance changed from “” to “Low”
Confirmed inconsistency in Rails 3.

I'm guessing that for SQLite setting the :limit to -1 by default should make it work. And for Postgres overriding any negative limit settings to nothing should work.

I could try to work on a patch in that direction, but is this desirable?

P.S Santiago: I assigned to you since I don't know if anybody else is active in AR.
Santiago Pastorino October 7th, 2010 @ 02:50 PM
- Milestone cleared.
- Assigned user changed from “Santiago Pastorino” to “Aaron Patterson”
Jeremy Kemper October 15th, 2010 @ 11:02 PM
- Milestone set to “3.0.2”
[bulk edit]
Ryan Bigg October 16th, 2010 @ 02:38 AM
- Tag changed from “sheepskin boots, activerecord” to “activerecord”
Automatic cleanup of spam.
Ryan Bigg October 19th, 2010 @ 08:36 AM
- Tag cleared.
Automatic cleanup of spam.
Repository October 21st, 2010 @ 01:17 AM
- State changed from “open” to “resolved”
(from [410114e85ac0048de3fd932a5aaac2a11b45be86]) adding a test to ensure offsets with no limits will work [#5316 state:resolved] http://github.com/rails/rails/commit/410114e85ac0048de3fd932a5aaac2...