#28553: Querysets: annotate() columns are forced into a certain position which 
may
disrupt union()
-------------------------------------+-------------------------------------
     Reporter:  David Sanders        |                    Owner:  nobody
         Type:                       |                   Status:  new
  Cleanup/optimization               |
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Flávio Juvenal):

 I've stumbled upon a case in production where this limitation prevents me
 for making a useful query. I've been able to create a test to reproduce
 this problem.
 It works with sqlite but fails with postgres.

 Add this test to {{{tests/queries/test_qs_combinators.py}}}:
 {{{
 #!python
 from django.db.models import F, IntegerField, TextField, Value

 def test_union_with_two_annotated_values_on_different_models(self):
     qs1 = Number.objects.annotate(
         text_annotation=Value('Foo', TextField())
     ).values('text_annotation', 'num')
     qs2 = ReservedName.objects.annotate(
         int_annotation=Value(1, IntegerField()),
     ).values('name', 'int_annotation')
     self.assertEqual(qs1.union(qs2).count(), 10)
 }}}

 In current master (`78f8b80f9b215e50618375adce4c97795dabbb84`), running
 {{{./runtests.py --parallel=1 --settings=tests.test_postgres
 
queries.test_qs_combinators.QuerySetSetOperationTests.test_union_with_two_annotated_values_on_different_models}}}
 fails:
 {{{
 Testing against Django installed in 'django/django'
 Creating test database for alias 'default'...
 Creating test database for alias 'other'...
 System check identified no issues (1 silenced).
 E
 ======================================================================
 ERROR: test_union_with_two_annotated_values_on_different_models
 (queries.test_qs_combinators.QuerySetSetOperationTests)
 ----------------------------------------------------------------------
 Traceback (most recent call last):
   File "django/django/db/backends/utils.py", line 85, in _execute
     return self.cursor.execute(sql, params)
 psycopg2.ProgrammingError: UNION types integer and character varying
 cannot be matched
 LINE 1: ..._annotation" FROM "queries_number") UNION (SELECT "queries_r...
                                                              ^


 The above exception was the direct cause of the following exception:

 Traceback (most recent call last):
   File "django/tests/queries/test_qs_combinators.py", line 140, in
 test_union_with_two_annotated_values_on_different_models
     self.assertEqual(qs1.union(qs2).count(), 10)
   File "django/django/db/models/query.py", line 382, in count
     return self.query.get_count(using=self.db)
   File "django/django/db/models/sql/query.py", line 494, in get_count
     number = obj.get_aggregation(using, ['__count'])['__count']
   File "django/django/db/models/sql/query.py", line 479, in
 get_aggregation
     result = compiler.execute_sql(SINGLE)
   File "django/django/db/models/sql/compiler.py", line 1054, in
 execute_sql
     cursor.execute(sql, params)
   File "django/django/db/backends/utils.py", line 68, in execute
     return self._execute_with_wrappers(sql, params, many=False,
 executor=self._execute)
   File "django/django/db/backends/utils.py", line 77, in
 _execute_with_wrappers
     return executor(sql, params, many, context)
   File "django/django/db/backends/utils.py", line 85, in _execute
     return self.cursor.execute(sql, params)
   File "django/django/db/utils.py", line 89, in __exit__
     raise dj_exc_value.with_traceback(traceback) from exc_value
   File "django/django/db/backends/utils.py", line 85, in _execute
     return self.cursor.execute(sql, params)
 django.db.utils.ProgrammingError: UNION types integer and character
 varying cannot be matched
 LINE 1: ..._annotation" FROM "queries_number") UNION (SELECT "queries_r...
                                                              ^


 ----------------------------------------------------------------------
 Ran 1 test in 0.007s

 FAILED (errors=1)
 Destroying test database for alias 'default'...
 Destroying test database for alias 'other'...
 }}}

 My {{{tests/test_postgres.py}}} is:
 {{{
 DATABASES = {
     'default': {
         'ENGINE': 'django.db.backends.postgresql_psycopg2',
         'NAME': 'django-test',
         'HOST': '127.0.0.1',
     },
     'other': {
         'ENGINE': 'django.db.backends.postgresql_psycopg2',
         'NAME': 'django-test-other',
     }
 }

 SECRET_KEY = "django_tests_secret_key"

 # Use a fast hasher to speed up tests.
 PASSWORD_HASHERS = [
     'django.contrib.auth.hashers.MD5PasswordHasher',
 ]
 }}}

-- 
Ticket URL: <https://code.djangoproject.com/ticket/28553#comment:3>
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/066.a02da7fb540122054b0cb004e4fc2824%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to