Re: [HACKERS] Solution for LIMIT cost estimation

At 10:01 AM 2/11/00 +1100, Chris Bitmead wrote:
>
>A couple of things occur to me. One is that it sounds like this
>proposal could mean that successive SELECTS with LIMIT could
>execute completely different plans and therefore return inconsistent
>results. For example, let's say I have 26 customers a through z.
>My first call to SELECT name from customer limit 3 might return...
>a
>b
>c
>and then my next SELECT name from customer limit 3, 3 might return
>a
>b
>c
>again, when I might expect d e f. Of course in this case I could SORT,
>but unless name is a unique field that won't work.

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.

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:

donb=# select * from foo;
i
---
1
2
(2 rows)

donb=# delete from foo where i=1;
DELETE 1
donb=# insert into foo values(1);
INSERT 147724 1
donb=# select * from foo;
i
---
2
1
(2 rows)

This isn't the only way to impact the ordering of delivered
rows, either. VACUUM ANALYZE could do it, for instance...

>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...

- 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.