Re: Why LIMIT and OFFSET are commutative

"Andrus" <kobruleht2(at)hot(dot)ee> writes:

>> Under what interpretation would the results differ?
>
> Results must differ for easy creation of LinQ-PostgreSQL driver.
> If results are always the same , PostgreSQL should not allow to use both
> order of clauses.
>
> Nicholas explains:
>
> Assuming the ordering is the same on each of them (because Skip and Take
> make no sense without ordering, LINQ to SQL will create an order for you,
> which irritates me to no end, but that's a separate thread), they will
> produce different results.
>
> Say your query will produce the ordered set {1, 2, 3}. Let n = 1, m =
> 2.
>
> The first query:
>
> var query = query.Skip(n).Take(m);
>
> converted to SELECT ... OFFSET n LIMIT m
>
> Will return the ordered set {2, 3}, while the second query:
>
> var query = query.Take(m).Skip(n);
>
> converted to SELECT ... LIMIT m OFFSET n

You should use subqueries if you want to do that. Take() and Skip() can take a
query and rewrite it as:

SELECT * FROM (old query) LIMIT n
or
SELECT * FROM (old query) OFFSET n

So you'll end up with a query like

SELECT * FROM (SELECT * FROM (query) LIMIT n) OFFSET n

or vice versa.

Or alternatively do the arithmetic. If there's already an offset in the query
structure when Skip() is called then add that amount to the offset. I'm
assuming your methods are called on some kind of object which can store
arbitrary state and not simply on a query string.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning