[Django] #26196: Django 1.9 ORM generates invalid subqueries for FK fields with 'to_field'
Django
----------------------------------------------+--------------------
Reporter: hellpain | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.9
Severity: Release blocker | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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:
{{{
#!div style="font-size: 80%"
Code highlighting:
{{{#!python
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
{{{
#!div style="font-size: 80%"
Code highlighting:
{{{#!sql
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
{{{
#!div style="font-size: 80%"
Code highlighting:
{{{#!sql
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:
{{{
#!div style="font-size: 80%"
Code highlighting:
{{{#!sql
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:
{{{
#!div style="font-size: 80%"
Code highlighting:
{{{#!python
product_collections = ProductCollection.objects.filter(id__gte=0)
products =
Product.objects.filter(collection__in=list(product_collections))
}}}
}}}
{{{
#!div style="font-size: 80%"
Code highlighting:
{{{#!sql
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/26196>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: hellpain | Owner: charettes
Type: Bug | Status: assigned
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* status: new => assigned
* needs_better_patch: => 0
* needs_tests: => 0
* owner: nobody => charettes
* needs_docs: => 0
* stage: Unreviewed => Accepted
Comment:
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`:
{{{#!python
Product.objects.filter(collection__in=product_collections.values('slug'))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26196#comment:1>
Django
Reporter: hellpain | Owner: charettes
Type: Bug | Status: assigned
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
* needs_docs: 0 => 1
Comment:
Here's a [https://github.com/django/django/pull/6111 PR] which will
require a review from familar with the ORM and a release note.
--
Ticket URL: <https://code.djangoproject.com/ticket/26196#comment:2>
Django
Reporter: hellpain | Owner: charettes
Component: Database layer | Version: 1.9
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"46ecfb9b3a11a360724e3375ba78c33c46d6a992" 46ecfb9b]:
{{{
#!CommitTicketReference repository=""
revision="46ecfb9b3a11a360724e3375ba78c33c46d6a992"
Fixed #26196 -- Made sure __in lookups use to_field as default.
Thanks Simon Charette for the test.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26196#comment:3>
Django
Reporter: hellpain | Owner: charettes
Type: Bug | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1
Comment (by Tim Graham <timograham@…>):
In [changeset:"1d9ee181fe09a5c3784bbbf802cc522f11ff25ef" 1d9ee18]:
{{{
#!CommitTicketReference repository=""
revision="1d9ee181fe09a5c3784bbbf802cc522f11ff25ef"
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26196#comment:4>