Code

Opened 5 years ago

Closed 5 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: UI/UX:

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.

Attachments (0)

Change History (1)

comment:1 Changed 5 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to invalid
  • Status changed from new to closed

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']

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.