[Django] #26551: Django ORM generates invalid PostgreSQL query with Inverted Q() object that crosses relations
Django
crosses relations
-------------------------------+-----------------------------
Reporter: Azendale | Owner: nobody
Type: Bug | Status: new
Component: Documentation | Version: 1.9
Severity: Normal | Keywords: Q(), postgreSQL
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+-----------------------------
I found that the following code creates an invalid PostgreSQL query:
{{{
AgentAgreement.objects.filter(~(Q(book__listings__contract__handoffdate__lte=timezone.now())
& Q(book__listings__contract__returndate=None) ))
}}}
But, this does not
{{{
BookInstance.objects.filter(~(Q(listings__contract__handoffdate__lte=timezone.now())
& Q(listings__contract__returndate=None) ))
}}}
The error I get is:
{{{
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/tmp/test2/testenv/lib/python3.4/site-
packages/django/db/models/query.py", line 234, in __repr__
data = list(self[:REPR_OUTPUT_SIZE + 1])
File "/tmp/test2/testenv/lib/python3.4/site-
packages/django/db/models/query.py", line 258, in __iter__
self._fetch_all()
File "/tmp/test2/testenv/lib/python3.4/site-
packages/django/db/models/query.py", line 1074, in _fetch_all
self._result_cache = list(self.iterator())
File "/tmp/test2/testenv/lib/python3.4/site-
packages/django/db/models/query.py", line 52, in __iter__
results = compiler.execute_sql()
File "/tmp/test2/testenv/lib/python3.4/site-
packages/django/db/models/sql/compiler.py", line 848, in execute_sql
cursor.execute(sql, params)
File "/tmp/test2/testenv/lib/python3.4/site-
packages/django/db/backends/utils.py", line 79, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "/tmp/test2/testenv/lib/python3.4/site-
packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/tmp/test2/testenv/lib/python3.4/site-
packages/django/db/utils.py", line 95, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/tmp/test2/testenv/lib/python3.4/site-
packages/django/utils/six.py", line 685, in reraise
raise value.with_traceback(tb)
File "/tmp/test2/testenv/lib/python3.4/site-
packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: invalid reference to FROM-clause entry
for table "problemdemo_bookinstance"
LINE 1: ...d") WHERE (U3."returndate" IS NULL AND U1."id" = ("problemde...
^
HINT: Perhaps you meant to reference the table alias "u1".
}}}
The SQL generated, (with whitespace added by me) is:
{{{
SELECT "problemdemo_agentagreement"."id",
"problemdemo_agentagreement"."book_id"
FROM "problemdemo_agentagreement"
WHERE
NOT (
"problemdemo_agentagreement"."book_id" IN
(
SELECT U2."book_instance_id" AS Col1
FROM "problemdemo_listing" U2
INNER JOIN "problemdemo_contract" U3
ON (U2."id" = U3."listing_id")
WHERE U3."handoffdate" <= 2016-04-27
19:11:05.130778+00:00
)
AND
"problemdemo_agentagreement"."book_id" IN
(
SELECT U1."id" AS Col1
FROM "problemdemo_bookinstance" U1
LEFT OUTER JOIN "problemdemo_listing" U2
ON (U1."id" = U2."book_instance_id")
LEFT OUTER JOIN "problemdemo_contract" U3
ON (U2."id" = U3."listing_id")
WHERE
(
U3."returndate" IS NULL
AND
U1."id" = ("problemdemo_bookinstance"."id")
)
)
)
}}}
This is using the following models.py:
{{{
from django.db import models
# from django.utils import timezone
# from django.db.models import Q
# from problemdemo.models import *
#
AgentAgreement.objects.filter(~(Q(book__listings__contract__handoffdate__lte=timezone.now())
& Q(book__listings__contract__returndate=None) ))
class BookInstance(models.Model):
someattr = models.CharField(max_length=13, blank=True)
class Contract(models.Model):
listing = models.OneToOneField('Listing', related_name="contract",
null=True, blank=True, default=None)
handoffdate = models.DateTimeField(null=True, blank=True,
default=None)
returndate = models.DateTimeField(null=True, blank=True,
default=None)
class AgentAgreement(models.Model):
book = models.ForeignKey(BookInstance, related_name='delegation')
class Listing(models.Model):
book_instance = models.ForeignKey(BookInstance,
related_name='listings')
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26551>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
crosses relations
Reporter: Azendale | Owner: nobody
Type: Bug | Status: new
Component: Documentation | Version: 1.9
Keywords: Q(), postgreSQL | Triage Stage: Unreviewed
Has patch: 0 | Easy pickings: 0
UI/UX: 0 |
Changes (by Azendale):
* Attachment "models.py" added.
models.py (with comment that has command to run on shell to trigger
behavior)
Django
crosses relations
Reporter: Azendale | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Q(), postgreSQL | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: => 0
* needs_docs: => 0
* component: Documentation => Database layer (models, ORM)
* needs_tests: => 0
* stage: Unreviewed => Accepted
Comment:
Reproduce at f945fb24a31737c6625205a8cd90eabdf1c33584 with the attached
test app.
--
Ticket URL: <https://code.djangoproject.com/ticket/26551#comment:1>
Django
crosses relations
Reporter: Azendale | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Q(), postgreSQL | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* Attachment "t26551.tar.gz" added.
Django
crosses relations
Reporter: Erik B. Andersen | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by François Freitag):
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/7451 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/26551#comment:2>
Django
crosses relations
Reporter: Erik B. Andersen | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Q(), postgreSQL | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* needs_better_patch: 0 => 1
Comment:
Left a few comments for improvement.
--
Ticket URL: <https://code.djangoproject.com/ticket/26551#comment:3>
Django
crosses relations
Reporter: Erik B. Andersen | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Q(), postgreSQL | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by François Freitag):
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/26551#comment:4>
Django
crosses relations
Reporter: Erik B. Andersen | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Q(), postgreSQL | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by François Freitag):
* cc: mail@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/26551#comment:5>
Django
crosses relations
Reporter: Erik B. Andersen | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Keywords: Q(), postgreSQL | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"e124d2da94cc1233729d8f0200809589f6c5afc8" e124d2d]:
{{{
#!CommitTicketReference repository=""
revision="e124d2da94cc1233729d8f0200809589f6c5afc8"
Fixed #26551 -- Fixed negated Q() queries that span relations.
Prevented queries from reusing trimmed joins.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26551#comment:6>
Django
crosses relations
Reporter: Erik B. Andersen | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: Q(), postgreSQL | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Erik B. Andersen):
What release/version of Django will have this fix?
--
Ticket URL: <https://code.djangoproject.com/ticket/26551#comment:7>
Django
crosses relations
Reporter: Erik B. Andersen | Owner: nobody
Type: Bug | Status: closed
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: Q(), postgreSQL | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by François Freitag):
* version: 1.9 => master
Comment:
This has been merged on master 5 days ago, so Django 2.0 should include
this fix.
--
Ticket URL: <https://code.djangoproject.com/ticket/26551#comment:8>