Re: Why LIMIT and OFFSET are commutative - Mailing list pgsql-general

> 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

    Will return the ordered set {2}.

    The reason for this is that in the first query, the Skip method skips
one element, then takes the remaining two, while in the second query, the
first two elements are taken, and then the first one is skipped.

> <http://www.postgresql.org/docs/8.2/interactive/queries-limit.html>
>> If a limit count is given, no more than that many rows will be returned
>> (but possibly less, if the query itself yields less rows).
> ...
>> OFFSET says to skip that many rows before beginning to return rows.
>
> Why would the position of either clause matter, assuming the clause is in
> a legal position?
> In both your examples, the LIMIT is 'm', so you will get 'm' rows.
>
> In both your examples, the OFFSET is 'n', so you will skip 'n' rows before
> returning those 'm' rows.
>
> I see no inconsistency.

Different results - the first gives results (zero-based) n to n+m-1.
The second gives results 0 to Min(n-1, m-1).

> Also, neither LIMIT nor OFFSET is a binary operator, so the term
> "commutative" has to be understood metaphorically at best.  What exactly
> do you mean by "commutative"?

I meant result should depend on the order of OFFSET and LIMIT clauses are
present in SELECT clause.

SELECT ... OFFSET n LIMIT m
SELECT ... LIMIT m OFFSET n

should return different results in sime cases.

Filtering and ordering are effectively orthogonal. LIMIT and OFFSET are
clearly *not* orthogonal.

Otherwise I see no way to implement efficient LinQ-PostgreSQL driver.


Andrus.