Nicolas Williams <[EMAIL PROTECTED]> wrote:
Sparse tables are a problem. I've tried this sort of thing but it
doesn't work every time for sparse tables:
SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET
(abs(random()) % (SELECT rowid FROM foo ORDER BY rowid DESC LIMIT 1))
- 1;
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.
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.
Can someone explain this:
sqlite> select rowid, * from foo;
rowi bar
---- --------------
-5 x
5 y
sqlite> select * from foo order by rowid limit 1 offset 0;
bar
----
x
sqlite> select * from foo order by rowid limit 1 offset -1;
bar
----
x
sqlite> select * from foo order by rowid limit 1 offset 1;
bar
----
y
sqlite>
Is that a bug?
Is what a bug? What precisely do you feel is wrong with this picture?
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
