# Annotations outcome in generated SQL - duplicates/repetition

**URL:** <https://forum.djangoproject.com/t/annotations-outcome-in-generated-sql-duplicates-repetition/12912>\
**Category:** ORM\
**Created:** [March 30, 2022, 1:22pm UTC](https://forum.djangoproject.com/t/annotations-outcome-in-generated-sql-duplicates-repetition/12912 "2022-03-30T13:22:23Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![plopidou](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/plopidou/32/5148_2.png) [@plopidou](https://forum.djangoproject.com/u/plopidou)\
**Post date:** [March 30, 2022, 1:22pm UTC](https://forum.djangoproject.com/t/annotations-outcome-in-generated-sql-duplicates-repetition/12912/1 "2022-03-30T13:22:23Z")

</div>

Hi,

I want to try and understand why the generated SQL for annotations always (?) results in the subqueries being duplicated when re-used in other annotations, instead of using the relevant `AS` variables. For example:

```auto
class Person(models.Model):
    created_datetime = models.DateTimeField('Created at', null=False, blank=False, default=timezone.now)
    name = models.CharField('Name', max_length=128, null=True, blank=True)
    parent = models.ForeignKey('self', null=True, blank=True, on_delete=models.CASCADE)
    
    def __str__ (self):
        return self.name

class PersonLog(models.Model):
    person = models.ForeignKey('person.Person', null=True, blank=True, on_delete=models.CASCADE)
    created_datetime = models.DateTimeField('Created at', null=False, blank=False, default=timezone.now)
    login = models.BooleanField('Login', null=True, blank=True)
    
    def __str__ (self):
        return self.person.name

```

In an example used elsewhere, I build a queryset like so:

```auto
persons = Person.objects.all().annotate(
    log_login_count=Count(
        'personlog',
        filter=Q(personlog__login=True)
    ),
    log_login_duration_over=Now() - F('created_datetime'),
    log_login_frequency=ExpressionWrapper(
        F('log_login_duration_over') / F('log_login_count'),
        output_field=models.DurationField(),
    ),
).values()

```

Testing this on sqlite (but the same happens when running the same query on MySQL – no idea about PG), the generated SQL is:

```auto
SELECT
  "person_person"."id",
  "person_person"."created_datetime",
  "person_person"."name",
  "person_person"."parent_id",
  COUNT("person_personlog"."id") FILTER (
    WHERE
      "person_personlog"."login"
  ) AS "log_login_count",
  django_timestamp_diff(
    CURRENT_TIMESTAMP,
    "person_person"."created_datetime"
  ) AS "log_login_duration_over",
  (
    django_format_dtdelta(
      '/',
      django_timestamp_diff(
        CURRENT_TIMESTAMP,
        "person_person"."created_datetime"
      ),
      COUNT("person_personlog"."id") FILTER (
        WHERE
          "person_personlog"."login"
      )
    )
  ) AS "log_login_frequency"
FROM
  "person_person"
  LEFT OUTER JOIN "person_personlog" ON (
    "person_person"."id" = "person_personlog"."person_id"
  )
GROUP BY
  "person_person"."id",
  "person_person"."created_datetime",
  "person_person"."name",
  "person_person"."parent_id",
  django_timestamp_diff(
    CURRENT_TIMESTAMP,
    "person_person"."created_datetime"
  )

```

Notice this:

```auto
  (
    django_format_dtdelta(
      '/',
      django_timestamp_diff(
        CURRENT_TIMESTAMP,
        "person_person"."created_datetime"
      ),
      COUNT("person_personlog"."id") FILTER (
        WHERE
          "person_personlog"."login"
      )
    )
  ) AS "log_login_frequency"

```

Given the two operands of the division are computed/declared just above, why is the complete query not generated as:

```auto
SELECT
  "person_person"."id",
  "person_person"."created_datetime",
  "person_person"."name",
  "person_person"."parent_id",
  COUNT("person_personlog"."id") FILTER (
    WHERE
      "person_personlog"."login"
  ) AS "log_login_count",
  django_timestamp_diff(
    CURRENT_TIMESTAMP,
    "person_person"."created_datetime"
  ) AS "log_login_duration_over",
  (
    django_format_dtdelta(
      '/',
      log_login_duration_over,
      log_login_count
    )
  ) AS "log_login_frequency"
FROM
  "person_person"
  LEFT OUTER JOIN "person_personlog" ON (
    "person_person"."id" = "person_personlog"."person_id"
  )
GROUP BY
  "person_person"."id",
  "person_person"."created_datetime",
  "person_person"."name",
  "person_person"."parent_id",
  django_timestamp_diff(
    CURRENT_TIMESTAMP,
    "person_person"."created_datetime"
  )

```

where both `log_login_duration_over` and `log_login_count` are actually used directly after being “declared”.

The advantages seem obvious:

- less bytes sent over the wire
- more performance? Would the redeclaration/recomputation be heavily cached at database level anyway?

I am sure there is a good reason for this, but would like to understand it!

Many thanks!

---

<div class="post-metadata">

**Author:** ![felixxm](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/felixxm/32/2479_2.png) [@felixxm](https://forum.djangoproject.com/u/felixxm)\
**Post date:** [March 31, 2022, 4:12am UTC](https://forum.djangoproject.com/t/annotations-outcome-in-generated-sql-duplicates-repetition/12912/2 "2022-03-31T04:12:48Z")

</div>

Using `AS` in SQL queries doesn’t create virtual columns or sth like variables. Declared aliases are not accessible in `SELECT` or `WHERE` clauses and cannot be reused. The proposed query will crash with:

```auto
Error: no such column: log_login_count
Error: no such column: log_login_duration_over

```

---

<div class="post-metadata">

**Author:** ![plopidou](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/plopidou/32/5148_2.png) [@plopidou](https://forum.djangoproject.com/u/plopidou)\
**Post date:** [March 31, 2022, 8:30am UTC](https://forum.djangoproject.com/t/annotations-outcome-in-generated-sql-duplicates-repetition/12912/3 "2022-03-31T08:30:31Z")

</div>

Yes that is a good reason, thanks 🙂

Just tried this on MySQL 8.xx though, and it works (it’s a quick test mind you, so probably a can of worms with baddies I am not aware of…)

```auto
SELECT
  DISTINCT 
  `person`.`id`,
  `person`.`name`,
  @login_count := COUNT(
    CASE WHEN `person_personlog`.`login` THEN `person_personlog`.`id` ELSE NULL END
  ) AS `login_count`,
  @login_duration_over := TIMESTAMPDIFF(
    MICROSECOND,
    `person_person`.`created_datetime`,
    CURRENT_TIMESTAMP
  ) AS `login_duration_over`,
  @login_frequency := (@login_duration_over / @login_count) AS `login_frequency`,

<snip>

```

Where `login_duration_over `, `login_count` are _declared_ once and reused to compute `login_frequency`.

Thoughts?

---

<div class="post-metadata">

**Author:** ![felixxm](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/felixxm/32/2479_2.png) [@felixxm](https://forum.djangoproject.com/u/felixxm)\
**Post date:** [March 31, 2022, 9:08am UTC](https://forum.djangoproject.com/t/annotations-outcome-in-generated-sql-duplicates-repetition/12912/4 "2022-03-31T09:08:43Z")

</div>

This won’t work on all database and as far as I’m aware a similar syntax doesn’t exist on Oracle or SQLite. Moreover database optimizer should reuse it anyway.

---

<div class="post-metadata">

**Author:** ![plopidou](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/plopidou/32/5148_2.png) [@plopidou](https://forum.djangoproject.com/u/plopidou)\
**Post date:** [March 31, 2022, 10:04am UTC](https://forum.djangoproject.com/t/annotations-outcome-in-generated-sql-duplicates-repetition/12912/5 "2022-03-31T10:04:47Z")

</div>

yep ok.  
I suspected this would be optimised/cached at database level anyway, but glad to get a confirmation.

thanks 🙂