Opened 15 years ago

Closed 15 years ago

#11349 closed (invalid)

Error in sql request

Reported by: nahuel Owned by: nobody
Component: Uncategorized Version: 1.0
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (1)

comment:1 by Russell Keith-Magee, 15 years ago

Resolution: invalid
Status: newclosed

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']
Note: See TracTickets for help on using tickets.
Back to Top