id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
30211	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	nobody	"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
}}}"	Bug	closed	Database layer (models, ORM)	2.1	Normal	duplicate	values_list annotate	Keryn Knight	Unreviewed	0	0	0	0	0	0