"Tom Lane" <tgl@sss.pgh.pa.us> writes:
> Sam Mason <sam@samason.me.uk> writes:
>> In relational algebra terms, try thinking about what would happen if you
>> did something like:
>
>> SELECT * FROM foo LIMIT val;
>
>> Where the table foo has more than one row (and val had different values
>> for each row). Which row would the database use? I believe these are
>> the semantics Trevor was referring to.
>
> Exactly: if the numbers are coming from within the query then there is
> no guarantee that there's a unique value to use. The numbers can be
> variable so long as they come from "outside" the query and so are fixed
> over any one query execution. That includes the case of a subselect
> getting its limit from the outer query, for instance ... but not what
> the OP was trying to do.
I suspect there's still some confusion here. I want to try to spell out just
why the original query was entirely nonsensical.
A query like this is saying you want up to "val" records:
SELECT * FROM foo LIMIT val
But if val is a column in foo then it's getting a new *separate* value of val
for every record. So how many records should the query return?
Worse, a query like this:
SELECT * FROM foo OFFSET val
says the output should skip the first "val" records. But that means it somehow
has to predict the future and know that after skipping, say, 50 records it
will magically find a record that says it should in fact have skipped 50
records. Of course then if the next record says it should only have skipped 1
record then what does that mean?
The limit and offset values can be anything, even something "VOLATILE" like
random() or a subquery, but they have to be something which can be evaluated
independently of the output that will then be generated based on them.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!
