| StevenK | wgrant: http://pastebin.ubuntu.com/5876192/ | 03:18 |
|---|---|---|
| wgrant | StevenK: You generate different code depending on the backend? | 03:28 |
| 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:49 |
| 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:53 |
| StevenK | wgrant: Doesn't that pastebin prove it does? | 03:54 |
| wgrant | StevenK: No, the tests are inadequate, I suspect | 03:55 |
| wgrant | eg. the SQLite implementation doesn't have an ORDER BY | 03:56 |
| StevenK | wgrant: That's on purpose. | 03:56 |
| 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:57 |
| 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:58 |
| 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 | 03:59 |
| 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:00 |
| StevenK | We got lifeless'd. | 04:01 |
| lifeless | are you passive-aggressive trolling ? :) | 04:02 |
| StevenK | wgrant: http://pastebin.ubuntu.com/5876270/ | 04:02 |
| 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:03 |
| 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:04 |
| * StevenK ponders being evil, grabbing the query string, appending ORDER BY and then executing it | 04:08 | |
| 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. | 04:32 |
| === tasdomas_afk is now known as tasdomas | ||
| StevenK | wgrant: I have convinced sqlite and Django to add ORDER BY | 06:29 |
| 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:31 |
| wgrant | StevenK: Does that work? | 06:35 |
| wgrant | eg. are the operations performed in the right order? | 06:35 |
| * wgrant gone for a while | 06:35 | |
| === 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 | ||
| StevenK | wgrant: So I'm not certain why the sqlite test works without the ORDER BY, but it also works with it. | 23:45 |
| 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:46 |
| 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:51 |
| 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:53 |
| StevenK | wgrant: EXPLAIN in sqlite is ... odd | 23:55 |
| wgrant | It's different from PostgreSQL's, yes | 23:55 |
| StevenK | wgrant: http://pastebin.ubuntu.com/5879198/ | 23:56 |
| wgrant | StevenK: Oh, you probably want EXPLAIN QUERY PLAN | 23:57 |
| 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:58 |
| wgrant | What does it say? | 23:59 |
| StevenK | wgrant: http://pastebin.ubuntu.com/5879202/ | 23:59 |
Generated by irclog2html.py 2.7 by Marius Gedminas - find it at mg.pov.lt!