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. :)
------------------------------------------------------------------------------
Dive into the World of Parallel Programming! The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
