Possible Bug (MYSQL)

[Paulo Gabriel Poiati]

Hello,

I would like some help from the core guys. I'm getting an error while executing the following query (the objective of this query is basically get the last created employee for each company):

Company.objects.annotate(Max('employee__pk')).filter(employee__pk=F('employee__pk__max'))

The error follows:

OperationalError: (1054, "Unknown column 'T3.id' in 'having clause'")

Not sure if my query is correct in the Django perspective but it's look OK when looking at DB level. There are some gotchas I noticed:

1 - It works in SQLite.

2 - If I change the field (eg: `employee__pk` to `employee__created`) it works fine. It seems to crash only when using PK.

4 - The generated SQL does not include the `T3.id` in the `SELECT` clause. If I add it through the `QuerySet#extras` the problem goes away.

Thanks in advance,

Paulo Poiati

blog.paulopoiati.com

[Josh Smeaton]

Hi Paulo,

Have you opened a ticket on Trac for this yet? If not, please do so. This mailing list isn't really for verifying bugs. That said, does the query work if you provide an actual alias for the aggregate?

Company.objects.annotate(max_pk=Max('employee__pk')).filter(employee__pk=F('max_pk'))

I suspect there's an ordering issue when doing field resolving that's not checking the annotated_select dict first. Maybe. Open up a ticket on Trac and we can discuss further there.