#30628: Order by with union can result in invalid ORDER BY clause
-------------------------------------+-------------------------------------
Reporter: Julien | Owner: nobody
Enselme |
Type: Bug | Status: new
Component: Database | Version: 2.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
When doing a union of 2 querysets and then doing an `order_by` on the
resulting queryset, if we order on a field whose type is present multiple
time, the ordering will be incorrect if the field we sort on is not the
1st field of the query. Explicitly settings values with `.values('field1',
'field2')` on the base querysets or after the union has no effect.
For instance, with this model, with 2 `DecimalField`s and one
`BooleanField`:
{{{
class Listing(models.Model):
sale_price = models.DecimalField('Sale price', max_digits=10,
decimal_places=2)
yearly_rent = models.DecimalField('Yearly rent', max_digits=10,
decimal_places=2)
toto = models.BooleanField()
}}}
{{{
# Create 2 qs.
qs1 = Listing.objects.all()
qs2 = Listing.objects.all()
# Create the union QS.
qs3 = qs1.union(qs2)
# Order on the 1st decimal field. This prints (which is correct) :
# SELECT "union_listing"."id", "union_listing"."sale_price",
"union_listing"."yearly_rent" FROM "union_listing" UNION SELECT
"union_listing"."id", "union_listing"."sale_price",
"union_listing"."yearly_rent" FROM "union_listing" ORDER BY (2) ASC
print(qs3.order_by('sale_price').query)
# Order on the 2nd deciamal field. This will print the same query as above
which is incorrect.
print(qs3.order_by('yearly_rent').query)
# Not ordering on a DecimalField. This is correct again.
print(qs3.order_by('toto').query)
}}}
From the debugging I did, it seems to come from
[https://github.com/django/django/commit/bc7e288ca9554ac1a0a19941302dea19df1acd21
this commit]: If I revert `def __eq__` back to what it was in Django 2.1
(https://github.com/django/django/blob/stable/2.1.x/django/db/models/expressions.py#L363)
it works as normal again. The difference between the two methods that can
explain this is that in Django 2.1, we have a check on the actual field
instances thanks to `other_args[1]`, but in 2.2, because of
`identity[2][1]` which is the class of the field, we can't distinguish two
fields of the same type (please refer to the respective implementations to
know the values of `other_args` and `identity`).
[https://github.com/Jenselme/dj-test-unions Sample projet to reproduce]
(sqlite db included) ([https://github.com/Jenselme/dj-test-
unions/blob/master/union/models.py Model], [https://github.com/Jenselme
/dj-test-unions/blob/master/test-script.py test file]). Steps:
1. Install Django 2.2
2. Run `DJANGO_SETTINGS_MODULE=testunion.settings python test-script.py`
3. You will see the queries for `qs3.order_by('sale_price')` and
`qs3.order_by('yearly_rent')` They are exactly the same whereas they
should be different (one with `ORDER BY (1)` and the other with `ORDER BY
(2)`).
--
Ticket URL: <https://code.djangoproject.com/ticket/30628>
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/051.046ced5ea3fd84654eaf13f98d5dfb25%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
