MySQL 5.0 does not support LIMIT in subqueries

[Pavel Anossov]

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)

[Chris Lamb]

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.

[Chris Lamb]

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

[Chris Lamb]

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. :(

[knabar@…]

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.

[Chris Lamb]

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

[knabar@…]

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.

[Chris Lamb]

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

[knabar@…]

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.

[Malcolm Tredinnick]

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

[dc]

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

[Jacob]

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

[Russell Keith-Magee]

(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).

[Russell Keith-Magee]

(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.

[Jacob]

Milestone 1.2 deleted