[Django] #26551: Django ORM generates invalid PostgreSQL query with Inverted Q() object that crosses relations

[Django]

#26551: Django ORM generates invalid PostgreSQL query with Inverted Q() object 
that
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.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/051.d52ffb027878185d8ddce9ed6b25b2ac%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.