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." 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. > 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). > >Is that a bug? > > Is what a bug? What precisely do you feel is wrong with this picture? In a cut-n-paste error I neglected to show what I describe above: that some offsets work and some don't, even though in all cases there are no rows at those offsets and even though in all cases there are rows with rowids higher and lower than the offset I was trying. Check this out: sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -5; bar ---- y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -4; bar ---- y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -3; bar ---- y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -2; bar ---- y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -1; bar ---- y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 0; bar ---- y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 1; bar ---- x sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 2; sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 3; sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 4; sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 5; sqlite> sqlite> SELECT rowid, * FROM foo; rowi bar ---- -------------- -5 x 5 y sqlite> That definitely looks like a bug to me. Nico -- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
