|
From: Oleg B. <ph...@ph...> - 2014-12-30 20:18:41
|
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.
--
Oleg Broytman http://phdru.name/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
