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

[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.

[Alex Mitelman]

This looks similar to #30224
Possible duplicate?

[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

[Mariusz Felisiak]

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>)