Don Baccus wrote:
> Well...SQL *is* a set language, and the tuples returned aren't guaranteed
> to be returned in the same order from query to query. The order in
> which they're returned is entirely undefined.
Which would make LIMIT a pretty useless function unless you include
every field in your ORDER BY, otherwise LIMIT returns not defined
results.
To keep strict SET based semantics LIMIT should disallowed unless you
ORDER BY a UNIQUE field, or you ORDER BY with every single field in the
clause.
> You MUST establish an order on the target tuples if you expect to
> see them returned in a consistent order. The RDMS only has to
> deliver the tuples that satisfy the query, nothing more.
>
> You aren't guaranteed what you want even with the optimizer the
> way it is:
I know, I know, but the current behaviour is "close enough" for
a lot of applications.
> >The other thing is, I would like at some stage to change limit so
> >that it is attached to a SELECT rather than an entire query so
> >you could...
> >SELECT * from x where y in (SELECT y from z LIMIT 10) LIMIT 20;
> >and I'm not sure how this would interact with that.
>
> Since ORDER BY applies to the target row, the rows returned from
> the subselect would be in indeterminate order anyway...
Oh. Well then I'd like ORDER BY in the subselect too :-).