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