id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
26551	Django ORM generates invalid PostgreSQL query with Inverted Q() object that crosses relations	Erik B. Andersen	nobody	"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')
}}}

"	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed	Q(), postgreSQL	mail@…	Accepted	1	0	0	0	0	0