Using QuerySet.union with a QuerySet.values_list with annotations results in broken SQL where the order of the fields from one side of the union is wrong

[Kal Sze]

Consider these three models:

from django.db import models


class Car(models.Model):
    manufacturer = models.TextField()
    model = models.TextField()
    year = models.PositiveSmallIntegerField()
    top_speed = models.PositiveSmallIntegerField()


class Train(models.Model):
    STEAM = 'steam'
    MAGLEV = 'maglev'
    HSR = 'high-speed rail'
    manufacturer = models.TextField()
    model = models.TextField()
    category = models.TextField(choices=((STEAM, STEAM), (MAGLEV, MAGLEV), (HSR, HSR)))
    top_speed = models.PositiveSmallIntegerField()


class TrainVariant(models.Model):
    train = models.ForeignKey(Train, related_name='variants', on_delete=models.PROTECT)
    year = models.PositiveSmallIntegerField()
    model = models.TextField()
    top_speed = models.PositiveSmallIntegerField()

If I create a union on annotated QuerySets of these two models, the generated SQL has some of the columns in the wrong order, and then PostgreSQL won't be able to match the data types (resulting in django.db.utils.ProgrammingError: UNION types smallint and text cannot be matched)

To illustrate:

from vehicles.modes import Car, Train, TrainVariant


# Create some instances
car = Car.objects.create(manufacturer='Ford', model='Mustang', year=2009, top_speed=220)
t = Train.objects.create(manufacturer='AA', model='N6', category=Train.HSR, top_speed=300)
tv = TrainVariant.objects.create(train=t, year=2018, model='N6A', top_speed=310)

# Create the query sets
from django.db.models import Value, TextField, Subquery, OuterRef

cars = Car.objects.annotate(
    category=Value('not a train', output_field=TextField()),
).values_list('id', 'year', 'category', 'top_speed')

trains = Train.objects.annotate(
    year=Subquery(TrainVariant.objects.filter(train=OuterRef('id')).order_by('-year')[:1].values('year')),
).values_list('id', 'year', 'category', 'top_speed')

# Take the union, this is supposed to work as the `values_lists` should have fields with matching data types in matching order
union = cars.union(trains)

# However, iterating over the query set results in `ProgrammingError: UNION types smallint and text cannot be matched`
for _id, year, category, top_speed in union:
    print(year)

# Let's take a look at the generated query
print(union.query)

# (SELECT
#     "vehicles_car"."id",
#     "vehicles_car"."year",
#     "vehicles_car"."top_speed",
#     not a train AS "category"
# FROM
#     "vehicles_car")
# UNION
# (SELECT
#     "vehicles_train"."id",
#     "vehicles_train"."category",
#     "vehicles_train"."top_speed",
#     (SELECT U0."year"
#      FROM "vehicles_trainvariant" U0
#      WHERE U0."train_id" = ("vehicles_train"."id")
#      ORDER BY U0."year" DESC  LIMIT 1) AS "year"
# FROM
#     "vehicles_train")

# Oops

[Kal Sze]

Just to add a bit of info: according to kezabelle in the #django channel on Freenode IRC, the bug exists at least as far back as Django 1.9.

[Mariusz Felisiak]

Duplicate of #28553.