#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 django-updates+unsubscr...@googlegroups.com. To post to this group, send email to django-updates@googlegroups.com. 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.