Andrus wrote:
Why Postgres does not throw error when SELECT ... LIMIT ... OFFSET is used ?
That's not sql and should cause error.
Good point, it is not standard SQL. Postgres varies from the standard in
several ways, some of them useful. (INSERT ... RETURNING is a useful one.)
No SQL RDBMS follows the standard, AFAIK. LIMIT / OFFSET is a common
enhancement, but being non-standard, appears in different forms in different
dialects.
However, the LIMIT / OFFSET idiom *is* Postgres SQL, it *is* documented and
therefore it should *not* cause an error when used. That would just be whacky.
Given that the idiom is documented and does work in Postgres's particular
dialect of SQL, it then must work exactly as described in Postgres's
documentation of its particular dialect of SQL. And, hey, presto! It does.
I agree that they should document the extra flexibility it provides in the
order of its clauses. I find it hard to assess such flexibility as a bad
thing; I tend to appreciate it.
They do hint at it in one place - Section VI, Chapter I, _SELECT_, the LIMIT
clause,
<http://www.postgresql.org/docs/8.2/interactive/sql-select.html#SQL-LIMIT>
The LIMIT clause consists of two independent sub-clauses:
LIMIT { /count/ | ALL }
OFFSET /start/
/count/ specifies the maximum number of rows to return,
while /start/ specifies the number of rows to skip
before starting to return rows. When both are specified,
/start/ rows are skipped before starting to count the
/count/ rows to be returned.
The word "independent" indicates at least the either may appear without the
other, and implies that the order of the clauses doesn't matter.
Note also that the behavior of the clause is *exactly* as documented. You
really have to stop resisting that, now, and accept it.
--
Lew
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
