[SQLObject] Accumulation functions over limited results

[Michael R. <mi...@ti...>]

Hi.

Ran into an issue the other day, which I think could be described as a bug, or 
perhaps a mis-feature, depending on how you look at it.

If you use an accumulator function on a SelectResults object (sum, avg, etc.), 
it will silently do the "wrong thing" if that SelectResults object has a LIMIT 
in it.

Now, I understand that adding a LIMIT to a standard SQL query won't affect the 
result either:

     SELECT AVG(size) FROM Thing ORDER BY size LIMIT 5

This will return the average size of all rows, not just the smallest 5, as if 
the ORDER BY and LIMIT weren't there at all.  In this sense, the current 
behavior of SQLObject is correct.

However, consider this code:

     class Thing(SQLObject):
         size = IntCol()

     things = [Thing(size=n) for n in range(10)]

     allThings = Thing.select()
     smallThings = allThings.orderBy(Thing.q.size).limit(5)
     bigThings = allThings.orderBy(Thing.q.size).reversed().limit(5)
     evenThings = allThings.filter(Thing.q.size % 2 == 0)

     print allThings.avg(Thing.q.size)
     print smallThings.avg(Thing.q.size)
     print bigThings.avg(Thing.q.size)
     print evenThings.avg(Thing.q.size)

In this case, iterating over any of these SelectResults objects works as you 
would expect, but the values returned by accumulators on smallThings and 
bigThings are "wrong".

So, my question is, would it be reasonable for SQLObject to automatically detect 
this, and form a subquery?  A query like this yields the correct results:

     SELECT AVG(size) FROM (SELECT size FROM Thing ORDER BY SIZE LIMIT 5) AS _tmp

If so, it would make user code much easier to read/write, as compared to using 
sqlbuilder to do the same thing.  I was able to do it, but it was painfully 
awkward and ugly.

Alternatively, if that's not possible, maybe at least throw an exception similar 
to .limit(n).count()?

Cheers,
-miker

P.S. Been a long-time SQLObject user--it's one of my favorite Python things.  
Thanks, Oleg, for all your hard work on it.  :)