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
29195	Postgres Regression, annotating Exists through a OuterRef query.	Oli Warner	Simon Charette	"First proper report here, sorry if I mess it up. I've been using some fairly advance subqueries since they were released but on upgrading a mature-ish 1.11 site to 2.0 I notice that some of these broke. I've distilled this down to a very simple pair of models and a test. This passes on SQLite, fails on Postgres 10 and 9.6. Worked on both in Django 1.11.

All I'm trying to do here is find Booking instances that have any Payments linked to them.

{{{#!python
from django.db import models

class Booking(models.Model):
    pass

class Payment(models.Model):
    booking = models.ForeignKey('Booking', models.CASCADE)
}}}

{{{#!python
from django.test import TestCase
from django.db.models import Exists, OuterRef

from .models import Booking, Payment

class AnnotateTestCase(TestCase):
    def setUp(self):
        self.booking_one = Booking.objects.create()
        self.booking_two = Booking.objects.create()

        Payment.objects.create(booking=self.booking_one)

    def test_annotation(self):
        # find bookings with payments
        payment_query = Payment.objects.order_by().filter(booking=OuterRef('pk')).values('booking')
        booking_query = Booking.objects.all().annotate(has_payments=Exists(payment_query)).filter(has_payments=True)

        self.assertEqual(booking_query.count(), 1)
}}}

I've also tested the master branch. Same problem. Here's the trace.

{{{
Traceback (most recent call last):
  File ""/home/oli/Desktop/testsite/testsite/myapp/tests.py"", line 19, in test_annotation
    self.assertEqual(booking_query.count(), 1)
  File ""/home/oli/Desktop/testsite/django/django/db/models/query.py"", line 382, in count
    return self.query.get_count(using=self.db)
  File ""/home/oli/Desktop/testsite/django/django/db/models/sql/query.py"", line 494, in get_count
    number = obj.get_aggregation(using, ['__count'])['__count']
  File ""/home/oli/Desktop/testsite/django/django/db/models/sql/query.py"", line 479, in get_aggregation
    result = compiler.execute_sql(SINGLE)
  File ""/home/oli/Desktop/testsite/django/django/db/models/sql/compiler.py"", line 1053, in execute_sql
    cursor.execute(sql, params)
  File ""/home/oli/Desktop/testsite/django/django/db/backends/utils.py"", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File ""/home/oli/Desktop/testsite/django/django/db/backends/utils.py"", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File ""/home/oli/Desktop/testsite/django/django/db/backends/utils.py"", line 85, in _execute
    return self.cursor.execute(sql, params)
  File ""/home/oli/Desktop/testsite/django/django/db/utils.py"", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File ""/home/oli/Desktop/testsite/django/django/db/backends/utils.py"", line 85, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: operator does not exist: boolean = integer
LINE 1: ...0 WHERE U0.""booking_id"" = (""myapp_booking"".""id"")) = 1 GROUP ...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
}}}"	Bug	closed	Database layer (models, ORM)	2.0	Release blocker	fixed		Simon Charette	Accepted	1	0	0	0	0	0