#29195: Postgres Regression, annotating Exists through a OuterRef query.
-------------------------------------+-------------------------------------
               Reporter:  Oli        |          Owner:  nobody
  Warner                             |
                   Type:  Bug        |         Status:  new
              Component:  Database   |        Version:  2.0
  layer (models, ORM)                |
               Severity:  Normal     |       Keywords:
           Triage Stage:             |      Has patch:  0
  Unreviewed                         |
    Needs documentation:  0          |    Needs tests:  0
Patch needs improvement:  0          |  Easy pickings:  0
                  UI/UX:  0          |
-------------------------------------+-------------------------------------
 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.
 }}}

-- 
Ticket URL: <https://code.djangoproject.com/ticket/29195>
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/052.9f16b6ea06973564b9a05a10e775c42d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to