#25796: annotate() exception if there is same field name OR in Q
----------------------------------------------+----------------------
Reporter: Falldog | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.8
Severity: Normal | Keywords: annotate
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------
I had try a complete annotate, got this issue
Fail on MySQL, good on SQLite
here is example models
{{{
class Shipment(models.Model):
id = models.AutoField(primary_key=True)
name = models.CharField(max_length=32, null=True) # => field name
same as below
class SubShipment(models.Model):
id = models.AutoField(primary_key=True)
primary_shipment = models.ForeignKey(Shipment,
related_name='sub_shipment_set')
name = models.CharField(max_length=32, null=True) # => field `name`
same as ForeignKey(Shipment).`name`
class Commodity(models.Model):
id = models.AutoField(primary_key=True)
shipment = models.ForeignKey(Shipment, related_name='commodity_set')
description = models.CharField(max_length=128)
}}}
here is query example
only exception on Q() by `OR` on the same field name condition, `name` and
`sub_shipment_set__name`
I try Q() `AND` is okay
{{{
query =
models.Shipment.objects.annotate(commodity_count=Count('commodity_set'))
# OR all condition
q = Q(commodity_count=0)
q |= Q(name__isnull=False) | Q(sub_shipment_set__name__isnull=False)
# query same `name` will cause exception
query = query.filter(q)
}}}
got exception below
{{{
(1054, "Unknown column 'myapp_subshipment.name' in 'having clause'")
}}}
----
I had dump SQL query below
{{{
SELECT `myapp_shipment`.`id`,
`myapp_shipment`.`name`,
COUNT(`myapp_commodity`.`id`) AS `commodity_count`
FROM `myapp_shipment`
LEFT OUTER JOIN `myapp_commodity` ON (`myapp_shipment`.`id` =
`myapp_commodity`.`shipment_id`)
LEFT OUTER JOIN `myapp_subshipment` ON (`myapp_shipment`.`id` =
`myapp_subshipment`.`primary_shipment_id`)
GROUP BY `myapp_shipment`.`id`,
`myapp_shipment`.`name`,
`myapp_subshipment`.`name`
HAVING (COUNT(`myapp_commodity`.`id`) = 0
OR `myapp_shipment`.`name` IS NOT NULL
OR `myapp_subshipment`.`name` IS NOT NULL)
ORDER BY NULL
}}}
for MySQL query command `GROUP BY` on `myapp_subshipment`.`name`
it must appear in `SELECT` area
I try to put it into `SELECT` area and execute query again, it's okay
--
Ticket URL: <https://code.djangoproject.com/ticket/25796>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/050.558343be6ab69a2720cbaaa4a5d64d41%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
