subject:"\[Django\] #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"

Re: [Django] #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

2019-02-25 Thread Django

#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:  closed
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  duplicate
 Keywords:  values_list  | Triage Stage:
  annotate   |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by felixxm):

 * status:  new => closed
 * resolution:   => duplicate


Comment:

 Duplicate of #28553.

-- 
Ticket URL: 
Django 
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/062.dec8a3f87e92df007be1f2f26ea82293%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #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

2019-02-25 Thread Django

#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 layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  values_list  | Triage Stage:
  annotate   |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by 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.

-- 
Ticket URL: 
Django 
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/062.13ab1c26501e5024318ef26c689a859f%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #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

2019-02-25 Thread Django

#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 layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  values_list  | Triage Stage:
  annotate   |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Keryn Knight):

 * cc: Keryn Knight (added)


-- 
Ticket URL: 
Django 
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/062.ab9ac147c74b383ac9b287b72c42a623%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

[Django] #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

2019-02-25 Thread Django

#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: 
Django 
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.

Re: [Django] #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

Re: [Django] #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

Re: [Django] #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

[Django] #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

4 matches

Site Navigation

Mail list logo

Footer information