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.


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

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>

Referenced by