Happy to take a stab at making a patch. Mostly, I wanted to see if the community thought it was a good idea before I took the time.
-miker On 12/30/2014 12:18 PM, Oleg Broytman wrote: > Hello! > > On Tue, Dec 30, 2014 at 09:53:45AM -0800, Michael Root <mi...@tippett.com> > wrote: >> 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. > Quite an interesting idea! Do you want to try to implement it and > produce a patch (a pull request)? > >> 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. :) > You are welcome! > > Oleg. ------------------------------------------------------------------------------ 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 sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
