Having run in to this issue in the past, automatically changing the meaning and/or breaking the query can turn in to a debug time sink. Option 3 could save people a bit of time.
Cheers, Michael Manfre On Wednesday, October 28, 2020 at 8:46:46 PM UTC-4 charettes wrote: > I'm also a fan of option 3. to require an explicit opt-in or raise an > error. > > Not a lot of folks are familiar with this requirement imposed by the usage > of DISTINCT and even if Model.Meta.ordering is the most common reason but > unexpected ordering it can also be caused by the dynamic creation of a > queryset through multiple abstractions (e.g. A DRF API filter backend that > applies ordering). > > I think this is a similar problem to > https://code.djangoproject.com/ticket/14357#comment:11 > > Cheers, > Simon > > Le mercredi 28 octobre 2020 à 18:09:10 UTC-4, Tobias McNulty a écrit : > >> I tend to agree, though I'll note that was also a bug at one point: >> https://code.djangoproject.com/ticket/5321 >> >> On Wed, Oct 28, 2020 at 5:38 PM Fran Hrženjak <fran.h...@gmail.com> >> wrote: >> >>> Personally I would prefer to get a ProgrammingError, so option 3. >>> >>> Explicit is better then implicit 😁 >>> >>> Here is a great explanation of this exact issue, cleared it up for me: >>> >>> https://blog.jooq.org/2018/07/13/how-sql-distinct-and-order-by-are-related/ >>> >>> >>> >>> On 28.10.2020., at 20:04, Tobias McNulty <tob...@caktusgroup.com> wrote: >>> >>> >>> Hi all, >>> >>> Starting a thread on ticket #28560 >>> <https://code.djangoproject.com/ticket/28560>, "distinct() on ordered >>> queryset with restricted list of columns returns incorrect result." In a >>> nutshell: >>> >>> $ cat testapp/models.py >>> >>> from django.db import models >>> >>> >>> >>> class School(models.Model): >>> >>> name = models.CharField(max_length=255) >>> >>> county = models.CharField(max_length=255) >>> >>> >>> class Meta: >>> >>> ordering = ("name",) >>> >>> $ python manage.py shell >>> >>> ... >>> >>> >>> from testapp.models import School >>> >>> >>> str(School.objects.values("county").distinct().query) >>> >>> 'SELECT DISTINCT "testapp_school"."county", *"testapp_school"."name"* >>> FROM "testapp_school" ORDER BY "testapp_school"."name" ASC' >>> >>> Note the "name" column is added implicitly to the SELECT clause due to >>> the default ordering on the model (I believe with good reason, since >>> #7070 <https://code.djangoproject.com/ticket/7070>). This is not >>> specific to a default ordering; it's also possible to generate >>> unintended results with a mismatching list of columns between an explicit >>> order_by() and values(). >>> >>> It's possible to fix with an empty order_by(): >>> >>> >>> str(School.objects.values("county").order_by().distinct().query) >>> >>> 'SELECT DISTINCT "testapp_school"."county" FROM "testapp_school"' >>> >>> >>> But, this still feels like a case where we could do better. Some >>> potential options: >>> >>> 1. It looks like there was an initial attempt to fix the issue with >>> a subquery, but from what I can tell it was not possible to preserve >>> ordering >>> <https://github.com/django/django/pull/9055#issuecomment-338276279> >>> in the outer query. >>> 2. My colleague Dmitriy pointed out that there may be a precedent >>> for excluding the default ordering >>> <https://github.com/django/django/pull/10005> for queries like this. >>> 3. An option I suggested on the ticket >>> <https://code.djangoproject.com/ticket/28560#comment:13> is to raise >>> an error if the list of columns in values() is insufficient to run the >>> requested query (i.e., never add a column implicitly if the user >>> specified >>> a list of columns via values() or values_list()). >>> >>> What do others think? What are other potential fixes I'm not thinking of? >>> >>> Cheers, >>> >>> >>> *Tobias McNulty*Chief Executive Officer >>> >>> tob...@caktusgroup.com >>> www.caktusgroup.com >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "Django developers (Contributions to Django itself)" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to django-develop...@googlegroups.com. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/django-developers/CAMGFDKRQsbUz%3DeYKxX2U%2B892AgQbXr%3DMA9AiWw9vfiPcM%2BnAXw%40mail.gmail.com >>> >>> <https://groups.google.com/d/msgid/django-developers/CAMGFDKRQsbUz%3DeYKxX2U%2B892AgQbXr%3DMA9AiWw9vfiPcM%2BnAXw%40mail.gmail.com?utm_medium=email&utm_source=footer> >>> . >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "Django developers (Contributions to Django itself)" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to django-develop...@googlegroups.com. >>> >> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/django-developers/942B7E2C-7B44-4ECF-9105-6EAB90874E98%40gmail.com >>> >>> <https://groups.google.com/d/msgid/django-developers/942B7E2C-7B44-4ECF-9105-6EAB90874E98%40gmail.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/691662a4-9ad4-44a7-b2d2-1ecd0d95c30fn%40googlegroups.com.
