Opened 5 years ago
Closed 5 years ago
#31163 closed Bug (duplicate)
Use filters after when case, the table is not reused.
Reported by: | Ashe | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 2.2 |
Severity: | Normal | Keywords: | join |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Model
class Device(models.Model): pass class DeviceItem(models.Model): device = models.ForeignKey( Device, on_delete=models.PROTECT, related_name='item_set') class DeviceItemLocationHistory(models.Model): device_item = models.ForeignKey( DeviceItem, on_delete=models.PROTECT, related_name='devicerequestitem_set') location = models.ForeignKey(Location, on_delete=models.PROTECT) prev = models.OneToOneField( 'self', on_delete=models.PROTECT, blank=True, null=True) next = models.OneToOneField( 'self', on_delete=models.PROTECT, blank=True, null=True) changed_on = models.DateTimeField(auto_now_add=True) class DeviceMoveRequest(models.Model): device_item = models.ForeignKey( DeviceItem, on_delete=models.PROTECT, related_name='location_history_set') location_history = models.OneToOneField( 'devices.DeviceItemLocationHistory', verbose_name='위치', on_delete=models.PROTECT, blank=True, null=True, ) request_type = models.CharField(max_length=50)
Query
queryset = Device.objects.annotate( diligence_site_partition_id=Case( When( item_set__location_history_set__devicerequestitem__request_type=[ 'purchaserequest', 'storerequest', ], then=F('item_set__location_history_set__location_id'), ), When( item_set__location_history_set__devicerequestitem__request_type='releaserequest', then=F('item_set__location_history_set__prev__location_id') ), output_field=IntegerField() ) ).values( 'diligence_site_partition_id', 'item_set__device', ).filter( item_set__location_history_set__changed_on__year='2020', )
SQL
I expected
SELECT `devices_deviceitem`.`device_id`, CASE WHEN `logistics_devicemoverequest`.`request_type` IN ('purchaserequest', 'storerequest') THEN `devices_deviceitemlocationhistory`.`location_id` WHEN `logistics_devicemoverequest`.`request_type` = 'releaserequest' THEN T8.`location_id` ELSE NULL END AS `diligence_site_partition_id`, FROM `devices_device` LEFT OUTER JOIN `devices_deviceitem` ON (`devices_device`.`id` = `devices_deviceitem`.`device_id`) LEFT OUTER JOIN `devices_deviceitemlocationhistory` ON (`devices_deviceitem`.`id` = `devices_deviceitemlocationhistory`.`device_item_id`) LEFT OUTER JOIN `logistics_devicemoverequest` ON (`devices_deviceitemlocationhistory`.`id` = `logistics_devicemoverequest`.`location_history_id`) LEFT OUTER JOIN `devices_deviceitemlocationhistory` T8 ON (`devices_deviceitemlocationhistory`.`prev_id` = T8.`id`) WHERE (`devices_deviceitemlocationhistory`.`changed_on` BETWEEN '2019 - 12 - 31 15:00:00' AND '2020-12-31 14:59:59.999999') GROUP BY `devices_deviceitem`.`device_id`, CASE WHEN `logistics_devicemoverequest`.`request_type` IN ('purchaserequest', 'storerequest') THEN `devices_deviceitemlocationhistory`.`location_id` WHEN `logistics_devicemoverequest`.`request_type` = 'releaserequest' THEN T8.`location_id` ELSE NULL END ORDER BY NULL;
but not reuse table
Unexpected join will occur.
SELECT `devices_deviceitem`.`device_id`, CASE WHEN `logistics_devicemoverequest`.`request_type` IN ('purchaserequest', 'storerequest') THEN `devices_deviceitemlocationhistory`.`location_id` WHEN `logistics_devicemoverequest`.`request_type` = 'releaserequest' THEN T8.`location_id` ELSE NULL END AS `diligence_site_partition_id`, FROM `devices_device` LEFT OUTER JOIN `devices_deviceitem` ON (`devices_device`.`id` = `devices_deviceitem`.`device_id`) LEFT OUTER JOIN `devices_deviceitemlocationhistory` ON (`devices_deviceitem`.`id` = `devices_deviceitemlocationhistory`.`device_item_id`) LEFT OUTER JOIN `logistics_devicemoverequest` ON (`devices_deviceitemlocationhistory`.`id` = `logistics_devicemoverequest`.`location_history_id`) LEFT OUTER JOIN `devices_deviceitemlocationhistory` T8 ON (`devices_deviceitemlocationhistory`.`prev_id` = T8.`id`) INNER JOIN `devices_deviceitem` T11 ON (`devices_device`.`id` = T11.`device_id`) INNER JOIN `devices_deviceitemlocationhistory` T12 ON (T11.`id` = T12.`device_item_id`) WHERE (`T12`.`changed_on` BETWEEN '2019 - 12 - 31 15:00:00' AND '2020-12-31 14:59:59.999999') GROUP BY `devices_deviceitem`.`device_id`, CASE WHEN `logistics_devicemoverequest`.`request_type` IN ('purchaserequest', 'storerequest') THEN `devices_deviceitemlocationhistory`.`location_id` WHEN `logistics_devicemoverequest`.`request_type` = 'releaserequest' THEN T8.`location_id` ELSE NULL END ORDER BY NULL;
Change History (1)
comment:1 by , 5 years ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Summary: | use filters after when case, the table is not reused. → Use filters after when case, the table is not reused. |
Type: | Uncategorized → Bug |
Note:
See TracTickets
for help on using tickets.
Duplicate of #10060.