annotate -> union -> values gives wrong values

[Tom Carrick]

With the following code:

import uuid

from django.conf import settings
from django.contrib.auth import get_user_model
from django.db import models
from django.db.models.functions import Cast


class DocumentQuerySet(models.QuerySet):
    def for_user(self, user):
        return self.filter(things__user=user).union(
            self.filter(other_things__user=user)
        )


class Document(models.Model):
    name = models.CharField()
    id = models.UUIDField(default=uuid.uuid4, primary_key=True)

    objects = DocumentQuerySet().as_manager()


class Thing(models.Model):
    user = models.ForeignKey(
        settings.AUTH_USER_MODEL, related_name="things", on_delete=models.CASCADE
    )
    document = models.ForeignKey(
        Document, related_name="things", on_delete=models.CASCADE
    )


class OtherThing(models.Model):
    user = models.ForeignKey(
        settings.AUTH_USER_MODEL, related_name="other_things", on_delete=models.CASCADE
    )
    document = models.ForeignKey(
        Document, related_name="other_things", on_delete=models.CASCADE
    )


def broken():
    user = get_user_model().objects.first()
    return Document.objects.annotate(
        pk_str=Cast("pk", output_field=models.CharField())
    ).for_user(user).values_list("pk_str", flat=True)

Running broken(), I would expect to say the stringified UUIDs from the Document model. Instead I get the names. It also happens when using values(). If not using either, it works just fine, and you can access document.pk_str and it works just fine. After playing with this, it seems to be because it's defined first in the model. If I move id to the top, it actually returns the UUIDs, without the cast being applied. The query (as far as I can tell from Django) looks fine:

(SELECT "testapp_document"."id" AS "col1", "testapp_document"."name" AS "col2", ("testapp_document"."id")::varchar AS "pk_str" FROM "testapp_document" INNER JOIN "testapp_thing" ON ("testapp_document"."id" = "testapp_thing"."document_id") WHERE "testapp_thing"."user_id" = 1) UNION (SELECT "testapp_document"."id" AS "col1", "testapp_document"."name" AS "col2", ("testapp_document"."id")::varchar AS "pk_str" FROM "testapp_document" INNER JOIN "testapp_otherthing" ON ("testapp_document"."id" = "testapp_otherthing"."document_id") WHERE "testapp_otherthing"."user_id" = 1)

And logged from Postgres also looks correct:

(SELECT "testapp_document"."name" AS "col1", "testapp_document"."id" AS "col2", ("testapp_document"."id")::varchar AS "pk_str" FROM "testapp_document" INNER JOIN "testapp_thing" ON ("testapp_document"."id" = "testapp_thing"."document_id") WHERE "testapp_thing"."user_id" = 1) UNION (SELECT "testapp_document"."name" AS "col1", "testapp_document"."id" AS "col2", ("testapp_document"."id")::varchar AS "pk_str" FROM "testapp_document" INNER JOIN "testapp_otherthing" ON ("testapp_document"."id" = "testapp_otherthing"."document_id") WHERE "testapp_otherthing"."user_id" = 1) LIMIT 21

[David Sanders]

Duplicate of #28900? 🤔

I've managed to boil this down the following example:

class Foo(Model):
    name = CharField()

class Bar(Model):
    name = CharField()

qs = (
    Foo.objects.annotate(alias=F("name"))
    .union(Bar.objects.annotate(alias=F("name")))
    .values("alias")
)
print(qs)

gives

<QuerySet [{'alias': 1}, {'alias': 1}]>

[Tom Carrick]

Oops! Actually I realised I was on 4.2, this seems to be working for me on dev, so it's more likely a duplicate of #28553.

But your version doesn't work on dev? Hard to say but they seem similar, the fix could be the same.

[David Sanders]

Duplicate of #28900

Ha no worries Tom 👍 I suspect it's the same. Let's close as a duplicate for now… keep an eye on solutions for #28900 to see if the patches work for you as well.