Opened 5 years ago

Closed 5 years ago

#30480 closed Bug (worksforme)

Discrepancy in `DateTime` field value If the django orm union() is used with the empty array in the filter.

Reported by: Shashank Parekh Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
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 Shashank Parekh)

class detail(models.Model):
      date_field = models.DateTimeField(null=True, index=True)
      guid = models.CharField(null=True, index=True)
      mobile = models.CharField(null=True, index=True)
      id = models.CharField(null=True, index=True)
p=detail.objects.filter(guid__in=[]).order_by('-date_field')
q=detail.objects.filter(mobile__in=['8970405058']).order_by('-date_field')
v = (p.union(q)).order_by('-date_field')
v[0].date_field   # Output is  '2018-10-08 12:51:39'.

For this case, instead of getting datetime value, getting the string value.


p=detail.objects.filter(guid__in=['some_garbage_value']).order_by('-date_field')
q=detail.objects.filter(mobile__in=['8970405058']).order_by('-date_field')
v = (p.union(q)).order_by('-date_field')
v[0].date_field .  # Output is datetime.datetime(2018, 10, 8, 12, 51, 39)

If I don't pass the empty array in filtering then getting the DateTime value.


p=detail.objects.filter(guid__in=['some_garbage_value']).order_by('-date_field')
q=detail.objects.filter(mobile__in=['8970405058']).order_by('-date_field')
v = (p.union(q))
v[0].date_field .  # Output is datetime.datetime(2018, 10, 8, 12, 51, 39)

If I pass the empty array but withour order_by clause then getting the DateTime value.


There is a discrepancy in DateTime field value If the union is used with the empty array in the filter.

Change History (4)

comment:1 by Shashank Parekh, 5 years ago

Description: modified (diff)
Summary: Discrepancy in `DateTime` field value If the django orm union() is used with the EmptyQuerySet.Discrepancy in `DateTime` field value If the django orm union() is used with the empty array in the filter.

comment:2 by Alex Mitelman, 5 years ago

This looks similar to #30224
Possible duplicate?

comment:3 by Adwait Thattey, 5 years ago

In django 2.1.5,
>>> v = (p.union(q))
fails with error:

django.db.utils.DatabaseError: ORDER BY not allowed in subqueries of compound statements.

https://github.com/django/django/blob/567b9928a3ad37e95b9ae17ec41342daa6968739/django/db/models/sql/compiler.py#L413

comment:4 by Mariusz Felisiak, 5 years ago

Resolution: worksforme
Severity: Release blockerNormal
Status: newclosed
Version: 2.1master

Thanks for the report, however it contains a lot of gaps, e.g. detail model is not correct, ordering is not allowed in subqueries of compound statements (IMO it's not important) etc. Moreover it works for me in all described cases and with Django 2.1, 2.2 and on master. I tried to reproduce this issue with the following model

class Detail(models.Model):
      id = models.CharField(max_length=127, primary_key=True)
      date_field = models.DateTimeField(null=True)
      guid = models.CharField(null=True, max_length=127)
      mobile = models.CharField(null=True, max_length=127)

>>> p = Detail.objects.filter(guid__in=[])
>>> q = Detail.objects.filter(mobile__in=['8970405058'])
>>> v = (p.union(q)).order_by('-date_field')
>>> v[0].date_field
datetime.datetime(2019, 5, 16, 19, 19, 7, 426452, tzinfo=<UTC>)
Note: See TracTickets for help on using tickets.
Back to Top