Re: [HACKERS] Solution for LIMIT cost estimation

At 10:52 PM 2/10/00 -0500, Tom Lane wrote:

>4. Fascist variant of #3: make LIMIT without ORDER BY be an error.
>
>SQL92 does not define LIMIT at all, so it's not much help in
>deciding what to do. Is there anything in SQL3? What do other
>DBMSes do about this issue? Comments, other variants, better ideas
>anyone?

Well ... for my money I never expected LIMIT to be meaningful in
the sense of being deterministic without an ORDER BY clause.

But ... that doesn't mean that some folks might not want to use
it differently. What if LIMIT 2 were more efficient that COUNT(*)
in order to determine if more than one row satisfies a condition?

I don't know if that's even a remote possibility given the current
implementation, but it is an example where a non-deterministic
tuple ordering might not matter.

But I wouldn't feel badly at all if LIMIT limited to queries
with ORDER BY. I think this could be done gramatically, i.e.

[query] ORDER BY

is the SQL paradign, and you'd just hang LIMIT on ORDER BY (or
more properly at the same grammar level allow them in any order).

[ORDER BY | LIMIT clause]*

in one form of pseudo-grammar, with appropriate semantic checking
so you can't say ORDER BY .. ORDER BY ...

>
>> 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 is only allowed at the top level by SQL92, there
>would be no way for the user to ensure predictable results from
>such a query. I think that'd be a dangerous path to go down.

Yep.

>However, if you had an answer that ensured consistent results from
>queries with sub-LIMITs, I don't see that there'd be any problem
>with allowing the optimizer to optimize 'em.

No, it's not an optimizer problem.

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.