[Django] #32663: Remove Error raising on annotation & distinct call
Django
-------------------------------------+-------------------------------------
Reporter: Yovel | Owner: nobody
Cohen |
Type: | Status: new
Cleanup/optimization |
Component: Database | Version: 3.2
layer (models, ORM) | Keywords: SQLCompiler ORM
Severity: Normal | Query
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
if you try to call distinct on an annotated query, it sometimes works,
sometimes get's ignore, and sometimes raises an error.
I had a query facing all three scenarios and solving the last one was the
one that worked out.
the error being raised is:
```
NotImplementedError: annotate() + distinct(fields) is not implemented.
```
after looking at the source code for the exception at
django.db.models.sql.compiler.SQLCompiler
row 594
```
if grouping:
if distinct_fields:
raise NotImplementedError('annotate() + distinct(fields) is not
implemented.')
order_by = order_by or self.connection.ops.force_no_ordering()
result.append('GROUP BY %s' % ', '.join(grouping))
# rest of the as_sql() method
```
after just removing the distinct_fields condition:
```
if grouping:
order_by = order_by or self.connection.ops.force_no_ordering()
result.append('GROUP BY %s' % ', '.join(grouping))
# rest of the as_sql() method
```
it works, at least in the following ways I tried (annotations are just
made up for sake of the example)
```
model_scores_latest_date_annotation = Max('model_scores__date')
latest_score_annotation = Case(When(model_scores__date=F('latest_date'),
then='model_scores__score')
base_query_set =
(Model.objects.filter(**filters).alias(latest_date=model_scores_latest_date_annotation).values(ID).annotate(latest_score=latest_score_annotation,
latest_date=model_scores_latest_date_annotation)
```
adding the following distinct calls all worked:
```
query_set = base_query_set.order_by('latest_date').distinct('id',
'latest_date')
query_set = base_query_set.distinct('id')
query_set = base_query_set.distinct('id', 'latest_date')
```
which makes me think that the as_sql method on SQLCompiler can handle more
cases easily and this just fell beneath the cracks.
--
Ticket URL: <https://code.djangoproject.com/ticket/32663>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: SQLCompiler ORM | Triage Stage:
Query | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
}}}
}}}
{{{
}}}
all the following distinct calls worked:
{{{
query_set = base_query_set.order_by('latest_date').distinct('id',
'latest_date')
query_set = base_query_set.distinct('id')
query_set = base_query_set.distinct('id', 'latest_date')
}}}
which makes me think that the as_sql method on SQLCompiler can handle more
cases easily and this just fell beneath the cracks.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32663#comment:1>
Django
-------------------------------------+-------------------------------------
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: SQLCompiler ORM | Triage Stage:
Query | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> if you try to call distinct on an annotated query, it sometimes works,
> all the following distinct calls worked:
>
> {{{
>
> query_set = base_query_set.order_by('latest_date').distinct('id',
> 'latest_date')
>
> query_set = base_query_set.distinct('id')
>
> query_set = base_query_set.distinct('id', 'latest_date')
>
> }}}
>
> which makes me think that the as_sql method on SQLCompiler can handle
> more cases easily and this just fell beneath the cracks.
New description:
}}}
}}}
{{{
)
}}}
all the following distinct calls worked:
{{{
query_set = base_query_set.order_by('latest_date').distinct('id',
'latest_date')
query_set = base_query_set.distinct('id')
query_set = base_query_set.distinct('id', 'latest_date')
}}}
which makes me think that the as_sql method on SQLCompiler can handle more
cases easily and this just fell beneath the cracks.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32663#comment:2>
Django
-------------------------------------+-------------------------------------
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: SQLCompiler ORM | Triage Stage:
Query | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> if you try to call distinct on an annotated query, it sometimes works,
> base_query_set = (Model.objects.
> filter(**filters).
> alias(latest_date=model_scores_latest_date_annotation).
> values(ID).
> annotate(latest_score=latest_score_annotation,
> latest_date=model_scores_latest_date_annotation)
> )
>
> }}}
>
> all the following distinct calls worked:
>
> {{{
>
> query_set = base_query_set.order_by('latest_date').distinct('id',
> 'latest_date')
>
> query_set = base_query_set.distinct('id')
>
> query_set = base_query_set.distinct('id', 'latest_date')
>
> }}}
>
> which makes me think that the as_sql method on SQLCompiler can handle
> more cases easily and this just fell beneath the cracks.
New description:
}}}
}}}
{{{
base_query_set = base_query_set = (Model.objects.
filter(**filters).
alias(latest_date=model_scores_latest_date_annotation).
values(ID).
annotate(latest_score=latest_score_annotation,
latest_date=model_scores_latest_date_annotation)
)
}}}
all the following distinct calls worked:
{{{
query_set = base_query_set.order_by('latest_date').distinct('id',
'latest_date')
query_set = base_query_set.distinct('id')
query_set = base_query_set.distinct('id', 'latest_date')
}}}
which makes me think that the as_sql method on SQLCompiler can handle more
cases easily and this just fell beneath the cracks.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32663#comment:3>
Django
-------------------------------------+-------------------------------------
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: SQLCompiler ORM | Triage Stage:
Query | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
There's a valid reason why this error is raised as explained in these
comment https://code.djangoproject.com/ticket/6422#comment:59; it's less
about the validity of the generated SQL and more about the correctness of
the returned results.
Given the rare cases where mixing aggregation and distinct is necessary
and the complexity involved in ''getting it right'' I'm of opinion that we
should keep disallowing it.
--
Ticket URL: <https://code.djangoproject.com/ticket/32663#comment:4>
Django
-------------------------------------+-------------------------------------
Cleanup/optimization |
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: SQLCompiler ORM | Triage Stage:
Query | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => wontfix
Comment:
I agree with Simon.
--
Ticket URL: <https://code.djangoproject.com/ticket/32663#comment:5>