Opened 7 years ago

Closed 6 years ago

Last modified 4 years ago

#10099 closed (wontfix)

MySQL 5.0 does not support LIMIT in subqueries

Reported by: Anossov Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: chris@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

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 lamby 7 years ago.

Download all attachments as: .zip

Change History (18)

comment:1 Changed 7 years ago by lamby

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 Changed 7 years ago by lamby

  • Cc chris@… added

comment:3 Changed 7 years ago by lamby

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

Changed 7 years ago by lamby

comment:4 Changed 7 years ago by lamby

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 Changed 7 years ago by 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.

comment:6 Changed 7 years ago by lamby

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 Changed 7 years ago by 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.

comment:8 Changed 7 years ago by lamby

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 Changed 7 years ago by 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.

comment:10 Changed 7 years ago by jacob

  • milestone set to 1.1
  • Triage Stage changed from Unreviewed to Accepted

comment:11 Changed 7 years ago by mtredinnick

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 Changed 6 years ago by dc

  • milestone changed from 1.1 to 1.2

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

comment:13 Changed 6 years ago by paltman

  • Has patch set

comment:14 Changed 6 years ago by jacob

  • Resolution set to wontfix
  • Status changed from new to closed

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

comment:15 Changed 5 years ago by russellm

(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 Changed 5 years ago by russellm

(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 Changed 4 years ago by jacob

  • milestone 1.2 deleted

Milestone 1.2 deleted

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