Opened 2 years ago

Closed 2 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 Changed 2 years ago by Shashank Parekh

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 Changed 2 years ago by Alex Mitelman

This looks similar to #30224
Possible duplicate?

comment:3 Changed 2 years ago by Adwait Thattey

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 Changed 2 years ago by Mariusz Felisiak

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