Code

Opened 4 years ago

Closed 11 months ago

#13461 closed Bug (fixed)

Reference to aggregated field AND double underscore notation results in bad SQL

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

Description

I have simple model like this one:

class Plan(models.Model):
	cap = models.IntegerField()

class Phone(models.Model):
	plan = models.ForeignKey(Plan, related_name='phones')

class Call(models.Model):
	phone = models.ForeignKey(Phone, related_name='calls')
	cost = models.IntegerField()

Call to:

Phone.objects.annotate(total_cost=Sum('calls__cost')).filter(total_cost__gte=0.5*F('plan__cap'))

results in error:

ProgrammingError: column "app_plan.cap" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...."plan_id" HAVING SUM("app_call"."cost") >=  0.5 * "app_plan"....


Generated SQL is:

SELECT "app_phone"."id", "app_phone"."plan_id", SUM("app_call"."cost") AS "total_cost"
FROM "app_phone"
INNER JOIN "app_plan" ON ("app_phone"."plan_id" = "app_plan"."id")
LEFT OUTER JOIN "app_call" ON ("app_phone"."id" = "app_call"."phone_id")
GROUP BY "app_phone"."id", "app_phone"."plan_id"
HAVING SUM("app_call"."cost") >=  0.5 * "app_plan"."cap"

Is it Django bug?

Attachments (0)

Change History (8)

comment:1 Changed 4 years ago by parxier

  • Cc bas@… added
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 4 years ago by russellm

  • Triage Stage changed from Unreviewed to Accepted

comment:3 Changed 4 years ago by parxier

I don't know how to modify ticket description here, but in case someone can see ambiguity here I'd like Django to add "app_plan"."cap" column into GROUP BY clause in this particular case.

comment:5 Changed 3 years ago by julien

  • Severity set to Normal
  • Type set to Bug

comment:6 Changed 2 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:7 Changed 2 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:8 Changed 11 months ago by akaariai

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

This seems to be fixed in master, see aggregation_regress/test_aggregate_fexpr()

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.