Negative Indexing on results fails

If we don't support negative indices, we should check for them and raise an exception if we see them.

I implemented negative indices for the storm.sqlobject compatibility layer, but you usually want to avoid them. In many cases, negative indexing will require an additional COUNT() query to be issued to work out what the index actually refers to.

If you are after items at the end of the result set, I'd recommend using order_by() to reverse the order of the set and then use positive indices.

I just filed bug 421829, but it may be a duplicate of this one. Leaving that for the Storm engineers to decide.

Doing a little testing:

    >>> from storm.locals import *
    >>> from storm.tracer import debug
    >>> store = Store(create_database("sqlite:"))
    >>> store.execute("CREATE TABLE foo (id integer primary key, name text)", noresult=True)
    >>> store.execute("INSERT INTO foo VALUES (1, 'one')", noresult=True)
    >>> store.execute("INSERT INTO foo VALUES (2, 'two')", noresult=True)
    >>> store.commit()
    >>> class Foo(object):
    ... __storm_table__ = "foo"
    ... id = Int(primary=True)
    ... name = Unicode()
    ... def __repr__(self):
    ... return "<Foo id=%d name=%r>" % (self.id, self.name)
    ...
    >>> result = store.find(Foo)
    >>> result.order_by(Foo.id)
    <storm.store.ResultSet object at 0x7f71f522bdd0>
    >>> result[0]
    [17:06:19.741235] EXECUTE: u'SELECT foo.id, foo.name FROM foo LIMIT 1', ()
    [17:06:19.741481] DONE
    <Foo id=1 name=u'one'>
    >>> result[1]
    [17:06:21.634938] EXECUTE: u'SELECT foo.id, foo.name FROM foo LIMIT 1 OFFSET 1', ()
    [17:06:21.635120] DONE
    <Foo id=2 name=u'two'>
    >>> result[-1]
    [17:06:26.436038] EXECUTE: u'SELECT foo.id, foo.name FROM foo LIMIT 1 OFFSET -1', ()
    [17:06:26.436222] DONE
    <Foo id=1 name=u'one'>
    >>> result[-2]
    [17:06:27.676154] EXECUTE: u'SELECT foo.id, foo.name FROM foo LIMIT 1 OFFSET -2', ()
    [17:06:27.676375] DONE
    <Foo id=1 name=u'one'>
    >>> list(result[-1:])
    [17:09:15.687092] EXECUTE: u'SELECT foo.id, foo.name FROM foo ORDER BY foo.id LIMIT 9223372036854775807 OFFSET -1', ()
    [17:09:15.687396] DONE
    [<Foo id=1 name=u'one'>, <Foo id=2 name=u'two'>]

The limit clause in the last case comes from the sqlite specific compile code, but all the results are the same for the PostgreSQL backend. I haven't tested the MySQL backend.

At a minimum, we should be raising FeatureError for negative indexing rather than giving incorrect results. It probably isn't a good idea to do a COUNT() like the sqlobject compatibility layer does for the performance reasons listed above.

It might also be worth exposing the order-by reversing code from ResultSet.last() to make it easier for people to grab the last N rows of a result set.

I put together that transcript from a few sessions. All the SQL statements reported by the tracer have the "ORDER BY" clause. The results are otherwise accurate for Storm trunk as of r329.

The order reversing code would permit negative indices to be directly satisfied - reverse, constraint and deliver.