Opened 15 years ago

Closed 14 years ago

Last modified 12 years ago

#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)

03-subquery-in-limit.diff (3.8 KB ) - added by Chris Lamb 15 years ago.

Download all attachments as: .zip

Change History (18)

comment:1 by Chris Lamb, 15 years ago

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.

comment:2 by Chris Lamb, 15 years ago

Cc: chris@… added

comment:3 by Chris Lamb, 15 years ago

Updated patch as it wasn't working when used across a foreign key.

by Chris Lamb, 15 years ago

Attachment: 03-subquery-in-limit.diff added

comment:4 by Chris Lamb, 15 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 knabar@…, 15 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 Chris Lamb, 15 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 knabar@…, 15 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 Chris Lamb, 15 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 knabar@…, 15 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 Jacob, 15 years ago

milestone: 1.1
Triage Stage: UnreviewedAccepted

comment:11 by Malcolm Tredinnick, 15 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 dc, 15 years ago

milestone: 1.11.2

Moving to 1.2 as this is not a Django bug and feature freeze is occured.

comment:13 by paltman, 14 years ago

Has patch: set

comment:14 by Jacob, 14 years ago

Resolution: wontfix
Status: newclosed

Marking wontfix: there's plenty of places where Django could generate SQL that the database won't support. Abstractions are leaky.

comment:15 by Russell Keith-Magee, 14 years ago

(In [12912]) Fixed #12328 -- Corrected the handling of subqueries with ordering and slicing, especially when used in delete subqueries. Thanks to Walter Doekes for the report.

This fixes a feature that isn't available under MySQL and Oracle (Refs #10099).

comment:16 by Russell Keith-Magee, 14 years ago

(In [12914]) [1.1.X] Fixed #12328 -- Corrected the handling of subqueries with ordering and slicing, especially when used in delete subqueries. Thanks to Walter Doekes for the report.

This fixes a feature that isn't available under MySQL and Oracle (Refs #10099).

Backport of r12912 from trunk.

comment:13 by Jacob, 12 years ago

milestone: 1.2

Milestone 1.2 deleted

Note: See TracTickets for help on using tickets.
Back to Top