#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
-------------------------------------+-------------------------------------
Reporter: Kal Sze | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 2.1
layer (models, ORM) | Keywords: values_list
Severity: Normal | annotate
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30211>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/047.84e995c7b6b1ada2105d1c603b274fa2%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
