Opened 7 years ago

Closed 7 years ago

#28374 closed Bug (needsinfo)

Updating an Annotated Queryset results in ProgrammingError

Reported by: quindraco Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords: QuerySet Update Annotation Generic View Form
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Tim Graham)

I have a model called "item" in an app called "PoliceInventory". I have a View for updating this model from a form, which inherits from DetailView and UpdateView. Note that I am leaving out code I believe to be irrelevant, but can provide more if needed. I am running 1.11.3 (to make sure I have the new fix for #19513, which I believe to be the same bug). I have placed a comment in the code below where the ProgrammingError is thrown. This is in Python 3.5.2.

ProgrammingError:

ProgrammingError at /PoliceInventory/item/update/1

subquery has too many columns
LINE 1: ... "type" = '9mm' WHERE "PoliceInventory_item"."id" IN (SELECT...

The real sql query, mogrified from the cursor (Postgres 9.6, psycopg2)

UPDATE "PoliceInventory_item" 
SET "_created" = '2017-05-23T16:15:46.562060'::timestamp,
     "_created_by_id" = 1,
     "_last_updated" = '2017-07-07T09:10:23.234226'::timestamp,
     "_last_updated_by_id" = 2,
     "brand_id" = 3,
     "type" = '9mm' 
WHERE "PoliceInventory_item"."id" IN (SELECT U0."id" AS Col1,
     CONCAT(
        CONCAT(U1."name"::text,'')::text,
		CONCAT(' '::text,
               CONCAT(U0."type"::text,'')
              )
     ) AS "annotateString" 
FROM "PoliceInventory_item" U0 
INNER JOIN "PoliceInventory_brand" U1 
ON (U0."brand_id" = U1."id") 
WHERE U0."id" = 1);

Manager, model, and view code:

class customManager(models.Manager):
    def get_queryset(self):
        return self.model.annotateObjects(super().get_queryset())

    def asked_by(self, asker=None):
        return self.model.annotateObjects(self.get_queryset(), asker=asker) #this annotates the Model; I can provide how, if helpful.

class customModel(models.Model):
    objects = customManager()
    class Meta:
        base_manager_name = 'objects'

class ViewObject(django.views.generic.DetailView,django.views.generic.UpdateView):
    def form_valid(self, form):
        obj = form.save(commit=False)
        obj._last_updated_by = self.user
        obj.save() #CRASH HAPPENS HERE
        form.save_m2m()
        self.object = obj
        return super().form_valid(form)

The bug is fairly obvious - the annotation, in this case named annotateString, is being held onto for no apparent reason. The following code is how I am currently fixing it, inside my customModel class, but it has some pretty obvious major flaws:

    def _do_update(self, *args, **kwargs):
        if 'base_qs' in kwargs:
            original = kwargs['base_qs']
        else:
            original = args[0]
        if original.query.annotations:
            c = original.all()
            c.query.annotations.clear()
            c.filter(pk__in=original.values_list('pk', flat=True))
            if 'base_qs' in kwargs:
                kwargs['base_qs'] = c
            else:
                args = list(args)
                args[0] = c
                args = tuple(args)
        return super()._do_update(*args,**kwargs)

Change History (2)

comment:1 by Tim Graham, 7 years ago

Component: UncategorizedDatabase layer (models, ORM)
Description: modified (diff)
Type: UncategorizedBug

Can you try to simplify the steps to reproduce? For example, I don't think a view should be required. Also, include a minimal model. If you can provide a test for Django's test suite, that's ideal.

comment:2 by Tim Graham, 7 years ago

Resolution: needsinfo
Status: newclosed

Might be a duplicate of #26539. Feel free to reopen if it doesn't seem to be and if better steps to reproduce are added.

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