Re: Variable LIMIT and OFFSET in SELECTs

On Thu, Nov 15, 2007 at 05:34:43PM +0100, Reg Me Please wrote:
> Il Thursday 15 November 2007 14:09:16 Trevor Talbot ha scritto:
> > On 11/15/07, Reg Me Please <regmeplease(at)gmail(dot)com> wrote:
> > > In any case, what'd be the benefit for not allowing "variables" as LIMIT
> > > and OFFSET argument?
> >
> > When you can fully describe the semantics of your example, you'll
> > probably be able to answer that question too :)
>
> The question is: why not correcting the syntax checker to allow also the
> first solution?

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.

In implementation terms, the problem is that a query is planned without
getting any data from the database. If you're planning a query it helps
to know how many rows you're getting back. If you're getting few rows
back then it's probably better to make the query work differently than
if it's returning lots of rows. Therefore, knowing what the LIMIT is,
at planning time, makes a lot of difference. How would this work in the
presence of arbitrary expressions for LIMIT?

Sam