Opened 9 years ago

Closed 9 years ago

Last modified 9 years ago

#26196 closed Bug (fixed)

Django 1.9 ORM generates invalid subqueries for FK fields with 'to_field'

Reported by: Сергей Owned by: Simon Charette
Component: Database layer (models, ORM) Version: 1.9
Severity: Release blocker Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: yes
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

Consider we have two models:
​https://github.com/hellpain/django_to_field/blob/master/example/models.py

Product model referers to ProductCollection using Fk with 'to_field'.
Suppose I want to fetch all Products and I have already filtered ProductCollections:

Code highlighting: 

      product_collections = ProductCollection.objects.filter(id__gte=0)
      products = Product.objects.filter(collection__in=product_collections)

Queries in my project are a bit more complex, but this one has complexity to show bug.
Generated query in django 1.8 looks like

Code highlighting: 

SELECT "example_product"."id", "example_product"."collection_id" FROM "example_product" WHERE "example_product"."collection_id" IN (SELECT U0."slug" FROM "example_productcollection" U0 WHERE U0."id" >= 0)

As we see this is what we wanted to get.
Lets look what sql generates django 1.9

Code highlighting: 

SELECT "example_product"."id", "example_product"."collection_id" FROM "example_product" WHERE "example_product"."collection_id" IN (SELECT U0."id" FROM "example_productcollection" U0 WHERE U0."id" >= 0)

Difference between them is in:

Code highlighting: 

SELECT U0."id"  and  SELECT U0."slug"

As we see ORM on 1.9 didn`t took into attention that we have a 'to_field' param in Product model. Query returns incorrect result.
This example project on github best of all shows problem:
​https://github.com/hellpain/django_to_field/blob/master/example/tests.py
Tests are passing on 1.8 and fail on 1.9
If we wrap first query in list django starts to respect 'to_field' params on both versions:

Code highlighting: 

      product_collections = ProductCollection.objects.filter(id__gte=0)
      products = Product.objects.filter(collection__in=list(product_collections))

Code highlighting: 

SELECT "example_product"."id", "example_product"."collection_id" FROM "example_product" WHERE "example_product"."collection_id" IN (one, two, three)

But we generate 2 queries and this is inefficient approach.
I suppose this bug is somehow connected with
​https://docs.djangoproject.com/en/1.9/releases/1.9/#implicit-queryset-in-lookup-removed
but this improvement influences only on incorrect code where 'in' operator was skipped.
Perhaps while refactoring the issue above this bug have appeared.

May it be fixed in 1.9 release fixes? Can someone find place in django code caused this degradation?

Change History (4)

comment:1 by Simon Charette, 9 years ago

Owner: changed from nobody to Simon Charette
Status: new → assigned
Triage Stage: Unreviewed → Accepted

Regression bisected to b68212f539f206679580afbfd008e7d329c9cd31 which is related to #24267.

In the meantime you can use values() instead of a converting the queryset to a list:

Product.objects.filter(collection__in=product_collections.values('slug'))

comment:2 by Simon Charette, 9 years ago

Has patch: set
Needs documentation: set
Patch needs improvement: set

Here's a ​PR which will require a review from familar with the ORM and a release note.

comment:3 by Tim Graham <timograham@…>, 9 years ago

Resolution: → fixed
Status: assigned → closed

In 46ecfb9b:

Fixed #26196 -- Made sure in lookups use to_field as default.

Thanks Simon Charette for the test.

comment:4 by Tim Graham <timograham@…>, 9 years ago

In 1d9ee18:

[1.9.x] Fixed #26196 -- Made sure in lookups use to_field as default.

Thanks Simon Charette for the test.

Backport of 46ecfb9b3a11a360724e3375ba78c33c46d6a992 from master

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