|
From: Michael R. <mi...@ti...> - 2014-12-30 20:25:23
|
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...@ti...> 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. |
