Django ORM generates invalid PostgreSQL query with Inverted Q() object that crosses relations

[Erik B. Andersen]

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')

[Erik B. Andersen]

models.py (with comment that has command to run on shell to trigger behavior)

[Tim Graham]

Reproduce at f945fb24a31737c6625205a8cd90eabdf1c33584 with the attached test app.

[François Freitag]

​PR

[Tim Graham]

Left a few comments for improvement.

[Tim Graham <timograham@…>]

In e124d2d:

Fixed #26551 -- Fixed negated Q() queries that span relations.

Prevented queries from reusing trimmed joins.

[Erik B. Andersen]

What release/version of Django will have this fix?

[François Freitag]

This has been merged on master 5 days ago, so Django 2.0 should include this fix.