Solution for LIMIT cost estimation

How about this as a compromise:

If you give an offset without an ORDER BY the offset
is useless if this optimisation is in place. If you
allowed the offset with the optimisation and no
order by it would be encouraging broken behaviour.

So therefore it would be reasonable to optimise a
limit,offset query with no order by as if there were
no offset. This would give consistent results, albeit
it may not choose the best plan. But at least it
won't hurt anyone.

The only snag is that it's not technically correct to
have an offset unless the ORDER BY yields a unique
criteria. If it's not unique, either because that
field is declared UNIQUE or because every single
field is mentioned in the order by, then optimisation
should be turned off if there is an offset. If it is
allowed people will randomly get missing results. I
mean the only purpose of OFFSET is to get something
like consistency between calls.

The thing is, I'll bet a whole lot of people will use
LIMIT,OFFSET with an ORDER BY, just not a fully unique
ORDER BY. That's why I find this "optimisation"
questionable. Unless you're _extremely_ careful with
your ORDER BY clause your results would be crap. Or
if the above idea is implemented, the execution
plan would be crap. If offset were not available,
then none of this would matter.

If this optimisation is implemented, are we going to
carefully explain exactly when an ORDER BY clause will
and won't yield consistent results? Because not just
any ORDER BY is good enough. Anybody who read that
manual page is probably going to be very confused.

--
Chris Bitmead
mailto:chris(at)bitmead(dot)com