Unexpected result using union querysets with annotated columns.

[Stefan Wehrmeyer]

I have two models:

class ModelA(models.Model):
    column_a = models.CharField(max_length=255, blank=True)
    column_b = models.CharField(max_length=255, blank=True)


class ModelB(models.Model):
    column_b = models.CharField(max_length=255, blank=True)

I want to query both at once using union, filling in a default value for column_a in ModelB:

from django.db.models import Value, CharField

ModelA.objects.create(
    column_a='a',
    column_b='b',
)
ModelB.objects.create(
    column_b='b',
)

columns = ('column_a', 'column_b')

a_qs = ModelA.objects.all().values(*columns)
b_qs = (
    ModelB.objects.all()
    .annotate(
        column_a=Value("a", output_field=CharField())
    )
    .values(*columns)
)
qs = a_qs.union(b_qs)
qs

This leads to the following unexpected output:

<QuerySet [{'column_a': 'a', 'column_b': 'b'}, {'column_a': 'b', 'column_b': 'a'}]>

Note that the second row in the result (coming from b_qs) has the values for the columns mixed up!

The SQL query is:

print(qs.query)
SELECT "example_modela"."column_a", "example_modela"."column_b" FROM "example_modela" UNION SELECT "example_modelb"."column_b", a AS "column_a" FROM "example_modelb"

I'm not sure if I'm using the ORM in a wrong way. I would consider my use case (=union+annotate) valid and would expect some kind of error if the ORM was not able to deal with it.

[Mariusz Felisiak]

Thanks for the report, this is a duplicate of #28553. Shortly, annotated columns are forced to be in a certain position, I'm not sure if we will be able to fix this. A simple workaround in your scenario is to keep annotated columns at the end i.e. columns = ('column_b', 'column_a')