<div dir="ltr">Hi Matthew!<br><br>Thanks for the reporting - this was a bug, and I just fixed it and upload the new version of code to GitHub.<br><br><span style="font-size:12.8000001907349px">> Why do these generate different SQL?<br></span><br>In the first query Pony tried to optimize the query by replacing subselect with LEFT JOIN+GROUP BY+HAVING, because for many databases subselects are less effective then joins. Pony can do such optimization if an expression inside the aggregate function is just an attribute chain, like sum(customer.orders.products.price). If the subquery is expressed as a full-blown generator, Pony cannot apply such optimization yet, and this is the reason why the second query still uses subselect instead of the left join.<br><br>As it turns out, this optimization had a bug which manifests itself when the query requires the second level of aggregation. In your query you have two different count() - one is for k.users and other is for the entire query result. In such case it is incorrect to put both counts into the same query. Instead of this, it is necessary to wrap the main query into another query, which do second level of aggregation.<br><br>Now, after the bug is fixed, the first (correctly optimized) SQL query should look something like<br><br><span style="font-size:12.8000001907349px">SELECT COUNT(*)<br></span>FROM (<br> SELECT "k".id<br style="font-size:12.8000001907349px"><span style="font-size:12.8000001907349px"> FROM "keyword" "k"</span><br style="font-size:12.8000001907349px"><span style="font-size:12.8000001907349px"> LEFT JOIN "keyword_user" "t-1"</span><br style="font-size:12.8000001907349px"><span style="font-size:12.8000001907349px"> ON "k"."id" = "t-1"."keyword"</span><br style="font-size:12.8000001907349px"><span style="font-size:12.8000001907349px"> WHERE "k"."scheduled" < %(p1)s</span><br style="font-size:12.8000001907349px"><span style="font-size:12.8000001907349px"> GROUP BY "k"."id"</span><br style="font-size:12.8000001907349px"><span style="font-size:12.8000001907349px"> HAVING COUNT(DISTINCT "t-1"."user") > 0<br></span>)<br><br>and the second (not optimized) query should still looks as before:<br><br><span style="font-size:12.8000001907349px">SELECT COUNT(*)</span><br style="font-size:12.8000001907349px"><span style="font-size:12.8000001907349px">FROM "keyword" "k"</span><br style="font-size:12.8000001907349px"><span style="font-size:12.8000001907349px">WHERE (</span><br style="font-size:12.8000001907349px"><span style="font-size:12.8000001907349px"> SELECT COUNT(DISTINCT "t-1"."user")</span><br style="font-size:12.8000001907349px"><span style="font-size:12.8000001907349px"> FROM "keyword_user" "t-1"</span><br style="font-size:12.8000001907349px"><span style="font-size:12.8000001907349px"> WHERE "k"."id" = "t-1"."keyword"</span><br style="font-size:12.8000001907349px"><span style="font-size:12.8000001907349px"> ) > 0</span><br style="font-size:12.8000001907349px"><span style="font-size:12.8000001907349px"> AND "k"."scheduled" < %(p1)s</span><br><br>But the result of these queries should be the same. Maybe some time in the future Pony will be able to optimize the second query as well.<br><br><br>Regards,<br>Alexander Kozlovsky<br><br><br></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, May 19, 2015 at 10:06 AM, Matthew Bell <span dir="ltr"><<a href="mailto:matthewrobertbell@gmail.com" target="_blank">matthewrobertbell@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div><div>Hi,<br><br></div>The problematic query I have is:<br><br>In [9]: count(k for k in Keyword if count(k.users) > 0 and k.scheduled < datetime.date.today())<br>SELECT COUNT(*)<br>FROM "keyword" "k"<br> LEFT JOIN "keyword_user" "t-1"<br> ON "k"."id" = "t-1"."keyword"<br>WHERE "k"."scheduled" < %(p1)s<br>GROUP BY "k"."id"<br>HAVING COUNT(DISTINCT "t-1"."user") > 0<br>{'p1':datetime.date(2015, 5, 19)}<br><br></div>It is always only returning one result, I think because of the distinct.<br><br></div>On the other hand, this query works fine:<br><div><br>In [3]: count(k for k in Keyword if count(u for u in k.users) > 0 and k.scheduled < datetime.date.today())<br>GET CONNECTION FROM THE LOCAL POOL<br>SWITCH TO AUTOCOMMIT MODE<br>SELECT COUNT(*)<br>FROM "keyword" "k"<br>WHERE (<br> SELECT COUNT(DISTINCT "t-1"."user")<br> FROM "keyword_user" "t-1"<br> WHERE "k"."id" = "t-1"."keyword"<br> ) > 0<br> AND "k"."scheduled" < %(p1)s<br>{'p1':datetime.date(2015, 5, 19)}<br><br></div><div>Why do these generate different SQL?<br><br></div><div>Thanks<span class="HOEnZb"><font color="#888888"><br clear="all"></font></span></div><span class="HOEnZb"><font color="#888888"><div><div><div><div><div><br>-- <br><div>Regards,<br><br>Matthew Bell<br></div> </div></div></div></div></div></font></span></div> <br>_______________________________________________<br> ponyorm-list mailing list<br> <a href="mailto:ponyorm-list@ponyorm.org">ponyorm-list@ponyorm.org</a><br> <a href="/ponyorm-list" target="_blank">/ponyorm-list</a><br> <br></blockquote></div><br></div>