Opened 3 years ago

Last modified 3 weeks ago

#19513 new Bug

update() after annotate(val=Sum(...)) crashes on PostgreSQL & Oracle

Reported by: mengzhuo1203@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

An QuerySet operation like:
Combination.objects.all().update(tag='TagName')

The structure like:

class Combination:
    items = ForeignKey(Item)
CombinationManager:
    def get_query_set(self):
        qs = super(CombinationManager, self).get_query_set()
        qs = qs.annotate(price=Sum('items__price'))

The SQLite and MySQL runs fine with such structure, not with Oracle, it will encounter "ORA-00913: too many values"

Change History (4)

comment:1 Changed 3 years ago by mengzhuo

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

I think that I know why MySQL and SQlite runs fine, cause both of them will query first then update the queried sets, however Oracle backend will not, it simply puts everything in the same query.

Here is the buggy Oracle Query set

UPDATE "ITEM_COMBINATION" SET "ACTIVE" = :arg0 WHERE "ITEM_COMBINATION"."ID" IN (
    SELECT U0."ID", SUM(U8."PRICE") AS "PRICE" 
           FROM "ITEM_COMBINATION" U0 
           LEFT OUTER JOIN "ITEM_COMBINATION_ITEMS" U7 ON (U0."ID" = U7."COMBINATION_ID") 
           LEFT OUTER JOIN "ITEM_ITEM" U8 ON (U7."ITEM_ID" = U8."ID") WHERE U0."DURATION_ID" = :arg1  
           GROUP BY U0."ID", U0."ITEM_ID", U0."NAME", U0."SOMEDUMMYITEM", U0."SOMEDUMMYITEM", U0."SOMEDUMMYITEM", U0."SOMEDUMMYITEM", U0."SOMEDUMMYITEM", U0."SOMEDUMMYITEM", U0."ACTIVE", U0."SOMEDUMMYITEM", U0."SOMEDUMMYITEM", U0."CATEGORY_ID"); args=(False, 2)

Here is MySQL Query Set

SELECT U0.`id`, SUM(U8.`price`) AS `price`, MIN(U8.`store`) AS `store` FROM `item_combination` U0 LEFT OUTER JOIN `item_combination_items` U7 ON (U0.`id` = U7.`combination_id`) LEFT OUTER JOIN `item_item` U8 ON (U7.`item_id` = U8.`id`) GROUP BY U0.`id`, U0.`item_id`, U0.`SOMEDUMMYITEM`, U0.`SOMEDUMMYITEM`, U0.`SOMEDUMMYITEM`, U0.`SOMEDUMMYITEM`, U0.`SOMEDUMMYITEM`, U0.`SOMEDUMMYITEM`, U0.`SOMEDUMMYITEM`, U0.`active`, U0.`SOMEDUMMYITEM`, U0.`SOMEDUMMYITEM`, U0.`SOMEDUMMYITEM` ORDER BY NULL; args=()

UPDATE `item_combination` SET `active` = 1 WHERE `item_combination`.`id` IN (1, 3); args=(True, 1, 3)

comment:2 Changed 3 years ago by akaariai

  • Triage Stage changed from Unreviewed to Accepted

I had to use way too much time reproducing this. Your models do not actually work, the SQL in the comment doesn't match the queries in the description. The queries in the description doesn't match the models.

Here are the models & code to reproduce this (on master):

class Item(models.Model):
    price = models.IntegerField()
  
class CombinationManager(models.Manager):
    def get_query_set(self):
        qs = super(CombinationManager, self).get_query_set()
        qs = qs.annotate(price=models.Sum('items__price'))
        return qs
  
class Combination(models.Model):
    tag = models.CharField(max_length=32)
    items = models.ForeignKey(Item)
    objects = CombinationManager()

Combination.objects.all().update(tag='TagName')

The problem is the SUM() select in the subquery. Happens on PostgreSQL too.

comment:3 Changed 3 weeks ago by timgraham

  • Summary changed from Annotate broken django.db.backends.oracle while updating to update() after annotate(val=Sum(...)) crashes on PostgreSQL & Oracle

comment:4 Changed 3 weeks ago by jarshwah

See also #25171

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