Opened 7 years ago

Closed 7 years ago

Last modified 4 years ago

#10362 closed (fixed)

Queryset.update throws ProgrammingError when updating inherited fields

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

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 Changed 7 years ago by jacob

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

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 Changed 7 years ago by mtredinnick

  • Owner changed from nobody to mtredinnick

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 Changed 7 years ago by jacob

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

comment:4 Changed 7 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

(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 Changed 7 years ago by mtredinnick

(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 Changed 4 years ago by jacob

  • milestone 1.1 deleted

Milestone 1.1 deleted

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