[03:18] <StevenK> wgrant: http://pastebin.ubuntu.com/5876192/
[03:28] <wgrant> StevenK: You generate different code depending on the backend?
[03:49] <StevenK> wgrant: Sadly, I have to.
[03:49] <StevenK>         # DISTINCT ON is not supported in sqlite3, and GROUP BY with all fields
[03:49] <StevenK>         # is not supported in psql.
[03:53] <wgrant> psql is a command-line client; I think you mean PostgreSQL or postgres :)
[03:53] <wgrant> But sounds reasonable
[03:53] <wgrant> Does it work?
[03:54] <StevenK> wgrant: Doesn't that pastebin prove it does?
[03:55] <wgrant> StevenK: No, the tests are inadequate, I suspect
[03:56] <wgrant> eg. the SQLite implementation doesn't have an ORDER BY
[03:56] <StevenK> wgrant: That's on purpose.
[03:57] <wgrant> Oh
[03:57] <StevenK> If I add a ORDER BY, Django adds every field to the GROUP BY and sqlite returns everything
[03:57] <wgrant> How does it work, then?
[03:57] <StevenK> wgrant: TBH, I was happy enough to find a query that sqlite3 actually liked and returned the right data.
[03:58] <wgrant> Sure
[03:58] <wgrant> And we shouldn't write queries based on what Django's ORM supports -- we should write queries that work.
[03:58] <wgrant> Then work out how to make Django like them
[03:58] <wgrant> The sqlite query at present only works by accident
[03:59] <StevenK> GROUP BY is a terrible hack, because Django wants everyone to use their aggregation stuff
[03:59] <wgrant> Sure
[03:59] <wgrant> IDGAF about Django atm
[03:59] <wgrant> Work out SQL that does what you want
[03:59] <wgrant> Then Djangofy it
[03:59] <wgrant> Possibly using lots of quotes...
[03:59] <wgrant> Working > Djangoy
[03:59] <StevenK> wgrant: I hit IDGAF about Django on about Tuesday of last week
[04:00] <StevenK> Seriously considered Pylons
[04:00] <wgrant> s/Pylons/Pyramid/
[04:00] <StevenK> That
[04:00] <wgrant> Would have been a much more sensible choice, but our deployment story for that is, I believe weaker.
[04:00] <wgrant> (I argued for it at the time :P)
[04:00] <StevenK> I remember.
[04:01] <StevenK> We got lifeless'd.
[04:02] <lifeless> are you passive-aggressive trolling ? :)
[04:02] <StevenK> wgrant: http://pastebin.ubuntu.com/5876270/
[04:03] <wgrant> StevenK: Hm?
[04:03] <StevenK> lifeless: A little, this exercise has moved my opinion of Django's ORM from hate to outright loathing
[04:03] <wgrant> I'm not sure why you're not just bypassing most of it here
[04:03] <wgrant> You're not doing any complex object-related queries
[04:03] <wgrant> SQL would probably work just as well, as we do sometimes in Storm
[04:04] <StevenK> The filtering stuff I'm doing for object operation and actor works well enough
[04:04] <StevenK> It's just distinct that makes me want to stab things
[04:08]  * StevenK ponders being evil, grabbing the query string, appending ORDER BY and then executing it
[04:32] <StevenK> Oh, blah.
[04:32] <StevenK> If you ask for a raw query from a QuerySet, it quotes EVERYTHING *except* string data that you're asking the database for.
[06:29] <StevenK> wgrant: I have convinced sqlite and Django to add ORDER BY
[06:31] <StevenK> SELECT "auditor_auditor"."id", "auditor_auditor"."date", "auditor_auditor"."operation", "auditor_auditor"."object", "auditor_auditor"."actor", "auditor_auditor"."comment", "auditor_auditor"."details" FROM "auditor_auditor" WHERE ("auditor_auditor"."object" IN (lp-development:PackageUpload:16) AND "auditor_auditor"."operation" IN (packageupload-accepted, packageupload-rejected)) GROUP BY (object), (operation) ORDER BY object, operation, -date
[06:35] <wgrant> StevenK: Does that work?
[06:35] <wgrant> eg. are the operations performed in the right order?
[06:35]  * wgrant gone for a while
[23:45] <StevenK> wgrant: So I'm not certain why the sqlite test works without the ORDER BY, but it also works with it.
[23:46] <StevenK> (And the code that actually injects ORDER BY so that Django doesn't just go ahead and add every field to the GROUP BY is truly hideous.)
[23:51] <wgrant> StevenK: There could be a flaw in the test, or perhaps it works by accident due to SQLite implicitly sorting the right way
[23:51] <wgrant> Does the behaviour become obviously incorrect if you invert the sort?
[23:51] <wgrant> And does the auditor test suite run across both backends?
[23:51] <StevenK> No
[23:53] <StevenK> wgrant: Hm, if I switch from -date to date in the ORDER BY it doesn't fail
[23:53] <wgrant> Right, so it's probably executing in the wrong order
[23:53] <wgrant> An explain will confir
[23:55] <StevenK> wgrant: EXPLAIN in sqlite is ... odd
[23:55] <wgrant> It's different from PostgreSQL's, yes
[23:56] <StevenK> wgrant: http://pastebin.ubuntu.com/5879198/
[23:57] <wgrant> StevenK: Oh, you probably want EXPLAIN QUERY PLAN
[23:58] <wgrant> Rather than the underlying VM instructions
[23:58] <StevenK> Oh
[23:58] <wgrant> But you can tell from that EXPLAIN that it's doing the sort at the end
[23:58] <wgrant> You may need to use a subselect for sqlite
[23:58] <StevenK> EXPLAIN QUERY PLAN is pretty useless too
[23:59] <wgrant> What does it say?
[23:59] <StevenK> wgrant: http://pastebin.ubuntu.com/5879202/