#10099 closed (wontfix)
MySQL 5.0 does not support LIMIT in subqueries
Reported by: | Pavel Anossov | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Keywords: | ||
Cc: | chris@… | Triage Stage: | Accepted |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
As "in" lookup with querysets uses subqueries, it will create a subquery with limit with a sliced queryset. MySQL 5.0 does not support it:
>>> User.objects.filter( group__in = Group.objects.all()[:5] ) # SELECT (...) FROM `users` WHERE `users`.`group_id` IN (SELECT U0.`id` FROM `groups` U0 LIMIT 5) ... NotSupportedError: (1235, "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'") >>> django.db.connection.get_server_version() (5, 0, 67)
Attachments (1)
Change History (18)
comment:1 by , 16 years ago
comment:2 by , 16 years ago
Cc: | added |
---|
by , 16 years ago
Attachment: | 03-subquery-in-limit.diff added |
---|
comment:4 by , 16 years ago
Updated patch to fix issue when the evaluated was empty which was resulting in invalid SQL being generated ("WHERE foo_id IN ()"). This patch getting uglier and uglier. :(
comment:5 by , 16 years ago
I applied the patch, but then got another error message saying "'BaseQuery' object has no attribute 'query'" in this part of the patch:
if lookup_type == 'in': query_uses_limit = value.query.high_mark is not None or \ value.query.low_mark
Sorry for not keeping the traceback, I already rolled back and am trying to work around this another way.
comment:6 by , 16 years ago
Although you don't have the traceback, do you know what query that was causing the problem? That check probably just needs more conditionals. (I mentioned this was ugly already, right?)
comment:7 by , 16 years ago
Thanks for the quick response :-)
I reapplied the patch to get the traceback info for you: http://dpaste.com/118722/
The query is pretty convoluted, here is where it breaks:
mediaobj = get_object_or_404(Media.objects.filter(name=media, record__name=record, record__collection__id__in=accessible_ids(request.user, Collection), storage__id__in=accessible_ids(request.user, Storage)).distinct())
accessible_ids returns a query itself - please let me know if you need more code.
comment:8 by , 16 years ago
Thanks for the traceback. Also thanks for the snippet, but alas it's fairly meaningless as I have no idea what accessible_ids really does. If you could you provide a minimal testcase without all your custom code, that means anybody can reproduce the problem locally and hack on it. Bonus points if it uses the Post
/Comment
models used everywhere else.
(Also, aren't the 'id' bits redundant?)
comment:9 by , 16 years ago
I did some more research on this and I think I tracked in down to a change between Django 1.0 and 1.1 - the QuerySet API reference (http://docs.djangoproject.com/en/dev/ref/models/querysets/#in) has this code example
inner_q = Blog.objects.filter(name__contains='Cheddar').values('pk').query entries = Entry.objects.filter(blog__in=inner_q)
which does not seem to be valid in Django 1.1 anymore.
It still seems to work though if this patch is not applied to the Django codebase, so I did not notice this change before.
I'll rewrite my code according to the latest documentation and see if it works - apologies for anybody's wasted time.
comment:10 by , 16 years ago
milestone: | → 1.1 |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:11 by , 16 years ago
comment:9 is not an accurate diagnosis. The older syntax is still valid (after all, that's basically what is used internally). However, we've now introduced a better public API (the previous method shouldn't have ever really been documented, so as not to expose internal details).
comment:12 by , 16 years ago
milestone: | 1.1 → 1.2 |
---|
Moving to 1.2 as this is not a Django bug and feature freeze is occured.
comment:13 by , 15 years ago
Has patch: | set |
---|
comment:14 by , 15 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
Marking wontfix: there's plenty of places where Django could generate SQL that the database won't support. Abstractions are leaky.
First stab at a patch attached. If/when MySQL every grows support for this, we can just make the 'allow_limit_in_in_subquery' conditional on that version.