Opened 16 years ago

Closed 16 years ago

Last modified 13 years ago

#10362 closed (fixed)

Queryset.update throws ProgrammingError when updating inherited fields

Reported by: vbmendes Owned by: Malcolm Tredinnick
Component: Database layer (models, ORM) Version: dev
Severity: Keywords: queryset batch update
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

When trying to batch update an inherited field, using queryset.update() method, a ProgrammingError is thrown.

Lets supose I have two models:

class Pessoa(models.Model):
    nome = models.CharField(max_length="255")
    removido = models.BooleanField(default=False,editable=False)

class PessoaJuridica(Pessoa):
    cnpj = models.CharField(verbose_name=u'CNPJ',max_length="50",blank=True)

The error is thrown by this code:

>>> import django
>>> django.get_version()
u'1.1 alpha 1 SVN-9905'
>>> from pessoas.models import Pessoa, PessoaJuridica
>>> PessoaJuridica.objects.filter(pk__in=[61,]).update(cnpj='123')
1
>>> Pessoa.objects.filter(pk__in=[61,]).update(removido=True)
1
>>> Pessoa.objects.filter(pk__in=[61,]).update(removido=False)
1
>>> PessoaJuridica.objects.filter(pk__in=[61,]).update(removido=True)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/lib/python2.5/site-packages/django/db/models/query.py", line 450, in update
    rows = query.execute_sql(None)
  File "/usr/lib/python2.5/site-packages/django/db/models/sql/subqueries.py", line 119, in execute_sql
    cursor = super(UpdateQuery, self).execute_sql(result_type)
  File "/usr/lib/python2.5/site-packages/django/db/models/sql/query.py", line 2019, in execute_sql
    sql, params = self.as_sql()
  File "/usr/lib/python2.5/site-packages/django/db/models/sql/subqueries.py", line 131, in as_sql
    self.pre_sql_setup()
  File "/usr/lib/python2.5/site-packages/django/db/models/sql/subqueries.py", line 201, in pre_sql_setup
    for rows in query.execute_sql(MULTI):
  File "/usr/lib/python2.5/site-packages/django/db/models/sql/query.py", line 2028, in execute_sql
    cursor.execute(sql, params)
  File "/usr/lib/python2.5/site-packages/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
ProgrammingError: syntax error at or near "WHERE"
LINE 1: SELECT U1."id" FROM WHERE U0."pessoa_ptr_id" IN (61)
                            ^

>>> 

So, one can notice that the error only occurs with inherited fields. 'cnpj' is a field of PessoaJuridica and 'removido' is inherited from Pessoa. I read the documentation where it says that batch update doesn't works with related fields. I know that in this case, in database level, 'removido' is a related field. But in object level, it isn't. So I expect the batch update works in this case, but it doesn't happens.

Change History (6)

comment:1 by Jacob, 16 years ago

Triage Stage: UnreviewedAccepted

I know that in this case, in database level, 'removido' is a related field.

That's the key, right there. If the field is a related field -- which is what subclassing does -- then update() doesn't work. So the fact that it doesn't work is correct behavior, for the definition of "correct" which means "as documented."

I'm leaving this open, though, because even if we don't support this, we need to (a) make the documentation clearer about this fact and (b) make a better error message (the query shouldn't make it to the database since we already know it'll fail).

comment:2 by Malcolm Tredinnick, 16 years ago

Owner: changed from nobody to Malcolm Tredinnick

Jacob, updates on inherited models should work. It's even tested, I'm pretty sure (and was a complete pain in the butt to get working due to lots of database oddities). So I'm pretty sure there's something else going on here.

That query fragment looks hosed -- we shouldn't ever be producing something that broken.

comment:3 by Jacob, 16 years ago

D'oh, I really misread that part of the code :P

comment:4 by Malcolm Tredinnick, 16 years ago

Resolution: fixed
Status: newclosed

(In [9967]) Fixed #10362 -- An update() that only affects a parent model no longer crashes.

This includes a fairly large refactor of the update() query path (and
the initial portions of constructing the SQL for any query). The
previous code appears to have been only working more or less by accident
and was very fragile.

comment:5 by Malcolm Tredinnick, 16 years ago

(In [9968]) [1.0.X] Fixed #10362 -- An update() that only affects a parent model no longer crashes.

This includes a fairly large refactor of the update() query path (and
the initial portions of constructing the SQL for any query). The
previous code appears to have been only working more or less by accident
and was very fragile.

Backport of r9967 from trunk.

comment:6 by Jacob, 13 years ago

milestone: 1.1

Milestone 1.1 deleted

Note: See TracTickets for help on using tickets.
Back to Top