Error in sql request

[nahuel]

I'm using django 1.0.2 with mysql 5.0.51 in production environment, and 5.0.81.
I made an sql request via the orm with:
position = models.Mail.objects.filter(mbox=self.mboxdb.id).extra(select={'max':'max(position)'})[0].max

at home all work properly, but in production I have this error:
_mysql_exceptions.OperationalError: (1140, "M\xe9langer les colonnes GROUP (MIN(),MAX(),COUNT()...) avec des colonnes normales est interdit s'il n'y a pas de clause GROUP BY")

I think this shouldn't happend, however my production environment is a debian "stable", and if I use an ORM is to do not take care of sql queries.

[Russell Keith-Magee]

The error you are getting is completely correct and appropriate in this case. Your extra clause contains an aggregate (MAX), which requires additional handling in the generated SQL (a GROUP BY clause). However, there is no way for Django to determine from the extra that this extra handling is required. The extra clause is provided as a way of getting to certain difficult SQL edge cases, not as a general solution for all SQL-related queries. The preferred approach is to use the native ORM features where posisble, and only use extra when absolutely required.

While aggregate clauses are not officially supported in Django v1.0. However ,in Django trunk (soon to be v1.1) they are supported - using the new v1.1 features, the query you have posed would be expressed as:

position = Mail.objects.filter(mbox=self.mboxdb.id).aggregate(max=Max('position')['max']