Annotate ignored when using after union.

[Marc DEBUREAUX]

Using annotate after an union doesn't seem to have any effect at all when evaluated into a view/template and crashes when debugging step by step.

Here the example by using the embedded shell with iPython:

Input:

company_ids = [15, 20]

base_query = TweetCse.objects.exclude(valid=False).filter(target=True).annotate(
                su=F("tw_su__company_id")).values_list("su", flat=True).distinct()

cse_subqueries = [base_query.filter(tw_corpo__company_id=company_id) for company_id in company_ids]

cse_query = TweetCse.objects.none().union(*cse_subqueries, all=True).order_by("su")

str(cse_query.query)

Output:

'(SELECT DISTINCT "generic_twitter"."company_id" AS "su" FROM "cse_tweetcse" INNER JOIN "generic_twitter" ON ("cse_tweetcse"."tw_su_id" = "generic_twitter"."username") INNER JOIN "generic_twitter" T4 ON ("cse_tweetcse"."tw_corpo_id" = T4."username") WHERE (NOT ("cse_tweetcse"."valid" = False AND "cse_tweetcse"."valid" IS NOT NULL) AND "cse_tweetcse"."target" = True AND T4."company_id" = 15)) UNION ALL (SELECT DISTINCT "generic_twitter"."company_id" AS "su" FROM "cse_tweetcse" INNER JOIN "generic_twitter" ON ("cse_tweetcse"."tw_su_id" = "generic_twitter"."username") INNER JOIN "generic_twitter" T4 ON ("cse_tweetcse"."tw_corpo_id" = T4."username") WHERE (NOT ("cse_tweetcse"."valid" = False AND "cse_tweetcse"."valid" IS NOT NULL) AND "cse_tweetcse"."target" = True AND T4."company_id" = 20)) ORDER BY (1) ASC'

Input:

cse_query = cse_query.annotate(nb=Count("su")).filter(nb__gte=len(company_ids))

str(cse_query.query)

Output:

'(SELECT DISTINCT "generic_twitter"."company_id" AS "su" FROM "cse_tweetcse" INNER JOIN "generic_twitter" ON ("cse_tweetcse"."tw_su_id" = "generic_twitter"."username") INNER JOIN "generic_twitter" T4 ON ("cse_tweetcse"."tw_corpo_id" = T4."username") WHERE (NOT ("cse_tweetcse"."valid" = False AND "cse_tweetcse"."valid" IS NOT NULL) AND "cse_tweetcse"."target" = True AND T4."company_id" = 15)) UNION ALL (SELECT DISTINCT "generic_twitter"."company_id" AS "su" FROM "cse_tweetcse" INNER JOIN "generic_twitter" ON ("cse_tweetcse"."tw_su_id" = "generic_twitter"."username") INNER JOIN "generic_twitter" T4 ON ("cse_tweetcse"."tw_corpo_id" = T4."username") WHERE (NOT ("cse_tweetcse"."valid" = False AND "cse_tweetcse"."valid" IS NOT NULL) AND "cse_tweetcse"."target" = True AND T4."company_id" = 20)) ORDER BY (1) ASC'

As you can see, the query is absolutly the same, it does not include the annotation neither the filter on this annotation. But the query construction doesn't fail whereas the evaluation does.

Input:

cse_query

Output:

---------------------------------------------------------------------------
IndexError                                Traceback (most recent call last)
~/.pyenv/versions/motherbase/lib/python3.7/site-packages/IPython/core/formatters.py in __call__(self, obj)
    700                 type_pprinters=self.type_printers,
    701                 deferred_pprinters=self.deferred_printers)
--> 702             printer.pretty(obj)
    703             printer.flush()
    704             return stream.getvalue()

~/.pyenv/versions/motherbase/lib/python3.7/site-packages/IPython/lib/pretty.py in pretty(self, obj)
    400                         if cls is not object \
    401                                 and callable(cls.__dict__.get('__repr__')):
--> 402                             return _repr_pprint(obj, self, cycle)
    403 
    404             return _default_pprint(obj, self, cycle)

~/.pyenv/versions/motherbase/lib/python3.7/site-packages/IPython/lib/pretty.py in _repr_pprint(obj, p, cycle)
    695     """A pprint that just redirects to the normal repr function."""
    696     # Find newlines and replace them with p.break_()
--> 697     output = repr(obj)
    698     for idx,output_line in enumerate(output.splitlines()):
    699         if idx:

~/.pyenv/versions/motherbase/lib/python3.7/site-packages/django/db/models/query.py in __repr__(self)
    248 
    249     def __repr__(self):
--> 250         data = list(self[:REPR_OUTPUT_SIZE + 1])
    251         if len(data) > REPR_OUTPUT_SIZE:
    252             data[-1] = "...(remaining elements truncated)..."

~/.pyenv/versions/motherbase/lib/python3.7/site-packages/django/db/models/query.py in __iter__(self)
    272                - Responsible for turning the rows into model objects.
    273         """
--> 274         self._fetch_all()
    275         return iter(self._result_cache)
    276 

~/.pyenv/versions/motherbase/lib/python3.7/site-packages/django/db/models/query.py in _fetch_all(self)
   1240     def _fetch_all(self):
   1241         if self._result_cache is None:
-> 1242             self._result_cache = list(self._iterable_class(self))
   1243         if self._prefetch_related_lookups and not self._prefetch_done:
   1244             self._prefetch_related_objects()

~/.pyenv/versions/motherbase/lib/python3.7/site-packages/django/db/models/query.py in __iter__(self)
    180         queryset = self.queryset
    181         compiler = queryset.query.get_compiler(queryset.db)
--> 182         for row in compiler.results_iter(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size):
    183             yield row[0]
    184 

~/.pyenv/versions/motherbase/lib/python3.7/site-packages/django/db/models/sql/compiler.py in apply_converters(self, rows, converters)
   1040         for row in map(list, rows):
   1041             for pos, (convs, expression) in converters:
-> 1042                 value = row[pos]
   1043                 for converter in convs:
   1044                     value = converter(value, expression, connection)

IndexError: list index out of range

[Marc DEBUREAUX]

Here the simplified model for testing purposes:

from django.db import models

# Create your models here.


class Company(models.Model):
    name = models.CharField(max_length=100)

    def __str__(self):
        return self.name

    class Meta:
        verbose_name = "company"
        verbose_name_plural = "companies"


class Twitter(models.Model):
    username = models.CharField(
        primary_key=True,
        max_length=128
    )
    company = models.ForeignKey(
        Company,
        related_name="twitters",
        on_delete=models.CASCADE
    )

    def __str__(self):
        return self.username

    class Meta:
        verbose_name = "twitter"


class TweetCse(models.Model):
    tw_su = models.ForeignKey(
        Twitter,
        related_name="cse_su",
        on_delete=models.CASCADE,
        verbose_name="corporate twitter"
    )
    tw_corpo = models.ForeignKey(
        Twitter,
        related_name="cse_corpo",
        on_delete=models.CASCADE,
        verbose_name="startup twitter"
    )
    valid = models.NullBooleanField(blank=True)
    target = models.NullBooleanField(blank=True)

    def __str__(self):
        return f"{self.tw_corpo_id} => {self.tw_su_id}"

    class Meta:
        verbose_name = "corporate-startup relationship"

[Marc DEBUREAUX]

Test project

[Mariusz Felisiak]

Yes annotate() is unsupported. As ​​per the documentation, "only LIMIT, OFFSET, COUNT(*), ORDER BY, and specifying columns (i.e. slicing, count(), order_by(), and values()/values_list()) are allowed on the resulting QuerySet.".

Duplicate of #27995.