Nicolas Williams <[EMAIL PROTECTED]> wrote:
On Fri, Jan 26, 2007 at 12:58:13PM -0500, Igor Tandetnik wrote:
Nicolas Williams
<[EMAIL PROTECTED]>
wrote:
I can't see why this doesn't work reliably, but if it did it would
be O(1).
Imagine that you have just two records in your table, with ROWIDs of
1
and 1000. So the inner select retrieves 1000, then you produce a
random
number X between 0 and 999, then you execute "SELECT ... OFFSET X"
against a two-row table. It is very likely that X>=2, in which case
you
end up with an empty set.
But I read that as "goto to offset 2 and return the first row after
offset 2."
Why offset 2, when the clause reads, say, OFFSET 500? Also, there are
just two rows, at offset 0 and offset 1. What do you mean by "return
first row after offset 2"? There are none.
You seem to be confusing offset with rowid. These are two very different
things, e.g. in a sparse table, or when OFFSET clause is applied to a
SELECT statement involving more than one table where the resultset does
not have a meaningful concept of a ROWID in the first place.
You appear to believe the query you show works like this one:
select * from table1 where rowid >= X order by rowid limit 1;
That is, selecting a row with the smallest rowid greater than or equal
to X (where X is that random number; I didn't repeat the subquery for
the sake of clarity). The problem with this statement is that the
probability distribution becomes non-uniform. In my example, the row
with rowid=1000 is 999 times more likely to be picked than the row with
rowid=1.
In my test the two rows were rowid==-5 and rowid==5 and
offsets -1, 0 and 1 all worked, but offset 2 didn't. I still don't
understand that.
Offset 0 retrieved the first row. Offset 1 retrieved the second.
Offset -1 is meaningless: I suspect any negative offset is treated as
offset 0. There is no row at offset 2, so you get none.
Moreover, even if it worked it wouldn't be O(1). "OFFSET X" clause
works
in O(X) time, by actually retrieving and throwing away the first X
records.
Actually, it should be O(log N) (binary search through the btree).
I'm not sure how binary search helps you pick a record at a particular
offset, as opposed to a record with a particular key. Again, you seem to
be confusing the two.
That definitely looks like a bug to me.
No, it's a case of false expectations.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
